主页 / dba / 问题 / 297365
Accepted
dierre
Asked: 2021-07-30 09:31:28 +0800 CST

如何在 mysql 脚本中转义单引号？

  • 772

我需要通过 mysql 脚本创建触发器

CREATE TRIGGER sync_new_oauth_sessions
    AFTER INSERT
    ON oauth_sessions FOR EACH ROW
BEGIN
    SET @s = 'INSERT INTO oauth_sessions_new(id, client_id, owner_type, owner_id, owner_id_new) VALUES (?, ?, ?, ?, CAST(NULLIF(?,'''') AS UNSIGNED))';
    -- INSERT INTO oauth_sessions_new(id, client_id, owner_type, owner_id, owner_id_new) VALUES (NEW.id, NEW.client_id, NEW.owner_type, NEW.owner_id, CAST(NULLIF(NEW.owner_id,'') AS UNSIGNED));
    PREPARE stmt2 FROM @s;
    SET @sid = NEW.id;
    SET @s_client_id = NEW.client_id;
    SET @s_owner_type = NEW.owner_type;
    SET @s_owner_id = NEW.owner_id;
    SET @s_owner_id_new = NEW.owner_id;
    EXECUTE stmt2 USING @sid, @s_client_id, @s_owner_type, @s_owner_id, @s_owner_id_new;
END

这是脚本的内容。我越来越： 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 5

我不明白我做错了什么。如果我这样做\'\'了，我也会遇到同样的问题。

谢谢

mysql mysql-5.7

1 个回答

  1. Best Answer

    正确的错误信息是

    Error Code: 1336. Dynamic SQL is not allowed in stored function or trigger

    SQL 准备语句（PREPARE、EXECUTE、DEALLOCATE PREPARE）可用于存储过程，但不能用于存储函数或触发器。因此，存储函数和触发器不能使用动态 SQL（您将语句构造为字符串然后执行它们）。

    见https://dev.mysql.com/doc/refman/8.0/en/stored-program-restrictions.html#stored-routines-trigger-restrictions

    利用

    DELIMITER //
CREATE TRIGGER sync_new_oauth_sessions
    AFTER INSERT
    ON oauth_sessions FOR EACH ROW
BEGIN

    INSERT INTO oauth_sessions_new(id, client_id, owner_type, owner_id, owner_id_new) VALUES (QUOTE(NEW.id), QUOTE(NEW.client_id), QUOTE(NEW.owner_type), QUOTE(NEW.owner_id), CAST(NULLIF(NEW.owner_id,'') AS UNSIGNED));
END
//
    • 2

相关问题