两种获取MySql数据库中所有表的主键和外键约束信息的Sql语句

àì夳堔傛蜴生んèń 2022-03-30 08:08 322阅读 0赞

最近在写Rafy底层的一些东西，在数据库方面把MySql数据库集成到里面去，里面有一个需求，需要获取非系统数据库，也就是我们自己建立的数据库中所有表的主键和外键元数据列表。

第一种方法：是网上的方法，可以直接使用，【**MySQL 查看数据库中有主外键关系的表信息**】

SELECT C.TABLE\_SCHEMA 拥有者,  
C.REFERENCED\_TABLE\_NAME 父表名称 ,  
C.REFERENCED\_COLUMN\_NAME 父表字段 ,  
C.TABLE\_NAME 子表名称,  
C.COLUMN\_NAME 子表字段,  
C.CONSTRAINT\_NAME 约束名,  
T.TABLE\_COMMENT 表注释,  
R.UPDATE\_RULE 约束更新规则,  
R.DELETE\_RULE 约束删除规则  
FROM INFORMATION\_SCHEMA.KEY\_COLUMN\_USAGE C  
JOIN INFORMATION\_SCHEMA. TABLES T  
ON T.TABLE\_NAME = C.TABLE\_NAME  
JOIN INFORMATION\_SCHEMA.REFERENTIAL\_CONSTRAINTS R  
ON R.TABLE\_NAME = C.TABLE\_NAME  
AND R.CONSTRAINT\_NAME = C.CONSTRAINT\_NAME  
AND R.REFERENCED\_TABLE\_NAME = C.REFERENCED\_TABLE\_NAME  
WHERE C.REFERENCED\_TABLE\_NAME IS NOT NULL ;  
  
UPDATE/DELETE\_RULE 类型：  
1. CASCADE: 从父表中删除或更新对应的行 ，同时自动的删除或更新自表中匹配的行 。ON DELETE CANSCADE和ON UPDATE CANSCADE都被InnoDB所支持 。  
2. SET NULL: 从父表中删除或更新对应的行 ，同时将子表中的外键列设为空 。注意， 这些在外键列没有被设为NOT NULL时才有效。 ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支持 。  
3. NO ACTION: InnoDB拒绝删除或者更新父表 。  
4. RESTRICT: 拒绝删除或者更新父表 。指定RESTRICT（ 或者NO ACTION ）和忽略ON DELETE或者ON UPDATE选项的效果是一样的 。  
5. SET DEFAULT: InnoDB目前不支持 。  
  
第二种方法：

select O.CONSTRAINT\_SCHEMA,O.CONSTRAINT\_NAME,O.TABLE\_SCHEMA,O.TABLE\_NAME,O.COLUMN\_NAME,O.REFERENCED\_TABLE\_SCHEMA,O.REFERENCED\_TABLE\_NAME,O.REFERENCED\_COLUMN\_NAME,O.UPDATE\_RULE,O.DELETE\_RULE,O.UNIQUE\_CONSTRAINT\_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 T.CONSTRAINT\_NAME=O.CONSTRAINT\_NAME where O.CONSTRAINT\_SCHEMA!='mysql' and O.CONSTRAINT\_SCHEMA!='sys';

第二种方法会列出所有的非系统数据所有表的主键信息和外键信息，第一种方法只会列出外键约束信息。