我很想知道是否可以使用mysql来判断哪些列已设置为CASCADE。做DESC表格;似乎没有产生任何明显的效果。有什么建议吗?
从MySQL 5.1.10开始,您可以通过将REFERENTIAL_CONSTRAINTS
连接到KEY_COLUMN_USAGE
:来从INFORMATION_SCHEMA
表中获取这些信息
SELECT kcu.COLUMN_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME)
WHERE 'CASCADE' IN (rc.DELETE_RULE, rc.UPDATE_RULE)
AND kcu.TABLE_SCHEMA = DATABASE()
AND kcu.TABLE_NAME = 'my_table'
您可以使用:
show create table tableName;
其将输出具有对级联删除的引用的创建表。
从文档中截取的示例:
mysql> SHOW CREATE TABLE ibtest11c'G
*************************** 1. row ***************************
Table: ibtest11c
Create Table: CREATE TABLE `ibtest11c` (
`A` int(11) NOT NULL auto_increment,
`D` int(11) NOT NULL default '0',
`B` varchar(200) NOT NULL default '',
`C` varchar(175) default NULL,
PRIMARY KEY (`A`,`D`,`B`),
KEY `B` (`B`,`C`),
KEY `C` (`C`),
CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`)
REFERENCES `ibtest11a` (`A`, `D`)
ON DELETE CASCADE ON UPDATE CASCADE, // <--- This is what you are looking for
CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`)
REFERENCES `ibtest11a` (`B`, `C`) // <--- This is what you are looking for
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB CHARSET=latin1
1 row in set (0.01 sec)