You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
28 lines
1.0 KiB
28 lines
1.0 KiB
-- 通信卡以 sim_card.iccid 为权威数据；机巢不再保存冗余 ICCID。
|
|
-- 执行前请备份数据库。本迁移会先将已登记机巢中的旧 ICCID 回填到通信卡表，随后删除 dock.iccid。
|
|
-- 不给 dock_id 加唯一约束：通信卡与机巢仅保留可选关联，不限制一对一。
|
|
|
|
SET @next_sim_card_id := (SELECT COALESCE(MAX(id), 0) FROM sim_card);
|
|
SET @row_number := 0;
|
|
|
|
INSERT INTO sim_card (
|
|
id, user_id, dock_id, carrier, phone, iccid, plan_gb, used_gb,
|
|
status, carrier_status, created_at, updated_at
|
|
)
|
|
SELECT
|
|
@next_sim_card_id := @next_sim_card_id + 1,
|
|
source.user_id, source.dock_id, '', '', source.iccid, 0, 0,
|
|
'active', 'normal', NOW(), NOW()
|
|
FROM (
|
|
SELECT d.user_id, MIN(d.dock_id) AS dock_id, d.iccid
|
|
FROM dock d
|
|
LEFT JOIN sim_card s ON s.iccid = d.iccid
|
|
WHERE d.user_id > 0
|
|
AND d.iccid IS NOT NULL
|
|
AND TRIM(d.iccid) <> ''
|
|
AND s.id IS NULL
|
|
GROUP BY d.user_id, d.iccid
|
|
ORDER BY d.user_id, d.iccid
|
|
) AS source;
|
|
|
|
ALTER TABLE dock DROP COLUMN iccid;
|
|
|