为什么MySQL Workbench告诉我我需要分号? [英] Why is MySQL Workbench telling me I need a semicolon?

问题描述

此代码已通过几次在线验证测试.我不知道怎么了在CONCAT函数之后，它说我需要一个分号，尽管那里已经有一个分号了.在end上，它表示在期望语句结束时是多余的输入.有什么作用?

This code has passed several online validation tests. I don't know what is wrong. After the CONCAT function, it says I need a semicolon, though there already is one there. And on the end it says it is extraneous input when it is expecting an end of statement. What gives?

create procedure AddColumnUnlessExists(
    IN dbName tinytext,
    IN tableName tinytext,
    IN fieldName tinytext,
    IN fieldDef text)
begin
    IF NOT EXISTS (
        SELECT * FROM information_schema.COLUMNS
        WHERE column_name=fieldName
        and table_name=tableName
        and table_schema=dbName
        )
    THEN
        set @ddl = CONCAT('ALTER TABLE ', dbName, '.', tableName, ' ADD COLUMN ', fieldName, ' ', fieldDef);
        prepare stmt from @ddl;
        execute stmt;
    END IF;
end;

推荐答案

我认为问题是:您没有使用DELIMITER.

I think the problem is : you are not using DELIMITER.

所以就这样说吧:

DELIMITER //
create procedure AddColumnUnlessExists(
    IN dbName tinytext,
    IN tableName tinytext,
    IN fieldName tinytext,
    IN fieldDef text)
begin
    IF NOT EXISTS (
        SELECT * FROM information_schema.COLUMNS
        WHERE column_name=fieldName
        and table_name=tableName
        and table_schema=dbName
        )
    THEN
        set @ddl = CONCAT('ALTER TABLE ', dbName, '.', tableName, ' ADD COLUMN ', fieldName, ' ', fieldDef);
        prepare stmt from @ddl;
        execute stmt;
    END IF;
end //
DELIMITER ;

编辑 https: //dev.mysql.com/doc/refman/5.7/zh-CN/stored-programs-defining.html

如果使用mysql客户端程序定义包含分号字符的存储程序，则会出现问题.默认情况下，mysql 本身将分号识别为语句定界符，因此您必须 临时重新定义定界符以使mysql传递整个 将程序定义存储到服务器.

If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.

要重新定义mysql分隔符，请使用delimiter命令.这 下面的示例演示如何针对dorepeat()过程执行此操作 刚刚显示.分隔符更改为//以启用整个 定义作为单个语句传递给服务器，然后 恢复到;在调用该程序之前.这将启用; 过程主体中使用的定界符，该定界符将传递给 服务器，而不是由mysql本身解释.

To redefine the mysql delimiter, use the delimiter command. The following example shows how to do this for the dorepeat() procedure just shown. The delimiter is changed to // to enable the entire definition to be passed to the server as a single statement, and then restored to ; before invoking the procedure. This enables the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql itself.

这篇关于为什么MySQL Workbench告诉我我需要分号?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！