今天翻到以前写的批量更新表中的数据的存储过程，故在此做一下记录。
当时MySQL中的表名具有如下特征，即根据需求将业务表类型分为了公有、私有和临时三种类型，即不同的业务对应三张表，，而所做的是区分出是什么类型（公有、私有、临时）的业务表对数据的固定字段做统一规律的处理。
下面为当时所编写的存储过程：
BEGIN DECLARE doneINT; DECLARE v_table_nameVARCHAR(100); DECLARE v_disableVARCHAR(100); DECLARE v_disable_temp VARCHAR(100); — 存放最终删除sql DECLARE v_table_preVARCHAR(100); DECLARE v_table_subVARCHAR(200); DECLARE v_disable_temp_2 VARCHAR(100); — 查询testkaifa库中以'temp_test_p_'开头的表 DECLARE cursor_table_gis CURSOR FOR SELECT DISTINCT table_name tableNameFROMinformation_schema.columnsWHEREtable_schema = 'testkaifa'AND table_name LIKE '%temp_test_p_%'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; SELECT @done; OPEN cursor_table_gis;cursor_loop: LOOPFETCH cursor_table_gis INTO v_table_name;IF done = 1 THENLEAVE cursor_loop;END IF;– 连接字符串函数SET @v_disable = concat_ws(' ', 'update ', v_table_name, 'set is_valid=false where expire_time>now();');SELECT @v_disable;PREPARE sqlstr FROM @v_disable;EXECUTE sqlstr;DEALLOCATE PREPARE sqlstr;SELECT substring_index(v_table_name, '_', 1)INTOv_table_pre;– IF v_table_pre = 'temp' THENSELECT reverse(left(reverse(v_table_name), instr(reverse(v_table_name), '_')))INTOv_table_sub;SET @v_disable_temp = concat_ws(' ', 'update ', v_table_name, 'set is_valid=false where (expire_time-now())> (select value_data from ', concat('platform_params_p', v_table_sub), 'where param_key=\&;tempDismissInterval\&;);');SELECT @v_disable_temp;PREPARE sqlstr2 FROM @v_disable_temp;EXECUTE sqlstr2;DEALLOCATE PREPARE sqlstr2;– END IF;SET @v_disable_temp_2 = concat_ws(' ', 'update ', v_table_name, 'set is_valid=false where (test_id in(select test_id from ', concat('temp_test_user_p', v_table_sub), ' where (max(latest_act_time )-now())> (select value_data from ', concat('platform_params_p', v_table_sub), 'where param_key=\&;tempDismissInterval\&;));');SELECT @v_disable_temp_2;PREPARE sqlstr2 FROM @v_disable_temp;EXECUTE sqlstr2;DEALLOCATE PREPARE sqlstr2; END LOOP cursor_loop; CLOSE cursor_table_gis; COMMIT;– END
本代码涉及到的MySQL的内容为：
1.查询表名
SELECT DISTINCT table_name tableNameFROMinformation_schema.columnsWHEREtable_schema = 'testkaifa'AND table_name LIKE '%temp_test_p_%';
2.执行拼接的字符串SQL
PREPARE statement_name FROM sql_text /*定义*/ EXECUTE statement_name [USING variable [,variable…]] /*执行预处理语句*/ DEALLOCATE PREPARE statement_name /*删除定义*/ 例如：
SET @v_disable_temp = concat_ws(' ', 'update ', v_table_name, 'set is_valid=false where (expire_time-now())> (select value_data from ', concat('platform_params_p', v_table_sub), 'where param_key=\&;tempDismissInterval\&;);');SELECT @v_disable_temp;PREPARE sqlstr2 FROM @v_disable_temp;EXECUTE sqlstr2;DEALLOCATE PREPARE sqlstr2;
我喜欢出发。凡是到达了的地方，
