MySQL : How to Create table dynamically in stored procedure?

Question

我想删除并创建一个表，其名称在存储过程参数中传递。我编写了以下存储过程。

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 中找到语法错​​误。我究竟做错了什么？问题一定出在 CREATE TABLE 相关语句中，因为当我注释第 8、10、12 和 13 行时，SP 工作正常。我正在使用 Mysql 5.6

Answer 1

糟糕!以下语句中缺少右括号:

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))" );
                                                                                           ^