MySQL查询主键相关

-- 查询数据库中所有表名和表的主键
select 
table_name,
column_name 
from information_schema.key_column_usage 
where constraint_name = 'primary' 
and table_name in (
select 
table_name
from information_schema.tables
where table_schema = 'testdb' 
order by table_name)
and table_schema = 'testdb';

-- 查询数据库中没有主键的表
SELECT
    a.TABLE_SCHEMA,
    a.TABLE_NAME 
FROM
    (
    SELECT
        TABLE_SCHEMA,
        TABLE_NAME 
    FROM
        information_schema.TABLES 
    WHERE
    TABLE_SCHEMA NOT IN 
	( 'mysql', 'information_schema', 'performance_schema', 'sys', 'sysdb' )) AS a
    LEFT JOIN (
    SELECT
        TABLE_SCHEMA,
        TABLE_NAME 
    FROM
        information_schema.TABLE_CONSTRAINTS 
    WHERE
        CONSTRAINT_TYPE = 'PRIMARY KEY' 
    AND TABLE_SCHEMA NOT IN 
	( 'mysql', 'information_schema', 'performance_schema', 'sys', 'sysdb' )) AS b 
    ON a.TABLE_SCHEMA = b.TABLE_SCHEMA 
    AND a.TABLE_NAME = b.TABLE_NAME 
WHERE
    b.TABLE_NAME IS NULL;