创建一个 MySQL 存储过程来更新记录 [英] Creating a MySQL stored procedure to update records

问题描述

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

I'm converting all of my existing MSSQL databases and stored procedures am stuck on a new stored procedure where I need to update an existing record. The procedure gets called from a web form once a record has been inserted into the database and en email sent successfully (or at least passed off to the SMTP server)

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

I've had a working procedure in MSSQL for a long time but am trying to convert it to MySQL. I'm passing in 3 variables - a bit indicating the email got sent, a string indicating which SMTP server has been used to sent the email and a unique record id so I'll know what record to update. I'm also adding the date and time to another field to know when the procedure ran.

我有以下内容，但不断收到错误消息#1064 - 您的 SQL 语法有错误；请查看与您的 MySQL 服务器版本相对应的手册，以了解在第 7 行的 '' 附近使用的正确语法 -但我在第 7 行看不到任何东西 - 至少在我看来是这样.

I've got the following but keep getting an error "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 7 - yet I don't see anything off at line 7 - at least to my eye.

我尝试使用的代码是:

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，最终将传入表名.但是，在去那里之前，我想让它在简化的级别上工作.

Just FYI, I'm using the CONCAT based on a previous answer I received from wchiquito and will be passing in the table name eventually. But, I wanted to get it to work on a simplified level before going there.

推荐答案

以下错误:

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

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

because you seem to be concatenating your SQL text with the value of tbl_JustSayThanks, but I think you mean to use the identifier itself. This should therefore be:

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

以下错误:

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

因为该变量是一个 VARCHAR，但您没有在 SQL 语句中将其作为字符串文字引用.很容易与多个级别的引用混淆.应该是:

because the variable is a VARCHAR but you don't quote it as a string literal in your SQL statement. It's easy to get mixed up with the multiple levels of quoting. It should be:

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

出于同样的原因，以下是错误的:

The following is wrong for the same reason:

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

应该是:

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

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

This still suffers from SQL injection problems, unless you can guarantee that the input parameters are safe (which is not a good assumption). If you need to concatenate values into your SQL expressions, you should use the QUOTE() function to do escaping:

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 查询中引用参数占位符.这样您就不会遇到像您发现的那样难以调试的语法错误.

• You don't need to delimit every identifier with back-ticks, only those that conflict with SQL reserved words, or contain whitespace or punctuation or international characters. None of your identifiers you show require delimiting.
• When you use prepared statements, you should use query parameters with the ? placeholders, intead of concatenating variables into the SQL string. You don't quote parameter placeholders in your SQL query. That way you won't run into hard-to-debug syntax errors like the ones you found.

以下是显示修复的示例:

Here's an example showing the fixes:

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 ;

最后评论:

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

• Your example query has no dynamic syntax elements, only dynamic values. So you don't need to use a prepared statement at all.

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

This is how I'd really write the procedure:

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 存储过程.改用应用程序代码.

You should also be aware that MySQL stored procedures are greatly inferior to Microsoft SQL Server. MySQL doesn't keep compiled stored procedures, it doesn't support packages, it doesn't have a debugger... I recommend you do not use MySQL stored procedures. Use application code instead.

这篇关于创建一个 MySQL 存储过程来更新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！