INSERT из SELECT, делающий пару столбцов уникальной

Question

INSERT из SELECT, делающий пару столбцов уникальной

У меня есть таблица (упрощенно, например, здесь):

CREATE TABLE msg
(
msg_id VARCHAR(30) NOT NULL,
user_id BIGINT NOT NULL,
message TEXT,

UNIQUE INDEX msg_index (msg_id, user_id);
);

Я разрешаю пользователю добавлять новые сообщения с помощью простой INSERT INTO, как в:

INSERT INTO msg (msg_id, user_id, message)
VALUES ($_POST["id"], $user["id"], $_POST["message"]);

Как только сообщение существует, я разрешаю пользователю создать дублировать. Утверждение довольно простое, я могу сделать это:

INSERT INTO msg (msg_id, user_id, message)
SELECT CONCAT(msg_id, "_Copy") AS msg_id, user_id, message
WHERE msg_id = $_POST["id"];

Это отлично работает в первый раз, только если пользователь попробует дублировать введите команду снова для того же сообщения, затем произойдет сбой из-за UNIQUE INDEX сдерживать.

Есть ли способ изменить CONTACT() оператор для создания уникального идентификатора (то есть «_Copy1», «_Copy2», …) или мне нужно проверить, является ли INSERT успешно, а если нет, попробуйте еще раз со следующей возможной записью? (Я знаю, как это сделать, мне просто интересно, предлагает ли MySQL невероятные функции, такие как автоматическое создание набора столбцов, например, волшебство!)

2

mysql php unique-constraint

Решение

Другие решения

Можно, но это подвергается гоночным условиям. Таким образом, он может работать почти все время, но не под большими нагрузками:

INSERT INTO msg (msg_id, user_id, message)
SELECT (CASE WHEN COUNT(*) = 0 THEN v_msg_id
ELSE CONCAT_WS('_', SUBSTRING_INDEX(v_msg_id, '_', 1), COUNT(*) + 1) AS msg_id,
v_user_id, v_message
FROM msg
WHERE msg_id = v_msg_id;

Примечание: это предполагает, что msg_id не имеет подчеркивания Легко настроить логику, если это возможно.

Более безопасной альтернативой будет добавление UUID до конца. Однако это довольно уродливо.

1

Поскольку «msg_id» является строкой, я, вероятно, попытался бы поиграть с метками времени в миллисекундах, так что вы получаете из коробки метки времени, производительность (не нужно искать все предыдущие копии) и уникальность «более или менее», которая будет быть нарушенным, если вы превышаете скорость 1000 копий сообщений в 1 секунду.

INSERT INTO msg (msg_id, user_id, message)
SELECT CONCAT(msg_id, '_', ROUND(UNIX_TIMESTAMP(CURTIME(4)) * 1000)) AS msg_id,
user_id,
message
WHERE msg_id = $_POST['id'];

Заметка

Этот синтаксис msg_id = $_POST['id']; действительно уязвим из-за возможных инъекций SQL. Я бы рекомендовал использовать заполнители и не передавать ввод непосредственно в запросе.

1

Источник

Accepted Answer

Вы всегда можете использовать RAND() функция для создания случайного числа (и вы можете искать SO относительно того, как генерировать случайные строки в MySQL), но я действительно не думаю, что это хорошее решение.

Ваш идентификатор должен быть уникальным и, вероятно, автоматически увеличенным.
Было бы намного лучше добавить новый столбец, возможно, «source_msg», в котором будет сохранен идентификатор исходного сообщения (таким образом, вы всегда можете узнать, из какого исходного сообщения пользователь продублировал текущее сообщение).

В случае, если это «оригинальное» сообщение — просто поставьте 0.

Структура таблицы:

CREATE TABLE msg
(
msg_id int NOT NULL AUTO_INCREMENT,
source_msg INT NOT NULL,
user_id BIGINT NOT NULL,
message TEXT,
PRIMARY KEY (ID),
UNIQUE INDEX msg_index (msg_id, user_id);
);

Новое сообщение:

INSERT INTO msg (msg_id, source_msg, user_id, message)
VALUES (NULL, 0, $user["id"], $_POST["message"]);

Дубликат сообщения:

INSERT INTO msg (msg_id, source_msg, user_id, message)
SELECT null, msg_id, user_id, message;

Безотносительно — читайте о столах Бобби.

1