MySQL 存储过程错误 IF...THEN...END IF; 声明
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17544957/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL stored Procedure error with IF...THEN...END IF; statements
提问by Josh
I have a MySQL stored procedure that throws an error.
我有一个抛出错误的 MySQL 存储过程。
DELIMITER $$
DROP PROCEDURE IF EXISTS `test_schema`.`TEST_SPROC` $$
CREATE PROCEDURE `test_schema`.`TEST_SPROC` (IN var0 INT, var1 INT)
BEGIN
DECLARE var0 INT;
DECLARE var1 INT;
SELECT COUNT(*) INTO var0 FROM INFORMATION_SCHEMA.`TABLES`
WHERE `TABLE_SCHEMA`='test_schema' AND `TABLE_NAME`='original_table';
SELECT COUNT(*) INTO var1 FROM INFORMATION_SCHEMA.`COLUMNS`
WHERE `TABLE_SCHEMA`='test_schema' AND `TABLE_NAME`='new_table'
AND `COLUMN_NAME`='id';
IF var0=1 THEN
RENAME TABLE test_schema.original_table TO test_schema.new_table;
END IF;
IF var1=1 THEN
ALTER TABLE test_schema.new_table CHANGE id AccountID VARCHAR;
END IF; #error is thrown here.
END $$
DELIMITER ;
Error:
错误：
Script line: 4 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 IF;
END' at line 15
脚本行：4 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在 '; 附近使用的正确语法。万一;
END' 在第 15 行
What is wrong with my if statement?
我的 if 语句有什么问题？
采纳答案by Tom Mac
Change this line:
改变这一行：
ALTER TABLE test_schema.new_table CHANGE id AccountID VARCHAR;
to this:
对此：
ALTER TABLE test_schema.new_table CHANGE id AccountID VARCHAR(100);
Of course you should specify a length for the VARCHARcolumn that is appropriate. I've just used VARCHAR(100)as an example.
当然，您应该VARCHAR为合适的列指定一个长度。我只是VARCHAR(100)举个例子。
回答by Eric Leschinski
You have to specify a length for the alter table statement. See the comment below:
您必须为 alter table 语句指定长度。请参阅下面的评论：
IF varTable=1 THEN
RENAME TABLE test_schema.original_table TO test_schema.new_table;
SELECT COUNT(*) INTO varColumn FROM INFORMATION_SCHEMA.`COLUMNS`
WHERE `TABLE_SCHEMA`='test_schema' AND `TABLE_NAME`='new_table'
AND `COLUMN_NAME`='id';
IF varColumn=1 THEN
#The following statement must be "VARCHAR(255)" not just "VARCHAR"
ALTER TABLE test_schema.new_table CHANGE id AccountID VARCHAR(255);
ELSE
#statements.
END IF;
END IF;
