144 lines
3.1 KiB
SQL
144 lines
3.1 KiB
SQL
-- ============================================
|
||
-- 改名方案数据调试 SQL
|
||
-- ============================================
|
||
|
||
-- 1. 检查表是否存在
|
||
SELECT
|
||
'=== 表存在性检查 ===' as step,
|
||
COUNT(*) as table_exists
|
||
FROM information_schema.tables
|
||
WHERE table_schema = DATABASE()
|
||
AND table_name = 'yifan_naming_solutions';
|
||
|
||
-- 2. 查看所有数据（不加任何条件）
|
||
SELECT
|
||
'=== 所有数据（无条件） ===' as step;
|
||
|
||
SELECT
|
||
id,
|
||
created_id,
|
||
report_id,
|
||
name,
|
||
status,
|
||
is_deleted,
|
||
created_time
|
||
FROM yifan_naming_solutions
|
||
ORDER BY id DESC
|
||
LIMIT 10;
|
||
|
||
-- 3. 统计各种状态的数据
|
||
SELECT
|
||
'=== 数据统计 ===' as step;
|
||
|
||
SELECT
|
||
'总数据量' as stat_type,
|
||
COUNT(*) as count
|
||
FROM yifan_naming_solutions
|
||
UNION ALL
|
||
SELECT
|
||
'status=0(启用)' as stat_type,
|
||
COUNT(*) as count
|
||
FROM yifan_naming_solutions
|
||
WHERE status = 0
|
||
UNION ALL
|
||
SELECT
|
||
'status=1(停用)' as stat_type,
|
||
COUNT(*) as count
|
||
FROM yifan_naming_solutions
|
||
WHERE status = 1
|
||
UNION ALL
|
||
SELECT
|
||
'is_deleted=0(未删除)' as stat_type,
|
||
COUNT(*) as count
|
||
FROM yifan_naming_solutions
|
||
WHERE is_deleted = 0
|
||
UNION ALL
|
||
SELECT
|
||
'is_deleted=1(已删除)' as stat_type,
|
||
COUNT(*) as count
|
||
FROM yifan_naming_solutions
|
||
WHERE is_deleted = 1
|
||
UNION ALL
|
||
SELECT
|
||
'status=0 AND is_deleted=0' as stat_type,
|
||
COUNT(*) as count
|
||
FROM yifan_naming_solutions
|
||
WHERE status = 0 AND is_deleted = 0;
|
||
|
||
-- 4. 查看 created_id 分布
|
||
SELECT
|
||
'=== created_id 分布 ===' as step;
|
||
|
||
SELECT
|
||
created_id,
|
||
COUNT(*) as count,
|
||
GROUP_CONCAT(name SEPARATOR ', ') as names
|
||
FROM yifan_naming_solutions
|
||
GROUP BY created_id
|
||
ORDER BY count DESC;
|
||
|
||
-- 5. 查看你的用户ID（假设你是管理员）
|
||
SELECT
|
||
'=== 当前用户信息 ===' as step;
|
||
|
||
SELECT
|
||
id as user_id,
|
||
username,
|
||
name,
|
||
is_superuser,
|
||
dept_id
|
||
FROM sys_user
|
||
WHERE username = 'admin' OR id = 1
|
||
LIMIT 1;
|
||
|
||
-- 6. 模拟查询（假设当前用户ID=1）
|
||
SELECT
|
||
'=== 模拟查询（假设用户ID=1） ===' as step;
|
||
|
||
-- 无权限过滤
|
||
SELECT
|
||
'A. 无权限过滤' as query_type,
|
||
COUNT(*) as count
|
||
FROM yifan_naming_solutions;
|
||
|
||
-- 仅本人数据（created_id=1）
|
||
SELECT
|
||
'B. 仅本人数据(created_id=1)' as query_type,
|
||
COUNT(*) as count
|
||
FROM yifan_naming_solutions
|
||
WHERE created_id = 1;
|
||
|
||
-- 仅本人数据（created_id=当前用户）
|
||
SELECT
|
||
'C. 仅本人数据(created_id=当前用户)' as query_type,
|
||
COUNT(*) as count
|
||
FROM yifan_naming_solutions
|
||
WHERE created_id = (SELECT id FROM sys_user WHERE username = 'admin' LIMIT 1);
|
||
|
||
-- 7. 检查是否有 NULL 的 created_id
|
||
SELECT
|
||
'=== created_id 为 NULL 的数据 ===' as step;
|
||
|
||
SELECT
|
||
id,
|
||
name,
|
||
created_id,
|
||
status,
|
||
is_deleted
|
||
FROM yifan_naming_solutions
|
||
WHERE created_id IS NULL
|
||
LIMIT 5;
|
||
|
||
-- 8. 推荐的修复方案
|
||
SELECT
|
||
'=== 修复建议 ===' as step;
|
||
|
||
SELECT
|
||
'如果数据的 created_id 都不是你的用户ID，执行以下SQL：' as suggestion
|
||
UNION ALL
|
||
SELECT
|
||
'UPDATE yifan_naming_solutions SET created_id = 1 WHERE created_id IS NULL OR created_id != 1;' as suggestion
|
||
UNION ALL
|
||
SELECT
|
||
'（将 1 替换为你的实际用户ID）' as suggestion;
|