记录一次java+mysql创建存储过程得踩坑
mysql在navicat创建存储过程,需要使用DELIMITER 定义结束符,也就是,sql为:
DROP PROCEDURE IF EXISTS copyData;
DELIMITER $$
create procedure copyData()
begin
IF ( SELECT count( * ) FROM information_schema.TABLES WHERE table_name = 'student') = 0 THEN
CREATE TABLE student AS SELECT * FROM student_old;
END IF;
END $$
call copyData();
在Java中使用executeUpdate()方法中执行sql,需要进行将三条sql置为三个string类型字段分别执行,并且将DELIMITER删除,改为
create procedure copyData()
begin
IF ( SELECT count( * ) FROM information_schema.TABLES WHERE table_name = 'student') = 0 THEN
CREATE TABLE student AS SELECT * FROM student_old;
END IF;
END