添加或从SQL表中检索唯一键和值

names is a table that maps every unique name to an id:

names是一个将每个唯一名称映射到id的表：

CREATE TABLE names (
  name       VARCHAR(20)    NOT NULL,
  name_id    INT            NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (name),
  UNIQUE (name_id)
) ENGINE=InnoDB

If 'foobar' is already stored in names, I want to get its name_id. Otherwise I want to store it and get the name_id that was generated for it.

如果'foobar'已存储在名称中，我想获取其name_id。否则我想存储它并获取为其生成的name_id。

How does one do this, dealing with the possibility of two threads racing to add 'foobar'? (Note: we never delete from this table.)

如何做到这一点，处理两个线程竞争添加'foobar'的可能性？（注意：我们永远不会从此表中删除。）

With myISAM I wrote:

用myISAM我写道：

GET_LOCK('foobar');
SELECT name_id FROM names WHERE name = 'foobar';
if name wasn't found
    INSERT INTO names SET name='foobar';
    SELECT LAST_INSERT_ID(); -- that is, name_id
RELEASE_LOCK('foobar');

However, I'm very unclear about how to achieve this using transactions and row-level locking in innoDB.

但是，我很清楚如何使用innoDB中的事务和行级锁定来实现这一点。

UPDATE: mySQL requires that AUTO_INCREMENT only be used for the PK. I'm using MariaDB, which only requires that the AUTO_INCREMENT column be defined as key. Updating example accordingly.

更新：mySQL要求AUTO_INCREMENT仅用于PK。我正在使用MariaDB，它只需要将AUTO_INCREMENT列定义为键。相应地更新示例。

1 个解决方案

#1

Try the following:

请尝试以下方法：

INSERT IGNORE INTO names (name) VALUES ('foobar');
IF ROW_COUNT() == 1
THEN SELECT LAST_INSERT_ID() AS name_id;
ELSE SELECT name_id FROM names WHERE name = 'foobar';
END IF;

You don't need an explicit transaction if auto-commit is enabled, there will be an implicit transaction around the INSERT IGNORE query, making it atomic.

如果启用了自动提交，则不需要显式事务，INSERT IGNORE查询周围会有一个隐式事务，使其成为原子事务。

ROW_COUNT() will tell you whether it was able to insert the new row; it will be 1 if the name was available, 0 if a duplicate was found so no row could be inserted.

ROW_COUNT（）将告诉您是否能够插入新行;如果名称可用，则为1;如果找到重复，则为0，因此不能插入行。

#1