59 lines
1.3 KiB
SQL
59 lines
1.3 KiB
SQL
-- ============================================
|
||
-- 验证 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);
|