- 查询数据库中所有表的磁盘占用情况
SELECT
table_schema AS "数据库",
table_name AS "表名",
table_rows AS "记录数",
truncate(data_length / 1024 / 1024, 2) AS "数据容量(MB)",
truncate(index_length / 1024 / 1024, 2) AS "索引容量(MB)",
truncate(data_free / 1024 / 1024, 2) AS "碎片(MB)"
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
ORDER BY data_length DESC, index_length DESC;- 查看大写库名和大写的表名
--database
SELECT SCHEMA_NAME
FROM information_schema.SCHEMATA
WHERE md5(SCHEMA_NAME) <> md5(lower(SCHEMA_NAME));
--table
SELECT table_schema, table_name
FROM information_schema.TABLES
WHERE table_schema NOT IN ('mysql', 'information_schema')
AND (md5(table_name) <> md5(lower(table_name))
OR md5(table_schema) <> md5(lower(table_schema)));- 查看所有的无主键表
SELECT
concat(t.table_schema, '.',
t.TABLE_NAME) AS tablename,
ENGINE,
TABLE_ROWS, DATA_LENGTH + INDEX_LENGTH + DATA_FREE AS table_size_MB
FROM information_schema.TABLES t
LEFT JOIN (
SELECT CONSTRAINT_SCHEMA, table_name
FROM information_schema.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY') p
ON t.table_name = p.table_name
AND t.TABLE_SCHEMA = p.CONSTRAINT_SCHEMA
WHERE t.table_schema NOT IN ('performance_schema', 'information_schema', 'mysql')
AND p.table_name IS NULL
AND t.TABLE_TYPE = 'BASE TABLE'
ORDER BY t.TABLE_ROWS DESC;- 查询出非innodb引擎，再组合成alter语句
SELECT concat('alter table`', table_schema, '`.`', table_name, '`ENGINE=InnoDB ;')
FROM information_schema.tables
WHERE table_schema NOT IN ('sys', 'mysql', 'information_schema', 'performance_schema')
AND ENGINE <> 'InnoDB';- 查找出ROW_FORMAT非Dynamic的表组合语句
SELECT concat('alter table`', table_schema, '`.`', table_name, '`ROW_FORMAT =DYNAMIC ;')
FROM information_schema.TABLES
WHERE table_schema NOT IN ('sys', 'mysql', 'information_schema', 'performance_schema')
AND ROW_FORMAT <> 'Dynamic';
