我的插入击中了哪个唯一键? [英] Which unique key is hit with my insert?
问题描述
使用 MySQL,当我有一个包含(例如)三个键的表(一个主键、自动增量和列上的两个唯一键,例如代码"和名称")时,我该如何(有效地)知道在进行插入时违反了哪个唯一性约束吗?
Using MySQL, when I have a table with (for example) three keys (one primary, auto-increment, and two uniques on a column, eg. 'code' and 'name'), how can I (efficiently) know which uniqueness constraint was violated when doing an insert?
您收到错误 #1062 - 键 2 的重复条目值"
,但我怎么知道键 2 是代码"列的键而不是名称"列的键?
You get an error #1062 - Duplicate entry 'Value' for key 2
, but how do I know key 2 is the key for the 'code' column and not the 'name' column?
我们与多个开发人员一起开发这个应用程序,我想防止有一天我们不以相同的顺序将约束添加到表中,所以我机器上的第二个键是另一台机器上的第三个键,反之亦然-相反.
We develop this application with multiple developers, and I want to prevent the day we don't add the constraints to a table in the same order, so the second key on my machine is the third key on another machine, and vice-versa.
映射到所涉及的确切列名并不是必需的,只映射到键名就足够了.
Mapping to the exact column names involved is not really necessary, just to the key names is enough.
推荐答案
正如 Bobby 所建议的,SHOW 索引返回一个带有相关键索引的结果集,检查这个 页面 了解更多示例:
As Bobby has suggested, SHOW indexes returns a resultset with relevant key index, check this page for further examples:
SHOW INDEXES FROM products_to_categories
WHERE Key_name = "PRIMARY"
AND Seq_in_index = '2';
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| products_to_categories | 0 | PRIMARY | 2 | category_id | A | 0 | NULL | NULL | | BTREE | |
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
但是,如果不超过以下解决方法,这可能需要尽可能多的代码.
However this will probably require as much code if not more than the following workaround.
检查您插入的值与表中的现有行发生冲突:
Check which value you inserted collides with an existing row in the table:
SELECT code FROM myTable WHERE code = 'the_value_you_tried_to_insert';
或
SELECT name FROM myTable WHERE name = 'the_value_you_tried_to_insert';
虽然不是一个非常优雅的解决方案.
Albeit not a very elegant solution.
这篇关于我的插入击中了哪个唯一键?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!