MySQL常用巡检SQL(3)

5、查询非utf8编码的表

SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION
 from information_schema.TABLES 
WHERE TABLE_COLLATION NOT LIKE 'utf8%'
 AND table_schema NOT IN ('information_schema' ,'mysql','performance_schema', 'sys');

6、查询无主键的表，并生成添加主键sql

SELECT t.TABLE_SCHEMA, t.TABLE_NAME,t.CREATE_OPTIONS,
    CONCAT(
        'ALTER TABLE `', t.TABLE_SCHEMA, '`.`', t.TABLE_NAME, '` ',
        'ADD COLUMN `id` BIGINT UNSIGNED AUTO_INCREMENT FIRST, ',
        'ADD PRIMARY KEY (`id`)'
    ) AS ADD_PRIMARY_KEY_SQL
FROM information_schema.tables t
LEFT JOIN (
    SELECT DISTINCT table_schema, table_name
    FROM information_schema.columns
    WHERE COLUMN_KEY = 'PRI'
) c ON t.TABLE_SCHEMA = c.table_schema AND t.TABLE_NAME = c.table_name
WHERE t.TABLE_SCHEMA NOT IN ('sys', 'mysql', 'information_schema', 'performance_schema')
    AND c.table_schema IS null;

7、查询无主键的表，但是表里边已经包含列名为id的列

SELECT t.TABLE_SCHEMA, t.TABLE_NAME
FROM information_schema.tables t
JOIN (
    SELECT TABLE_SCHEMA, TABLE_NAME
    FROM information_schema.columns
    WHERE COLUMN_NAME = 'id'
) c ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
LEFT JOIN (
    SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME
    FROM information_schema.columns
    WHERE COLUMN_KEY = 'PRI'
) k ON t.TABLE_SCHEMA = k.TABLE_SCHEMA AND t.TABLE_NAME = k.TABLE_NAME
WHERE t.TABLE_SCHEMA NOT IN ('sys', 'mysql', 'information_schema', 'performance_schema')
    AND k.TABLE_NAME IS NULL;

8、查看有哪些存储过程、函数

--5.7 查询sql 

SELECT db,name,type
FROM mysql.proc
WHERE db not in ('mysql','information_schema','performance_schema','sys')
GROUP BY db,name,type
order by 1,3;

--8.0 查询sql
SELECT  ROUTINE_SCHEMA,ROUTINE_NAME , ROUTINE_TYPE
FROM information_schema.ROUTINES
WHERE  ROUTINE_SCHEMA not in ('mysql','information_schema','performance_schema','sys') 
GROUP BY ROUTINE_SCHEMA,ROUTINE_NAME , ROUTINE_TYPE
order by 1,3;