Вставка в таблицу, если запрос на выборку возвращает 0 строк
Я хочу вставить новую строку в таблицу базы данных, только если предварительно выполняется условие.
Контекст База данных MariaDB, 10.3.38-MariaDB-0ubuntu0.20.04.1
Это моя таблица:
+-----------+-------------+------+
| sender_id | receiver_id | col3 |
+-----------+-------------+------+
Где sender_id и Receiver_id — это 16-байтовые столбцы внешнего ключа, а col3 — логическое значение.
- Эта таблица отслеживает отношения между двумя пользователями, столбцы id представляют собой 16-байтовый UUID.
- Пользователи могут запросить вставку новой связи между собой и другим пользователем в таблицу.
- UUID пользователя, который инициирует отношения, помещается в столбец: sender_id, а UUID пользователя, с которым они хотят установить связь, помещается в столбец: Receiver_id.
Проблема Всякий раз, когда пользователь пытается создать новую связь, база данных должна проверить, есть ли у него уже связь, И убедиться, что другой связи со значениями uuid, переставленными между столбцами, не существует.
Например: Если бы база данных выглядела так,
+-----------+-------------+------+
| sender_id | receiver_id | col3 |
+-----------+-------------+------+
| user 1 | user 2 | true |
+-----------+-------------+------+
Предположим, что пользователь 2 пытается установить НОВЫЕ отношения с пользователем 1. В этом случае база данных будет выглядеть так.
+-----------+-------------+------+
| sender_id | receiver_id | col3 |
+-----------+-------------+------+
| user 1 | user 2 | true |
+-----------+-------------+------+
| user 2 | user 1 | true |
+-----------+-------------+------+
Как я могу запретить пользователю вставлять новую строку, даже если в этом случае она технически отличается?
Что я пробовал Я попытался использовать оператор IF в сочетании с запросом SELECT, чтобы проверить, существуют ли какие-либо строки, соответствующие моим параметрам, и, если нет, вставить строку; однако он выдал ошибку SQL ..
Ограничения Я хочу использовать как можно меньше операторов SQL. Желательно, чтобы было отправлено только одно заявление.
Я новичок в SQL, и некоторое время ломал голову над этой проблемой. Если кто-нибудь из вас, умные люди, может помочь мне найти решение, я был бы бесконечно благодарен.
@ysth ... а затем, возможно, добавить уникальный ключ на (sender_id, receiver_id).
обратите внимание, что если вы отсортируете sender_id/receiver_id, чтобы избежать дублирования, то имена этих столбцов на самом деле не будут точными, и вы не будете знать, какой пользователь фактически инициировал отношения
Ответы 2
Выбор и вставка на основе результата выбора - это шаблон "проверить и сделать". Это открыто для условий гонки, если два пользователя пытаются выбрать и вставить одновременно. Они оба проверяют, видят, что строки нет, а затем оба пытаются вставить: у одного не получится.
Вы можете обойти это с помощью таких вещей, как транзакции и выбор для обновления, но это требует тщательного кодирования и блокировок, которые могут привести к взаимоблокировке.
Лучше «сделать, а потом проверить» за один шаг. Это «атомарное» означает, что это одна операция, которую нельзя прервать.
- Настройте ограничения, чтобы невозможно было добавить дубликат.
- Делай вставку.
- Если есть конфликт, обновите только 3-й столбец.
Есть несколько способов установить ограничение.
Самый простой и надежный способ — использовать два уникальных индекса, один на (sender_id, receiver_id) и один на (receiver_id, sender_id). Это охватывает оба случая. Это позволяет сохранить порядок отправителя/получателя.
Другой вариант — использовать один уникальный индекс для (sender_id, receiver_id) и добавить ограничение , требующее, чтобы sender_id всегда был меньше, чем Receive_id (или наоборот): check(sender_id < receiver_id). Это также гарантирует, что они не одинаковы. Затем в логике вставки измените порядок аргументов, чтобы пользователи всегда вставлялись в правильном порядке. Вы можете сделать это в операторе вставки, используя if () (проще), или вы можете использовать перед вставкой и перед триггерами обновления, чтобы автоматически выполнить переупорядочение (более надежно). Поскольку вы переупорядочиваете аргументы, уже неясно, кто был отправителем и получателем. Вы можете переименовать столбцы во что-то более общее, например user1 и user2.
Наконец, используйте при обновлении дубликатов ключа, чтобы установить 3-й столбец (если это то, что вы хотите).
insert into relationships (sender_id, receiver_id, col3) values
(
if ('user1' < 'user2', 'user1', 'user2'),
if ('user1' > 'user2', 'user1', 'user2'),
true
)
on duplicate key update col3=true
Добавьте производный столбец с уникальными ограничениями, который отслеживает уникальную комбинацию двух пользователей как «user1-user2», где user1 — это «самый низкий» пользователь, а user2 — «самый высокий»:
create table mytable (
sender_id text,
receiver_id text,
col3 text,
combo_key text not null
default concat(least(sender_id, receiver_id), '-', greatest(sender_id, receiver_id))
unique
)
Смотрите живую демонстрацию.
Попытка вставить логический дубликат вызовет исключение «дубликат записи».
Тире (может быть что угодно) добавляется между пользовательскими строками, чтобы отличать пару пользователей "abc" и "de" от "ab" и "cde".
Спасибо за ответ, я собираюсь использовать это решение. Однако в моем случае оба UUID гарантированно имеют одинаковую длину, поэтому в этом случае необходим ли разделитель между двумя идентификаторами в столбце combo_key?
@leo Нет проблем; мы здесь, чтобы помочь. Вы правы: разделитель не нужен.
поскольку кажется, что вам на самом деле все равно, какой sender_id и какой Receiver_id, самый простой способ сделать это — всегда сортировать перед выполнением вставки, чтобы sender_id был меньше, чем Receiver_id, и игнорировать вставку.