mysql - 创建一个 MySQL 存储过程来更新记录

Question

我正在转换所有现有的 MSSQL 数据库，存储过程卡在一个新的存储过程中，我需要在其中更新现有记录。一旦将记录插入数据库并成功发送电子邮件(或至少传递到 SMTP 服务器)，就会从 Web 表单调用该过程

我已经在 MSSQL 中使用了很长时间的工作程序，但我正在尝试将其转换为 MySQL。我传递了 3 个变量 - 一个指示电子邮件已发送的位，一个指示已使用哪个 SMTP 服务器发送电子邮件的字符串和一个唯一的记录 ID，以便我知道要更新的记录。我还将日期和时间添加到另一个字段以了解程序何时运行。

我有以下内容，但一直收到错误“#1064 - 你的 SQL 语法有错误；请查看与你的 MySQL 服务器版本对应的手册，了解在第 7 行的 '' 附近使用的正确语法 -但我在第 7 行没有看到任何异常 - 至少在我看来是这样。

我尝试使用的代码是:

CREATE PROCEDURE `sp_Test`(
  `emailSent_In` BIGINT UNSIGNED,
  `emailTransport_In` VARCHAR(100),
  `formSecret_In` VARCHAR(32)
)
BEGIN
  SET @`query` := CONCAT('UPDATE ',`tbl_JustSayThanks`,' 
  SET `emailSent` = `emailSent_In`,
  `emailTransport` = ',`emailTransport_In`,',
`emailSentDate` = NOW()
    WHERE `formSecret` = ', `formSecret_In`, '');
  PREPARE `stmt` FROM @`query`;
  EXECUTE `stmt`;
       @`query` := NULL;
  DEALLOCATE PREPARE `stmt`;
END//
DELIMITER ;

仅供引用，我使用的是基于我从 wchiquito 收到的先前答案的 CONCAT，最终将传递表名。但是，我想让它在去那里之前在简化的水平上工作。

Answer 1

以下是错误的:

SET @`query` := CONCAT('UPDATE ',`tbl_JustSayThanks`,' 

因为您似乎将 SQL 文本与 tbl_JustSayThanks 的值 连接在一起，但我认为您的意思是使用标识符本身。因此，这应该是:

SET @`query` := CONCAT('UPDATE `tbl_JustSayThanks`', 

以下是错误的:

`emailTransport` = ',`emailTransport_In`,',

因为变量是 VARCHAR，但您没有在 SQL 语句中将其作为字符串文字引用。很容易混淆多层次的引用。应该是:

`emailTransport` = ''', `emailTransport_In`, ''',

同理下面错误的是:

WHERE `formSecret` = ', `formSecret_In`, '');

应该是:

WHERE `formSecret` = ''', `formSecret_In`, '''');

这仍然会遇到 SQL 注入(inject)问题，除非您可以保证输入参数是安全的(这不是一个好的假设)。如果需要将值连接到 SQL 表达式中，则应使用 QUOTE() 函数进行转义:

SET @query = CONCAT('
  UPDATE tbl_JustSayThanks 
  SET emailSent = ', QUOTE(emailSent_In), '
      emailTransport = ', QUOTE(emailTransport_In), '
      emailSentDate = NOW()
  WHERE formSecret = ', QUOTE(formSecret_In));

更多评论:

• 您不需要用反引号分隔每个标识符，只需要那些与 SQL 保留字冲突或包含空格、标点符号或国际字符的标识符。您显示的所有标识符都不需要定界。
• 当您使用准备好的语句时，您应该使用带有 ? 占位符的查询参数，而不是将变量连接到 SQL 字符串中。您不在 SQL 查询中引用参数占位符。这样您就不会遇到像您发现的那些难以调试的语法错误。

这是一个显示修复的示例:

CREATE PROCEDURE sp_Test(
  emailSent_In BIGINT UNSIGNED,
  emailTransport_In VARCHAR(100),
  formSecret_In VARCHAR(32)
)
BEGIN
  SET @query = '
    UPDATE tbl_JustSayThanks
    SET emailSent = ?,
        emailTransport = ?,
        emailSentDate = NOW()
    WHERE formSecret = ?';
  SET @es = emailSent_In;
  SET @et = emailTransport_In;
  SET @fs = formSecret_In;
  PREPARE stmt FROM @query;
  EXECUTE stmt USING @es, @et, @fs;
  DEALLOCATE PREPARE stmt;
END//
DELIMITER ;

最后评论:

• 您的示例查询没有动态语法元素，只有动态值。所以你根本不需要使用准备好的语句。

这就是我真正编写程序的方式:

CREATE PROCEDURE sp_Test(
  emailSent_In BIGINT UNSIGNED,
  emailTransport_In VARCHAR(100),
  formSecret_In VARCHAR(32)
)
BEGIN
  UPDATE tbl_JustSayThanks
  SET emailSent = emailSent_In,
      emailTransport = emailTransport_In,
      emailSentDate = NOW()
  WHERE formSecret = formSecret_In;
END//
DELIMITER ;

您还应该知道，MySQL 存储过程远不如 Microsoft SQL Server。 MySQL 不保留已编译的存储过程，它不支持包，它没有调试器……我建议你不要使用 MySQL 存储过程。请改用应用程序代码。