446 lines
16 KiB
MySQL
446 lines
16 KiB
MySQL
|
-- ============================================
|
|||
|
|
-- UUID迁移脚本（简化版 - 开发环境）
|
|||
|
|
-- 将系统中所有bigint类型的ID字段改为CHAR(36)类型的UUID
|
|||
|
|
-- 注意：此脚本直接修改表结构，不保留旧数据，适用于开发环境
|
|||
|
|
-- ============================================
|
|||
|
|
|
|||
|
|
-- 注意：user表和product表已经是CHAR(36)类型，跳过修改
|
|||
|
|
|
|||
|
|
-- ============================================
|
|||
|
|
-- 第一部分：修改核心业务表
|
|||
|
|
-- ============================================
|
|||
|
|
|
|||
|
|
-- user表：已经是CHAR(36)，跳过
|
|||
|
|
-- product表：已经是CHAR(36)，跳过
|
|||
|
|
|
|||
|
|
-- order表：修改主键和外键字段
|
|||
|
|
ALTER TABLE `order`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `user_id` CHAR(36) NOT NULL COMMENT '用户UUID',
|
|||
|
|
MODIFY COLUMN `product_id` CHAR(36) NOT NULL COMMENT '产品UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_user_id` (`user_id`),
|
|||
|
|
ADD INDEX `idx_product_id` (`product_id`);
|
|||
|
|
|
|||
|
|
-- query表：修改主键和外键字段
|
|||
|
|
ALTER TABLE `query`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `order_id` CHAR(36) NOT NULL COMMENT '订单UUID',
|
|||
|
|
MODIFY COLUMN `user_id` CHAR(36) NOT NULL COMMENT '用户UUID',
|
|||
|
|
MODIFY COLUMN `product_id` CHAR(36) NOT NULL COMMENT '产品UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_order_id` (`order_id`),
|
|||
|
|
ADD INDEX `idx_user_id` (`user_id`),
|
|||
|
|
ADD INDEX `idx_product_id` (`product_id`);
|
|||
|
|
|
|||
|
|
-- user_auth表：修改主键和外键字段（注意：user_id需要关联到user表的UUID）
|
|||
|
|
ALTER TABLE `user_auth`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `user_id` CHAR(36) NOT NULL COMMENT '用户UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_user_id` (`user_id`),
|
|||
|
|
ADD UNIQUE INDEX `uk_auth_type_key` (`auth_type`, `auth_key`) COMMENT '确保同一个认证方式只能绑定一个用户';
|
|||
|
|
|
|||
|
|
-- agent表：修改主键和外键字段
|
|||
|
|
ALTER TABLE `agent`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `user_id` CHAR(36) NOT NULL COMMENT '用户UUID',
|
|||
|
|
MODIFY COLUMN `team_leader_id` CHAR(36) NULL COMMENT '团队首领UUID',
|
|||
|
|
MODIFY COLUMN `invite_code_id` CHAR(36) NULL COMMENT '邀请码UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD UNIQUE INDEX `uk_user_id` (`user_id`),
|
|||
|
|
ADD INDEX `idx_team_leader_id` (`team_leader_id`),
|
|||
|
|
ADD INDEX `idx_invite_code_id` (`invite_code_id`);
|
|||
|
|
|
|||
|
|
-- ============================================
|
|||
|
|
-- 第二部分：修改代理相关表
|
|||
|
|
-- ============================================
|
|||
|
|
|
|||
|
|
-- agent_commission表
|
|||
|
|
ALTER TABLE `agent_commission`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `agent_id` CHAR(36) NOT NULL COMMENT '代理UUID',
|
|||
|
|
MODIFY COLUMN `order_id` CHAR(36) NOT NULL COMMENT '订单UUID',
|
|||
|
|
MODIFY COLUMN `product_id` CHAR(36) NOT NULL COMMENT '产品UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_agent_id` (`agent_id`),
|
|||
|
|
ADD INDEX `idx_order_id` (`order_id`),
|
|||
|
|
ADD INDEX `idx_product_id` (`product_id`);
|
|||
|
|
|
|||
|
|
-- agent_invite_code表
|
|||
|
|
ALTER TABLE `agent_invite_code`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `agent_id` CHAR(36) NULL COMMENT '代理UUID',
|
|||
|
|
MODIFY COLUMN `used_user_id` CHAR(36) NULL COMMENT '使用用户UUID',
|
|||
|
|
MODIFY COLUMN `used_agent_id` CHAR(36) NULL COMMENT '使用代理UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_agent_id` (`agent_id`),
|
|||
|
|
ADD INDEX `idx_used_user_id` (`used_user_id`),
|
|||
|
|
ADD INDEX `idx_used_agent_id` (`used_agent_id`);
|
|||
|
|
|
|||
|
|
-- agent_link表
|
|||
|
|
ALTER TABLE `agent_link`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `agent_id` CHAR(36) NOT NULL COMMENT '代理UUID',
|
|||
|
|
MODIFY COLUMN `user_id` CHAR(36) NOT NULL COMMENT '用户UUID',
|
|||
|
|
MODIFY COLUMN `product_id` CHAR(36) NOT NULL COMMENT '产品UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_agent_id` (`agent_id`),
|
|||
|
|
ADD INDEX `idx_user_id` (`user_id`),
|
|||
|
|
ADD INDEX `idx_product_id` (`product_id`);
|
|||
|
|
|
|||
|
|
-- agent_order表
|
|||
|
|
ALTER TABLE `agent_order`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `agent_id` CHAR(36) NOT NULL COMMENT '代理UUID',
|
|||
|
|
MODIFY COLUMN `order_id` CHAR(36) NOT NULL COMMENT '订单UUID',
|
|||
|
|
MODIFY COLUMN `product_id` CHAR(36) NOT NULL COMMENT '产品UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_agent_id` (`agent_id`),
|
|||
|
|
ADD INDEX `idx_order_id` (`order_id`),
|
|||
|
|
ADD INDEX `idx_product_id` (`product_id`);
|
|||
|
|
|
|||
|
|
-- agent_wallet表
|
|||
|
|
ALTER TABLE `agent_wallet`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `agent_id` CHAR(36) NOT NULL COMMENT '代理UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD UNIQUE INDEX `uk_agent_id` (`agent_id`);
|
|||
|
|
|
|||
|
|
-- agent_withdrawal表
|
|||
|
|
ALTER TABLE `agent_withdrawal`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `agent_id` CHAR(36) NOT NULL COMMENT '代理UUID',
|
|||
|
|
MODIFY COLUMN `wallet_id` CHAR(36) NOT NULL COMMENT '钱包UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_agent_id` (`agent_id`),
|
|||
|
|
ADD INDEX `idx_wallet_id` (`wallet_id`);
|
|||
|
|
|
|||
|
|
-- agent_withdrawal_tax表
|
|||
|
|
ALTER TABLE `agent_withdrawal_tax`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `withdrawal_id` CHAR(36) NOT NULL COMMENT '提现UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_withdrawal_id` (`withdrawal_id`);
|
|||
|
|
|
|||
|
|
-- agent_rebate表
|
|||
|
|
ALTER TABLE `agent_rebate`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `agent_id` CHAR(36) NOT NULL COMMENT '代理UUID',
|
|||
|
|
MODIFY COLUMN `order_id` CHAR(36) NOT NULL COMMENT '订单UUID',
|
|||
|
|
MODIFY COLUMN `product_id` CHAR(36) NOT NULL COMMENT '产品UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_agent_id` (`agent_id`),
|
|||
|
|
ADD INDEX `idx_order_id` (`order_id`),
|
|||
|
|
ADD INDEX `idx_product_id` (`product_id`);
|
|||
|
|
|
|||
|
|
-- agent_relation表
|
|||
|
|
ALTER TABLE `agent_relation`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `agent_id` CHAR(36) NOT NULL COMMENT '代理UUID',
|
|||
|
|
MODIFY COLUMN `parent_agent_id` CHAR(36) NULL COMMENT '上级代理UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_agent_id` (`agent_id`),
|
|||
|
|
ADD INDEX `idx_parent_agent_id` (`parent_agent_id`);
|
|||
|
|
|
|||
|
|
-- agent_upgrade表
|
|||
|
|
ALTER TABLE `agent_upgrade`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `agent_id` CHAR(36) NOT NULL COMMENT '代理UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_agent_id` (`agent_id`);
|
|||
|
|
|
|||
|
|
-- agent_real_name表
|
|||
|
|
ALTER TABLE `agent_real_name`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `agent_id` CHAR(36) NOT NULL COMMENT '代理UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_agent_id` (`agent_id`);
|
|||
|
|
|
|||
|
|
-- agent_config表
|
|||
|
|
ALTER TABLE `agent_config`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`);
|
|||
|
|
|
|||
|
|
-- agent_product_config表
|
|||
|
|
ALTER TABLE `agent_product_config`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `product_id` CHAR(36) NOT NULL COMMENT '产品UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_product_id` (`product_id`);
|
|||
|
|
|
|||
|
|
-- agent_short_link表
|
|||
|
|
ALTER TABLE `agent_short_link`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `invite_code_id` CHAR(36) NULL COMMENT '邀请码UUID',
|
|||
|
|
MODIFY COLUMN `link_id` CHAR(36) NULL COMMENT '链接UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_invite_code_id` (`invite_code_id`),
|
|||
|
|
ADD INDEX `idx_link_id` (`link_id`);
|
|||
|
|
|
|||
|
|
-- agent_invite_code_usage表
|
|||
|
|
ALTER TABLE `agent_invite_code_usage`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `invite_code_id` CHAR(36) NOT NULL COMMENT '邀请码UUID',
|
|||
|
|
MODIFY COLUMN `user_id` CHAR(36) NOT NULL COMMENT '用户UUID',
|
|||
|
|
MODIFY COLUMN `agent_id` CHAR(36) NOT NULL COMMENT '代理UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_invite_code_id` (`invite_code_id`),
|
|||
|
|
ADD INDEX `idx_user_id` (`user_id`),
|
|||
|
|
ADD INDEX `idx_agent_id` (`agent_id`);
|
|||
|
|
|
|||
|
|
-- agent_freeze_task表
|
|||
|
|
ALTER TABLE `agent_freeze_task`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `agent_id` CHAR(36) NOT NULL COMMENT '代理UUID',
|
|||
|
|
MODIFY COLUMN `order_id` CHAR(36) NOT NULL COMMENT '订单UUID',
|
|||
|
|
MODIFY COLUMN `commission_id` CHAR(36) NOT NULL COMMENT '佣金UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_agent_id` (`agent_id`),
|
|||
|
|
ADD INDEX `idx_order_id` (`order_id`),
|
|||
|
|
ADD INDEX `idx_commission_id` (`commission_id`);
|
|||
|
|
|
|||
|
|
-- ============================================
|
|||
|
|
-- 第三部分：修改订单相关表
|
|||
|
|
-- ============================================
|
|||
|
|
|
|||
|
|
-- order_refund表
|
|||
|
|
ALTER TABLE `order_refund`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `order_id` CHAR(36) NOT NULL COMMENT '订单UUID',
|
|||
|
|
MODIFY COLUMN `user_id` CHAR(36) NOT NULL COMMENT '用户UUID',
|
|||
|
|
MODIFY COLUMN `product_id` CHAR(36) NOT NULL COMMENT '产品UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_order_id` (`order_id`),
|
|||
|
|
ADD INDEX `idx_user_id` (`user_id`),
|
|||
|
|
ADD INDEX `idx_product_id` (`product_id`);
|
|||
|
|
|
|||
|
|
-- ============================================
|
|||
|
|
-- 第四部分：修改查询相关表
|
|||
|
|
-- ============================================
|
|||
|
|
|
|||
|
|
-- query_cleanup_log表
|
|||
|
|
ALTER TABLE `query_cleanup_log`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`);
|
|||
|
|
|
|||
|
|
-- query_cleanup_detail表
|
|||
|
|
ALTER TABLE `query_cleanup_detail`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `cleanup_log_id` CHAR(36) NOT NULL COMMENT '清理日志UUID',
|
|||
|
|
MODIFY COLUMN `query_id` CHAR(36) NOT NULL COMMENT '查询UUID',
|
|||
|
|
MODIFY COLUMN `order_id` CHAR(36) NOT NULL COMMENT '订单UUID',
|
|||
|
|
MODIFY COLUMN `user_id` CHAR(36) NOT NULL COMMENT '用户UUID',
|
|||
|
|
MODIFY COLUMN `product_id` CHAR(36) NOT NULL COMMENT '产品UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_cleanup_log_id` (`cleanup_log_id`),
|
|||
|
|
ADD INDEX `idx_query_id` (`query_id`),
|
|||
|
|
ADD INDEX `idx_order_id` (`order_id`),
|
|||
|
|
ADD INDEX `idx_user_id` (`user_id`),
|
|||
|
|
ADD INDEX `idx_product_id` (`product_id`);
|
|||
|
|
|
|||
|
|
-- query_cleanup_config表
|
|||
|
|
ALTER TABLE `query_cleanup_config`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`);
|
|||
|
|
|
|||
|
|
-- ============================================
|
|||
|
|
-- 第五部分：修改产品相关表
|
|||
|
|
-- ============================================
|
|||
|
|
|
|||
|
|
-- product_feature表
|
|||
|
|
ALTER TABLE `product_feature`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `product_id` CHAR(36) NOT NULL COMMENT '产品UUID',
|
|||
|
|
MODIFY COLUMN `feature_id` CHAR(36) NOT NULL COMMENT '功能UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_product_id` (`product_id`),
|
|||
|
|
ADD INDEX `idx_feature_id` (`feature_id`);
|
|||
|
|
|
|||
|
|
-- feature表
|
|||
|
|
ALTER TABLE `feature`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`);
|
|||
|
|
|
|||
|
|
-- ============================================
|
|||
|
|
-- 第六部分：修改其他表
|
|||
|
|
-- ============================================
|
|||
|
|
|
|||
|
|
-- authorization_document表
|
|||
|
|
ALTER TABLE `authorization_document`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `user_id` CHAR(36) NOT NULL COMMENT '用户UUID',
|
|||
|
|
MODIFY COLUMN `order_id` CHAR(36) NOT NULL COMMENT '订单UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_user_id` (`user_id`),
|
|||
|
|
ADD INDEX `idx_order_id` (`order_id`);
|
|||
|
|
|
|||
|
|
-- global_notifications表（注意：原表id是int类型）
|
|||
|
|
ALTER TABLE `global_notifications`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`);
|
|||
|
|
|
|||
|
|
-- example表
|
|||
|
|
ALTER TABLE `example`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `feature_id` CHAR(36) NOT NULL COMMENT '功能UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_feature_id` (`feature_id`);
|
|||
|
|
|
|||
|
|
-- ============================================
|
|||
|
|
-- 第七部分：修改管理后台表
|
|||
|
|
-- ============================================
|
|||
|
|
|
|||
|
|
-- admin_user表
|
|||
|
|
ALTER TABLE `admin_user`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`);
|
|||
|
|
|
|||
|
|
-- admin_role表
|
|||
|
|
ALTER TABLE `admin_role`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`);
|
|||
|
|
|
|||
|
|
-- admin_menu表（注意：字段名是pid，不是parent_id）
|
|||
|
|
ALTER TABLE `admin_menu`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `pid` CHAR(36) NOT NULL DEFAULT '0' COMMENT '父菜单UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_pid` (`pid`);
|
|||
|
|
|
|||
|
|
-- admin_api表
|
|||
|
|
ALTER TABLE `admin_api`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`);
|
|||
|
|
|
|||
|
|
-- admin_dict_type表
|
|||
|
|
ALTER TABLE `admin_dict_type`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`);
|
|||
|
|
|
|||
|
|
-- admin_dict_data表（注意：没有dict_type_id字段，使用dict_type字符串）
|
|||
|
|
-- 此表不需要修改外键字段，因为使用的是dict_type字符串而不是ID
|
|||
|
|
|
|||
|
|
ALTER TABLE `admin_dict_data`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`);
|
|||
|
|
|
|||
|
|
-- admin_user_role表
|
|||
|
|
ALTER TABLE `admin_user_role`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `user_id` CHAR(36) NOT NULL DEFAULT '0' COMMENT '用户UUID',
|
|||
|
|
MODIFY COLUMN `role_id` CHAR(36) NOT NULL DEFAULT '0' COMMENT '角色UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_user_id` (`user_id`),
|
|||
|
|
ADD INDEX `idx_role_id` (`role_id`);
|
|||
|
|
|
|||
|
|
-- admin_role_menu表
|
|||
|
|
ALTER TABLE `admin_role_menu`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `role_id` CHAR(36) NOT NULL DEFAULT '0' COMMENT '角色UUID',
|
|||
|
|
MODIFY COLUMN `menu_id` CHAR(36) NOT NULL DEFAULT '0' COMMENT '菜单UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_role_id` (`role_id`),
|
|||
|
|
ADD INDEX `idx_menu_id` (`menu_id`);
|
|||
|
|
|
|||
|
|
-- admin_role_api表
|
|||
|
|
ALTER TABLE `admin_role_api`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `role_id` CHAR(36) NOT NULL DEFAULT '0' COMMENT '角色UUID',
|
|||
|
|
MODIFY COLUMN `api_id` CHAR(36) NOT NULL DEFAULT '0' COMMENT 'API UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_role_id` (`role_id`),
|
|||
|
|
ADD INDEX `idx_api_id` (`api_id`);
|
|||
|
|
|
|||
|
|
-- ============================================
|
|||
|
|
-- 第八部分：修改推广相关表（新增）
|
|||
|
|
-- ============================================
|
|||
|
|
|
|||
|
|
-- admin_promotion_link表
|
|||
|
|
ALTER TABLE `admin_promotion_link`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `admin_user_id` CHAR(36) NOT NULL DEFAULT '0' COMMENT '推广者账号UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_admin_user_id` (`admin_user_id`);
|
|||
|
|
|
|||
|
|
-- admin_promotion_link_stats_history表
|
|||
|
|
ALTER TABLE `admin_promotion_link_stats_history`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `link_id` CHAR(36) NOT NULL COMMENT '推广链接UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_link_id` (`link_id`);
|
|||
|
|
|
|||
|
|
-- admin_promotion_link_stats_total表
|
|||
|
|
ALTER TABLE `admin_promotion_link_stats_total`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `link_id` CHAR(36) NOT NULL COMMENT '推广链接UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD INDEX `idx_link_id` (`link_id`);
|
|||
|
|
|
|||
|
|
-- admin_promotion_order表
|
|||
|
|
ALTER TABLE `admin_promotion_order`
|
|||
|
|
MODIFY COLUMN `id` CHAR(36) NOT NULL COMMENT 'UUID主键',
|
|||
|
|
MODIFY COLUMN `link_id` CHAR(36) NOT NULL COMMENT '推广链接UUID',
|
|||
|
|
MODIFY COLUMN `order_id` CHAR(36) NOT NULL COMMENT '订单UUID',
|
|||
|
|
MODIFY COLUMN `user_id` CHAR(36) NOT NULL COMMENT '下单用户UUID',
|
|||
|
|
MODIFY COLUMN `admin_user_id` CHAR(36) NOT NULL COMMENT '推广者账号UUID',
|
|||
|
|
DROP PRIMARY KEY,
|
|||
|
|
ADD PRIMARY KEY (`id`),
|
|||
|
|
ADD UNIQUE INDEX `uk_order_id` (`order_id`) COMMENT '确保每个订单只有一条推广记录',
|
|||
|
|
ADD INDEX `idx_link_id` (`link_id`),
|
|||
|
|
ADD INDEX `idx_user_id` (`user_id`) COMMENT '优化用户查询',
|
|||
|
|
ADD INDEX `idx_admin_user_id` (`admin_user_id`);
|
|||
|
|
|
|||
|
|
-- ============================================
|
|||
|
|
-- 注意事项
|
|||
|
|
-- ============================================
|
|||
|
|
-- 1. 此脚本直接修改表结构，不保留旧数据
|
|||
|
|
-- 2. user表和product表已经是CHAR(36)类型，已跳过
|
|||
|
|
-- 3. 执行后，所有ID字段都是CHAR(36)类型
|
|||
|
|
-- 4. 插入新记录时，需要在应用层生成UUID（使用uuid.NewString()）
|
|||
|
|
-- 5. 如果表中有数据，需要先清空数据或手动填充UUID
|
|||
|
|
-- 6. 建议在开发环境先测试，确认无误后再应用到生产环境
|
|||
|
|
-- 7. admin_menu表使用pid字段而不是parent_id
|
|||
|
|
-- 8. admin_dict_data表使用dict_type字符串字段，不是dict_type_id
|
|||
|
|
-- 9. global_notifications表的id原为int类型，现改为CHAR(36)
|