mysql 查询数据库表的主键_Mysql查询数据库中各表的主键、自增标识

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

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 = 数据库名称

AND t.CONSTRAINT_TYPE = 'PRIMARY KEY';