MYSQL:在插入具有重复唯一键的记录时获取现有主键? [英] MYSQL: Getting existing primary key when inserting record with duplicate unique key?

问题描述

我有一个带有表的mysql数据库，该表既具有自动增量主键又具有唯一的字符串值键(sha-1哈希).

I've got a mysql database with a table that has both a auto-increment primary key and unique string valued key (a sha-1 hash).

如果我尝试添加与现有记录具有相同sha-1哈希的记录，我只想获取现有记录的主键.当尝试插入具有现有哈希值的记录时，可以使用"INSERT ... ON DUPLICATE KEY UPDATE"或"INSERT IGNORE"之类的方法来防止异常.

If I try to add a record that has the same sha-1 hash as an existing record, I just want to get the primary key of the existing record. I can use something like "INSERT ... ON DUPLICATE KEY UPDATE" or "INSERT IGNORE" to prevent an exception when trying to insert a record with a existing hash value.

但是，当发生这种情况时，我需要检索现有记录的主键.我找不到用单个SQL语句执行此操作的方法.如果重要的话，我的代码是用Java编写的，并且我使用的是JDBC.

However, when that happens, I need to retrieve the primary key of the existing record. I can't find a way to do that with a single SQL statement. If it matters, my code is in Java and I'm using JDBC.

或者，我可以用两个语句来完成此操作(如果找不到，则在查询后插入一个语句，如果存在重复的键，则在插入后插入一个查询).但是我认为一个声明会更有效.

Alternatively, I can do it with two statements (either a query followed by an insertion if not found, or a insertion followed by a query if a duplicate key exists). But I presume a single statement would be more efficient.

推荐答案

如果我尝试添加与现有哈希1哈希相同的记录 记录，我只想获取现有记录的主键.一世 可以使用"INSERT ... ON DUPLICATE KEY UPDATE"或"INSERT"之类的东西 IGNORE"以防止在尝试插入带有 现有的哈希值.

If I try to add a record that has the same sha-1 hash as an existing record, I just want to get the primary key of the existing record. I can use something like "INSERT ... ON DUPLICATE KEY UPDATE" or "INSERT IGNORE" to prevent an exception when trying to insert a record with a existing hash value.

如果列上有一个UNIQUE索引，则无论您尝试什么，RDMS都将不允许允许该列中的重复项(NULL值除外).

If you have an UNIQUE index on a column, no matter what you tried, the RDMS will not allow duplicates in that column (except for the NULL value).

正如您所说，如果有此附加说明，则有解决方案可防止错误".您可能是INSERT IGNORE.

As you said, there is solution to prevent "error" if this appends. Probably INSERT IGNORE in your case.

无论如何，INSERT和UPDATE修改数据库. MySQL 从不返回这些语句的值.读取数据库的唯一方法是使用SELECT语句.

Anyway, INSERT and UPDATE modify the database. MySQL never return values for these statements. The only way to read your DB is to use a SELECT statement.

由于您有UNIQUE列，因此解决方法"很简单:

Here the "workaround" is simple, since you have an UNIQUE column:

INSERT IGNORE INTO tbl (pk, sha_key) VALUES ( ... ), ( ... );
SELECT pk, sha_key FROM tbl WHERE sha_key IN ( ... );
--                                             ^^^
--             Here the list of the sha1 keys you *tried* to insert

这篇关于MYSQL:在插入具有重复唯一键的记录时获取现有主键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！