MySQL中的 DELIMITER $$

最新推荐文章于 2024-10-30 19:08:52 发布

cpcmq9810

最新推荐文章于 2024-10-30 19:08:52 发布

阅读量3.9k

点赞数 2

文章标签：数据库

开发给了1个存储过程的脚本，说是从其他库上导出来的，现在创建报错：

check the manual that corresponds to your MySQL server version for the right syntax to use near \'\' at line 3

在本地测试，source 2.sql ,发现全是这些莫名其妙的错误。发现是脚本中有多行，以分号结尾。MySQL默认的行分隔符是分号，遇到分号，就执行前面一段语句。

脚本中有多行，我们希望不按照分号来一句一句执行，希望一段代码作为一个整体执行。
在脚本最前面加上 delimiter $$

执行成功。

点击(此处)折叠或打开

DELIMITER $$
CREATE PROCEDURE `test`(in iv_date varchar(8),in iv_table_name varchar(50))
begin
declare vi_task_id int;
declare vv_task_name varchar(50);
declare vv_table_name varchar(50);
declare vv_task_pos varchar(50);
declare vv_sql varchar(500);
declare vi_result int;
declare vi_chk_flag int;
declare vi_data_count int;
declare vv_partition_name varchar(30);
/** * @description 初始化分区名 */
set vv_partition_name = upper(concat(\'p_\',iv_date));
/** * @description 查询当前周期对应表分区是否有创建 */
select count(1) into vi_chk_flag from information_schema.partitions ta where ta.table_name = upper(iv_table_name) and ta.partition_name = vv_partition_name;
/*select vi_chk_flag;*/
/** * @description 如果分区已经存在 */
if vi_chk_flag > 0 then
select table_rows into vi_data_count from information_schema.partitions ta where ta.table_name = upper(iv_table_name) and ta.partition_name = vv_partition_name;
if vi_data_count > 0 then
set @vv_sql = concat(\'alter table \', upper(iv_table_name), \' truncate partition \', upper(vv_partition_name));
prepare stmt from @vv_sql;
execute stmt;
deallocate prepare stmt;
commit;
end if;
else
/** * @description 新建分区 */
set @vv_sql = concat(\'alter table \', upper(iv_table_name), \' add partition (partition \', upper(vv_partition_name), \' values in (\', iv_date, \'))\');
/*select @vv_sql;*/
prepare stmt from @vv_sql;
execute stmt;
deallocate prepare stmt;
commit;
end if;
/** end */
end