将分隔符与 Aurora Serverless MySQL 5.6 结合使用时出现语法错误 [英] Syntax error when using Delimiters with Aurora Serverless MySQL 5.6

问题描述

我正在使用 Aurora Serverless MySQL 5.6 创建以下触发器，当数据插入另一个表但收到语法错误时，该触发器将更新一个表，特别是围绕 Delimiter 关键字.

I'm using Aurora Serverless MySQL 5.6 to create the following trigger which will update one table when data is inserted into another table but am receiving syntax errors, specifically around the Delimiter keyword.

DELIMITER $$
CREATE TRIGGER Create_Media_Like_Trigger AFTER INSERT ON MediaLike
FOR EACH ROW
BEGIN 
    IF NEW.likeType = 'LIKE' THEN
        UPDATE Media 
        SET Media.numLikes = Media.numLikes + 1 
        WHERE Media.mediaId = NEW.mediaId;
    ELSEIF NEW.likeType = 'DISLIKE' THEN
        UPDATE Media 
        SET Media.numLikes = Media.numLikes - 1 
        WHERE Media.mediaId = NEW.mediaId;
    ENDIF;
END $$
DELIMITER ;

就像我上面说的，我在 DELIMITER 附近收到语法错误，这个问题是 AWS 特有的，我该如何解决?

Like I said above, I am receiving syntax errors near DELIMITER, is this issue AWS specific and how can I fix it?

带有错误消息的更新:

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 'DELIMITER $$ CREATE TRIGGER Create_Media_Like_Trigger 
AFTER INSERT ON MediaLike ' at line 1

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 'ELSEIF NEW.likeType = 'DISLIKE' THEN UPDATE Media SET 
Media.numLikes = Media.num' at line 1

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 'END $$' at line 1

推荐答案

我花了一天时间试图解决这个问题，所以希望这能帮助那里的人...

I spent a day trying to figure this out so hopefully this helps someone out there...

DELIMITER 是客户端的特性，而不是 MySQL 服务器的特性.RDS 查询编辑器是一个客户端，但它不支持更改分隔符，因此尝试运行您提供的脚本将不起作用，因为它第一次看到分号时会将其解释为命令的结尾并失败语法错误.

DELIMITER is a feature of the client, not the MySQL server. The RDS Query Editor is a client but it does not support changing the delimiter so attempting to run the script you've provided won't work since the first time it sees a semicolon it will interpret that as the end of the command and fail with a syntax error.

那么，如何创建类似存储过程的内容，其中包含多个语句和分号?您必须将其创建为 .sql 文件，并使用来自 Lambda 函数或 CLI 的数据 API 发送它.

So, how do you create something like a stored procedure that has multiple statements and semicolons in it? You must create it as an .sql file and send it using the Data API from either a Lambda function or the CLI.

首先，在没有任何 DELIMITER 命令或备用分隔符的 .sql 文件中创建您的脚本.

First, create your script in a .sql file without any DELIMITER commands or alternate delimiters.

例如:function.sql

CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
   SELECT COUNT(*) INTO param1 FROM t;
END

然后，像这样使用 CLI 运行脚本:

Then, run the script using the CLI like this:

cat function.sql | xargs -0 aws rds-data execute-statement \
    --resource-arn arn:aws:rds:eu-west-1:xxx:cluster:cluster-name \
    --secret-arn arn:aws:secretsmanager:eu-west-1:xxx:secret:secret-name-xxx \
    --database "database_name" \
    --sql

或者，您可以创建一个 Lambda 函数来读取文件并使用 rds_client.execute_statement() 通过数据 API 将脚本发送到服务器.但同样，不要使用 DELIMITER 语句.服务器看到 BEGIN 和 END 行并相应地采取行动，而无需更改分隔符.

Alternatively, you can create a Lambda function that reads the file and uses rds_client.execute_statement() to send the script to the server via the Data API. But again, do NOT use the DELIMITER statement. The server sees the BEGIN and END lines and acts accordingly without the need to change the delimiter.

这篇关于将分隔符与 Aurora Serverless MySQL 5.6 结合使用时出现语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！