mysql的定界符是一对分号吗,为什么MySQL Workbench告诉我我需要分号？

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;

解决方案

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

So just put it this way:

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 ;

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.