MYSQL 查询数据库中所有主键列以及自增数值

[malaohu]

SELECT DISTINCT
	T1.TABLE_SCHEMA,
	T1.TABLE_NAME,
	T2.COLUMN_NAME,
	T1.AUTO_INCREMENT

FROM
	information_schema.TABLES AS T1
	INNER JOIN information_schema.KEY_COLUMN_USAGE AS T2 ON T1.TABLE_SCHEMA = T2.CONSTRAINT_SCHEMA 
	AND T1.TABLE_NAME = T2.TABLE_NAME 
WHERE
	T2.CONSTRAINT_NAME = 'PRIMARY' 
	AND T2.CONSTRAINT_SCHEMA = '数据库名称' 
	AND T1.AUTO_INCREMENT IS NOT NULL
	AND T1.TABLE_NAME LIKE 'info_%'

[malaohu]

SELECT DISTINCT
	T1.TABLE_SCHEMA,
	T1.TABLE_NAME,
	T2.COLUMN_NAME,
	T1.AUTO_INCREMENT,
	T3.DATA_TYPE
FROM
	information_schema.TABLES AS T1
	INNER JOIN information_schema.KEY_COLUMN_USAGE AS T2 ON T1.TABLE_SCHEMA = T2.CONSTRAINT_SCHEMA 
	AND T1.TABLE_NAME = T2.TABLE_NAME 
	INNER JOIN information_schema.`COLUMNS` as T3
	ON T1.TABLE_SCHEMA = T3.TABLE_SCHEMA AND T1.TABLE_NAME=T3.TABLE_NAME AND T2.COLUMN_NAME=T3.COLUMN_NAME
WHERE
	T2.CONSTRAINT_NAME = 'PRIMARY' 
	AND T2.CONSTRAINT_SCHEMA = 'manna' 
	AND T1.AUTO_INCREMENT IS NOT NULL
	AND T1.TABLE_NAME LIKE 'info_%'
	AND T3.DATA_TYPE='int'

[js]

突然 技术贴 猝不及防

[49679514]

很好，很技术