我的项目中有一个单元执行用户之间的消息交换。为此，我使用了两个 MySQL 表:

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;

但我认为这不是最佳选择，因为我必须先获取 idmessage。如何优化程序并使其更可靠？是否可以在 Hdm1.FDConnection1.ExecSQL() 的一次调用中完成插入？

编辑

根据@Alex Tartan 和@whosrdaddy 的评论，我得出了这样的结论。这是最优方法吗？或者我需要改变什么？

  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) + ');';
  Hdm1.FDConnection1.ExecSQL(s, [Edit12.Text, Memo3.Text]);

编辑2

在@kobik 的提议下，我得出了以下结论:

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

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

编辑3

在@Rick-James 的宝贵建议下，代码被转换为以下...

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

  Hdm1.FDQViaFDTransaction.Transaction.StartTransaction;
  try
    Hdm1.FDQViaFDTransaction.ExecSQL(s, [Edit12.Text, Memo3.Text]);
    Hdm1.FDQViaFDTransaction.Transaction.Commit;
    ShowMessage('Message sent.');
    Button10Click(self);
  except
    Hdm1.FDQViaFDTransaction.Transaction.Rollback;
  end;

最佳答案

为了提高效率，使用多行插入:

INSERT INTO table2 
    (id, x)
    VALUES
    (@last_id,"newVal_1"),
    (@last_id,"newVal_2"),
    (@last_id,"newVal_3");

关于mysql - 在 MySQL 主从表对中插入记录的最佳方式，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/31935540/