MySQL 实战：按楼层自动编号 room_no 字段

 

---

🧩 MySQL 实战：按楼层自动编号 room_no 字段（格式：floor_no-001）

在实际业务系统中，我们经常需要为数据库中某些字段进行 分组自动编号。本文将以一个真实案例为例，演示如何在 MySQL 中为设备资源表 gw_room_resource 中的 room_no 字段进行按楼层编号，生成规则如下：

• 每个楼层（字段 floor_no）单独从 001 开始编号；

• 编号结果拼接为：floor_no + '-' + 三位编号，如 -DK1-001, -DK1-002, DK8-001 等；

• 仅对 room_no IS NULL 的记录进行填充；

• 最终将结果写回原表中。

---

📊 原始数据结构

表名：gw_room_resource

equip_type	room_name	room_no	meter_id	floor_no	equip_person_type	status
01	-1AT3-GSB1-备用2	NULL	16076	-DK1	06	0
01	9APE1-电动窗控制箱2	NULL	16054	DK9	06	0
…	…	…	…	…	…	…

---

🎯 目标

将原本为空的 room_no 字段自动赋值为形如：

-DK1-001
-DK1-002
...
DK8-001
DK9-001

要求每层楼独立编号，编号从 001 递增。

---

🛠️ 技术挑战

MySQL 早期版本不支持 ROW_NUMBER() 这样的窗口函数，我们需要通过用户变量 @row_num 和 @prev_floor 进行手动分组编号。

---

✅ 解决方案：完整 SQL 脚本

-- 1. 创建临时表保存结果
CREATE TEMPORARY TABLE temp_room_numbers (
    meter_id INT PRIMARY KEY,
    new_room_no VARCHAR(20)
);

-- 2. 初始化自增变量
SET @prev_floor := NULL;
SET @row_num := 0;

-- 3. 生成 room_no：格式为 floor_no-001
INSERT INTO temp_room_numbers (meter_id, new_room_no)
SELECT
    meter_id,
    new_room_no
FROM (
    SELECT
        meter_id,
        @row_num := IF(@prev_floor = floor_no, @row_num + 1, 1) AS seq,
        CONCAT(CAST(floor_no AS CHAR), '-', LPAD(@row_num, 3, '0')) AS new_room_no,
        @prev_floor := floor_no
    FROM gw_room_resource
    WHERE room_no IS NULL
    ORDER BY floor_no, meter_id
) AS numbered_rooms;

-- 4. 回写到主表
UPDATE gw_room_resource AS r
JOIN temp_room_numbers AS t USING (meter_id)
SET r.room_no = t.new_room_no;

-- 5. 验证结果
SELECT floor_no, meter_id, room_no
FROM gw_room_resource
ORDER BY floor_no, room_no;

---

📌 执行效果预览

floor_no	meter_id	room_no
-DK1	16076	-DK1-001
-DK1	16075	-DK1-002
DK8	16033	DK8-001
DK9	16054	DK9-001

---

🛡️ 建议与扩展

• 提前备份字段：

  ALTER TABLE gw_room_resource ADD COLUMN room_no_backup VARCHAR(50);
  UPDATE gw_room_resource SET room_no_backup = room_no;
  

• 控制更新范围：可按需添加过滤条件，如 equip_type = '01' 或指定楼层；

• 封装为存储过程：将逻辑封装成 SP_generate_room_no() 存储过程，方便复用。

---

✨ 总结

通过本案例，我们掌握了如何用 MySQL 的变量机制实现分组自动编号。该方法适用于 MySQL 5.x 无窗口函数的环境，特别适合数据治理、设备管理等场景下的字段填充、编号与格式化处理。