IT俱乐部 MySql mysql表类型查询示例详解

mysql表类型查询示例详解

发布: 2025年 7月 14日 18阅读

目录

普通表

SELECT
    table_schema AS database_name,
    table_name
FROM
    information_schema.tables
WHERE
    table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    AND table_type = 'BASE TABLE'
    AND table_name NOT IN (
        SELECT DISTINCT table_name 
        FROM information_schema.partitions 
        WHERE partition_name IS NOT NULL
    )
ORDER BY
    table_schema, table_name;

分区表

SELECT
    p.table_schema AS database_name,
    p.table_name,
    GROUP_CONCAT(p.partition_name ORDER BY p.partition_ordinal_position) AS partitions,
    p.partition_method,
    p.partition_expression
FROM
    information_schema.partitions p
WHERE
    p.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    AND p.partition_name IS NOT NULL
GROUP BY
    p.table_schema, p.table_name, p.partition_method, p.partition_expression
ORDER BY
    p.table_schema, p.table_name;

区分表

SELECT
    t.table_schema AS database_name,
    t.table_name,
    CASE
        WHEN p.table_name IS NULL THEN '普通表'
        ELSE '分区表'
    END AS table_type,
    p.partition_method,
    p.partition_expression
FROM
    information_schema.tables t
LEFT JOIN (
    SELECT DISTINCT
        table_schema, 
        table_name,
        partition_method,
        partition_expression
    FROM
        information_schema.partitions 
    WHERE
        partition_name IS NOT NULL
) p ON t.table_schema = p.table_schema AND t.table_name = p.table_name
WHERE
    t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    AND t.table_type = 'BASE TABLE'
ORDER BY
    t.table_schema, t.table_name;

查出数据量

SELECT
    t.table_schema AS '数据库名',
    t.table_name AS '表名',
    CASE
        WHEN p.table_name IS NULL THEN '普通表'
        ELSE CONCAT('分区表(', p.partition_method, ')')
    END AS '表类型',
    t.table_rows AS '数据行数(估算)',
    CONCAT(ROUND(t.data_length / (1024 * 1024), 2), ' MB') AS '数据大小',
    CONCAT(ROUND(t.index_length / (1024 * 1024), 2), ' MB') AS '索引大小',
    CONCAT(ROUND((t.data_length + t.index_length) / (1024 * 1024), 2), ' MB') AS '总大小',
    p.partition_expression AS '分区键'
FROM
    information_schema.tables t
LEFT JOIN (
    SELECT DISTINCT
        table_schema, 
        table_name,
        partition_method,
        partition_expression
    FROM
        information_schema.partitions 
    WHERE
        partition_name IS NOT NULL
) p ON t.table_schema = p.table_schema AND t.table_name = p.table_name
WHERE
    t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    AND t.table_type = 'BASE TABLE'
ORDER BY
    t.table_schema, 
    CASE WHEN p.table_name IS NULL THEN 0 ELSE 1 END,  -- 普通表在前
    t.table_name;
SELECT
    t.table_schema AS '数据库',
    t.table_name AS '表名',
    CASE
        WHEN p.partition_method IS NULL THEN '普通表'
        ELSE CONCAT('分区表(', p.partition_method, ')')
    END AS '表类型',
    t.table_rows AS '估算行数',
    CONCAT(ROUND(t.data_length/1024/1024, 2), ' MB') AS '数据大小',
    p.partition_expression AS '分区键'
FROM
    information_schema.tables t
LEFT JOIN (
    SELECT
        table_schema, 
        table_name,
        partition_method,
        partition_expression
    FROM
        information_schema.partitions
    WHERE
        partition_name IS NOT NULL
    GROUP BY
        table_schema, table_name, partition_method, partition_expression
) p ON t.table_schema = p.table_schema AND t.table_name = p.table_name
WHERE
    t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    AND t.table_type = 'BASE TABLE'
ORDER BY
    t.table_schema, t.table_name;

查出表行数

SELECT
    t.table_schema AS '数据库',
    t.table_name AS '表名',
    CASE
        WHEN p.partition_method IS NULL THEN '普通表'
        ELSE CONCAT('分区表(', p.partition_method, ')')
    END AS '表类型',
    t.table_rows AS '估算行数',
    p.partition_expression AS '分区键'
FROM
    information_schema.tables t
LEFT JOIN (
    SELECT DISTINCT
        table_schema, 
        table_name,
        partition_method,
        partition_expression
    FROM
        information_schema.partitions
    WHERE
        partition_name IS NOT NULL
) p ON t.table_schema = p.table_schema AND t.table_name = p.table_name
WHERE
    t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    AND t.table_type = 'BASE TABLE'
ORDER BY
    t.table_schema, t.table_name;

到此这篇关于mysql表类型查询的文章就介绍到这了,更多相关mysql表类型查询内容请搜索IT俱乐部以前的文章或继续浏览下面的相关文章希望大家以后多多支持IT俱乐部！

本文收集自网络，不代表IT俱乐部立场，转载请注明出处。https://www.2it.club/database/mysql/15559.html

19赞

标签:mysql 查询示例类型详解