查询mysql数据库外键和外键关联的表名

业务需求：获取指定数据库中所有表的外键以及外键关联的表名

SELECT
	O.TABLE_SCHEMA,
	O.TABLE_NAME,
	O.COLUMN_NAME,
	O.REFERENCED_TABLE_SCHEMA,
	O.REFERENCED_TABLE_NAME,
	T.CONSTRAINT_TYPE 
FROM
	(
	SELECT
		K.CONSTRAINT_SCHEMA,
		K.CONSTRAINT_NAME,
		K.TABLE_SCHEMA,
		K.TABLE_NAME,
		K.COLUMN_NAME,
		K.REFERENCED_TABLE_SCHEMA,
		K.REFERENCED_TABLE_NAME,
		K.REFERENCED_COLUMN_NAME,
		R.UPDATE_RULE,
		R.DELETE_RULE,
		R.UNIQUE_CONSTRAINT_NAME 
	FROM
		information_schema.KEY_COLUMN_USAGE K
		LEFT JOIN information_schema.REFERENTIAL_CONSTRAINTS R ON K.CONSTRAINT_NAME = R.CONSTRAINT_NAME 
	) AS O
	INNER JOIN Information_schema.TABLE_CONSTRAINTS T ON O.Table_Name = T.TABLE_NAME 
	AND o.TABLE_SCHEMA = upper( '{database}' ) 
	AND T.CONSTRAINT_NAME = O.CONSTRAINT_NAME 
	AND T.CONSTRAINT_TYPE = 'FOREIGN KEY' 
WHERE
	O.CONSTRAINT_SCHEMA != 'mysql' 
	AND O.CONSTRAINT_SCHEMA != 'sys'

备注

TABLE_SCHEMA：数据库名
TABLE_NAME：表名
COLUMN_NAME：外键所在的列名
REFERENCED_TABLE_SCHEMA：外键关联的数据库
REFERENCED_TABLE_NAME：外键关联的表名