🐬
MySQL 치트시트

LIMIT 페이징, DATE_FORMAT, ON DUPLICATE KEY UPDATE, GROUP_CONCAT, 인덱스 힌트, 윈도우 함수, EXPLAIN 및 트랜잭션 락 레퍼런스
#mysql#sql#database#rdbms#innodb#explain#cte최종 업데이트: 2026-09-26
MySQL 치트시트 핵심 명령어 및 실전 가이드

LIMIT 페이징, DATE_FORMAT, ON DUPLICATE KEY UPDATE, GROUP_CONCAT, 인덱스 힌트, 윈도우 함수, EXPLAIN 및 트랜잭션 락 레퍼런스
## 1. 페이징 쿼리 (LIMIT & OFFSET)
```sql
-- 1. LIMIT count OFFSET offset (가장 가독성 높은 표준)
SELECT id, username, email
FROM users
ORDER BY id DESC
LIMIT 10 OFFSET 20; -- 21번째부터 10건 조회

-- 2. LIMIT offset, count (축약형 - 순서 주의: offset 먼저!)
SELECT id, username
FROM users
ORDER BY id DESC
LIMIT 20, 10;

-- 💡 대용량 페이징 성능 최적화 (커버링 인덱스 + No Offset 기법)
-- WHERE id < 마지막본_ID ORDER BY id DESC LIMIT 10;
```

## 2. 날짜 & 시간 함수 (Date & Time)
```sql
-- 현재 일시
SELECT NOW(), CURRENT_TIMESTAMP(), CURDATE(), CURTIME();

-- 1. 포맷 변환 (DATE_FORMAT / STR_TO_DATE)
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS formatted_now;
SELECT STR_TO_DATE('2026-09-26 14:30:00', '%Y-%m-%d %H:%i:%s');

-- 2. 날짜 가감 (DATE_ADD / DATE_SUB)
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY)   AS 일주일_후;
SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH) AS 한달_후;
SELECT DATE_SUB(NOW(), INTERVAL 1 HOUR)  AS 한시간_전;

-- 3. 날짜 차이 계산 (DATEDIFF / TIMESTAMPDIFF)
SELECT DATEDIFF('2026-09-26', '2026-09-01'); -- 25 (일 단위 차이)
SELECT TIMESTAMPDIFF(MINUTE, '2026-09-26 12:00:00', NOW()); -- 분 단위 차이
```

## 3. NULL 처리 & 조건 분기 (IFNULL & IF)
```sql
-- 1. IFNULL: null이면 대체값 반환
SELECT IFNULL(point, 0) FROM users;

-- 2. IF: 3항 연산자 조건문 (조건, 참값, 거짓값)
SELECT IF(score >= 80, '합격', '불합격') FROM exam_results;

-- 3. COALESCE: null이 아닌 첫 번째 값 반환
SELECT COALESCE(tel, mobile, email, '없음') FROM contacts;

-- 4. CASE WHEN 조건 분기
SELECT 
  CASE 
    WHEN role = 'ADMIN' THEN '관리자'
    WHEN role = 'USER'  THEN '일반회원'
    ELSE '게스트'
  END AS role_name
FROM users;
```

## 4. 문자열 조작 & 그룹 합치기 (GROUP_CONCAT)
```sql
-- 1. 문자열 연결 (CONCAT & CONCAT_WS)
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
SELECT CONCAT_WS('-', '010', '1234', '5678'); -- 구분자 자동 삽입 ('010-1234-5678')

-- 2. GROUP_CONCAT: 그룹별 여러 행 문자열을 한 줄로 합치기
SELECT dept_id, 
       GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') AS member_names
FROM employees
GROUP BY dept_id;

-- 3. 문자열 자르기 & 길이
SELECT SUBSTRING('MySQL', 1, 3); -- 'MyS' (1부터 시작)
SELECT CHAR_LENGTH('한글');      -- 2 (글자수)
SELECT LENGTH('한글');           -- 6 (UTF-8 바이트수)
```

## 5. Upsert: ON DUPLICATE KEY UPDATE
```sql
-- 1. MySQL 8.0.19 이상 최신 권장 문법 (AS new_val 별칭 활용)
INSERT INTO user_points (user_id, point, updated_at)
VALUES (101, 50, NOW()) AS new_row
ON DUPLICATE KEY UPDATE
  point = point + new_row.point,
  updated_at = new_row.updated_at;

-- 2. 레거시 문법 (VALUES(컬럼명) - 8.0.20부터 Deprecated)
INSERT INTO user_points (user_id, point)
VALUES (101, 50)
ON DUPLICATE KEY UPDATE
  point = point + VALUES(point);
```

## 6. AUTO_INCREMENT & 발급 ID 확인
```sql
CREATE TABLE boards (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL
);

INSERT INTO boards (title) VALUES ('첫 번째 공지');

-- 💡 현재 세션에서 방금 생성된 AUTO_INCREMENT ID 확인
SELECT LAST_INSERT_ID();
```

## 7. 인덱스 힌트 (USE / FORCE / IGNORE INDEX)
```sql
-- 1. 특정 인덱스 사용 권장 (USE INDEX)
SELECT * FROM orders USE INDEX (idx_order_date) 
WHERE order_date >= '2026-01-01';

-- 2. 특정 인덱스 강제 적용 (FORCE INDEX: 비용 계산 무시)
SELECT * FROM users FORCE INDEX (idx_users_email) 
WHERE email = 'test@example.com';

-- 3. 특정 인덱스 무시 (풀스캔 유도 시)
SELECT * FROM products IGNORE INDEX (idx_prod_category) 
WHERE category = 'SALE';
```

## 8. 윈도우 함수 & CTE (MySQL 8.0+)
```sql
-- 1. 순위 및 누적합
SELECT 
  emp_id, dept_id, salary,
  ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rank,
  SUM(salary)  OVER (PARTITION BY dept_id) AS dept_total
FROM employees;

-- 2. 계층형 재귀 쿼리 (WITH RECURSIVE)
WITH RECURSIVE CategoryPath AS (
  SELECT id, parent_id, name, CAST(name AS CHAR(200)) AS path
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, CONCAT(cp.path, ' > ', c.name)
  FROM categories c
  INNER JOIN CategoryPath cp ON c.parent_id = cp.id
)
SELECT * FROM CategoryPath;
```

## 9. 실행계획 분석 (EXPLAIN & FORMAT=TREE)
```sql
-- 1. 기본 실행계획 (type, possible_keys, key, rows 체크)
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- 💡 type 우선순위: const > eq_ref > ref > range > index > ALL (ALL은 풀테이블스캔)

-- 2. MySQL 8.0 신규 트리 포맷 실행계획 (실제 비용과 조인 알고리즘 직관적 파악)
EXPLAIN FORMAT=TREE 
SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE u.age > 20;

-- 3. 실제 실행 통계 측정 (EXPLAIN ANALYZE)
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
```

## 10. 세션 관리 & 트랜잭션 락 (FOR UPDATE)
```sql
-- 1. 비관적 락: 배타적 읽기 락 (동시 수정 방지)
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- 타 트랜잭션 대기
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
COMMIT;

-- 2. 현재 실행 중인 쿼리 목록 확인
SHOW FULL PROCESSLIST;

-- 3. 특정 쿼리/세션 강제 종료 (KILL id)
-- KILL 4821;

-- 4. InnoDB 락 상태 및 트랜잭션 모니터링 (8.0+)
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;
```
💬
MySQL 치트시트

의견 및 질문

댓글 수정

댓글 삭제
