Вставить n строк с LAST_INSERT_ID

У меня есть 3 таблицы с именами СООБЩЕНИЯ, ХЭШТЕГИ и POSTS_HASHTAGS_RELATION, как показано ниже.

CREATE TABLE POSTS(
  post_id int unsigned NOT NULL AUTO_INCREMENT,
  content varchar(200) NOT NULL,
  PRIMARY KEY (post_id)
);

CREATE TABLE HASHTAGS(
  hashtag_id int unsigned NOT NULL AUTO_INCREMENT,
  hashtag varchar(40) NOT NULL,
  PRIMARY KEY (hashtag_id)
);

CREATE TABLE POSTS_HASHTAGS_RELATION(
  post_id int unsigned NOT NULL,
  hashtag_id int unsigned NOT NULL,
  PRIMARY KEY (post_id, hashtag_id)
);

Когда пользователь публикует сообщения, он выбирает до 20 хэштегов из сохраненных в ХЭШТЕГИ. Я отправляю hashtag_id(s) этих хэштегов из внешнего интерфейса в виде строки, разделенной запятыми, в бэкэнд, где он преобразуется в список в nodejs.

Во-первых, есть ли лучший подход к структурированию этого?

Во-вторых, как мне вставить переменное количество строк в POSTS_HASHTAGS_RELATION в одном запросе?

INSERT INTO POSTS (content) VALUES ('bla bla bla bla');
SET @post_id = LAST_INSERT_ID();
INSERT INTO POSTS_HASHTAGS_RELATION (post_id, hashtag_id) VALUES (@post_id, 19), (@post_id, 41) ...;
// Something like this but the number of values can be between 1 and 20

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

?? Почему вы пропускаете определения FOREIGN KEY из структур таблиц?

Akina 10.04.2022 19:13

Просто чтобы вопрос был короче. Внешние ключи и индексы есть.

longhorn 10.04.2022 19:15
Просто чтобы вопрос был короче. .. и менее актуально. Предположим, что hashtag_id(s) доступны в виде списка в nodejs. Вам нужно решение на стороне SQL, не так ли? но SQL ничего не знает о списках nodejs... вы должны указать, в каком формате SQL-сервер будет получать эти данные. CSV? JSON? что-то другое?
Akina 10.04.2022 19:29

Хэштеги уже хранятся в таблице mysql. Когда пользователь публикует сообщения, он просто выбирает из существующих хэштегов. Они могут выбрать от 1 до 20 хэштегов. Я отправляю hashtag_id (и) этих хэштегов из моего интерфейса в виде строки, разделенной запятыми, на сервер, где я конвертирую строку в список в nodejs. Теперь моя проблема заключается в том, что, поскольку количество строк, которые нужно ввести в POSTS_HASHTAGS_RELATION, не фиксировано, как мне записать его в запросе.

longhorn 10.04.2022 19:45
Я отправляю hashtag_id (и) этих хэштегов из моего интерфейса в виде строки, разделенной запятыми, на сервер, где я конвертирую строку в список в nodejs. Не преобразовывать CSV, предоставлять его как есть, анализировать и сохранять на стороне MySQL. Рекомендуемый формат сохранения - хранимая процедура.
Akina 10.04.2022 20:15
Формы c голосовым вводом в React с помощью Speechly
Формы c голосовым вводом в React с помощью Speechly
Пытались ли вы когда-нибудь заполнить веб-форму в области электронной коммерции, которая требует много кликов и выбора? Вас попросят заполнить дату,...
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Будучи разработчиком веб-приложений, легко впасть в заблуждение, считая, что приложение без JavaScript не имеет права на жизнь. Нам становится удобно...
Flatpickr: простой модуль календаря для вашего приложения на React
Flatpickr: простой модуль календаря для вашего приложения на React
Если вы ищете пакет для быстрой интеграции календаря с выбором даты в ваше приложения, то библиотека Flatpickr отлично справится с этой задачей....
В чем разница между Promise и Observable?
В чем разница между Promise и Observable?
Разберитесь в этом вопросе, и вы значительно повысите уровень своей компетенции.
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Клиент для URL-адресов, cURL, позволяет взаимодействовать с множеством различных серверов по множеству различных протоколов с синтаксисом URL.
Четыре эффективных способа центрирования блочных элементов в CSS
Четыре эффективных способа центрирования блочных элементов в CSS
У каждого из нас бывали случаи, когда нам нужно отцентрировать блочный элемент, но мы не знаем, как это сделать. Даже если мы реализуем какой-то...
0
5
17
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

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

То, что вы показываете, это то, как я бы это сделал. Вы можете вставить несколько строк, используя INSERT, как показано, написав несколько конструкторов строк после ключевого слова VALUES. Если вы это сделаете, вы должны включить значение для всех столбцов, названных в вашем операторе INSERT, в каждый конструктор строк. Поэтому вы должны ссылаться на переменную @post_id в каждом конструкторе строк.

Если вам действительно не нравится писать @post_id более одного раза, вы можете сделать что-то вроде этого:

INSERT INTO POSTS_HASHTAGS_RELATION (post_id, hashtag_id) 
 SELECT @post_id, hashtag_id FROM (
  SELECT 19 AS hashtag_id UNION SELECT 41 UNION SELECT 42 UNION ...
 ) AS t;

Но это кажется меньше ясным и читаемым, чем то, как вы это делали.


Повторите ваш комментарий:

Я не программист node.js, но я использовал эту технику на других языках для создания оператора SQL с несколькими конструкторами строк на основе входного списка. Правильные параметры запроса можно использовать только вместо скалярных значений, а не списков, выражений, идентификаторов, ключевых слов SQL и т. д. Но я понимаю, что node.js выполняет дополнительную магию подстановки строк, поэтому на самом деле они не выполняют параметры запроса.

Предположим, вы только что выполнили INSERT в POSTS, вы можете зафиксировать последний идентификатор вставки:

var postId = result.insertId;

Затем создайте частичный оператор INSERT для следующей вставки:

insert = 'INSERT INTO POSTS_HASHTAGS_RELATION (post_id, hashtag_id) VALUES';

Вам понадобится массив для конструкторов строк и массив для параметров:

let rowConstructors = [];
let parameters = [];

hashtags.forEach(function (hashtag) {
  rowConstructors.push('(?, ?)');
  parameters.concat(postId, hashtag);
});

Теперь у вас есть массив конструкторов строк, который toString() превратится в строку, разделенную запятыми. И у вас есть массив значений для передачи в качестве параметров.

connection.query(insert + rowConstructors.toString(), 
  parameters, function (error, results, fields) {
  if (error) throw error;
  // ...
});

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

Опять же, я не программист node.js, поэтому простите меня, если есть ошибки или проблемы со стилем. Но это должно дать вам представление об этой технике.

Спасибо за ответ Билл. Я могу ссылаться на @post_id несколько раз, но количество значений, которые должны быть вставлены в POSTS_HASHTAGS_RELATION, не является фиксированным. Пользователь может выбрать только 1 хэштег или 20. Как это учесть в запросе?

longhorn 10.04.2022 19:01

Кстати, я использую nodejs. Я пытался использовать VALUES ? и построение строки с использованием цикла for, но mysql выдает ошибку.

longhorn 10.04.2022 19:05

Да, узнал об этом после суда. Вариант использования, который я упомянул, не должен быть таким уж необычным, но я не могу найти ничего полезного.

longhorn 10.04.2022 19:37

Смотрите обновление к моему ответу выше.

Bill Karwin 10.04.2022 19:59

Я думаю, что это сработает, но при этом мы правильно разделим запрос на 2 части. В нашем фактическом запросе есть еще несколько шагов, и он выполняется как транзакция. Как это можно сделать в одном запросе? В любом случае ценю ваш подробный ответ.

longhorn 10.04.2022 20:17

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

Bill Karwin 10.04.2022 20:20

Я нашел пример двух вставок в одной транзакции: github.com/mysqljs/mysql#транзакции

Bill Karwin 10.04.2022 20:28

В тот момент, когда вы разместили свой предпоследний комментарий, у меня загорелась лампочка. Почему-то это просто вылетело из головы. Спасибо Билл.

longhorn 10.04.2022 20:48

Сохранение данных в таблицы в формате SP:

CREATE PROCEDURE save_post_and_tags (post_content VARCHAR(200), hashtag_ids TEXT)
BEGIN
    DECLARE post_id INT UNSIGNED;
    DECLARE tag_id INT UNSIGNED;
    INSERT INTO posts VALUES (DEFAULT, post_content);
    SET post_id := LAST_INSERT_ID();
    SET hashtag_ids := concat(hashtag_ids, ',');
    REPEAT
        SET tag_id := 0 + SUBSTRING_INDEX(hashtag_ids, ',', 1);
        SET hashtag_ids := TRIM(SUBSTRING(hashtag_ids FROM 1 + LOCATE(',', hashtag_ids)));
        INSERT INTO posts_hashtags_relation VALUES (post_id, tag_id);
    UNTIL hashtag_ids = '' END REPEAT;
END

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a9c622ea7ad2dd48dba18312c7a33487 (небрежный CSV используется намеренно).

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