섹션4. MySQL에서의 기본적인 CRUD

배치 처리 방법

INSERT INTO posts(...) VALUES
	(1,2,3),
	(1,2,3);

-- Prepared Statement로 반복 INSERT 최적화
PREPARE stmt_insert_comment FROM
    'INSERT INTO comments (post_id, user_id, content) VALUES (?, ?, ?)';
 
-- 변수 설정 후 실행 (루프에서 반복)
SET @post_id = 1;
SET @user_id = 1;
SET @content = '첫 번째 댓글';
 
EXECUTE stmt_insert_comment USING @post_id, @user_id, @content;
 
-- 다른 데이터로 재실행
SET @post_id = 1;
SET @user_id = 2;
SET @content = '두 번째 댓글';
SET @status = 'APPROVED';
 
EXECUTE stmt_insert_comment USING @post_id, @user_id, @content;
 
-- 정리
DEALLOCATE PREPARE stmt_insert_comment;
 

-- INSERT INTO
 
use crud_patterns;
 
-- 단일 사용자 등록
INSERT INTO users (username, email, password_hash, display_name)
VALUES ('john_doe', 'john@example.com', SHA2('password123', 256), 'John Doe');
 
-- 게시글 작성
INSERT INTO posts (user_id, category_id, title, content, status)
VALUES (1, 1, '제목', '내용', 'PUBLISHED');
 
-- 댓글 작성
INSERT INTO comments (post_id, user_id, content)
VALUES (1, 1, '댓글 내용');
 
 
 
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 (?, ?, ?)';
 
-- 변수 설정 후 실행 (루프에서 반복)
SET @post_id = 1;
SET @user_id = 1;
SET @content = '첫 번째 댓글';
 
EXECUTE stmt_insert_comment USING @post_id, @user_id, @content;
 
-- 다른 데이터로 재실행
SET @post_id = 1;
SET @user_id = 2;
SET @content = '두 번째 댓글';
SET @status = 'APPROVED';
 
EXECUTE stmt_insert_comment USING @post_id, @user_id, @content;
 
-- 정리
DEALLOCATE PREPARE stmt_insert_comment;
 
 
 
-- CSV 파일에서 직접 로드 (가장 빠른 방법)
LOAD DATA INFILE '/tmp/users_data.csv'
    INTO TABLE users
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    IGNORE 1 ROWS  -- 헤더 스킵
    (username, email, display_name, @password)
    SET password_hash = SHA2(@password, 256),
        status = 'ACTIVE',
        created_at = NOW();
 
 
-- 중복 시 에러 없이 건너뛰기
INSERT IGNORE INTO users (username, email, password_hash, display_name)
VALUES
    ('john_doe', 'john@example.com', SHA2('pass1', 256), 'John'),
    ('jane_doe', 'jane@example.com', SHA2('pass2', 256), 'Jane'),
    ('john_doe', 'john2@example.com', SHA2('pass3', 256), 'John2');
 
 
-- -> SELECT -> UPDATE or INSERT
 
-- 중복 시 특정 컬럼만 업데이트
INSERT INTO users (username, email, password_hash, display_name, login_count)
VALUES ('john_doe', 'john@example.com', SHA2('pass', 256), 'John Doe', 1)
ON DUPLICATE KEY UPDATE
         display_name = VALUES(display_name),
         login_count = login_count + 1,  -- 기존값 + 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', 'john_new@example.com', SHA2('newpass', 256), 'John Updated');
 
 
-- EXISTS를 이용한 조건부 삽입
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)
);
 
-- 다른 테이블에서 데이터 복사
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;
 
-- @Transactional
 
-- 다중 테이블 INSERT
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;
 

13. 복잡한 서비스 위한 SELECT 고급화 기법

커서 기반 페이징

SELECT
    post_id,
    title,
    view_count,
    created_at
FROM posts
WHERE status = 'PUBLISHED'
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;

SELECT
    post_id,
    title,
    view_count,
    created_at
FROM posts
WHERE status = 'PUBLISHED'
ORDER BY created_at DESC
LIMIT 10 OFFSET 10;


SELECT post_id, title, created_at
FROM posts
WHERE status = 'PUBLISHED'
ORDER BY created_at DESC
LIMIT 10 OFFSET 10000;


-- 커서 기반의 페이징 첫번쨰 페이지
SELECT post_id, title, created_at
FROM posts
WHERE status = 'PUBLISHED'
ORDER BY created_at DESC, post_id DESC
LIMIT 10;

-- 커서 기반의 페이징 두번쨰 페이지
SELECT post_id, title, created_at
FROM posts
WHERE status = 'PUBLISHED' AND post_id < 12345
ORDER BY created_at DESC, post_id DESC
LIMIT 10;


SELECT
    기본컬럼들,
    윈도우함수() OVER (PARTITION BY 그룹컬럼 ORDER BY 정렬컬럼) as 결과컬럼
FROM 테이블;


SELECT
    post_id,
    title,
    view_count,

    -- 순차적인 순위 (1,2,3,4...)
    ROW_NUMBER() OVER (ORDER BY view_count DESC) as 전체순위,

    -- 동점자 고려한 순위 (1,2,2,4...)
    RANK() OVER (ORDER BY view_count DESC) as 랭킹

FROM posts
WHERE status = 'PUBLISHED'
ORDER BY view_count DESC;

SELECT
    user_id,
    post_id,
    title,
    view_count,

    -- 각 사용자 안에서의 순위
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY view_count DESC) as 사용자내순위

FROM posts
WHERE status = 'PUBLISHED'
ORDER BY user_id, 사용자내순위;


SELECT
    post_id,
    title,
    created_at,
    view_count,

    -- 이전 게시글의 조회수
    LAG(view_count, 1) OVER (ORDER BY created_at) as 이전글조회수,

    -- 다음 게시글의 조회수
    LEAD(view_count, 1) OVER (ORDER BY created_at) as 다음글조회수

FROM posts
WHERE status = 'PUBLISHED'
ORDER BY created_at;

WITH 임시테이블명 AS (
    SELECT ... -- 1단계 계산
    )
SELECT ...     -- 2단계에서 1단계 결과 사용
    FROM 임시테이블명;

WITH post_summary AS (
    -- 1단계: 사용자별 게시글 통계 계산
    SELECT
        user_id,
        COUNT(*) as 게시글수,
        SUM(view_count) as 총조회수,
        AVG(view_count) as 평균조회수
    FROM posts
    WHERE status = 'PUBLISHED'
    GROUP BY user_id
)
-- 2단계: 사용자 정보와 결합해서 최종 결과
SELECT
    u.username,
    ps.게시글수,
    ps.총조회수,
    ps.평균조회수
FROM post_summary ps
         INNER JOIN users u ON ps.user_id = u.user_id
ORDER BY ps.총조회수 DESC;

SELECT
    u.username,
    COUNT(*) as 게시글수,
    SUM(p.view_count) as 총조회수,
    AVG(p.view_count) as 평균조회수
FROM posts p
         INNER JOIN users u ON p.user_id = u.user_id
WHERE p.status = 'PUBLISHED'
GROUP BY u.user_id, u.username
ORDER BY SUM(p.view_count) DESC;




WITH 활성사용자 AS (
    -- 1단계: 활성 사용자만 추출
    SELECT user_id, username
    FROM users
    WHERE status = 'ACTIVE'
),
     최근게시글 AS (
         -- 2단계: 최근 30일 게시글 통계
         SELECT user_id, COUNT(*) as 최근글수
         FROM posts
         WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
           AND status = 'PUBLISHED'
         GROUP BY user_id
     )
-- 3단계: 두 결과를 조합
SELECT
    a.username,
    COALESCE(r.최근글수, 0) as 최근30일글수
FROM 활성사용자 a
         LEFT JOIN 최근게시글 r ON a.user_id = r.user_id
ORDER BY 최근30일글수 DESC;

SELECT
    u.username,
    COALESCE(r.최근글수, 0) as 최근30일글수
FROM users u
         LEFT JOIN (
    SELECT user_id, COUNT(*) as 최근글수
    FROM posts
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
      AND status = 'PUBLISHED'
    GROUP BY user_id
) r ON u.user_id = r.user_id
WHERE u.status = 'ACTIVE'
ORDER BY 최근30일글수 DESC;


SELECT
    user_id,
    username
FROM users u
WHERE status = 'ACTIVE'
  AND EXISTS (
    SELECT 1  -- 실제 값은 중요하지 않음, 존재만 확인
    FROM posts p
    WHERE p.user_id = u.user_id
      AND p.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
);

SELECT username
FROM users u
WHERE EXISTS (
    SELECT 1 FROM posts p WHERE p.user_id = u.user_id
);

SELECT username
FROM users u
WHERE u.user_id IN (
    SELECT user_id FROM posts
);

14. UPDATE, DELETE 완벽 가이드

UPDATE users
SET last_login_at = CURRENT_TIMESTAMP
WHERE user_id = 1;

UPDATE users
SET
    display_name = '새로운 표시명',
    profile_image_url = 'https://example.com/new-image.jpg',
    updated_at = CURRENT_TIMESTAMP
WHERE user_id = 123 AND status = 'ACTIVE';


UPDATE posts
SET like_count = CASE
        WHEN like_count > 0 THEN like_count - 1
        ELSE like_count + 1
    END
WHERE post_id = 1;

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);

UPDATE posts p
    INNER JOIN users u ON p.user_id = u.user_id
SET p.author_name = u.display_name
WHERE u.updated_at > p.updated_at;

UPDATE users
SET status = 'INACTIVE'
    WHERE last_login_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
    AND status = 'ACTIVE'
LIMIT 1000;


DELIMITER //

CREATE PROCEDURE BatchUpdateInactiveUsers()
BEGIN
    DECLARE affected_rows INT DEFAULT 1;

    WHILE affected_rows > 0 DO
            UPDATE users
            SET status = 'INACTIVE',
                updated_at = CURRENT_TIMESTAMP
            WHERE last_login_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
              AND status = 'ACTIVE'
            LIMIT 1000;

            SET affected_rows = ROW_COUNT();  -- 실제 수정된 행 수

            SELECT SLEEP(0.1);  -- 0.1초 대기 (시스템 부하 방지)
        END WHILE;
END //

DELIMITER ;

CALL BatchUpdateInactiveUsers();


DELIMITER $$

CREATE PROCEDURE safe_deduct_points()
BEGIN
    DECLARE current_points INT DEFAULT 0;

    -- 트랜잭션 시작
    START TRANSACTION;

    -- 1. 현재 포인트 확인 (락 걸기)
    SELECT COALESCE(points, 0) INTO current_points
    FROM users
    WHERE user_id = 1 FOR UPDATE;

    -- 2. 포인트가 충분한지 검증
    IF current_points >= 100 THEN
        -- 3. 포인트 차감
        UPDATE users
        SET points = points - 100,
            updated_at = CURRENT_TIMESTAMP
        WHERE user_id = 1;

        -- 4. 사용 내역 기록
        INSERT INTO point_history (user_id, amount, type, description)
        VALUES (1, -100, 'USE', '상품 구매');

        COMMIT;  -- 성공
    ELSE
        ROLLBACK;  -- 포인트 부족으로 실패
    END IF;
END$$

DELIMITER ;


-- ---------------------------------------------------------------------------------------------------

DELETE FROM comments
WHERE comment_id = 456
  AND user_id = 123;

UPDATE posts
SET status = 'DELETED',
    updated_at = CURRENT_TIMESTAMP
WHERE post_id = 1 AND user_id = 1;

DELIMITER //

CREATE PROCEDURE BatchDeleteOldData()
BEGIN
    DECLARE affected_rows INT DEFAULT 1;

    WHILE affected_rows > 0 DO
            -- 30일 이상 삭제된 댓글 실제 삭제
            DELETE FROM comments
            WHERE status = 'DELETED'
              AND updated_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
            LIMIT 1000;

            SET affected_rows = ROW_COUNT();

            -- 진행상황 출력
            SELECT CONCAT('Deleted ', affected_rows, ' comments') as progress;

            SELECT SLEEP(0.5);  -- 0.5초 대기
        END WHILE;
END //

DELIMITER ;

CALL BatchDeleteOldData();


ALTER TABLE logs DROP PARTITION p_202301;