Оператор mysql on duplicate key для не ключей

Я работаю над игрой, которая требует от пользователя (в первую очередь детей) объединить префикс и суффикс в уникальное имя пользователя, скажем, BlueBaron. Теперь префиксов и суффиксов так много, поэтому, если пользователь создает уже существующий, к нему добавляется число, скажем, BlueBaron2.

У меня есть следующая таблица:

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

id - это уникальный, непустой первичный ключ с автоматическим приращением - я предполагаю, что в этом конкретном случае мне не нужно беспокоиться о id. prefix_id и suffix_id не являются нулевыми, но поскольку они относятся к общим префиксам и суффиксам, они не уникальны. Остальные строки просто ненулевые.

В идеале я хотел бы проверить, есть ли у нового пользователя те же prefix_id и suffix_id, что и другой пользователь, и увеличить identifier_index.

Я пробовал это с несколькими операторами (SELECT, затем INSERT), но я боюсь, что данные могут быть не обновленными / уникальными (другой пользователь мог вставить между временем, которое потребовалось для вставки, и т. д.).

Возможно ли это в рамках одного оператора вставки? Я читал о ON DUPLICATE KEY, но не уверен, что это применимо здесь.

Обновлено:

В соответствии с комментариями и ответами ниже я создал уникальный индекс для трех рассматриваемых столбцов: enter image description here

Однако identifier_index увеличивается, даже если prefix_id и suffix_id различны. А в случае последней записи вообще не будет увеличиваться, что приведет к ошибке дублирования записи:

enter image description here

Если цель состоит в том, чтобы выполнить эту проверку только при создании нового пользователя, я думаю, вам следует делать это в своем приложении, а не в базе данных. Так было бы проще и логичнее.

Thomas G 10.09.2018 10:06

@ThomasG ну, цель - веб-игра с API-шлюзом и лямбда-бэкендом. Я выбрал подход к базе данных, потому что это последний шаг в процессе. Возможно, у меня недостаточно богатого воображения, но я не вижу способа сделать это на сайте.

zack_falcon 10.09.2018 11:04

Сначала вы должны выполнить проверку следующим образом: SELECT COALESCE(MAX(identifier_index),0) AS max_identifier_index FROM thetable WHERE prefix_id=xxxx AND suffix_id = yyyyy. Если max_identifier_index = 0, это означает, что записи с этим префиксом и суффиксом еще нет, если больше 0, вы увеличиваете значение на 1. Затем вы делаете запрос INSERT

Thomas G 10.09.2018 11:33

Нет ли опасности, что между вашим оператором SELECT и оператором INSERT кто-то еще вставил в таблицу, тем самым сделав результаты оператора SELECT недействительными?

zack_falcon 10.09.2018 13:21

Хорошее замечание. Я дам тебе правильный ответ позже

Thomas G 10.09.2018 13:28
0
5
43
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Это хороший вопрос. Я не разработчик, но с точки зрения администратора базы данных, я бы сказал, что вам нужно сделать это вот так.

Вам определенно нужен уникальный индекс, охватывающий 3 столбца.

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;

Это обязательно, вы хотите гарантировать целостность данных!

Ваш оператор вставки будет выглядеть так:

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';

Однако имейте в виду, что вы можете столкнуться с тупиковыми ситуациями. Это происходит, когда другая транзакция пытается вставить, пока этот запрос все еще выполняется. Однако тупиковые ситуации не представляют серьезной проблемы. Обычно приложение построено таким образом, что оно просто пытается снова, пока вставка не будет успешной.

Хм. Для чего эта строчка UNIQUE KEY 'uidx_psi' ('prefix_id','suffix_id','identifier_index')?

zack_falcon 10.09.2018 13:36

Это гарантирует целостность ваших данных. Уникальный индекс гарантирует отсутствие дубликатов (охватывающих 3 столбца). Вы же не хотите, чтобы строка ('asdf', 'qwer', 1) повторялась несколько раз, не так ли?

fancyPants 10.09.2018 13:38

Это более или менее то предложение, которое я собирался сделать. Уникальный ключ гарантирует, что у вас никогда не будет дублей, в случае, если вы поднялись в комментариях. Вместо этого он вернет ошибку, которую вы можете поймать, чтобы снова попробовать запрос INSERT. +1

Thomas G 10.09.2018 13:40

Я попробую это сделать, как только смогу, и опубликую свои результаты. Я сам не стою перед ПК. Но спасибо!

zack_falcon 10.09.2018 13:47

@fancyPants Я тестировал его на верстаке MySQL. Еще я добавил, что uidx_psi в индексы. К сожалению, похоже, что индекс идентификатора просто увеличивает другое имя пользователя, а в некоторых случаях вообще отказывается увеличиваться, что приводит к ошибке повторяющейся записи. Я обновил вопрос, добавив изображения. Следует отметить, что с тех пор в таблицу добавлено больше столбцов. Могло ли это быть причиной проблемы?

zack_falcon 11.09.2018 11:42

@zack_falcon Вы заметили, что вам нужно указать значения, которые вы хотите вставить, два раза? Один раз в предложении select и один раз в предложении where. У меня работает безупречно.

fancyPants 11.09.2018 11:45

Ага! Пропустил! Да, действительно работает безупречно. Спасибо!

zack_falcon 11.09.2018 11:53

Другие вопросы по теме