我想删除并创建一个在存储过程参数中传递了名称的表。我编写了以下存储过程。
CREATE DATABASE db1;
USE db1;
DROP PROCEDURE IF EXISTS drop_create;
DELIMITER $$
CREATE PROCEDURE drop_create(IN tbname VARCHAR(15))
BEGIN
SET @droptable = CONCAT ("DROP TABLE IF EXISTS ", tbname);
SET @createtable = CONCAT("CREATE TABLE ", tbname, " (c0 INT PRIMARY KEY, c1 VARCHAR(16000)" );
PREPARE deletetb FROM @droptable;
PREPARE createtb FROM @createtable ;
EXECUTE deletetb ;
EXECUTE createtb;
DEALLOCATE PREPARE createtb ;
DEALLOCATE PREPARE deletetb ;
END $$
DELIMITER ;
当我执行它时，我会得到以下错误。
Call db1.drop_create('abd');
ERROR 1064 (42000): 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 '' at line 1
显然，我无法在上面的SP中找到语法错误。我做错什么了？创建表相关语句时一定有问题，因为当我注释第8、10、12和13行时，SP工作正常。我正在使用Mysql 5.6
最佳答案
哎呀！以下语句中缺少右大括号：
SET @createtable = CONCAT("CREATE TABLE ", tbname, " (c0 INT PRIMARY KEY, c1 VARCHAR(16000)" );
应该是：
SET @createtable = CONCAT("CREATE TABLE ", tbname, " (c0 INT PRIMARY KEY, c1 VARCHAR(16000))" );
^