45 lines
1.1 KiB
SQL
45 lines
1.1 KiB
SQL
-- 将 user_temp 迁移为 user（mobile 为空）与 user_auth
|
||
|
||
START TRANSACTION;
|
||
|
||
-- 1) 迁移临时用户到 user（mobile 置为空）
|
||
INSERT INTO
|
||
`user` (
|
||
`delete_time`,
|
||
`del_state`,
|
||
`version`,
|
||
`mobile`,
|
||
`password`,
|
||
`nickname`,
|
||
`info`,
|
||
`inside`
|
||
)
|
||
SELECT NULL, 0, COALESCE(ut.version, 0), NULL, NULL, NULL, '', 0
|
||
FROM `user_temp` ut
|
||
WHERE
|
||
ut.del_state = 0;
|
||
|
||
-- 2) 将临时认证迁移到 user_auth（按插入顺序关联最近插入的 user.id）
|
||
INSERT INTO
|
||
`user_auth` (
|
||
`delete_time`,
|
||
`del_state`,
|
||
`version`,
|
||
`user_id`,
|
||
`auth_key`,
|
||
`auth_type`
|
||
)
|
||
SELECT NULL, 0, COALESCE(ut.version, 0), u.id, ut.auth_key, ut.auth_type
|
||
FROM `user_temp` ut
|
||
JOIN `user` u ON u.del_state = 0
|
||
AND u.mobile IS NULL
|
||
WHERE
|
||
ut.del_state = 0;
|
||
|
||
-- 注意：以上为示意，实际生产应通过显式映射（如临时ID与新UserID映射表）确保一一对应，避免笛卡尔匹配。
|
||
|
||
COMMIT;
|
||
|
||
-- 唯一索引保障
|
||
ALTER TABLE `user_auth`
|
||
ADD UNIQUE INDEX `idx_auth_type_key` (`auth_type`, `auth_key`); |