INFORMATION_SCHEMA.KEY_COLUMN_USAGE表可用于查找从特定表到其他表的所有外键关系,例如:
SELECT `TABLE_NAME`,
`COLUMN_NAME`,
`REFERENCED_TABLE_NAME`,
`REFERENCED_COLUMN_NAME`
FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`
WHERE `TABLE_SCHEMA` = SCHEMA() -- current schema
AND `REFERENCED_TABLE_NAME` IS NOT NULL
AND `TABLE_NAME` = 'orders'; -- name of table to get relationships to other tables
这应该返回如下内容:
+--------------+-------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+--------------+-------------+-----------------------+------------------------+
| orders | user_id | users | user_id |
+--------------+-------------+-----------------------+------------------------+
上面的操作可以用PHP完成,然后可以迭代结果以动态构造一个查询,从这些表中选择,连接列出的列.将该部分作为练习;-)