50 lines
1.7 KiB
MySQL
50 lines
1.7 KiB
MySQL
|
-- ============================================
|
|||
|
|
-- 多身份功能 - 数据库脚本
|
|||
|
|
-- 创建时间：2026-02-28
|
|||
|
|
-- 功能：创建 user_roles 表，支持一个账号多个角色
|
|||
|
|
-- ============================================
|
|||
|
|
|
|||
|
|
-- 1. 创建 user_roles 表
|
|||
|
|
CREATE TABLE IF NOT EXISTS `user_roles` (
|
|||
|
|
`id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
|
|||
|
|
`user_id` BIGINT NOT NULL COMMENT '用户ID',
|
|||
|
|
`role_type` VARCHAR(50) NOT NULL COMMENT '角色类型：teacher/manager/distributor/provider/parent',
|
|||
|
|
`is_primary` TINYINT DEFAULT 0 COMMENT '是否主身份：0=否，1=是',
|
|||
|
|
`status` TINYINT DEFAULT 1 COMMENT '状态：0=禁用，1=启用',
|
|||
|
|
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|||
|
|
`update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|||
|
|
INDEX `idx_user_id` (`user_id`),
|
|||
|
|
INDEX `idx_role_type` (`role_type`),
|
|||
|
|
UNIQUE KEY `uk_user_role` (`user_id`, `role_type`)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户角色关联表';
|
|||
|
|
|
|||
|
|
-- 2. 初始化数据：将现有用户的主身份同步到 user_roles 表
|
|||
|
|
INSERT INTO `user_roles` (`user_id`, `role_type`, `is_primary`, `status`)
|
|||
|
|
SELECT
|
|||
|
|
`id` as user_id,
|
|||
|
|
`role` as role_type,
|
|||
|
|
1 as is_primary,
|
|||
|
|
1 as status
|
|||
|
|
FROM `user`
|
|||
|
|
WHERE `role` IS NOT NULL AND `role` != ''
|
|||
|
|
ON DUPLICATE KEY UPDATE `is_primary` = 1;
|
|||
|
|
|
|||
|
|
-- 3. 验证数据
|
|||
|
|
SELECT
|
|||
|
|
'数据同步完成' as message,
|
|||
|
|
COUNT(*) as total_users,
|
|||
|
|
COUNT(DISTINCT user_id) as users_with_roles
|
|||
|
|
FROM user_roles;
|
|||
|
|
|
|||
|
|
-- 4. 查看前10条数据
|
|||
|
|
SELECT
|
|||
|
|
u.id,
|
|||
|
|
u.phone,
|
|||
|
|
u.role as primary_role,
|
|||
|
|
GROUP_CONCAT(ur.role_type) as all_roles,
|
|||
|
|
GROUP_CONCAT(IF(ur.is_primary = 1, '✓', '')) as primary_mark
|
|||
|
|
FROM user u
|
|||
|
|
LEFT JOIN user_roles ur ON u.id = ur.user_id
|
|||
|
|
GROUP BY u.id
|
|||
|
|
LIMIT 10;
|