DELIMITER $$
DROP PROCEDURE IF EXISTS add_stat_flow_part $$
CREATE PROCEDURE add_stat_flow_part(stat_time char(14), upper_time char(14), table_space char(128))
begin
DECLARE v_sql varchar(512); -- icp
/* 例如在下面的begin end块中,如果出错,则整个procedure都不会再运行。
如果让存储过程在执行sql出错后,依然接着运行后面的sql语句呢? */
begin
set v_sql = concat('alter table ismg_icp_flow add partition (partition ismg_icp_flow_', stat_time, ' values less than (', upper_time, ') ENGINE=MyISAM)');
set @l_sql = v_sql;
prepare s1 from @l_sql;
execute s1;
deallocate prepare s1;
end; begin
set v_sql = concat('alter table ismg_icp_flow_hour add partition (partition ismg_icp_hour_', stat_time, ' values less than (', upper_time, ') ENGINE=MyISAM)');
set @l_sql = v_sql;
prepare s1 from @l_sql;
execute s1;
deallocate prepare s1;
end; begin
set v_sql = concat('alter table ismg_icp_flow_day add partition (partition ismg_icp_day_', stat_time, ' values less than (', upper_time, ') ENGINE=MyISAM)');
set @l_sql = v_sql;
prepare s1 from @l_sql;
execute s1;
deallocate prepare s1;
end; -- fwd
begin
set v_sql = concat('alter table ismg_fwd_flow add partition (partition ismg_fwd_flow_', stat_time, ' values less than (', upper_time, ') ENGINE=MyISAM)');
set @l_sql = v_sql;
prepare s1 from @l_sql;
execute s1;
deallocate prepare s1;
end; begin
set v_sql = concat('alter table ismg_fwd_flow_hour add partition (partition ismg_fwd_hour_', stat_time, ' values less than (', upper_time, ') ENGINE=MyISAM)');
set @l_sql = v_sql;
prepare s1 from @l_sql;
execute s1;
deallocate prepare s1;
end; begin
set v_sql = concat('alter table ismg_fwd_flow_day add partition (partition ismg_fwd_day_', stat_time, ' values less than (', upper_time, ') ENGINE=MyISAM)');
set @l_sql = v_sql;
prepare s1 from @l_sql;
execute s1;
deallocate prepare s1;
end; -- smsc
begin
set v_sql = concat('alter table ismg_smsc_flow add partition (partition ismg_smsc_flow_', stat_time, ' values less than (', upper_time, ') ENGINE=MyISAM)');
set @l_sql = v_sql;
prepare s1 from @l_sql;
execute s1;
deallocate prepare s1;
end; begin
set v_sql = concat('alter table ismg_smsc_flow_hour add partition (partition ismg_smsc_hour_', stat_time, ' values less than (', upper_time, ') ENGINE=MyISAM)');
set @l_sql = v_sql;
prepare s1 from @l_sql;
execute s1;
deallocate prepare s1;
end; begin
set v_sql = concat('alter table ismg_smsc_flow_day add partition (partition ismg_smsc_day_', stat_time, ' values less than (', upper_time, ') ENGINE=MyISAM)');
set @l_sql = v_sql;
prepare s1 from @l_sql;
execute s1;
deallocate prepare s1;
end; commit;
end$$
DELIMITER ;
DROP PROCEDURE IF EXISTS add_stat_flow_part $$
CREATE PROCEDURE add_stat_flow_part(stat_time char(14), upper_time char(14), table_space char(128))
begin
DECLARE v_sql varchar(512); -- icp
/* 例如在下面的begin end块中,如果出错,则整个procedure都不会再运行。
如果让存储过程在执行sql出错后,依然接着运行后面的sql语句呢? */
begin
set v_sql = concat('alter table ismg_icp_flow add partition (partition ismg_icp_flow_', stat_time, ' values less than (', upper_time, ') ENGINE=MyISAM)');
set @l_sql = v_sql;
prepare s1 from @l_sql;
execute s1;
deallocate prepare s1;
end; begin
set v_sql = concat('alter table ismg_icp_flow_hour add partition (partition ismg_icp_hour_', stat_time, ' values less than (', upper_time, ') ENGINE=MyISAM)');
set @l_sql = v_sql;
prepare s1 from @l_sql;
execute s1;
deallocate prepare s1;
end; begin
set v_sql = concat('alter table ismg_icp_flow_day add partition (partition ismg_icp_day_', stat_time, ' values less than (', upper_time, ') ENGINE=MyISAM)');
set @l_sql = v_sql;
prepare s1 from @l_sql;
execute s1;
deallocate prepare s1;
end; -- fwd
begin
set v_sql = concat('alter table ismg_fwd_flow add partition (partition ismg_fwd_flow_', stat_time, ' values less than (', upper_time, ') ENGINE=MyISAM)');
set @l_sql = v_sql;
prepare s1 from @l_sql;
execute s1;
deallocate prepare s1;
end; begin
set v_sql = concat('alter table ismg_fwd_flow_hour add partition (partition ismg_fwd_hour_', stat_time, ' values less than (', upper_time, ') ENGINE=MyISAM)');
set @l_sql = v_sql;
prepare s1 from @l_sql;
execute s1;
deallocate prepare s1;
end; begin
set v_sql = concat('alter table ismg_fwd_flow_day add partition (partition ismg_fwd_day_', stat_time, ' values less than (', upper_time, ') ENGINE=MyISAM)');
set @l_sql = v_sql;
prepare s1 from @l_sql;
execute s1;
deallocate prepare s1;
end; -- smsc
begin
set v_sql = concat('alter table ismg_smsc_flow add partition (partition ismg_smsc_flow_', stat_time, ' values less than (', upper_time, ') ENGINE=MyISAM)');
set @l_sql = v_sql;
prepare s1 from @l_sql;
execute s1;
deallocate prepare s1;
end; begin
set v_sql = concat('alter table ismg_smsc_flow_hour add partition (partition ismg_smsc_hour_', stat_time, ' values less than (', upper_time, ') ENGINE=MyISAM)');
set @l_sql = v_sql;
prepare s1 from @l_sql;
execute s1;
deallocate prepare s1;
end; begin
set v_sql = concat('alter table ismg_smsc_flow_day add partition (partition ismg_smsc_day_', stat_time, ' values less than (', upper_time, ') ENGINE=MyISAM)');
set @l_sql = v_sql;
prepare s1 from @l_sql;
execute s1;
deallocate prepare s1;
end; commit;
end$$
DELIMITER ;
解决方案 »
- ◆救命啊!mysql误删数据恢复◆
- 程序运行一段时间总是出现 max_prepared_stmt_count
- mysql中只读锁的问题
- mysql多表查询的优化
- 关于timestamp为什么总是自动更新
- postgres sql的问题
- mysql中是否有类似于同MS-SQL中的IDENT_CURRENT函数?
- 入门问题:请问mysql跟mysql-max有什么区别?
- 大家好!怎么即时备份MySQL数据?
- 怎么回事?太怪了 对mysql 使用select * from biao where bia2 like '人' 出错
- 关于myISAM插入次数和硬盘io写次数的关系问题
- 如何调出mysql的存储过程?
如果让存储过程在执行sql出错后,依然接着运行后面的sql语句呢? */
DECLARE duplicate_key CONDITION FOR 1062;
DECLARE no_define CONDITION FOR 1364;
DECLARE no_data CONDITION FOR 1329;
-- END Declare COnditions
-- START Declare Exception Handlers
DECLARE CONTINUE HANDLER FOR SQLWARNING,NOT FOUND,SQLEXCEPTION BEGIN
SET mysql_err_code = 1062;
END;
mysql 异常和错误 都有编号。上面可以屏蔽大部分异常情况。有些异常没屏蔽得自己添加,比如
DECLARE CONTINUE HANDLER FOR duplicate_key
BEGIN
SET mysql_err_code = 1062;
END;