-- 全面检查数据库中的旧图片URL
-- 2026-01-25
-- 目的: 找出所有包含旧URL格式的记录

-- ========================================
-- 1. 检查banner表
-- ========================================
SELECT '=== banner表 ===' AS 检查项;
SELECT id, title, image_url, created_time 
FROM banner 
WHERE image_url LIKE '%example.com%' 
   OR image_url LIKE '%localhost%' 
   OR image_url LIKE '%:8080%'
   OR image_url LIKE '%192.168%'
ORDER BY created_time DESC;

-- ========================================
-- 2. 检查service表
-- ========================================
SELECT '=== service表 ===' AS 检查项;
SELECT id, name, image, created_time 
FROM service 
WHERE image LIKE '%example.com%' 
   OR image LIKE '%localhost%' 
   OR image LIKE '%:8080%'
   OR image LIKE '%192.168%'
ORDER BY created_time DESC;

-- ========================================
-- 3. 检查teacher表
-- ========================================
SELECT '=== teacher表 ===' AS 检查项;
SELECT id, teacher_name, avatar, created_time 
FROM teacher 
WHERE avatar LIKE '%example.com%' 
   OR avatar LIKE '%localhost%' 
   OR avatar LIKE '%:8080%'
   OR avatar LIKE '%192.168%'
ORDER BY created_time DESC;

-- ========================================
-- 4. 检查student表
-- ========================================
SELECT '=== student表 ===' AS 检查项;
SELECT id, student_name, avatar, created_time 
FROM student 
WHERE avatar LIKE '%example.com%' 
   OR avatar LIKE '%localhost%' 
   OR avatar LIKE '%:8080%'
   OR avatar LIKE '%192.168%'
ORDER BY created_time DESC;

-- ========================================
-- 5. 检查check_in_record表(签到照片)
-- ========================================
SELECT '=== check_in_record表 ===' AS 检查项;
SELECT id, order_id, teacher_id, photo_url, check_time 
FROM check_in_record 
WHERE photo_url LIKE '%example.com%' 
   OR photo_url LIKE '%localhost%' 
   OR photo_url LIKE '%:8080%'
   OR photo_url LIKE '%192.168%'
   OR photo_url LIKE '%tmp/%'  -- 微信临时路径
ORDER BY check_time DESC
LIMIT 20;

-- ========================================
-- 6. 检查learning_record表(学习记录)
-- ========================================
SELECT '=== learning_record表 ===' AS 检查项;
SELECT id, student_id, images, videos, created_time 
FROM learning_record 
WHERE images LIKE '%example.com%' 
   OR images LIKE '%localhost%' 
   OR images LIKE '%:8080%'
   OR images LIKE '%192.168%'
   OR videos LIKE '%example.com%' 
   OR videos LIKE '%localhost%' 
   OR videos LIKE '%:8080%'
   OR videos LIKE '%192.168%'
ORDER BY created_time DESC
LIMIT 20;

-- ========================================
-- 7. 检查review表(评价图片)
-- ========================================
SELECT '=== review表 ===' AS 检查项;
SELECT id, order_id, images, created_time 
FROM review 
WHERE images LIKE '%example.com%' 
   OR images LIKE '%localhost%' 
   OR images LIKE '%:8080%'
   OR images LIKE '%192.168%'
ORDER BY created_time DESC
LIMIT 20;

-- ========================================
-- 8. 统计各表旧URL数量
-- ========================================
SELECT '=== 统计汇总 ===' AS 检查项;

SELECT 
    'banner' AS 表名,
    COUNT(*) AS 旧URL数量
FROM banner 
WHERE image_url LIKE '%example.com%' 
   OR image_url LIKE '%localhost%' 
   OR image_url LIKE '%:8080%'
   OR image_url LIKE '%192.168%'

UNION ALL

SELECT 
    'service' AS 表名,
    COUNT(*) AS 旧URL数量
FROM service 
WHERE image LIKE '%example.com%' 
   OR image LIKE '%localhost%' 
   OR image LIKE '%:8080%'
   OR image LIKE '%192.168%'

UNION ALL

SELECT 
    'teacher' AS 表名,
    COUNT(*) AS 旧URL数量
FROM teacher 
WHERE avatar LIKE '%example.com%' 
   OR avatar LIKE '%localhost%' 
   OR avatar LIKE '%:8080%'
   OR avatar LIKE '%192.168%'

UNION ALL

SELECT 
    'student' AS 表名,
    COUNT(*) AS 旧URL数量
FROM student 
WHERE avatar LIKE '%example.com%' 
   OR avatar LIKE '%localhost%' 
   OR avatar LIKE '%:8080%'
   OR avatar LIKE '%192.168%'

UNION ALL

SELECT 
    'check_in_record' AS 表名,
    COUNT(*) AS 旧URL数量
FROM check_in_record 
WHERE photo_url LIKE '%example.com%' 
   OR photo_url LIKE '%localhost%' 
   OR photo_url LIKE '%:8080%'
   OR photo_url LIKE '%192.168%'
   OR photo_url LIKE '%tmp/%'

UNION ALL

SELECT 
    'learning_record' AS 表名,
    COUNT(*) AS 旧URL数量
FROM learning_record 
WHERE images LIKE '%example.com%' 
   OR images LIKE '%localhost%' 
   OR images LIKE '%:8080%'
   OR images LIKE '%192.168%'
   OR videos LIKE '%example.com%' 
   OR videos LIKE '%localhost%' 
   OR videos LIKE '%:8080%'
   OR videos LIKE '%192.168%'

UNION ALL

SELECT 
    'review' AS 表名,
    COUNT(*) AS 旧URL数量
FROM review 
WHERE images LIKE '%example.com%' 
   OR images LIKE '%localhost%' 
   OR images LIKE '%:8080%'
   OR images LIKE '%192.168%';