【mysql系】数据库统计各个表的大小、表备注搜索表名、表分区等常用sql查询

查找数据库表行数以及容量大小

SELECT TABLE_NAME, concat( round( DATA_LENGTH / 1024 / 1024, 3),'MB') as 数据容量占用, concat( round(  INDEX_LENGTH / 1024 / 1024, 3),'MB') as 索引容量占用, TABLE_ROWS
FROM
 information_schema.TABLES 
WHERE
 TABLE_SCHEMA = '数据库名称' 
 and  TABLE_ROWS > 1
ORDER BY
 TABLE_ROWS DESC

通过表名注释来搜索对应表名

SELECT
    table_name '表名',
    TABLE_COMMENT '表注解' 
FROM
    INFORMATION_SCHEMA.TABLES 
WHERE
    table_schema = '数据库名称' 
    AND TABLE_COMMENT LIKE '%角色%';

查询数据库表数据量大于10万是否有创建分区

num_partitions > 0代表已经创建分区

SELECT 
    table_name, 
    COUNT(DISTINCT partition_ordinal_position) AS num_partitions,
    TABLE_ROWS 
FROM 
    information_schema.partitions 
where TABLE_ROWS > 100000 
GROUP BY 
    table_name 
HAVING 
    num_partitions = 0

查询mysq表分区详细信息

SELECT
    PARTITION_NAME,
    PARTITION_METHOD,
    PARTITION_EXPRESSION,
    PARTITION_DESCRIPTION,
    FROM_DAYS(PARTITION_DESCRIPTION),
    TABLE_ROWS,
    SUBPARTITION_NAME,
    SUBPARTITION_METHOD,
    SUBPARTITION_EXPRESSION 
FROM
    information_schema.PARTITIONS 
WHERE
    TABLE_SCHEMA = SCHEMA () 
    AND TABLE_NAME = 'po_order';

 取消数据库原有表主键自增ID为bigint(20)

SELECT 
CONCAT('ALTER TABLE ', TABLE_NAME, ' MODIFY  COLUMN ', COLUMN_NAME, ' ', ' bigint(20) NOT NULL FIRST',';') AS '改表sql'
FROM
information_schema.COLUMNS
WHERE
TABLE_SCHEMA = '库名'
AND column_key = 'PRI' ORDER BY table_name

查看数据库有外键约束的表

SELECT DISTINCT
	( TABLE_NAME ) 
FROM
	information_schema.KEY_COLUMN_USAGE 
WHERE
	CONSTRAINT_SCHEMA = 'upm_lmdm' 
	AND REFERENCED_TABLE_NAME IS NOT NULL

查看blob数据类型的字符串内容 

select  UNHEX(HEX(字段)) from 表名