(phpMyAdmin SQL 탭에 블록 단위로 그대로 붙여넣어 실행하면 됨)

0) 지금 내가 붙어있는 DB가 맞는지

SELECT DATABASE() AS current_db;
SHOW VARIABLES LIKE 'version%';

1) SQL Mode 차이(0값/자동증가에 영향 줄 수 있음)

SELECT @@sql_mode AS sql_mode;

• 결과에 NO_AUTO_VALUE_ON_ZERO가 있으면 id=0이 그대로 박힐 수 있는 환경이라 체크 포인트.

2) 핵심 테이블 “정의” 확인 (AUTO_INCREMENT/PK/엔진/문자셋)

SHOW CREATE TABLE g5_qb_exams;
SHOW CREATE TABLE g5_qb_exam_templates;
SHOW CREATE TABLE g5_qb_categories;
SHOW CREATE TABLE g5_qb_questions;
SHOW CREATE TABLE g5_qb_exam_questions;
SHOW CREATE TABLE g5_qb_exam_attempts;
SHOW CREATE TABLE g5_qb_exam_attempt_answers;

SHOW CREATE TABLE g5_qb_exam_template_attempts;
SHOW CREATE TABLE g5_qb_exam_template_attempt_questions;
SHOW CREATE TABLE g5_qb_exam_template_attempt_answers;

3) AUTO_INCREMENT가 “진짜로” 걸려있는지 빠른 확인(정보스키마)

SELECT
  t.TABLE_NAME,
  t.ENGINE,
  t.TABLE_COLLATION,
  t.AUTO_INCREMENT
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_SCHEMA = DATABASE()
  AND t.TABLE_NAME IN (
    'g5_qb_exams',
    'g5_qb_exam_templates',
    'g5_qb_categories',
    'g5_qb_questions'
  )
ORDER BY t.TABLE_NAME;

• AUTO_INCREMENT가 NULL이면 자동증가가 안 잡혀있거나, 테이블이 비어있어도 확인이 필요.

4) “0 ID” 즉시 탐지(가장 중요)

-- 고정 시험
SELECT COUNT(*) AS exams_id0 FROM g5_qb_exams WHERE exam_id = 0;

-- 템플릿
SELECT COUNT(*) AS tpl_id0 FROM g5_qb_exam_templates WHERE template_id = 0;

-- 카테고리(이건 이제 auto_increment면 보통 0 없어야 정상)
SELECT COUNT(*) AS cat_id0 FROM g5_qb_categories WHERE category_id = 0;

-- 문제(문제 PK가 question_id라면 0 체크)
SELECT COUNT(*) AS q_id0 FROM g5_qb_questions WHERE question_id = 0;

5) PK 중복/깨짐 빠른 점검

-- 고정시험 PK 중복
SELECT exam_id, COUNT(*) AS cnt
FROM g5_qb_exams
GROUP BY exam_id
HAVING cnt > 1;

-- 템플릿 PK 중복
SELECT template_id, COUNT(*) AS cnt
FROM g5_qb_exam_templates
GROUP BY template_id
HAVING cnt > 1;

-- 카테고리 PK 중복
SELECT category_id, COUNT(*) AS cnt
FROM g5_qb_categories
GROUP BY category_id
HAVING cnt > 1;

-- 문제 PK 중복
SELECT question_id, COUNT(*) AS cnt
FROM g5_qb_questions
GROUP BY question_id
HAVING cnt > 1;

6) 연결(참조) 무결성 체크(고아 데이터 탐지)

(A) 시험에 연결된 문항이 실제로 존재하는지

-- exam_questions에 있는데 exams가 없는 고아
SELECT eq.exam_id, COUNT(*) AS cnt
FROM g5_qb_exam_questions eq
LEFT JOIN g5_qb_exams e ON e.exam_id = eq.exam_id
WHERE e.exam_id IS NULL
GROUP BY eq.exam_id;

-- exam_questions에 있는데 questions가 없는 고아
SELECT eq.question_id, COUNT(*) AS cnt
FROM g5_qb_exam_questions eq
LEFT JOIN g5_qb_questions q ON q.question_id = eq.question_id
WHERE q.question_id IS NULL
GROUP BY eq.question_id;

(B) 고정시험 응시기록이 시험/문항과 일관되는지

-- attempts에 있는데 exams가 없는 고아
SELECT a.exam_id, COUNT(*) AS cnt
FROM g5_qb_exam_attempts a
LEFT JOIN g5_qb_exams e ON e.exam_id = a.exam_id
WHERE e.exam_id IS NULL
GROUP BY a.exam_id;

-- attempt_answers에 있는데 attempts가 없는 고아
SELECT aa.attempt_id, COUNT(*) AS cnt
FROM g5_qb_exam_attempt_answers aa
LEFT JOIN g5_qb_exam_attempts a ON a.attempt_id = aa.attempt_id
WHERE a.attempt_id IS NULL
GROUP BY aa.attempt_id;

-- attempt_answers에 있는데 questions가 없는 고아
SELECT aa.question_id, COUNT(*) AS cnt
FROM g5_qb_exam_attempt_answers aa
LEFT JOIN g5_qb_questions q ON q.question_id = aa.question_id
WHERE q.question_id IS NULL
GROUP BY aa.question_id;

(C) 템플릿 응시(랜덤) 쪽 고아 체크

-- template_attempts에 있는데 templates가 없는 고아
SELECT ta.template_id, COUNT(*) AS cnt
FROM g5_qb_exam_template_attempts ta
LEFT JOIN g5_qb_exam_templates t ON t.template_id = ta.template_id
WHERE t.template_id IS NULL
GROUP BY ta.template_id;

-- attempt_questions에 있는데 attempt가 없는 고아
SELECT aq.attempt_id, COUNT(*) AS cnt
FROM g5_qb_exam_template_attempt_questions aq
LEFT JOIN g5_qb_exam_template_attempts ta ON ta.attempt_id = aq.attempt_id
WHERE ta.attempt_id IS NULL
GROUP BY aq.attempt_id;

-- attempt_questions에 있는데 question이 없는 고아
SELECT aq.question_id, COUNT(*) AS cnt
FROM g5_qb_exam_template_attempt_questions aq
LEFT JOIN g5_qb_questions q ON q.question_id = aq.question_id
WHERE q.question_id IS NULL
GROUP BY aq.question_id;

7) “대표 샘플”로 실제 링크에 들어갈 값 확인

-- 고정시험 가장 작은/큰 ID 확인
SELECT MIN(exam_id) AS min_id, MAX(exam_id) AS max_id, COUNT(*) AS cnt FROM g5_qb_exams;

-- 템플릿 가장 작은/큰 ID 확인
SELECT MIN(template_id) AS min_id, MAX(template_id) AS max_id, COUNT(*) AS cnt FROM g5_qb_exam_templates;

-- id=0 행이 있으면 제목까지 확인
SELECT exam_id, exam_name FROM g5_qb_exams WHERE exam_id = 0;
SELECT template_id, title FROM g5_qb_exam_templates WHERE template_id = 0;

운영 팁(가져오기 후 루틴)

1. import 끝나자마자 4) 0 ID 체크

2. 바로 2) SHOW CREATE TABLE로 AUTO_INCREMENT/PK 확인

3. 이상 있으면 그때만 “정상화 SQL(0 → 새번호 + AUTO_INCREMENT 복구)” 실행