mysql设置变量的方式修改线上数据

背景

根据指定的数据， 批量修改线上各种关联数据

思路

定义变量， 批量 sql 处理

对应的SQL

SET collation_connection = 'utf8mb4_general_ci';
SET collation_database = 'utf8mb4_general_ci';
SET collation_server = 'utf8mb4_general_ci';

SET @container_code = 'A74366273';
SET @station_code = 'W1';
SET @bill_no := '';
SET @sub_task_group_no := '';
SET @put_wall_slot_code := '';

SELECT bill_no, sub_task_group_no, put_wall_slot_code 
INTO @bill_no, @sub_task_group_no, @put_wall_slot_code 
FROM ks_sub_task_1 
WHERE station_code = @station_code AND container_code = @container_code AND biz_status='DONE'
LIMIT 1;

SELECT @bill_no AS bill_no, @sub_task_group_no AS sub_task_group_no, @put_wall_slot_code AS put_wall_slot_code;





update ks_sub_task_1 set biz_status='PROCESSING' where sub_task_group_no = @sub_task_group_no and biz_status='UNDO';

update ks_sub_task_group_1 set biz_status='PROCESSING' where sub_task_group_no = @sub_task_group_no and biz_status='UNDO';

UPDATE ks_put_wall_slot SET order_ids = CONCAT('[', @bill_no, ']'), station_slot_status='WAITING_BINDING' WHERE station_code=@station_code and put_wall_slot_code=@put_wall_slot_code and station_slot_status='IDLE';

按照条件逐个更新

SET @container_code := _utf8mb4 'A00010248A' COLLATE utf8mb4_general_ci;

UPDATE `ks_container_node` SET `bay` = 1, `level` = 1 where container_code like CONCAT('%', @container_code, '%') and `bay` = 1 and `level` = 2 and container_name='A1';
UPDATE `ks_container_node` SET `bay` = 2, `level` = 1 where container_code like CONCAT('%', @container_code, '%') and `bay` = 1 and `level` = 1 and container_name='A2';
UPDATE `ks_container_node` SET `bay` = 1, `level` = 2 where container_code like CONCAT('%', @container_code, '%') and `bay` = 2 and `level` = 2 and container_name='B1';
UPDATE `ks_container_node` SET `bay` = 2, `level` = 2 where container_code like CONCAT('%', @container_code, '%') and `bay` = 2 and `level` = 1 and container_name='B2';