#查询数据库中所有表的自增主键：

SELECT
  t.TABLE_NAME,
  c.COLUMN_NAME,
  ts.AUTO_INCREMENT
FROM
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,
  information_schema.TABLES AS ts,
  information_schema.KEY_COLUMN_USAGE AS c
WHERE
  t.TABLE_NAME = ts.TABLE_NAME
  AND ts.TABLE_NAME  = c.TABLE_NAME
  -- AND t.TABLE_SCHEMA = 数据库名称
  AND t.CONSTRAINT_TYPE = 'PRIMARY KEY'
  ORDER BY ts.`AUTO_INCREMENT` DESC;
# 查询数据库中所有表的主键及数量：
SELECT
  t.TABLE_NAME,
  t.CONSTRAINT_TYPE,
  c.COLUMN_NAME,
  c.ORDINAL_POSITION
FROM
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c,
  information_schema.TABLES AS ts
WHERE
  t.TABLE_NAME = c.TABLE_NAME
  AND t.TABLE_SCHEMA = 'sakila'
  AND t.CONSTRAINT_TYPE = 'PRIMARY KEY';

# 查看所有没有主键的表
SELECT
    #CONCAT("truncate table ",table_name,";") 
table_name 
FROM
    information_schema. TABLES
WHERE
    table_schema = 'sakila'
AND TABLE_NAME NOT IN (
    SELECT
        table_name
    FROM
        information_schema.table_constraints t
    JOIN information_schema.key_column_usage k USING (
        constraint_name,
        table_schema,
        table_name
    )
    WHERE
        t.constraint_type = 'PRIMARY KEY'
    AND t.table_schema = 'sakila'
);

--
SELECT column_name FROM INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` 
 WHERE table_name='city' AND constraint_name='PRIMARY'
 AND CONSTRAINT_SCHEMA='sakila';

show keys from `city` where key_name='PRIMARY';

select DISTINCT COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where table_name='city' AND COLUMN_KEY='PRI';

SELECT
  t.TABLE_NAME,
  t.CONSTRAINT_TYPE,
  c.COLUMN_NAME,
  c.ORDINAL_POSITION
FROM
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c
WHERE
  t.TABLE_NAME = c.TABLE_NAME
  AND t.TABLE_SCHEMA = 'sakila'
  AND t.CONSTRAINT_TYPE = 'PRIMARY KEY';