我有一個錯誤彈出，引發如下錯誤：
IntegrityError: (999, "Duplicate entry 'XXXXX' for key 'constraint_name_here_uniq'")
所以我有約束名稱，有沒有一種簡單的方法可以找出在 mysql 命令列中參考了哪些表、列？這是一個非常大的資料庫，并嘗試在幾張桌子周圍閑逛SHOW CREATE TABLE，DESC <constraint name>但沒有運氣，我也嘗試過，但這也不起作用。
uj5u.com熱心網友回復：
這應該有效：
select *
from information_schema.KEY_COLUMN_USAGE
where CONSTRAINT_NAME ='constraint_name_here_uniq';
例子：
mysql> use information_schema;
Database changed
mysql> select * from KEY_COLUMN_USAGE where CONSTRAINT_NAME ='user_has_notification_types_user_idx' \G
*************************** 1. row ***************************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: kanboard
CONSTRAINT_NAME: user_has_notification_types_user_idx
TABLE_CATALOG: def
TABLE_SCHEMA: kanboard
TABLE_NAME: user_has_notification_types
COLUMN_NAME: user_id
ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: NULL
REFERENCED_TABLE_SCHEMA: NULL
REFERENCED_TABLE_NAME: NULL
REFERENCED_COLUMN_NAME: NULL
*************************** 2. row ***************************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: kanboard
CONSTRAINT_NAME: user_has_notification_types_user_idx
TABLE_CATALOG: def
TABLE_SCHEMA: kanboard
TABLE_NAME: user_has_notification_types
COLUMN_NAME: notification_type
ORDINAL_POSITION: 2
POSITION_IN_UNIQUE_CONSTRAINT: NULL
REFERENCED_TABLE_SCHEMA: NULL
REFERENCED_TABLE_NAME: NULL
REFERENCED_COLUMN_NAME: NULL
2 rows in set (1.70 sec)
以及使用索引的表：
mysql> use kanboard;
Database changed
mysql> show create table user_has_notification_types;
----------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
----------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| user_has_notification_types | CREATE TABLE `user_has_notification_types` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`notification_type` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `user_has_notification_types_user_idx` (`user_id`,`notification_type`),
CONSTRAINT `user_has_notification_types_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
----------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.02 sec)
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/400184.html