85 lines
6.3 KiB
SQL
85 lines
6.3 KiB
SQL
-- ============================================
|
|
-- Mokee Gateway: BIGINT → UUID 数据库迁移
|
|
-- 执行前请备份数据库
|
|
-- ============================================
|
|
|
|
-- 1. 添加临时 UUID 列并生成值
|
|
ALTER TABLE sys_system ADD COLUMN new_id VARCHAR(64) NOT NULL DEFAULT '' AFTER id;
|
|
ALTER TABLE sys_user ADD COLUMN new_id VARCHAR(64) NOT NULL DEFAULT '' AFTER id;
|
|
ALTER TABLE sys_role ADD COLUMN new_id VARCHAR(64) NOT NULL DEFAULT '' AFTER id;
|
|
ALTER TABLE sys_menu ADD COLUMN new_id VARCHAR(64) NOT NULL DEFAULT '' AFTER id;
|
|
ALTER TABLE sys_api ADD COLUMN new_id VARCHAR(64) NOT NULL DEFAULT '' AFTER id;
|
|
ALTER TABLE sys_dict ADD COLUMN new_id VARCHAR(64) NOT NULL DEFAULT '' AFTER id;
|
|
ALTER TABLE sys_file ADD COLUMN new_id VARCHAR(64) NOT NULL DEFAULT '' AFTER id;
|
|
ALTER TABLE sys_user_system ADD COLUMN new_id VARCHAR(64) NOT NULL DEFAULT '' AFTER id;
|
|
ALTER TABLE sys_user_role ADD COLUMN new_id VARCHAR(64) NOT NULL DEFAULT '' AFTER id;
|
|
ALTER TABLE sys_role_menu ADD COLUMN new_id VARCHAR(64) NOT NULL DEFAULT '' AFTER id;
|
|
ALTER TABLE sys_login_log ADD COLUMN new_id VARCHAR(64) NOT NULL DEFAULT '' AFTER id;
|
|
ALTER TABLE sys_operation_log ADD COLUMN new_id VARCHAR(64) NOT NULL DEFAULT '' AFTER id;
|
|
|
|
UPDATE sys_system SET new_id = REPLACE(UUID(),'-','');
|
|
UPDATE sys_user SET new_id = REPLACE(UUID(),'-','');
|
|
UPDATE sys_role SET new_id = REPLACE(UUID(),'-','');
|
|
UPDATE sys_menu SET new_id = REPLACE(UUID(),'-','');
|
|
UPDATE sys_api SET new_id = REPLACE(UUID(),'-','');
|
|
UPDATE sys_dict SET new_id = REPLACE(UUID(),'-','');
|
|
UPDATE sys_file SET new_id = REPLACE(UUID(),'-','');
|
|
UPDATE sys_user_system SET new_id = REPLACE(UUID(),'-','');
|
|
UPDATE sys_user_role SET new_id = REPLACE(UUID(),'-','');
|
|
UPDATE sys_role_menu SET new_id = REPLACE(UUID(),'-','');
|
|
UPDATE sys_login_log SET new_id = REPLACE(UUID(),'-','');
|
|
UPDATE sys_operation_log SET new_id = REPLACE(UUID(),'-','');
|
|
|
|
-- 2. 更新所有外键引用（用 new_id 关联）
|
|
UPDATE sys_api a JOIN sys_system s ON a.system_id = s.id SET a.system_id = s.new_id;
|
|
UPDATE sys_role r JOIN sys_system s ON r.system_id = s.id SET r.system_id = s.new_id;
|
|
UPDATE sys_menu m JOIN sys_system s ON m.system_id = s.id SET m.system_id = s.new_id;
|
|
UPDATE sys_user_system us JOIN sys_system s ON us.system_id = s.id SET us.system_id = s.new_id;
|
|
UPDATE sys_user_system us JOIN sys_user u ON us.user_id = u.id SET us.user_id = u.new_id;
|
|
UPDATE sys_user_role ur JOIN sys_role r ON ur.role_id = r.id SET ur.role_id = r.new_id;
|
|
UPDATE sys_user_role ur JOIN sys_user u ON ur.user_id = u.id SET ur.user_id = u.new_id;
|
|
UPDATE sys_role_menu rm JOIN sys_role r ON rm.role_id = r.id SET rm.role_id = r.new_id;
|
|
UPDATE sys_role_menu rm JOIN sys_menu m ON rm.menu_id = m.id SET rm.menu_id = m.new_id;
|
|
UPDATE sys_login_log l JOIN sys_user u ON l.user_id = u.id SET l.user_id = u.new_id;
|
|
UPDATE sys_login_log l JOIN sys_system s ON l.system_id = s.id SET l.system_id = s.new_id;
|
|
UPDATE sys_operation_log o JOIN sys_user u ON o.user_id = u.id SET o.user_id = u.new_id;
|
|
UPDATE sys_operation_log o JOIN sys_system s ON o.system_id = s.id SET o.system_id = s.new_id;
|
|
-- menu parent_id 自引用
|
|
UPDATE sys_menu child JOIN sys_menu parent ON child.parent_id = parent.id AND parent.new_id != '' SET child.parent_id = parent.new_id;
|
|
|
|
-- 3. 切换主键
|
|
ALTER TABLE sys_system DROP PRIMARY KEY, DROP COLUMN id, CHANGE new_id id VARCHAR(64) NOT NULL, ADD PRIMARY KEY (id);
|
|
ALTER TABLE sys_user DROP PRIMARY KEY, DROP COLUMN id, CHANGE new_id id VARCHAR(64) NOT NULL, ADD PRIMARY KEY (id);
|
|
ALTER TABLE sys_role DROP PRIMARY KEY, DROP COLUMN id, CHANGE new_id id VARCHAR(64) NOT NULL, ADD PRIMARY KEY (id);
|
|
ALTER TABLE sys_menu DROP PRIMARY KEY, DROP COLUMN id, CHANGE new_id id VARCHAR(64) NOT NULL, ADD PRIMARY KEY (id);
|
|
ALTER TABLE sys_api DROP PRIMARY KEY, DROP COLUMN id, CHANGE new_id id VARCHAR(64) NOT NULL, ADD PRIMARY KEY (id);
|
|
ALTER TABLE sys_dict DROP PRIMARY KEY, DROP COLUMN id, CHANGE new_id id VARCHAR(64) NOT NULL, ADD PRIMARY KEY (id);
|
|
ALTER TABLE sys_file DROP PRIMARY KEY, DROP COLUMN id, CHANGE new_id id VARCHAR(64) NOT NULL, ADD PRIMARY KEY (id);
|
|
ALTER TABLE sys_user_system DROP PRIMARY KEY, DROP COLUMN id, CHANGE new_id id VARCHAR(64) NOT NULL, ADD PRIMARY KEY (id);
|
|
ALTER TABLE sys_user_role DROP PRIMARY KEY, DROP COLUMN id, CHANGE new_id id VARCHAR(64) NOT NULL, ADD PRIMARY KEY (id);
|
|
ALTER TABLE sys_role_menu DROP PRIMARY KEY, DROP COLUMN id, CHANGE new_id id VARCHAR(64) NOT NULL, ADD PRIMARY KEY (id);
|
|
ALTER TABLE sys_login_log DROP PRIMARY KEY, DROP COLUMN id, CHANGE new_id id VARCHAR(64) NOT NULL, ADD PRIMARY KEY (id);
|
|
ALTER TABLE sys_operation_log DROP PRIMARY KEY, DROP COLUMN id, CHANGE new_id id VARCHAR(64) NOT NULL, ADD PRIMARY KEY (id);
|
|
|
|
-- 4. 修改所有 FK 列类型
|
|
ALTER TABLE sys_api MODIFY system_id VARCHAR(64) NOT NULL;
|
|
ALTER TABLE sys_role MODIFY system_id VARCHAR(64) NOT NULL;
|
|
ALTER TABLE sys_menu MODIFY system_id VARCHAR(64) NOT NULL, MODIFY parent_id VARCHAR(64) NOT NULL DEFAULT '0';
|
|
ALTER TABLE sys_user_system MODIFY user_id VARCHAR(64) NOT NULL, MODIFY system_id VARCHAR(64) NOT NULL;
|
|
ALTER TABLE sys_user_role MODIFY user_id VARCHAR(64) NOT NULL, MODIFY role_id VARCHAR(64) NOT NULL;
|
|
ALTER TABLE sys_role_menu MODIFY role_id VARCHAR(64) NOT NULL, MODIFY menu_id VARCHAR(64) NOT NULL;
|
|
ALTER TABLE sys_login_log MODIFY user_id VARCHAR(64), MODIFY system_id VARCHAR(64);
|
|
ALTER TABLE sys_operation_log MODIFY user_id VARCHAR(64), MODIFY system_id VARCHAR(64);
|
|
ALTER TABLE sys_file MODIFY system_id VARCHAR(64), MODIFY upload_by VARCHAR(64);
|
|
|
|
-- 5. 重新创建索引（因列类型变更）
|
|
-- sys_api
|
|
ALTER TABLE sys_api DROP INDEX uk_system_path_method, ADD UNIQUE KEY uk_system_path_method (system_id, api_path, api_method);
|
|
-- sys_role
|
|
ALTER TABLE sys_role DROP INDEX uk_system_role_code, ADD UNIQUE KEY uk_system_role_code (system_id, role_code);
|
|
-- 其他唯一键涉及 FK 的同样处理...
|
|
|
|
-- ============================================
|
|
-- 迁移完成
|
|
-- ============================================
|