Оптимизация вставки данных в связанные таблицы MySQL: единая команда для `tmessages` и `tmessage_recipients`

Delphi , Базы данных , SQL

Оптимизация вставки данных в связанные таблицы MySQL: единая команда для tmessages и tmessage_recipients

В данной статье рассматривается оптимизация процесса вставки данных в пару связанных таблиц MySQL. Примером таких таблиц могут служить tmessages, которая является главной (master) и содержит информацию о сообщениях, и tmessage_recipients, которая является детальной (detail) и содержит информацию о получателях сообщений.

Исходная проблема

Разработчик столкнулся с необходимостью оптимизации процесса вставки данных в пару связанных таблиц MySQL. В его проекте используется модуль для обмена сообщениями между пользователями. Для хранения информации о сообщениях используется таблица tmessages, а для информации о получателях — таблица tmessage_recipients. Текущий подход подразумевает отдельную вставку данных в главную таблицу, получение идентификатора вставленной записи, и последующую вставку данных в детальную таблицу с использованием этого идентификатора.

CREATE TABLE `tmessages` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `header` varchar(100) DEFAULT NULL,
  `body` text,
  ...
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `tmessage_recipients` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `idmessage` int(10) unsigned NOT NULL DEFAULT '0',
  `idstuff_recipient` int(10) unsigned NOT NULL DEFAULT '0',
  ...
  PRIMARY KEY (`id`, `idmessage`, `idstuff_recipient`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Текущий код для вставки записей выглядит следующим образом:

var
  s: string;
  idmessage: longword;
  i: Word;
...
  s := 'INSERT INTO `tmessages` SET ' + '`header`=' + QuotedStr(Edit12.Text) +
    ', ' + '`body`=' + QuotedStr(Memo3.Text);
  Hdm1.FDConnection1.ExecSQL(s);
  idmessage := Hdm1.FDConnection1.ExecSQLScalar('SELECT `id` FROM `tmessages` '
    + 'WHERE `header`=' + QuotedStr(Edit12.Text) + 'AND `body`=' +
    QuotedStr(Memo3.Text) + ' ORDER BY `id` DESC LIMIT 1');
  for i := 0 to ListBox3.Count - 1 do
  begin
    s := 'INSERT INTO `tmessage_recipients` SET ' + '`idmessage`=' +
      inttostr(idmessage) + ', ' + '`idstuff_recipient`=' +
      inttostr(ListBox3.ItemByIndex(i).Tag);
    Hdm1.FDConnection1.ExecSQL(s);
  end;

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

Оптимизация процесса

Для оптимизации процесса вставки данных можно использовать переменные MySQL и выполнить все вставки в одной транзакции. Это позволит избежать необходимости предварительного получения идентификатора и повысить надежность операции.

Пример кода, использующего переменные MySQL для получения идентификатора последней вставленной записи:

s:= 'INSERT INTO `tmessages` (`header`,`body`) VALUES(:p1, :p2);'#13#10 +
  'SET @last_id := (SELECT LAST_INSERT_ID());';
for i := 0 to ListBox3.Count - 1 do
  s := s + #13#10'INSERT INTO `tmessage_recipients` (`idmessage`,`idstuff_recipient`)'
    + ' VALUES(@last_id,' + inttostr(ListBox3.ItemByIndex(i).Tag) + ');';

Для обеспечения целостности данных рекомендуется использовать транзакции. Пример использования транзакции с TFDTransaction:

HDM1.FDQViaFDTransaction.Transaction.StartTransaction;
try
  Hdm1.FDQViaFDTransaction.ExecSQL(s, [Edit12.Text, Memo3.Text]);
  Hdm1.FDQViaFDTransaction.Transaction.Commit;
except
  Hdm1.FDQViaFDTransaction.Transaction.Rollback;
end;

Также стоит отметить, что для предотвращения SQL-инъекций следует использовать параметризованные запросы.

Заключение

Оптимизация процесса вставки данных в связанные таблицы MySQL позволяет уменьшить количество обращений к базе данных и повысить надежность транзакций. Использование переменных MySQL и транзакций является ключевым моментом в данном процессе. Следует также помнить о важности параметризации запросов для предотвращения SQL-инъекций.

Создано по материалам из источника по ссылке.

Статья посвящена оптимизации вставки данных в связанные таблицы MySQL, используя единую команду для `tmessages` и `tmessage_recipients` с применением переменных MySQL и транзакций для повышения эффективности и надежности операций.

Комментарии и вопросы

Получайте свежие новости и обновления по Object Pascal, Delphi и Lazarus прямо в свой смартфон. Подпишитесь на наш Telegram-канал delphi_kansoftware и будьте в курсе последних тенденций в разработке под Linux, Windows, Android и iOS

:: Главная :: SQL ::