56,683
社区成员
发帖
与我相关
我的任务
分享
delimiter //
DROP PROCEDURE IF EXISTS mockData//
CREATE PROCEDURE mockData(IN tableName VARCHAR(20), IN rowCount INT)
BEGIN
DECLARE insertCount INT;
SET insertCount = 0;
DROP TABLE IF EXISTS tableName;
CREATE TABLE tableName (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
`age` TINYINT(3) NOT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`),
KEY `age` (`age`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
start transaction;
loop_handler : LOOP
INSERT INTO tableName(`name`,`age`) VALUES( rand_string(20), ceil(rand()*110) );
set insertCount = insertCount + 1;
if (insertCount >= rowCount) then
leave loop_handler;
end if;
end LOOP loop_handler;
commit;
END;//
delimiter ;
delimiter //
DROP PROCEDURE IF EXISTS delTable//
CREATE PROCEDURE delTable(IN tableName VARCHAR(20))
BEGIN
SET @tbName = tableName;
PREPARE dropTable FROM "DROP TABLE IF EXISTS ?";
EXECUTE dropTable USING @tbName;
END;//
delimiter ;
然后call delTable('test')就报错如下:
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
========
我的整个代码是这样的,但不可用:
delimiter //
DROP PROCEDURE IF EXISTS mockData//
CREATE PROCEDURE mockData(IN tableName VARCHAR(20), IN rowCount INT)
BEGIN
DECLARE insertCount INT;
SET insertCount = 0;
set @tbName = tableName;
PREPARE dropTable FROM "DROP TABLE IF EXISTS ?";
EXECUTE dropTable USING @tbName;
PREPARE createTable FROM "
CREATE TABLE ? (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
`age` TINYINT(3) NOT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`),
KEY `age` (`age`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1";
EXECUTE dropTable USING @tbName;
start transaction;
PREPARE insertRecord FROM "INSERT INTO ? (`name`,`age`) VALUES( rand_string(20), ceil(rand()*110) )";
loop_handler : LOOP
EXECUTE insertRecord USING @tbName;
set insertCount = insertCount + 1;
if (insertCount >= rowCount) then
leave loop_handler;
end if;
end LOOP loop_handler;
commit;
END;//
delimiter ;