MySQL常用SQL

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;

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';

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';