PDO如何获取MySQL数据库中INSERT的重复键列名称? [英] PDO how to get duplicated key column name for INSERT in MySQL database?
问题描述
很抱歉，是否曾经有人问过这个问题，但是我很长一段时间都找不到答案.
I'm sorry if this questions has been asked before, but I couldn't find an answer for quite a while.
问题是当试图在具有多个UNIQUE键的MySQL表中插入一行时.如果我们以下表为参考:
The problem is when trying to insert a row in a MySQL table that has multiple UNIQUE keys. If we take the following table as a reference:
----------------------------------------
| id | username | email |
----------------------------------------
| 1 | myuser | test@test.com |
----------------------------------------
用户名和电子邮件都是唯一键.如果我运行查询以用户名='myuser'插入新行，则输入重复项时将出现1062错误，并且消息中的某些文本指出ID为3的键已重复.像这样:
Both username and email are UNIQUE keys. If I run a query to insert new row with username = 'myuser' I will get an 1062 error for duplicate entry and some text in the message saying that key with id 3 is duplicated. Something like this:
array(3) {
[0]=>
string(5) "23000"
[1]=>
int(1062)
[2]=>
string(34) "Duplicate entry 'myuser' for key 3"
}
PDO中是否有一种方法可以获取重复列的名称，以便我可以向用户显示由于此错误而需要更改的字段?
Is there a way in PDO to get the name of the duplicated column, so that I can show to the user which field he needs to change because of this error?
因为情况是两个字段都可以重复，也可以只是第一个/第二个字段，所以我需要告诉用户确切的字段.
Because the cases are that both fields can be duplicated, or just the first/second one and I need to tell the user which field exactly is.
我本以为解析文本消息并为密钥的ID进行硬编码，但是明天有人可以更改数据库中的密钥，删除密钥或添加新密钥，并且密钥号似乎不是可靠的来源.信息.
I thought to parse the text message and hard code the ID for the keys, but someone can change the keys in the database tomorrow, drop a key or add a new one and that Key Number does not seem like a reliable source of information.
也许最好的选择是选择所有关键值，然后在插入之前检查它们?
Or maybe the best option is to SELECT all the values that are keys to check them before INSERT?
谢谢！
推荐答案
否，无法获得更好的错误消息以及有关哪个表发生故障的信息.您在数组键3的重复条目'myuser'"中看到的消息几乎就是您想要的信息.数字3实际上是您创建的索引的名称.您可以更改为更有意义的名称(例如用户名")，并获得更好的错误消息，然后将其转发给用户.如果要制作自定义错误消息，则必须从该消息中提取所需的信息.
No, there is no way to get a better error message with information of what table that is failing. The message you see in the array "Duplicate entry 'myuser' for key 3" is almost the information you want. The number 3 is actually the name of the index that you've made. You could change to a more meaningful, like "username", and get a better error message which you could forward to the user. If you want to make custom error messages you have to extract the information you want out of that message.
这篇关于PDO如何获取MySQL数据库中INSERT的重复键列名称?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
