MySQL 语句 ON DUPLICATE KEY 用于非键 [英] MySQL statement ON DUPLICATE KEY for not keys

问题描述

我正在开发一款游戏，该游戏要求用户(主要是儿童)将前缀和后缀组合成唯一的用户名，例如 BlueBaron.现在只有这么多前缀和后缀，因此如果用户生成现有的前缀和后缀，则会在其后附加一个数字，例如 BlueBaron2.

I'm working on a game that requires the user (primarily kids) to combine a prefix and a suffix into a unique username, say, BlueBaron. Now there's only so many prefixes and suffixes, so if a user generates an existing one, a number is appended to it, say, BlueBaron2.

我有一个表格如下:

| id | prefix_id | suffix_id | identifier_index | username | hashbrown | salt | coins | ranking | date_created | date_updated

id 是一个自动递增的、唯一的、非空的主键 - 我假设对于这个特定的实例，我实际上不需要担心 id.prefix_id 和 suffix_id 不是空的，但是因为它们指的是通用的前缀和后缀，所以它们不是唯一的.其余的行只是非空.

The id is an auto-increment, unique, not-null primary key - I assume for this particular instance, I won't actually need to worry about the id. The prefix_id and suffix_id are not-null, but because they refer to common prefixes and suffixes, they are not unique. The rest of the rows are just not-nulls.

理想情况下，我想检查一个新用户是否与另一个用户具有完全相同的 prefix_id 和 suffix_id，并增加 identifier_index.

Ideally, I would like to check if a new user has the exact same prefix_id and suffix_id as another user, and increment the identifier_index.

我用多个(SELECT 然后 INSERT)语句尝试了这个，但我担心数据可能不会更新/唯一(另一个用户可能在你插入的时间之间插入了等等).

I tried this with multiple (SELECT then INSERT) statements, but I fear the data might not be updated / unique (another user might have inserted between the time it took for you to insert, etc.).

这可以在单个插入语句中实现吗?我读过 ON DUPLICATE KEY 但我不确定这是否适用于此.

Is this possible within a single insert statement? I've read of ON DUPLICATE KEY but I'm not sure that's applicable here.

更新:

根据下面的评论和答案，我为相关的三列创建了一个唯一索引:

Per the comments and answers below, I've created a unique index for the three columns in question:

然而，即使 prefix_id 和 suffix_id 不同，identifier_index 也会增加.在最后一个条目的情况下，根本不会增加导致重复条目错误:

However, the identifier_index increments even when the prefix_id and suffix_id are different. And in the case of the last entry, wouldn't increment at all resulting in a duplicate entry error:

推荐答案

这是个好问题.我不是开发人员，但从数据库管理员的角度来看，我会说你需要这样做.

That's a good question. I'm no developer, but from a database admins view, I'd say that you need to do it like this.

您肯定需要一个跨越 3 列的唯一索引.

You definitely need a unique index spanning over the 3 columns.

CREATE TABLE `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `prefix_id` varchar(10) NOT NULL,
  `suffix_id` varchar(10) NOT NULL,
  `identifier_index` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_psi` (`prefix_id`,`suffix_id`,`identifier_index`)
) ENGINE=InnoDB;

这是必须的，您要保证数据完整性！

This is a must, you want to guarantee data integrity!

您的插入语句如下所示:

Your insert statement would look like this:

insert into a (prefix_id, suffix_id, identifier_index) 
select 'asdf', 'qwer', coalesce(max(identifier_index) + 1, 1) 
from a 
where prefix_id = 'asdf' and suffix_id = 'qwer';

但请注意，您可能会遇到死锁问题.当该查询仍在运行时另一个事务试图插入时会发生这种情况.不过，死锁并不是什么严重的问题.通常，应用程序是以某种方式构建的，它只是再次尝试，直到插入成功.

Be aware though, that you can run into deadlock issues. This happens when another transaction is trying to insert while this query is still running. Deadlocks are no serious issue, though. Typically an application is built in a way, that it simply tries again, until the insertion is successful.

这篇关于MySQL 语句 ON DUPLICATE KEY 用于非键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！