mysql表类型查询

普通表

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_expressionFROM 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_expressionFROM 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_expressionFROM information_schema.partitionsWHERE partition_name IS NOT NULLGROUP 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_expressionFROM information_schema.partitionsWHERE 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;