MySQL ‐ Basic CRUD in MySQL - thought-corner/backend-roadmap GitHub Wiki

INSERT INTO posts (user_id, category_id, title, content, status) VALUES
     (1, 1, '제목1', '내용1', 'PUBLISHED'),
     (1, 2, '제목2', '내용2', 'PUBLISHED'),
     (2, 1, '제목3', '내용3', 'DRAFT');

-- Prepared Statement로 반복 INSERT 최적화
PREPARE stmt_insert_comment FROM
    'INSERT INTO comments (post_id, user_id, content) VALUES (?, ?, ?)';

START TRANSACTION;

-- 1. 게시글 작성
INSERT INTO posts (user_id, category_id, title, content, status)
VALUES (1, 1, '새 게시글', '내용', 'PUBLISHED');

SET @new_post_id = LAST_INSERT_ID();

-- 2. 첫 댓글 자동 생성 (같은 트랜잭션 → 원자적으로 함께 커밋/롤백)
INSERT INTO comments (post_id, user_id, content, status)
VALUES (@new_post_id, 1, '게시글을 작성했습니다.', 'APPROVED');

COMMIT;

PREPARE stmt_insert_comment FROM
    'INSERT INTO comments (post_id, user_id, content) VALUES (?, ?, ?)';

INSERT IGNORE INTO users (username, email, password_hash, display_name)
VALUES
    ('john_doe', '[email protected]',  SHA2('pass1', 256), 'John'),
    ('jane_doe', '[email protected]',  SHA2('pass2', 256), 'Jane'),
    ('john_doe', '[email protected]', SHA2('pass3', 256), 'John2');  -- 중복 → 조용히 스킵

-- 중복 시 특정 컬럼 업데이트
INSERT INTO users (username, email, password_hash, display_name, login_count)
VALUES ('john_doe', '[email protected]', SHA2('pass', 256), 'John Doe', 1)
ON DUPLICATE KEY UPDATE
    display_name = VALUES(display_name),
    login_count  = login_count + 1,           -- 기존 값 기반 증감
    updated_at   = CURRENT_TIMESTAMP;

-- 복잡한 업데이트 로직 (기존 내용에 이어붙이기 + 카운터 누적)
INSERT INTO posts (user_id, category_id, title, content, view_count)
VALUES (1, 1, '제목', '내용', 1)
ON DUPLICATE KEY UPDATE
    content    = CONCAT(content, '\n--- 업데이트됨 ---\n', VALUES(content)),
    view_count = view_count + VALUES(view_count),
    updated_at = CURRENT_TIMESTAMP;

REPLACE INTO users (user_id, username, email, password_hash, display_name)
VALUES (1, 'john_updated', '[email protected]', SHA2('newpass', 256), 'John Updated');

INSERT INTO posts (user_id, category_id, title, content, status)
SELECT
    user_id,
    1 AS category_id,
    CONCAT('임시 제목 - ', user_id) AS title,
    '자동 생성된 내용' AS content,
    'DRAFT' AS status
FROM users
WHERE status = 'ACTIVE'
  AND login_count > 10;

INSERT INTO comments (post_id, user_id, content)
SELECT 1, 2, '댓글 내용'
WHERE EXISTS (SELECT 1 FROM posts WHERE post_id = 1 AND status = 'PUBLISHED')
  AND NOT EXISTS (
        SELECT 1 FROM comments
        WHERE post_id = 1 AND user_id = 2
          AND created_at > DATE_SUB(NOW(), INTERVAL 1 MINUTE)   -- 도배 방지
      );

SET unique_checks = 0;
SET foreign_key_checks = 0;
-- ... 대량 INSERT / LOAD DATA ...
SET unique_checks = 1;
SET foreign_key_checks = 1;

INSERT INTO posts (...) VALUES (...);
SELECT LAST_INSERT_ID();          -- 방금 이 커넥션에서 생성된 AUTO_INCREMENT 값

-- ✅ 접두사 검색: 인덱스 range scan 가능 ('john' 으로 시작하는 구간만)
SELECT user_id, username
FROM users
WHERE username LIKE 'john%' AND status = 'ACTIVE';

-- ❌ 앞 와일드카드: 인덱스 무력화 → 풀 테이블 스캔
SELECT post_id, title
FROM posts
WHERE status = 'PUBLISHED'
  AND (
        title   LIKE '%데이터베이스%'
     OR content LIKE '%성능%'
      )
ORDER BY created_at DESC;

-- 전문 검색 인덱스 활용 (긴 본문에서 LIKE '%...%' 보다 훨씬 효율적)
SELECT post_id, title, content
FROM posts
WHERE MATCH(title, content) AGAINST('MySQL 최적화' IN NATURAL LANGUAGE MODE)
  AND status = 'PUBLISHED';

-- (user_id, status, created_at) 인덱스가 세 컬럼을 덮으면 테이블에 안 감 → 커버링
SELECT status, created_at FROM orders WHERE user_id = ?;

-- (status, created_at) 인덱스 → 필터 + 정렬을 한 인덱스로 해결
SELECT post_id, title FROM posts
WHERE status = 'PUBLISHED'
ORDER BY created_at DESC
LIMIT 20;

-- ❌ OFFSET 방식: 앞의 100,000행을 읽고 버림 → 뒤로 갈수록 급격히 느려짐
SELECT * FROM posts ORDER BY id DESC LIMIT 20 OFFSET 100000;

-- ✅ keyset 방식: 마지막으로 본 id 기준으로 이어서 → 항상 일정하게 빠름
SELECT * FROM posts
WHERE id < :last_seen_id
ORDER BY id DESC
LIMIT 20;

-- ❌ 불가: WHERE 절에서 윈도우 함수를 참조할 수 없음
-- SELECT ... , ROW_NUMBER() OVER (...) rn FROM posts WHERE rn <= 3;

-- ✅ 서브쿼리로 감싸 바깥에서 필터
SELECT *
FROM (
    SELECT post_id, category_id, view_count,
           ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY view_count DESC) AS rn
    FROM posts
) t
WHERE rn <= 3;   -- 카테고리별 조회수 상위 3개

WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 1 AS depth
    FROM categories
    WHERE parent_id IS NULL          -- 최상위(anchor)

    UNION ALL

    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id   -- 재귀
)
SELECT * FROM category_tree ORDER BY depth;

-- ❌ 버그: order_items 행 수만큼 orders.total 이 중복 합산됨
SELECT o.user_id, SUM(o.total_amount)
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY o.user_id;

-- ✅ 조인 전에 먼저 집계하거나, 서브쿼리로 분리
SELECT o.user_id, SUM(o.total_amount)
FROM orders o
WHERE EXISTS (SELECT 1 FROM order_items i WHERE i.order_id = o.id)
GROUP BY o.user_id;

-- ❌ COUNT(*)는 매칭 안 된 행도 1로 셈 → 댓글 0개 글도 1로 나옴
SELECT p.id, COUNT(*)          FROM posts p LEFT JOIN comments c ON c.post_id = p.id GROUP BY p.id;
-- ✅ 자식 키를 세면 NULL(미매칭)은 제외됨 → 실제 댓글 수
SELECT p.id, COUNT(c.id)       FROM posts p LEFT JOIN comments c ON c.post_id = p.id GROUP BY p.id;

-- status 필터는 집계 전이므로 WHERE (HAVING에 두면 불필요하게 다 집계 후 버림)
SELECT user_id, COUNT(*) AS cnt
FROM posts
WHERE status = 'PUBLISHED'          -- 집계 전 필터
GROUP BY user_id
HAVING cnt >= 10;                   -- 집계 후 필터

-- ❌ blocked_user_id 에 NULL이 섞이면 결과가 통째로 사라짐
SELECT * FROM users
WHERE id NOT IN (SELECT blocked_user_id FROM blocks);

-- ✅ NOT EXISTS 는 NULL-safe
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM blocks b WHERE b.blocked_user_id = u.id);

-- ✅ (created_at, id) 튜플 비교로 안전하게 이어감
SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

SET SQL_SAFE_UPDATES = 1;   -- 세션에서 키 없는 UPDATE/DELETE 차단

UPDATE posts
SET status = CASE
        WHEN view_count < 10    THEN 'DRAFT'       -- 조회수 낮으면 임시저장
        WHEN view_count >= 1000 THEN 'FEATURED'    -- 조회수 높으면 추천글
        ELSE 'PUBLISHED'                           -- 나머지는 일반 발행
    END,
    updated_at = CURRENT_TIMESTAMP
WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 대량 데이터를 나눠서 업데이트 — 단, "1회"가 아니라 반복 실행해야 완성된다
UPDATE users
SET status = 'INACTIVE'
WHERE last_login_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
  AND status = 'ACTIVE'     -- ✅ 이미 바꾼 행을 자동 제외 → 같은 쿼리 반복이 안전(루프-세이프)
LIMIT 1000;

DELETE FROM comments
WHERE comment_id = 456   -- WHERE는 필수: 없으면 테이블 전체 삭제
  AND user_id    = 123;

UPDATE comments SET deleted_at = NOW() WHERE comment_id = 456 AND user_id = 123;

-- 커서: 직전 배치에서 처리한 마지막 id (@last_id, 시작은 0)
-- 1) 이번 배치의 상한 id 를 구한다 (PK 순서로 1000개 앞)
SELECT id INTO @batch_max
FROM users
WHERE id > @last_id
ORDER BY id
LIMIT 1 OFFSET 999;        -- 1000번째 행의 id (없으면 NULL → 마지막 배치)

-- 2) 그 구간만 변경 (인덱스 범위 → 락 발자국 최소)
UPDATE users
SET status = 'INACTIVE'
WHERE id > @last_id
  AND (@batch_max IS NULL OR id <= @batch_max)
  AND last_login_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
  AND status = 'ACTIVE';

-- 3) 커서 전진 후 커밋. @batch_max 가 NULL 이면 종료.
SET @last_id = @batch_max;
COMMIT;
-- affected rows / @batch_max 를 보고 루프 지속 여부 결정. 배치 사이 짧은 휴지 권장.