mysql - 为什么 MySQL Workbench 告诉我我需要分号？

Question

此代码已通过多项在线验证测试。我不知道出了什么问题。在 CONCAT 函数之后，它说我需要一个分号，尽管那里已经有了分号。在 end 上，当它期望语句结束时，它表示它是无关输入。给了什么？

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;

Answer 1

我认为问题在于:您没有使用 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/en/stored-programs-defining.html

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.

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.