-- 实训室结构化布局增量迁移脚本(MySQL 5.7+/8.0)。 -- -- 设计说明: -- 1. layout_rows和layout_columns替代不可靠的layout_description文本。 -- 2. 标准的3X4、3×4、3*4可自动迁移;行列乘积必须和seat_count一致。 -- 3. 无法安全解析的历史文本回退为1行、seat_count列,保证机位不丢失。 -- 4. 完成数据回填和非空约束后删除layout_description,后续不再保存文本布局。 delimiter $$ drop procedure if exists migrate_training_room_layout$$ create procedure migrate_training_room_layout() begin if not exists ( select 1 from information_schema.columns where table_schema = database() and table_name = 'training_room' and column_name = 'layout_rows' ) then alter table training_room add column layout_rows int null comment '机位布局行数' after seat_count; end if; if not exists ( select 1 from information_schema.columns where table_schema = database() and table_name = 'training_room' and column_name = 'layout_columns' ) then alter table training_room add column layout_columns int null comment '机位布局列数' after layout_rows; end if; if exists ( select 1 from information_schema.columns where table_schema = database() and table_name = 'training_room' and column_name = 'layout_description' ) then -- 将常见分隔符归一为X,仅迁移纯数字的行列格式。 update training_room set layout_rows = cast(substring_index( upper(replace(replace(replace(trim(layout_description), '×', 'X'), '*', 'X'), ' ', '')), 'X', 1) as unsigned), layout_columns = cast(substring_index( upper(replace(replace(replace(trim(layout_description), '×', 'X'), '*', 'X'), ' ', '')), 'X', -1) as unsigned) where upper(replace(replace(replace(trim(layout_description), '×', 'X'), '*', 'X'), ' ', '')) regexp '^[1-9][0-9]{0,8}X[1-9][0-9]{0,8}$'; -- 只保留与seat_count一致的解析结果,防止错误文本改变现有机位数量。 update training_room set layout_rows = null, layout_columns = null where layout_rows is not null and layout_rows * layout_columns != seat_count; end if; update training_room set layout_rows = 1, layout_columns = greatest(coalesce(seat_count, 1), 1), seat_count = greatest(coalesce(seat_count, 1), 1) where layout_rows is null or layout_columns is null or layout_rows < 1 or layout_columns < 1 or layout_rows * layout_columns != seat_count; alter table training_room modify column layout_rows int not null comment '机位布局行数', modify column layout_columns int not null comment '机位布局列数'; if exists ( select 1 from information_schema.columns where table_schema = database() and table_name = 'training_room' and column_name = 'layout_description' ) then alter table training_room drop column layout_description; end if; end$$ call migrate_training_room_layout()$$ drop procedure if exists migrate_training_room_layout$$ delimiter ; -- 部署后核验:结果应为0。 select count(*) as invalid_training_room_layout_count from training_room where layout_rows < 1 or layout_columns < 1 or seat_count != layout_rows * layout_columns;