The query below helps you to locate tables without a primary key:
Posted on 2018-07-12 22:39 moss_tan_jun 阅读(164) 评论(0) 编辑 收藏 举报SELECT tables.table_schema, tables.table_name, tables.table_rows
FROM information_schema.tables
LEFT JOIN (
SELECT table_schema, table_name
FROM information_schema.statistics
GROUP BY table_schema, table_name, index_name
HAVING
SUM(
CASE WHEN non_unique = 0 AND nullable != 'YES' THEN 1 ELSE 0 END
) = COUNT(*)
) puks
ON tables.table_schema = puks.table_schema AND tables.table_name = puks.table_name
WHERE puks.table_name IS NULL
AND tables.table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
AND tables.table_type = 'BASE TABLE' AND engine='InnoDB';
mysql> SET GLOBAL slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN';
