59 lines
1.3 KiB
MySQL
59 lines
1.3 KiB
MySQL
|
-- ============================================
|
|||
|
|
-- 验证 user_roles 表数据
|
|||
|
|
-- ============================================
|
|||
|
|
|
|||
|
|
-- 1. 检查表是否创建成功
|
|||
|
|
SHOW TABLES LIKE 'user_roles';
|
|||
|
|
|
|||
|
|
-- 2. 查看表结构
|
|||
|
|
DESC user_roles;
|
|||
|
|
|
|||
|
|
-- 3. 统计数据
|
|||
|
|
SELECT
|
|||
|
|
'总记录数' as item,
|
|||
|
|
COUNT(*) as count
|
|||
|
|
FROM user_roles
|
|||
|
|
UNION ALL
|
|||
|
|
SELECT
|
|||
|
|
'用户数',
|
|||
|
|
COUNT(DISTINCT user_id)
|
|||
|
|
FROM user_roles
|
|||
|
|
UNION ALL
|
|||
|
|
SELECT
|
|||
|
|
'主身份数',
|
|||
|
|
COUNT(*)
|
|||
|
|
FROM user_roles
|
|||
|
|
WHERE is_primary = 1;
|
|||
|
|
|
|||
|
|
-- 4. 查看各角色分布
|
|||
|
|
SELECT
|
|||
|
|
role_type,
|
|||
|
|
COUNT(*) as count,
|
|||
|
|
COUNT(IF(is_primary = 1, 1, NULL)) as primary_count
|
|||
|
|
FROM user_roles
|
|||
|
|
GROUP BY role_type
|
|||
|
|
ORDER BY count DESC;
|
|||
|
|
|
|||
|
|
-- 5. 查看前20条数据（包含用户信息）
|
|||
|
|
SELECT
|
|||
|
|
u.id,
|
|||
|
|
u.phone,
|
|||
|
|
u.role as user_table_role,
|
|||
|
|
ur.role_type as user_roles_table_role,
|
|||
|
|
IF(ur.is_primary = 1, '主身份', '附加身份') as role_status,
|
|||
|
|
ur.create_time
|
|||
|
|
FROM user u
|
|||
|
|
INNER JOIN user_roles ur ON u.id = ur.user_id
|
|||
|
|
ORDER BY u.id
|
|||
|
|
LIMIT 20;
|
|||
|
|
|
|||
|
|
-- 6. 检查数据一致性（user.role 应该等于 user_roles 中的主身份）
|
|||
|
|
SELECT
|
|||
|
|
'数据一致性检查' as check_item,
|
|||
|
|
COUNT(*) as inconsistent_count
|
|||
|
|
FROM user u
|
|||
|
|
LEFT JOIN user_roles ur ON u.id = ur.user_id AND ur.is_primary = 1
|
|||
|
|
WHERE u.role IS NOT NULL
|
|||
|
|
AND u.role != ''
|
|||
|
|
AND (ur.role_type IS NULL OR u.role != ur.role_type);
|