查询数据库中所有表的自增主键:
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';
本文由 海拔科技 发布在 海拔科技,转载此文请保持文章完整性,并请附上文章来源(海拔科技)及本页链接。
原文链接:https://www.haibakeji.com/archives/254.html