시작하기
MySQL 연결
mysql 클라이언트가 MySQL의 표준 CLI입니다. 항상 -p(뒤에 공백 없음)를 써서 비밀번호를 셸 히스토리에 노출하지 말고 안전하게 프롬프트로 받으세요. -h는 호스트, -P(대문자)는 포트를 설정합니다. \s는 연결 상태를 출력합니다. 스크립트에서 일회성 쿼리는 -e로 실행하세요.
# connect to local server (prompt for password)
mysql -u root -p
# connect to a remote host on a custom port
mysql -h 192.168.1.100 -P 3307 -u admin -p
# connect directly to a specific database
mysql -u root -p mydb
# non-interactive: run a query and exit
mysql -u root -p -e "SELECT VERSION();"
# show server status inside the client
\s
SELECT VERSION(), CURRENT_USER, DATABASE();데이터베이스 관리
이모지를 포함한 완전한 유니코드 지원에는 utf8이 아닌 utf8mb4를 쓰세요. MySQL의 utf8은 모든 문자를 저장할 수 없는 3바이트 하위집합입니다. 올바른 정렬을 위해 utf8mb4_unicode_ci 콜레이션이 권장됩니다. IF EXISTS/IF NOT EXISTS는 스크립트에서 오류를 막습니다. DROP DATABASE는 모든 테이블과 데이터를 즉시 삭제합니다.
# list all databases
SHOW DATABASES;
# create a database with charset and collation
CREATE DATABASE mydb
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
# switch to a database
USE mydb;
# show the current database
SELECT DATABASE();
# drop a database (irreversible)
DROP DATABASE IF EXISTS mydb;
# alter a database's charset
ALTER DATABASE mydb CHARACTER SET utf8mb4;객체 확인과 설명
SHOW 명령은 MySQL 전용 인트로스펙션 도구입니다. DESCRIBE는 컬럼을 빠르게 개관하고, SHOW CREATE TABLE은 재사용 가능한 전체 DDL을 줍니다. 세미콜론 대신 \G를 붙이면 세로 출력이 되어 SHOW CREATE TABLE처럼 넓은 행에서 훨씬 읽기 좋습니다.
# list tables in the current database
SHOW TABLES;
# show table structure (columns, types, keys)
DESCRIBE users;
# equivalent
SHOW COLUMNS FROM users;
# show create statement for a table
SHOW CREATE TABLE users\G
# list indexes on a table
SHOW INDEX FROM users;
# show stored procedures / functions
SHOW PROCEDURE STATUS WHERE Db = 'mydb';
SHOW FUNCTION STATUS WHERE Db = 'mydb';
# list triggers
SHOW TRIGGERS\G서버 상태와 변수
MySQL 변수에는 SESSION(현재 연결)과 GLOBAL 스코프가 있습니다. SET GLOBAL은 실행 중인 서버를 바꾸지만 재시작하면 초기화되므로 설정은 my.cnf에 영속화하세요. @@var로 값을 읽습니다. SHOW STATUS는 모니터링에 유용한 런타임 카운터(연결, 업타임, 처리량)를 노출합니다.
# view a system variable
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'version%';
# view session vs global variable
SELECT @@session.sql_mode, @@global.sql_mode;
# set a variable for the current session
SET sql_mode = 'STRICT_TRANS_TABLES';
SET @@session.foreign_key_checks = 0;
# set globally (needs SUPER/privilege, persists until restart)
SET GLOBAL max_connections = 200;
# show server status counters
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Uptime';주석과 문장 기본
MySQL은 세 가지 주석 스타일을 지원합니다. -- (뒤 공백 필요), /* */ 블록, #부터 줄 끝. /*! ... */은 특수 실행 주석으로, 그 안의 코드는 MySQL에서만 실행되고 다른 SQL 엔진은 무시하므로 이식성 있는 스키마 파일에 유용합니다. \G는 실행하고 결과를 세로로 표시합니다.
-- single-line comment (note the space after --)
SELECT 1; -- inline comment
/* multi-line
comment block */
SELECT 2;
# hash-style comment (MySQL-specific, to end of line)
SELECT 3;
# MySQL executable comments: run only on MySQL
SELECT 1 /*!50100 , 2 */; /* the part runs on MySQL >= 5.1 */
# statements end with semicolon; \G ends and prints vertically
SELECT * FROM users\G설정 파일 (my.cnf)
my.cnf(Linux) / my.ini(Windows)는 섹션으로 조직된 영속적 서버/클라이언트 설정을 담습니다. 서버는 [mysqld], 클라이언트는 [client]/[mysql]. innodb_buffer_pool_size는 InnoDB의 가장 중요한 튜닝 노브입니다(보통 RAM의 50~75%). 수정 후에는 서버를 재시작하고, mysql --help 또는 SHOW VARIABLES로 확인하세요.
# /etc/my.cnf or ~/.my.cnf (Linux/macOS)
# C:\ProgramData\MySQL\MySQL Server 8.0\my.ini (Windows)
[mysqld]
port = 3306
datadir = /var/lib/mysql
max_connections = 200
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
innodb_buffer_pool_size = 2G
slow_query_log = 1
long_query_time = 2
[client]
default-character-set = utf8mb4
[mysql]
prompt = \u@\h [\d]>\_DDL 연산
테이블 생성
CREATE TABLE은 컬럼, 타입, 제약, 테이블 옵션을 정의합니다. InnoDB가 기본이자 권장 엔진입니다(트랜잭션, 행 수준 잠금, 외래 키 지원). AUTO_INCREMENT에는 BIGINT UNSIGNED가 오버플로를 피합니다. DECIMAL(p,s)은 돈에 정확합니다. ENUM은 고정 목록에 대해 검증합니다. TIMESTAMP DEFAULT CURRENT_TIMESTAMP는 삽입 시 자동 채웁됩니다.
CREATE TABLE users (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL,
birth_date DATE NULL,
status ENUM('active','inactive','banned') NOT NULL DEFAULT 'active',
balance DECIMAL(10,2) NOT NULL DEFAULT 0.00,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;테이블 변경
ALTER TABLE은 데이터를 재생성하지 않고 스키마를 진화시킵니다. AFTER와 함께 쓴 ADD COLUMN은 특정 위치에 배치합니다(컬럼 순서는 MySQL에서 미적 요소). MODIFY는 타입/기본값을 바꾸고, RENAME COLUMN(8.0+)이 CHANGE보다 깔끔합니다. 큰 ALTER는 테이블을 재구축하고 잠글 수 있으니 큰 테이블에는 pt-online-schema-change 또는 온라인 DDL을 쓰세요. RENAME TABLE은 원자적입니다.
# add a column
ALTER TABLE users
ADD COLUMN phone VARCHAR(20) NULL AFTER email;
# add multiple columns
ALTER TABLE users
ADD COLUMN first_name VARCHAR(50) NULL,
ADD COLUMN last_name VARCHAR(50) NULL;
# modify a column type
ALTER TABLE users
MODIFY COLUMN phone VARCHAR(30) NOT NULL;
# rename a column (MySQL 8+ preserves data)
ALTER TABLE users
RENAME COLUMN phone TO phone_number;
# rename a table
RENAME TABLE users TO members;
ALTER TABLE users RENAME TO members;Drop과 Truncate
DROP TABLE은 테이블을 완전히 제거하고, TRUNCATE는 구조를 유지한 채 비우며 AUTO_INCREMENT를 시작값으로 되돌립니다. TRUNCATE는 행별 삭제와 로깅을 건너뛰므로 DELETE보다 빠르지만 DDL입니다(롤백 불가, 트리거 미발동). 다른 테이블이 참조하는 테이블을 truncate할 때는 foreign_key_checks를 끄세요.
# drop a table (removes structure and data)
DROP TABLE IF EXISTS old_logs;
# drop multiple tables
DROP TABLE IF EXISTS temp1, temp2;
# truncate: empty the table, keep structure, reset AUTO_INCREMENT
TRUNCATE TABLE session_data;
# truncate cannot be rolled back in some engines
# (InnoDB: TRUNCATE is DDL, implicitly commits)
SET foreign_key_checks = 0;
TRUNCATE TABLE child_table;
SET foreign_key_checks = 1;AUTO_INCREMENT
AUTO_INCREMENT는 기본 키에 순차 번호를 생성합니다. 테이블당 하나만 가능합니다. LAST_INSERT_ID()는 현재 세션에서 가장 최근 INSERT가 생성한 id를 반환합니다(연결별이므로 동시 사용에 안전). 삭제, 롤백, 명시적 값 삽입 뒤에는 갭이 생길 수 있습니다. 리셋은 ALTER TABLE ... AUTO_INCREMENT = n으로 합니다.
CREATE TABLE orders (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
amount DECIMAL(10,2)
);
# set the next auto-increment value
ALTER TABLE orders AUTO_INCREMENT = 1000;
# insert without specifying id
INSERT INTO orders (amount) VALUES (99.50);
# get the last inserted id
SELECT LAST_INSERT_ID();
# show current AUTO_INCREMENT value
SHOW TABLE STATUS LIKE 'orders'\G테이블 제약
제약은 데이터베이스 수준에서 무결성을 강제합니다. InnoDB는 기본 키, UNIQUE, NOT NULL, CHECK(8.0.16부터 강제), 외래 키를 지원합니다. FOREIGN KEY ... ON DELETE CASCADE는 부모 삭제 시 자식 행을 제거하고, SET NULL은 null로 만들며, RESTRICT/NO ACTION은 삭제를 막습니다. 관리 편의를 위해 제약에 명시적으로 이름을 붙이세요. 외래 키에는 양쪽 컬럼의 인덱스가 필요합니다.
CREATE TABLE orders (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED NOT NULL,
total DECIMAL(10,2) NOT NULL,
status VARCHAR(20) NOT NULL,
# unique constraint
CONSTRAINT uq_order_no UNIQUE (user_id, status),
# check constraint (MySQL 8.0.16+ enforced)
CONSTRAINT chk_total CHECK (total >= 0),
# foreign key with cascading actions
CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE
ON UPDATE RESTRICT
) ENGINE=InnoDB;
# add a constraint later
ALTER TABLE orders
ADD CONSTRAINT chk_status CHECK (status IN ('paid','shipped','cancelled'));임시 테이블
TEMPORARY 테이블은 현재 세션에만 존재하며 세션이 닫히면 자동 삭제됩니다. 같은 이름의 실제 테이블을 가릴 수 있어 안전한 리팩터링이나 데이터 스테이징에 유용합니다. 두 세션이 같은 이름의 임시 테이블을 충돌 없이 만들 수 있습니다. 다른 연결에는 보이지 않고 기본적으로 binlog에 기록되지 않습니다.
# session-scoped, auto-dropped on disconnect
CREATE TEMPORARY TABLE tmp_active
SELECT id, username FROM users WHERE status = 'active';
# identical name shadowing a real table
CREATE TEMPORARY TABLE users
(id INT, name VARCHAR(50));
# only this session sees the temp table
SELECT * FROM users;
# explicit cleanup
DROP TEMPORARY TABLE IF EXISTS tmp_active;데이터 타입
숫자 타입
정확성이 중요한 금액에는 DECIMAL을 쓰세요. FLOAT/DOUBLE은 근사값이라 반올림 오류가 쌓입니다. UNSIGNED는 양수 범위를 두 배로 늘리지만 음수를 금지합니다. 표시 폭(예: INT(11))은 MySQL 8.0에서 폐기되어 무시됩니다. 원래 저장 범위를 제한하지 않았습니다. AUTO_INCREMENT에는 오버플로 회피를 위해 BIGINT UNSIGNED가 권장됩니다. SERIAL은 대체 키의 편리한 축약입니다.
# integers (with optional UNSIGNED)
TINYINT -- 1 byte, -128..127 or 0..255 (UNSIGNED)
SMALLINT -- 2 bytes
MEDIUMINT -- 3 bytes
INT -- 4 bytes
BIGINT -- 8 bytes (use for large AUTO_INCREMENT)
# fixed-point (exact, for money)
DECIMAL(10,2) -- 10 digits total, 2 after decimal
NUMERIC(8,4)
# floating-point (approximate)
FLOAT -- 4 bytes
DOUBLE -- 8 bytes
# other
BIT(8) -- bit-field
SERIAL -- alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE문자열과 텍스트 타입
짧은 고정 길이 코드가 아니 라면 CHAR(공백 패딩)보다 VARCHAR가 거의 항상 낫습니다. MySQL의 utf8mb4에서 각 문자는 최대 4바이트이므로 VARCHAR(255) 컬럼은 길이 포함 최대 1020바이트가 필요합니다. InnoDB의 인덱스 키 한도(3072바이트)에서 문제가 됩니다. TEXT/BLOB 계열은 큰 데이터를 페이지 밖에 저장하며 기본값을 가질 수 없고 인덱싱하려면 접두어 길이가 필요합니다.
# fixed vs variable length
CHAR(10) -- fixed 10 chars, padded with spaces
VARCHAR(255) -- variable, up to 255 chars (stores actual length)
# large text
TINYTEXT -- up to 255 bytes
TEXT -- up to 64 KB
MEDIUMTEXT -- up to 16 MB
LONGTEXT -- up to 4 GB
# binary counterparts
BINARY(16), VARBINARY(255), BLOB, MEDIUMBLOB, LONGBLOB
# national character set
NATIONAL VARCHAR(100) -- same as VARCHAR with utf8mb4
# VARCHAR with charset counts characters, not bytes
VARCHAR(100) CHARACTER SET utf8mb4날짜와 시간 타입
DATETIME은 시간대를 인식하지 않는 리터럴 날짜/시간을 저장하고, TIMESTAMP는 UTC를 저장해 표시할 때 세션 time_zone으로 변환하므로 시간대가 섞이는 앱에 더 낫습니다. 하지만 32비트 빌드에서는 2038 한도가 있습니다. 마이크로초 정밀도는 DATETIME(6)/TIMESTAMP(6)를 쓰세요. MySQL 5.6.5부터는 둘 모두 CURRENT_TIMESTAMP 기본값이 동작합니다. SET time_zone = '+08:00';으로 연결별 세션 time_zone을 설정하세요.
DATE -- 'YYYY-MM-DD', range 1000-01-01 .. 9999-12-31
TIME -- 'HH:MM:SS', can be negative for elapsed time
DATETIME -- 'YYYY-MM-DD HH:MM:SS', 8 bytes, no timezone
TIMESTAMP -- stored as UTC, converted to session timezone, 4 bytes
YEAR -- YEAR(4) e.g. 2024
# DATETIME with fractional seconds and auto-default
CREATE TABLE events (
ts DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3)
ON UPDATE CURRENT_TIMESTAMP(3)
);
# TIMESTAMP range: 1970-01-01 .. 2038-01-19 (32-bit limit)
# MySQL 8.0.28+ extends TIMESTAMP to 24 bits of rangeENUM과 SET 타입
ENUM은 고정 문자열 목록 중 하나를 컴팩트하게(1~2바이트) 저장하고 strict 모드에서 알 수 없는 값을 거부합니다. SET은 최대 64개 멤버의 임의 조합을 비트마스크로 저장합니다. 둘 다 스키마 변경 비용이 크고(멤버 추가/재정렬에 ALTER TABLE 필요) 쿼리도 번거로워 많은 팀이 룩업 테이블이나 CHECK가 있는 VARCHAR를 선호합니다. FIND_IN_SET이 SET 컬럼 쿼리에 도움이 됩니다.
# ENUM: exactly one value from a list
CREATE TABLE shirts (
size ENUM('S','M','L','XL') NOT NULL DEFAULT 'M',
color ENUM('red','green','blue') NULL
);
# stored as integer index (1-based), space-efficient
INSERT INTO shirts (size) VALUES ('L'), ('XL');
# SET: zero or more values from a list (bit flags)
CREATE TABLE posts (
tags SET('news','tech','sports','fun') NOT NULL DEFAULT ''
);
INSERT INTO posts (tags) VALUES ('tech,fun');
# query SET members with FIND_IN_SET
SELECT * FROM posts WHERE FIND_IN_SET('tech', tags);JSON 타입
JSON 타입(MySQL 5.7.8+)은 쓰기 시 검증되고 빠른 멤버 접근을 지원하는 바이너리 형식으로 JSON을 저장합니다. TEXT 컬럼에 JSON을 넣는 것보다 내부를 인덱싱하고 쿼리할 수 있어 바람직합니다. 값 생성에는 JSON_OBJECT(), JSON_ARRAY(), JSON_MERGE_PATCH()를 쓰세요. 몇몇 키를 자주 조회한다면 generated column으로 추출해 인덱싱하는 것이 성능상 최선입니다.
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
attrs JSON NOT NULL
);
# insert JSON literals
INSERT INTO products (id, name, attrs) VALUES
(1, 'Widget', '{"color":"red","size":42,"tags":["new","sale"]}'),
(2, 'Gadget', '{"color":"blue","in_stock":true}');
# JSON values are validated on insert and stored binary
SELECT attrs FROM products WHERE id = 1;
# pretty-print JSON
SELECT JSON_PRETTY(attrs) FROM products WHERE id = 1;BLOB과 바이너리 타입
BLOB은 임의 바이너리(이미지, 파일)를 저장하지만 쿼리가 번거롭고 기본값을 가질 수 없습니다. 큰 파일은 DB에는 경로만 저장하고 파일은 디스크/오브젝트 스토리지에 두는 편이 좋습니다. UUID는 CHAR(36)(36바이트) 대신 BINARY(16)(16바이트)로 저장하면 인덱스에 효율적입니다. MySQL 8.0은 인덱스 친화적 스왑 옵션과 함께 UUID_TO_BIN()/BIN_TO_UUID()를 추가했습니다.
# binary large objects
TINYBLOB -- up to 255 bytes
BLOB -- up to 64 KB
MEDIUMBLOB -- up to 16 MB
LONGBLOB -- up to 4 GB
# fixed-size binary (e.g. hashes, UUIDs)
BINARY(16) -- fixed 16 bytes, right-padded with \0
VARBINARY(255)
# store a UUID as binary for compactness
CREATE TABLE sessions (
id BINARY(16) PRIMARY KEY,
data VARBINARY(1000)
);
INSERT INTO sessions (id, data) VALUES (UUID_TO_BIN(UUID()), '...');
# retrieve as text
SELECT BIN_TO_UUID(id) FROM sessions;DML (INSERT / UPDATE / DELETE)
INSERT
다중 행 INSERT는 파싱, 네트워크 왕복, 인덱스 갱신을 분산시키므로 반복 단일 행 삽입보다 훨씬 빠릅니다. INSERT ... SELECT는 테이블 간 데이터를 복사합니다. SET 문법은 MySQL 전용이며 한 행에 편리합니다. 스키마 변경에도 문장이 살아남도록 대상 컬럼을 항상 명시적으로 나열하세요. 멱등 삽입에는 INSERT IGNORE 또는 ON DUPLICATE KEY UPDATE를 사용합니다.
# single row
INSERT INTO users (username, email)
VALUES ('alice', '[email protected]');
# multiple rows in one statement
INSERT INTO users (username, email) VALUES
('bob', '[email protected]'),
('carol', '[email protected]'),
('dave', '[email protected]');
# insert from a SELECT
INSERT INTO archive_users (username, email)
SELECT username, email FROM users WHERE status = 'inactive';
# insert with column-order-free syntax
INSERT INTO users SET username='eve', email='[email protected]';INSERT ... ON DUPLICATE KEY UPDATE (Upsert)
ON DUPLICATE KEY UPDATE는 upsert를 구현합니다. UNIQUE/PRIMARY KEY 충돌이 나면 오류 대신 기존 행을 갱신합니다. VALUES(col)는 삽입됐을 값을 가리키며, MySQL 8.0.19+에서는 행 별칭(AS new)이 권장되고 이는 폐기 예정입니다. 원자적이므로 별도 SELECT 후 INSERT/UPDATE보다 효율적입니다. INSERT IGNORE는 대신 충돌을 조용히 건너뜁니다.
CREATE TABLE counters (
name VARCHAR(50) PRIMARY KEY,
hits INT NOT NULL DEFAULT 0
);
# upsert: insert or update on key conflict
INSERT INTO counters (name, hits)
VALUES ('home', 1)
ON DUPLICATE KEY UPDATE hits = hits + 1;
# reference the proposed values with VALUES()
INSERT INTO counters (name, hits) VALUES ('about', 5)
ON DUPLICATE KEY UPDATE hits = VALUES(hits) + 1;
# MySQL 8.0.20+: use an alias for the row being inserted
INSERT INTO counters (name, hits) VALUES ('home', 1) AS new
ON DUPLICATE KEY UPDATE hits = counters.hits + new.hits;UPDATE
WHERE 절을 항상 포함하세요. 없는 UPDATE는 모든 행을 수정합니다. MySQL은 JOIN으로 다중 테이블 UPDATE를 지원하며 비정규화된 계산 값에 유용 합니다. ORDER BY + LIMIT는 거대 테이블에서 긴 잠금을 피하는 안전한 배치 갱신을 가능하게 합니다. 세이프 업데이트 모드에서는 WHERE 절에 키 없는 UPDATE/DELETE를 클라이언트가 거부해 실수로 인한 대량 변경을 막습니다.
# basic update with WHERE (always include one!)
UPDATE users
SET status = 'banned', email = NULL
WHERE id = 42;
# update with expression
UPDATE products
SET price = price * 1.10
WHERE category = 'electronics';
# multi-table update with JOIN
UPDATE users u
JOIN orders o ON o.user_id = u.id
SET u.total_spent = u.total_spent + o.amount
WHERE o.paid = 1;
# limit and order (useful for batched updates)
UPDATE logs SET archived = 1
WHERE archived = 0
ORDER BY created_at ASC
LIMIT 1000;DELETE
DELETE는 행을 하나씩 제거하며 트랜잭션적이고(롤백 가능) 트리거를 발동합니다. 다중 테이블 DELETE는 한 문장에서 여러 테이블에서 제거할 수 있습니다. 테이블 전체를 비우는 데는 TRUNCATE가 훨씬 빠르고 AUTO_INCREMENT를 리셋하지만 트랜잭션적이지 않습니다. QUICK 수정자(MyISAM)는 인덱스 리프 갱신을 건너뜁니다. InnoDB에서는 거의 필요 없습니다. 항상 WHERE 절을 사용하세요.
# delete specific rows
DELETE FROM users WHERE status = 'banned' AND last_login < '2020-01-01';
# delete with LIMIT (batched)
DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 5000;
# multi-table delete with JOIN
DELETE u, o
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.id = 99;
# delete all rows (keeps table & AUTO_INCREMENT, unlike TRUNCATE)
DELETE FROM temp_data;
# quick delete that doesn't return row count
DELETE QUICK FROM logs WHERE year = 2020;REPLACE
REPLACE는 MySQL 전용입니다. 같은 PK/UNIQUE 키의 행이 있으면 삭제 후 새 행을 삽입하고, 없으면 그냥 삽입합니다. upsert보다 단순하지만 부작용이 있습니다. UPDATE가 아니라 DELETE 후 INSERT 트리거를 발동하고, AUTO_INCREMENT를 리셋하며, 제공되지 않은 컬럼을 기본값으로 떨어뜨립니다. 대부분의 경우 INSERT ... ON DUPLICATE KEY UPDATE를 선호하세요.
# REPLACE deletes then inserts on key conflict
REPLACE INTO users (id, username, email)
VALUES (1, 'alice', '[email protected]');
# REPLACE ... SET form
REPLACE INTO users SET id = 1, username = 'alice', email = '[email protected]';
# REPLACE from SELECT
REPLACE INTO daily_stats (day, visits)
SELECT CURDATE(), COUNT(*) FROM visits_log;
# caveat: REPLACE fires DELETE + INSERT triggers,
# not UPDATE, and resets columns not listedLOAD DATA INFILE
LOAD DATA INFILE은 CSV 대량 로드의 가장 빠른 방법입니다. SQL 파싱을 우회하므로 INSERT 문보다 20배 빠릅니다. 서버는 보안 디렉터리(secure_file_priv)의 파일을 읽고, LOCAL은 클라이언트가 파일을 보내지만 보안상 기본 비활성화입니다. 로드 중 값 변환에는 @var로 컬럼을 받은 뒤 SET을 쓰세요. SELECT ... INTO OUTFILE이 대칭 내보내기입니다.
# fast bulk import from a CSV file
LOAD DATA INFILE '/var/lib/mysql-files/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(username, email, @created)
SET created_at = STR_TO_DATE(@created, '%Y-%m-%d %H:%i:%s');
# LOCAL reads from the client machine (needs --local-infile)
LOAD DATA LOCAL INFILE 'C:/data/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES (username, email);
# export counterpart
SELECT * FROM users INTO OUTFILE '/tmp/users.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';SELECT 쿼리
기본 SELECT
프로덕션에서 SELECT *은 피하세요. 불필요한 데이터를 전송하고, 컬럼 추가/제거 시 깨지며, 커버링 인덱스 사용을 막습니다. 필요한 컬럼을 명시하세요. LIMIT + OFFSET이 페이지네이션을 구현하지만 큰 오프셋에서 느립니다(건너뛴 행도 스캔). keyset 페이지네이션(WHERE id > last_id LIMIT n)이 훨씬 잘 확장됩니다.
# select all columns
SELECT * FROM users;
# select specific columns (preferred)
SELECT id, username, email FROM users;
# column aliases
SELECT username AS name, email AS contact FROM users;
# constants and expressions
SELECT 1 + 1 AS sum, NOW() AS now, 'hello' AS greeting;
# limit rows and skip (pagination)
SELECT id, username FROM users
ORDER BY id DESC
LIMIT 10 OFFSET 20;
# MySQL shorthand: LIMIT offset, count
SELECT id, username FROM users LIMIT 20, 10;WHERE와 연산자
NULL과의 비교는 NULL(거짓 취급)이므로 항상 IS NULL / IS NOT NULL을 쓰세요. IN/NOT IN 목록의 NULL은 빈 결과처럼 놀라운 동작을 낼 수 있으므로 NOT EXISTS나 상관 서브쿼리를 쓰세요. BETWEEN은 양 끝을 포함합니다. MySQL은 OR보다 AND를 먼저 평가하므로 혼합 조건은 괄호로 감싸세요. 성능을 위해 가장 선택적인 조건을 먼저 두고, 인덱스 컬럼이 연산자 왼쪽에 오게 하세요.
# comparison operators
SELECT * FROM products WHERE price < 100;
SELECT * FROM products WHERE price BETWEEN 50 AND 150;
SELECT * FROM users WHERE id IN (1, 5, 9);
# logical operators
SELECT * FROM users
WHERE status = 'active' AND (age >= 18 OR verified = 1);
# NULL checks (never use = with NULL)
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;
# BETWEEN is inclusive on both ends
SELECT * FROM orders WHERE created_at
BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59';
# NOT IN with NULL traps: NULL poisons the resultORDER BY와 LIMIT
ORDER BY가 결과를 정렬하며 여러 키는 왼쪽부터 적용됩니다. FIELD()는 열거 목록으로 커스텀 순서를 만듭니다. 큰 결과 집합에서 ORDER BY ... LIMIT는 인덱스가 지원하지 않으면 전체를 스캔·정렬하므로 ORDER BY 컬럼에 인덱스를 추가하세요. 그룹별 top-N의 정석은 윈도우 함수 패턴(ROW_NUMBER OVER PARTITION BY)이며 MySQL 8.0+에서 동작합니다.
# ascending (default) and descending
SELECT * FROM users ORDER BY created_at;
SELECT * FROM users ORDER BY created_at DESC, username ASC;
# order by expression
SELECT product, price * stock AS value
FROM inventory
ORDER BY value DESC;
# order by FIELD() for custom ordering
SELECT * FROM tasks
ORDER BY FIELD(priority, 'high', 'medium', 'low');
# stable top-N per group via window function (8.0+)
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY id DESC) rn
FROM orders
) t WHERE rn <= 3;DISTINCT와 그룹핑
DISTINCT는 동일 행을 축소하며 여러 컬럼에서는 조합을 중복 제거합니다. COUNT(DISTINCT col)는 고유한 non-null 값을 셉니다. DISTINCT는 사실상 선택된 모든 컬럼에 대한 GROUP BY입니다. 대표 행 하나를 남기며 중복 제거하려면(예: 최신) ROW_NUMBER() OVER (PARTITION BY ...)를 쓰세요. DISTINCT는 임시 테이블을 만들어 비쌀 수 있으니 인덱스를 선호하세요.
# unique values of one column
SELECT DISTINCT country FROM users;
# distinct over multiple columns
SELECT DISTINCT city, country FROM users;
# COUNT distinct values
SELECT COUNT(DISTINCT country) AS countries FROM users;
# GROUP BY returns one row per group; DISTINCT is a special case
SELECT country, COUNT(*) AS users
FROM users
GROUP BY country;
# deduplicate while keeping the latest row (8.0+)
WITH latest AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id DESC) rn
FROM users
)
SELECT id, email FROM latest WHERE rn = 1;LIKE와 REGEXP 패턴 매칭
LIKE는 단순하지만 선행 와일드카드(LIKE '%x')는 인덱스를 무력화해 전체 스캔을 강제합니다. 대소문자 구분은 콜레이션이 결정하며 utf8mb4_0900_ai_ci는 악센트/대소문자 무시입니다. REGEXP/RLIKE는 완전한 정규식 능력을 더하지만 절대 인덱스를 쓰지 못합니다. MySQL 8.0은 REGEXP_LIKE, REGEXP_REPLACE, REGEXP_INSTR, REGEXP_SUBSTR을 추가했습니다. 전문 검색에는 MATCH ... AGAINST와 FULLTEXT 인덱스를 쓰세요.
# LIKE wildcards: % (any chars) and _ (one char)
SELECT * FROM users WHERE username LIKE 'a%'; -- starts with a
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE username LIKE '_an'; -- 3 chars, ends 'an'
# escape a wildcard literally
SELECT * FROM users WHERE path LIKE '50\%' ESCAPE '\\';
# case-insensitive LIKE depends on column collation
# *_ci collations are case-insensitive
# REGEXP / RLIKE for regular expressions
SELECT * FROM users WHERE email REGEXP '^[a-z]+@';
SELECT * FROM products WHERE name RLIKE 'iPhone|Galaxy';
# capture groups with REGEXP_REPLACE (8.0+)
SELECT REGEXP_REPLACE(phone, '([0-9]{3})([0-9]{4})', '$1-$2')
FROM contacts;CASE 표현식
CASE가 SQL의 조건 표현식입니다. 탐색형은 WHEN 조건을 위에서 아래로 평가해 첫 매칭의 THEN 값을 반환하고, 없으면 ELSE(또는 NULL)입니다. CASE는 SELECT, WHERE, ORDER BY, 집계에서 쓸 수 있으며 SUM(CASE ...) 패턴은 고전적 피벗입니다. CASE는 집계를 단락 평가하지 않으므로 모든 행이 계산됩니다. 단순한 경우에는 IF()와 IFNULL()이 더 짧은 MySQL 축약입니다.
# searched CASE (like if/else)
SELECT username,
CASE
WHEN age < 18 THEN 'minor'
WHEN age < 65 THEN 'adult'
ELSE 'senior'
END AS age_group
FROM users;
# simple CASE (compare to a value)
SELECT order_id,
CASE status
WHEN 0 THEN 'pending'
WHEN 1 THEN 'paid'
WHEN 2 THEN 'shipped'
ELSE 'unknown'
END AS status_label
FROM orders;
# CASE in aggregate (pivot)
SELECT
SUM(CASE WHEN status='paid' THEN 1 ELSE 0 END) AS paid_count,
SUM(CASE WHEN status='shipped' THEN 1 ELSE 0 END) AS shipped_count
FROM orders;JOIN
INNER JOIN
INNER JOIN은 양쪽 테이블 모두에 매칭이 있을 때만 행을 반환하며 어느 쪽이든 매칭 없는 행은 버려집니다. JOIN은 INNER JOIN의 축약입니다. 컬럼 이름이 동일하고 결과에 한 번만 나타날 때 USING (col)이 ON a.col = b.col의 간결한 대안입니다. INNER JOIN은 정규화된 테이블을 연결하는 기본이자 가장 흔한 조인입니다.
# only matching rows from both tables
SELECT u.username, o.id AS order_id, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id
WHERE o.paid = 1;
# join three tables
SELECT u.username, o.id, oi.product_id, oi.qty
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id;
# USING clause when join columns share a name
SELECT u.username, o.id
FROM users u
JOIN orders o USING (user_id);LEFT와 RIGHT JOIN
LEFT JOIN은 왼쪽 테이블의 모든 행을 유지하고 매칭이 없으면 오른쪽을 NULL로 채웁니다. WHERE o.id IS NULL 트릭(안티 조인)은 매칭되는 오른쪽 행이 없는 왼쪽 행을 찾습니다. 특히 NULL이 있을 때 NOT IN보다 명확하고 빠른 경우가 많습니다. RIGHT JOIN은 대칭이며 대부분은 테이블 순서를 바꿔 LEFT JOIN으로 씁니다. 조인 키의 NULL은 절대 매칭되지 않습니다.
# LEFT JOIN: all left rows, with NULLs where no match
SELECT u.username, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;
# find users with NO orders (anti-join pattern)
SELECT u.username
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;
# RIGHT JOIN is the mirror (rarely used; swap tables instead)
SELECT u.username, o.id
FROM orders o
RIGHT JOIN users u ON o.user_id = u.id;CROSS JOIN
CROSS JOIN은 카테시안 곱을 반환합니다. 두 테이블 행의 모든 조합입니다. 조합 생성이나 차원 행렬 시딩에 유용하지만 N x M 행을 만들어 큰 테이블에서는 비쌉니다. 테이블 사이의 콤마(FROM a, b)는 암시적 CROSS JOIN입니다. MySQL 8.0+에서 시퀀스/차원 테이블을 만드는 표준 방법은 재귀 CTE 패턴입니다.
# Cartesian product: every row of A paired with every row of B
SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;
# same effect with a comma join
SELECT s.size, c.color FROM sizes s, colors c;
# generate a series of dates (no built-in GENERATE_SERIES)
WITH RECURSIVE nums(n) AS (
SELECT 1 UNION ALL SELECT n+1 FROM nums WHERE n < 7
)
SELECT DATE_ADD('2024-01-01', INTERVAL n-1 DAY) AS day
FROM nums;
# build a matrix of all category x region combinations
SELECT cat.name, reg.name
FROM categories cat CROSS JOIN regions reg;Self Join
self join은 같은 테이블을 다른 별칭으로 두 번 참조해 내부 행들을 연결합니다. 관리자/직원이나 인접 리스트 계층의 고전입니다. a.id < b.id 조건은 대칭 쌍의 중복을 피합니다. 깊은 계층에는 재귀 CTE(MySQL 8.0+)를 쓰세요. 고정 깊이 한도 없이 트리를 순회하며 REPEAT/CONCAT으로 들여쓰기 트리 출력도 만들 수 있습니다.
# employees and their managers (same table twice)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
# find pairs of users in the same city
SELECT a.username AS user_a, b.username AS user_b, a.city
FROM users a
JOIN users b ON a.city = b.city AND a.id < b.id;
# hierarchical tree with recursive CTE (8.0+)
WITH RECURSIVE org AS (
SELECT id, name, manager_id, 1 AS depth
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, o.depth + 1
FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT CONCAT(REPEAT('-- ', depth-1), name) AS tree
FROM org;FULL OUTER JOIN 우회
MySQL은 FULL OUTER JOIN을 구현하지 않습니다. 표준 에뮬레이션은 LEFT JOIN UNION RIGHT JOIN입니다. UNION이 중복을 제거해 매칭 행은 한 번만, 어느 쪽이든 매칭 없는 행은 다른 쪽 NULL과 함께 나타납니다. 큰 결과에는 UNION의 암시적 DISTINCT가 비싸므로 중복 제거 전략과 함께 UNION ALL을 선호하세요. 매칭 없는 행만 필요하면 두 안티 조인을 쓰세요.
# MySQL has no FULL OUTER JOIN — emulate with LEFT + UNION + RIGHT
SELECT u.username, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
UNION
SELECT u.username, o.id AS order_id
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id;
# the UNION removes duplicates; use UNION ALL to keep them
# for an anti-union (mismatches only) add WHERE o.id IS NULL
# or u.id IS NULL in each branchNATURAL JOIN과 USING
NATURAL JOIN은 취약합니다. 공유된 모든 컬럼 이름으로 조인하므로 컬럼 추가가 조용히 결과를 바꿀 수 있습니다. 프로덕션에서 피하세요. USING (col)은 더 안전하고 조인 컬럼을 출력에서 한 복사본으로 합칩니다. STRAIGHT_JOIN은 옵티마이저가 쓰는 테이블 순서를 강제하는 수동 오버라이드로, 나쁜 플랜용입니다. 실제 원인이 고쳐지면 제거하세요. 명확성을 위해 명시적 ON을 선호하세요.
# NATURAL JOIN matches all same-named columns automatically
SELECT * FROM users NATURAL JOIN profiles;
# USING matches specific shared columns, dedups them in output
SELECT u.username, user_id
FROM users u
JOIN orders o USING (user_id);
# STRAIGHT_JOIN forces the optimizer to join left-to-right
SELECT STRAIGHT_JOIN u.username, o.id
FROM users u
JOIN orders o ON o.user_id = u.id;집계와 그룹핑
집계 함수
집계 함수는 많은 행을 하나로 축소합니다. COUNT, SUM, AVG, MIN, MAX. COUNT(*)는 행 수, COUNT(col)는 col의 non-null 값 수, COUNT(DISTINCT col)는 고유 값 수입니다. AVG는 0이 아니라 NULL을 무시하므로 결과가 치우칠 수 있습니다. NULL을 0으로 취급하려 COALESCE(col,0)을 쓰세요. GROUP BY 없는 집계는 빈 테이블에서도 한 행을 반환합니다.
SELECT
COUNT(*) AS row_count,
COUNT(email) AS emails, -- ignores NULLs
COUNT(DISTINCT country) AS countries,
MIN(created_at) AS first_user,
MAX(created_at) AS last_user,
AVG(age) AS avg_age,
SUM(balance) AS total_balance
FROM users;
# aggregate over a filtered set
SELECT SUM(amount) AS paid_total
FROM orders
WHERE status = 'paid';GROUP BY
GROUP BY는 그룹 컬럼 값이 같은 행을 하나의 출력 행으로 축소하고 그룹별 집계를 계산합니다. MySQL 8.0은 기본으로 ONLY_FULL_GROUP_BY를 켜서 GROUP BY에 없는 컬럼을 선택하는 쿼리를 거부합니다(이전 버전은 임의 행을 반환. 흔한 버그). 표현식이나 별칭으로 그룹화할 수 있습니다. 보고서 시계열은 타임스탬프의 YEAR()/MONTH()/DATE()로 그룹화하세요.
# one row per country
SELECT country, COUNT(*) AS users, AVG(age) AS avg_age
FROM users
GROUP BY country;
# group by multiple columns
SELECT country, city, COUNT(*) AS users
FROM users
GROUP BY country, city
ORDER BY country, users DESC;
# group by expression
SELECT YEAR(created_at) AS yr, MONTH(created_at) AS m, COUNT(*)
FROM users
GROUP BY yr, m;
# MySQL 8.0 enforces ONLY_FULL_GROUP_BY:
# every non-aggregated SELECT column must be in GROUP BYHAVING
HAVING은 집계 결과를 필터하고 WHERE는 그룹화 전 입력 행을 필터합니다. 따라서 HAVING은 집계(SUM, COUNT)를 참조할 수 있고 WHERE는 못합니다. 효율을 위해 행 수준 필터는 WHERE에 두세요(그룹화 전 행을 줄임). MySQL에서는 HAVING이 별칭을 받습니다(HAVING users > 100). 한 쿼리가 둘 다 쓸 수 있습니다. WHERE가 행을 좁히고, GROUP BY가 버킷을 만들고, HAVING이 버킷을 좁힙니다.
# HAVING filters groups (after aggregation); WHERE filters rows (before)
SELECT country, COUNT(*) AS users
FROM users
GROUP BY country
HAVING COUNT(*) > 100
ORDER BY users DESC;
# filter on an aggregate
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 1000;
# combine WHERE and HAVING
SELECT country, COUNT(*) AS users, AVG(age) AS avg_age
FROM users
WHERE status = 'active'
GROUP BY country
HAVING avg_age >= 30;GROUP BY ROLLUP
WITH ROLLUP은 각 그룹핑 수준의 초 집계 행과 총합을 추가하며 그룹 컬럼이 NULL이 됩니다. GROUPING(col)은 소계/총계 행에서 1을 반환하므로 NULL을 'ALL'로 레이블할 수 있습니다. ROLLUP은 GROUP BY 컬럼의 왼쪽→오른쪽으로 동작합니다. MySQL에는 WITH CUBE가 없으므로 모든 차원 조합이 필요하면 여러 ROLLUP 쿼리의 UNION ALL로 에뮬레이트하세요.
# ROLLUP adds subtotals and a grand total row
SELECT country, city, COUNT(*) AS users
FROM users
GROUP BY country, city WITH ROLLUP;
# output includes:
# country | city | users
# 'US' | 'NYC' | 120
# 'US' | NULL | 200 <- US subtotal
# NULL | NULL | 950 <- grand total
# identify super-aggregate rows with GROUPING()
SELECT
IF(GROUPING(country)=1,'ALL',country) AS country,
IF(GROUPING(city)=1,'ALL',city) AS city,
COUNT(*) AS users
FROM users
GROUP BY country, city WITH ROLLUP;GROUP_CONCAT
GROUP_CONCAT은 MySQL의 문자열 집계 함수로 그룹의 값을 하나의 문자열로 연결합니다. DISTINCT로 중복 제거, ORDER BY로 정렬, SEPARATOR로 구분자 변경(기본 ',')을 하세요. 결과는 group_concat_max_len(기본 1024바이트)으로 제한되며 긴 목록은 이를 올리세요. 다른 DB에서는 LISTAGG/STRING_AGG이며 GROUP_CONCAT이 MySQL 등가물입니다.
# concatenate grouped values into one string
SELECT country,
GROUP_CONCAT(username) AS all_names
FROM users
GROUP BY country;
# order within the list and deduplicate
SELECT user_id,
GROUP_CONCAT(DISTINCT tag ORDER BY tag SEPARATOR ',') AS tags
FROM post_tags
GROUP BY user_id;
# the default separator is ',', max length is 1024 by default
# raise the limit:
# SET SESSION group_concat_max_len = 1000000;
SELECT department,
GROUP_CONCAT(name ORDER BY salary DESC SEPARATOR ' | ') AS ranking
FROM employees
GROUP BY department;WITH ROLLUP vs 윈도우 집계
ROLLUP은 소계 행을 추가해 결과를 줄이는 반면, 윈도우 집계(OVER)는 행 수를 유지한 채 집계를 추가 컬럼으로 덧붙입니다. 따라서 상세와 총계를 함께 볼 수 있습니다. 요약 보고서에는 ROLLUP, 집계와 함께 행별 맥락이 필요하면 윈도우 함수를 쓰세요. 누계는 전형적인 SUM OVER (ORDER BY ...) 사용 사례입니다.
# ROLLUP gives fewer rows (subtotals mixed into the result)
SELECT country, city, COUNT(*) AS users
FROM users
GROUP BY country, city WITH ROLLUP;
# a window aggregate keeps every detail row AND adds the total
SELECT
country, city,
COUNT(*) AS users_in_city,
COUNT(*) OVER (PARTITION BY country) AS users_in_country,
COUNT(*) OVER () AS users_total
FROM users;
# running total over time
SELECT created_at, amount,
SUM(amount) OVER (ORDER BY created_at) AS running_total
FROM daily_sales;서브쿼리와 CTE
스칼라 서브쿼리
스칼라 서브쿼리는 단일 값(한 행, 한 컬럼)을 반환하며 표현식이 유효한 어디서든 쓸 수 있습니다. SELECT 목록, WHERE, HAVING. 행을 반환하지 않으면 값은 NULL입니다. 옵티마이저는 상관 스칼라 서브쿼리를 조인으로 바꿀 때가 많습니다(서브쿼리 머터리얼라이제이션). 같은 서브쿼리를 반복 참조하면 명확성과 성능을 위해 CTE나 JOIN이 낫습니다.
# returns a single value
SELECT username, balance,
(SELECT AVG(balance) FROM users) AS avg_balance
FROM users
WHERE balance > (SELECT AVG(balance) FROM users);
# use in SELECT list, WHERE, HAVING, etc.
SELECT product, price,
price - (SELECT MIN(price) FROM products) AS above_min
FROM products;
# a scalar subquery must return exactly one row, one columnIN / ANY / ALL 서브쿼리
IN은 서브쿼리가 반환하는 어떤 값과도 매칭하고, NOT IN은 그 보합이지만 NULL에 중독됩니다. 서브쿼리가 NULL을 반환하면 NOT IN은 아예 행을 반환하지 않으므로 항상 NULL을 제외하거나 NOT EXISTS를 쓰세요. ALL은 모든 반환 값에 대해 비교가 성립할 때, ANY/SOME은 하나 이상일 때 참입니다. 옵티마이저는 이들을 세미 조인으로 재작성할 때가 많습니다.
# IN: value matches any row of the subquery
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE status='active');
# NOT IN with NULLs is dangerous — prefer NOT EXISTS
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);
# ANY / SOME / ALL with comparisons
SELECT * FROM products
WHERE price > ALL (SELECT price FROM products WHERE category='toys');
SELECT * FROM products
WHERE price > ANY (SELECT price FROM products WHERE category='toys');EXISTS와 NOT EXISTS
EXISTS는 데이터를 반환하지 않고 행 존재를 검사하며 첫 매칭 에서 스캔을 멈춰 존재 확인에 효율적입니다. 서브쿼리는 상관됩니다(외부 쿼리 참조). NOT EXISTS는 매칭 없는 행을 찾는 NULL 안전 방법으로 NOT IN보다 일반적으로 선호됩니다. EXISTS 안의 SELECT 1은 관례이며 컬럼 목록은 무관합니다. 옵티마이저는 EXISTS를 세미 조인으로 바꿀 때가 많습니다.
# EXISTS: true if the subquery returns any rows
SELECT u.username
FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);
# NOT EXISTS: the standard anti-semi-join (NULL-safe)
SELECT u.username
FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);
# correlated: the subquery references the outer row (u.id)
# EXISTS stops at the first matching row — efficient for presence checks파생 테이블
파생 테이블은 FROM 절의 서브쿼리로, 선택하거나 조인할 수 있는 중간 결과를 머터리얼라이즈합니다. 항상 별칭을 붙이세요. MySQL 8.0은 더 나은 플랜을 위해 많은 파생 테이블을 외부 쿼리로 병합합니다(derived_merge). 하지만 복잡한 집계나 LIMIT는 보통 머터리얼라이제이션을 강제합니다. 가독성과 재사용을 위해 인라인 파생 테이블보다 이름 있는 CTE(WITH)를 선호하세요.
# a subquery in the FROM clause is a derived table
SELECT t.country, t.users
FROM (
SELECT country, COUNT(*) AS users
FROM users
GROUP BY country
) t
WHERE t.users > 50;
# join a derived table
SELECT u.username, agg.order_count
FROM users u
JOIN (
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
) agg ON agg.user_id = u.id;
# derived tables must be aliased상관 서브쿼리
상관 서브쿼리는 외부 쿼리의 컬럼을 참조하므로 외부 행마다 논리적으로 다시 평가됩니다. 큰 집합에서는 느릴 수 있습니다. 옵티마이저가 조인으로 변환하거나 캐시(서브쿼리 캐시)로 완화할 수 있습니다. 순위/top-N 문제(예: n번째 최고 급여)는 DENSE_RANK() OVER (ORDER BY salary DESC) 같은 윈도우 함수가 더 명확하고 빠릅니다. MySQL 8.0+에서 그쪽을 쓰세요.
# the subquery references the outer row (u.id) and runs per row
SELECT u.username,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;
# correlated subquery in WHERE (nth-highest salary classic)
SELECT e1.name, e1.salary
FROM employees e1
WHERE 2 = (
SELECT COUNT(DISTINCT e2.salary)
FROM employees e2
WHERE e2.salary > e1.salary
);
# prefer a window function for rank-style problems in 8.0+공통 테이블 표현식 (CTE)
CTE(WITH 절)는 한 문장 안에서 가독성과 재사용을 위해 서브쿼리에 이름을 붙입니다. MySQL 8.0+가 지원합니다. RECURSIVE는 자기 참조 CTE를 가능하게 하며 트리 순회와 시퀀스 생성에 필수입니다(재귀는 UNION ALL, 중복 제거는 UNION). 재귀 CTE는 기저 사례, 재귀 가지, 종료 가드가 필요합니다. 여러 CTE는 콤마로 구분되며 같은 WITH 안에서 앞선 것을 참조할 수 있습니다.
# a named CTE (WITH) — readable, can be referenced multiple times
WITH active_users AS (
SELECT id, username FROM users WHERE status = 'active'
)
SELECT au.username, COUNT(o.id) AS orders
FROM active_users au
LEFT JOIN orders o ON o.user_id = au.id
GROUP BY au.username;
# RECURSIVE CTE: walk a hierarchy or generate a series
WITH RECURSIVE nums(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n FROM nums;
# multiple CTEs, comma-separated
WITH a AS (SELECT ...), b AS (SELECT ...)
SELECT * FROM a JOIN b USING (id);인덱스
인덱스 생성
인덱스는 쓰기 오버헤드와 저장 공간을 대가로 조회와 정렬을 빠르게 하는 자료구조입니다(InnoDB는 B+Tree). WHERE, JOIN, ORDER BY에 쓰이는 컬럼에 인덱스를 만드세요. UNIQUE 인덱스는 고유성 제약도 강제합니다. 긴 텍스트 컬럼의 접두어 인덱스는 공간을 아끼지만 커버링 스캔이나 ORDER BY에는 쓸 수 없습니다. InnoDB에서 모든 기본 키는 클러스터드이며 세컨더리 인덱스는 행 포인터로 PK 값을 저장합니다.
# single-column index
CREATE INDEX idx_email ON users(email);
# unique index (enforces uniqueness, allows fast lookup)
CREATE UNIQUE INDEX uq_username ON users(username);
# create with a table
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255),
UNIQUE KEY uq_email (email)
);
# full-table prefix index (limited length)
CREATE INDEX idx_name ON users(last_name(20));복합 인덱스
복합 인덱스는 가장 왼쪽 컬럼부터 정렬되므로 컬럼 목록의 왼쪽 접두어로 필터하는 쿼리만 돕습니다. 컬럼 순서가 결정적입니다. 가장 선택적인 등호 컬럼을 먼저, 그다음 범위를 두세요. 커버링 인덱스는 쿼리가 필요한 모든 컬럼을 담아 테이블 조회를 완전히 건너뛰는 인덱스 전용 스캔을 가능하게 합니다(EXPLAIN이 'Using index'를 표시). 과도한 인덱스는 금물입니다. 모든 인덱스가 쓰기를 느리게 합니다.
# multi-column index (column order matters!)
CREATE INDEX idx_last_first ON users(last_name, first_name);
# supports these lookups (leftmost prefix rule):
# WHERE last_name = 'X' -> uses index
# WHERE last_name = 'X' AND first_name = 'Y' -> uses index
# WHERE first_name = 'Y' -> CANNOT use index
# covering index: all needed columns are in the index
CREATE INDEX idx_cover ON orders(user_id, status, amount);
SELECT user_id, SUM(amount) FROM orders
WHERE user_id = 5 AND status = 'paid' GROUP BY user_id;
# ^ index-only scan, no table lookup needed전문(Full-Text) 인덱스
FULLTEXT 인덱스는 자연어와 불리언 텍스트 검색을 가능하게 하며 LIKE '%word%'보다 훨씬 낫습니다. MATCH ... AGAINST가 관련도 점수를 반환합니다. 불리언 모드 연산자: +필수, -제외, *와일드카드, >가중 증가, ~가중 감소. InnoDB(5.6+) 또는 MyISAM과 최소 단어 길이(ft_min_word_len / innodb_ft_min_token_size, 기본 3)가 필요합니다. 규모 있는 진지한 검색은 Elasticsearch 같은 전용 엔진을 고려하세요.
CREATE TABLE articles (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT KEY ft_title_body (title, body)
) ENGINE=InnoDB;
# natural-language search
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST ('database performance');
# boolean mode: +require -exclude ~relax
SELECT id, title
FROM articles
WHERE MATCH(title, body)
AGAINST ('+MySQL -Oracle >index' IN BOOLEAN MODE);
# query expansion (second pass adds related terms)
SELECT id FROM articles
WHERE MATCH(title, body) AGAINST ('database' WITH QUERY EXPANSION);공간(Spatial) 인덱스
공간(R-tree) 인덱스는 GEOMETRY/POINT/POLYGON 컬럼의 지리 쿼리를 가속합니다. MySQL 8.0은 SRID 4326(WGS 84 GPS 좌표)으로 표준화하고 실제 미터 거리를 위한 ST_Distance_Sphere를 추가했습니다. 컬럼은 NOT NULL이어야 합니다. 필터에는 ST_Within/ST_Contains/ST_Distance를 쓰세요. 좌표는 X/Y 관례 에 맞게 항상 (longitude, latitude)로 표현하세요. 공간 인덱스는 반경 쿼리를 훨씬 빠르게 합니다.
CREATE TABLE places (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
location POINT NOT NULL SRID 4326,
SPATIAL KEY sp_location (location)
) ENGINE=InnoDB;
# insert a point (longitude, latitude)
INSERT INTO places (name, location)
VALUES ('HQ', ST_PointFromText('POINT(116.40 39.90)', 4326));
# find points within a bounding distance
SELECT name, ST_Distance_Sphere(location,
ST_PointFromText('POINT(116.40 39.90)', 4326)) AS meters
FROM places
WHERE ST_Within(location,
ST_Buffer(ST_PointFromText('POINT(116.40 39.90)', 4326), 0.01));인덱스 관리
SHOW INDEX는 테이블의 인덱스를 컬럼 순서와 카디널리티(고유 값 추정)와 함께 나열합니다. DROP INDEX가 인덱스를 제거하고, 이름 변경(8.0+)은 drop+재구축을 피합니다. OPTIMIZE TABLE은 조각난 공간을 회수하고 인덱스 통계를 갱신하려 테이블을 재구축합니다. 잠금이 심하므로 트래픽 적을 때 예약하세요. 오래된 통계는 옵티마이저를 오도할 수 있으니 큰 데이터 로드 후 ANALYZE TABLE로 갱신하세요.
# show indexes on a table
SHOW INDEX FROM users;
# drop an index
DROP INDEX idx_email ON users;
ALTER TABLE users DROP INDEX uq_username;
# rename an index (8.0+)
ALTER TABLE users RENAME INDEX idx_email TO idx_user_email;
# rebuild all indexes on a table
ALTER TABLE users ENGINE=InnoDB;
OPTIMIZE TABLE users;
# check index cardinality
SHOW INDEX FROM users;
SELECT index_name, cardinality
FROM information_schema.statistics
WHERE table_schema='mydb' AND table_name='users';인비저블 인덱스와 힌트
인비저블 인덱스(8.0+)는 인덱스 제거를 안전하게 테스트하게 합니다. 옵티마이저는 무시하지만 쓰기는 최신으로 유지하므로, 쿼리가 나빠지면 즉시 되돌릴 수 있고 확신이 서면 드롭하세요. 옵티마이저 힌트(/*+ ... */)는 오래된 USE/FORCE/IGNORE INDEX 문법보다 바람직하며 태그된 문장에만 영향을 줍니다. 힌트는 아껴 쓰세요. 나쁜 데이터 분포나 누락 통계의 임시방편입니다.
# make an index invisible (8.0+): optimizer ignores it, still maintained
ALTER TABLE users ALTER INDEX idx_email SET INVISIBLE;
# ... observe query plans, then drop or restore:
ALTER TABLE users ALTER INDEX idx_email SET VISIBLE;
# optimizer hints (8.0+) to force / avoid indexes
SELECT /*+ INDEX(u idx_email) */ * FROM users u WHERE email LIKE 'a%';
SELECT /*+ NO_INDEX(u idx_email) */ * FROM users u;
SELECT /*+ FORCE INDEX(u idx_email) */ * FROM users u;
# index merge / IGNORE_INDEX legacy hint
SELECT * FROM users USE INDEX (idx_email) WHERE email = '[email protected]';
SELECT * FROM users IGNORE INDEX (idx_email) WHERE email = '[email protected]';뷰
뷰 생성
뷰는 가상 테이블처럼 동작하는 저장된 쿼리입니다. 복잡한 쿼리를 단순화하고, 스키마 변화를 추상화하고, 행/컬럼 가시성을 강제합니다. CREATE OR REPLACE는 정의를 한 단계에 갱신합니다. 뷰는 데이터를 저장하지 않으므로(머터리얼라이즈되지 않는 한 매번 기저 쿼리를 다시 실행) 쓰기 비용은 없지만 읽기 가속도 없습니다. 가능하면 뷰는 쿼리에 병합됩니다.
# a view is a stored SELECT
CREATE VIEW active_users AS
SELECT id, username, email
FROM users
WHERE status = 'active';
# query it like a table
SELECT * FROM active_users WHERE username LIKE 'a%';
# create or replace, with explicit columns
CREATE OR REPLACE VIEW user_summary (username, order_count, total) AS
SELECT u.username, COUNT(o.id), COALESCE(SUM(o.amount), 0)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.username;
# list and inspect views
SHOW FULL TABLES WHERE Table_type = 'VIEW';
SHOW CREATE VIEW active_users\G갱신 가능한 뷰
집계/distinct 없이 단일 기저 테이블 위의 뷰는 갱신 가능합니다. INSERT/UPDATE/DELETE가 기저 테이블로 전파됩니다. 기본적으로 행을 뷰의 WHERE 밖으로 옮기는 UPDATE는 성공하며(그 행은 뷰에서 사라짐) WITH CHECK OPTION은 그런 변경을 막아 행이 뷰 범위를 벗어나지 못하게 합니다. JOIN, GROUP BY, DISTINCT, 서브쿼리가 있는 뷰는 보통 갱신 불가입니다.
# a view over a single base table is updatable
CREATE VIEW active_users AS
SELECT id, username, email, status
FROM users
WHERE status = 'active';
# INSERT through the view inserts into the base table
INSERT INTO active_users (id, username, email, status)
VALUES (10, 'newbie', '[email protected]', 'active');
# UPDATE through the view only touches visible rows
UPDATE active_users SET email = '[email protected]' WHERE id = 10;
# WITH CHECK OPTION prevents inserts/updates that leave the view
CREATE OR REPLACE VIEW active_users AS
SELECT id, username, email, status FROM users WHERE status='active'
WITH CHECK OPTION;뷰 CHECK OPTION
CHECK OPTION은 뷰를 통해 바뀐 행이 그 뷰에 계속 보이게 강제합니다. LOCAL은 그 뷰 자신의 WHERE만 검사하고, CASCADED(기본)는 참조하는 기저 뷰들의 WHERE 절까지 강제합니다. 뷰를 쌓을 때는 체인 전체를 일관되게 유지하려 CASCADED를 쓰고, 기저 뷰의 필터를 의도적으로 완화하고 싶을 때만 LOCAL을 쓰세요.
# LOCAL: only the defining view's WHERE is enforced
CREATE VIEW v_paid AS
SELECT * FROM orders WHERE status='paid' WITH LOCAL CHECK OPTION;
# CASCADED (default): all underlying views' checks are enforced too
CREATE VIEW v_paid_large AS
SELECT * FROM v_paid WHERE amount > 100
WITH CASCADED CHECK OPTION;
# an insert that violates v_paid's filter:
INSERT INTO v_paid_large (id, status, amount) VALUES (1, 'unpaid', 200);
# CASCADED -> rejected (status would leave v_paid)
# LOCAL -> accepted (only v_paid_large's filter checked)뷰 관리
ALTER VIEW는 뷰를 드롭하지 않고(권한 보존) 재정의합니다. DROP VIEW IF EXISTS는 뷰가 없을 때 오류를 피합니다. RENAME TABLE도 뷰에 동작하지만 흔치 않습니다. information_schema.views는 각 뷰의 정의, 갱신 가능 여부, 보안 유형을 노출해 감사에 유용합니다. 뷰는 기저 테이블에 의존하므로 기저 테이블을 드롭하면 재생성 전까지 뷰가 무효가 됩니다.
# alter a view's definition (CREATE OR REPLACE is simpler)
ALTER VIEW active_users AS
SELECT id, username FROM users WHERE status='active';
# drop a view
DROP VIEW IF EXISTS active_users;
# drop multiple views
DROP VIEW IF EXISTS v1, v2, v3;
# rename a view (no RENAME VIEW; recreate or use a table rename trick)
RENAME TABLE old_view TO new_view;
# view metadata
SELECT table_name, view_definition, is_updatable
FROM information_schema.views
WHERE table_schema = 'mydb';뷰 보안과 결정성
SQL SECURITY DEFINER는 뷰를 정의자 권한으로 실행합니다. 기저 테이블 접근이 없는 사용자에게 필터된 데이터를 노출할 때 유용합니다(예: 행 수준 보안 뷰). INVOKER는 대신 호출자의 권한을 검사합니다. ALGORITHM MERGE는 뷰를 쿼리에 접습니다(빠르고 갱신 가능한 뷰 허용). TEMPTABLE은 먼저 머터리얼라이즈합니다(일부 구성에 필요하지만 갱신성을 잃고 느릴 수 있음). UNDEFINED는 MySQL이 고르게 합니다.
# DEFINER (default): runs with the view creator's privileges
CREATE VIEW secret_emails SQL SECURITY DEFINER AS
SELECT email FROM users;
# INVOKER: runs with the querying user's privileges
CREATE VIEW my_emails SQL SECURITY INVOKER AS
SELECT email FROM users WHERE id = CURRENT_USER_ID();
# ALGORITHM: MERGE (preferred) vs TEMPTABLE vs UNDEFINED
CREATE ALGORITHM=MERGE VIEW v_active AS
SELECT * FROM users WHERE status='active';저장 프로시저
프로시저 생성과 호출
저장 프로시저는 이름 붙은 사전 컴파일 SQL 블록입니다. 프로시저는 세미콜론을 포함할 수 있으므로 DELIMITER를 바꿔 전체 CREATE가 하나의 문장으로 실행되게 한 뒤 되돌립니다. CALL이 프로시저를 실행합니다. 프로시저는 값을 직접 반환하지 않지만 결과 집합을 반환하고 OUT 파라미터를 수정할 수 있습니다. 서버 측 실행으로 다단계 로직의 네트워크 왕복을 줄이지만 애플리케이션 코드보다 버전 관리와 테스트가 어렵습니다.
DELIMITER //
CREATE PROCEDURE get_user_by_id(IN p_id INT)
BEGIN
SELECT id, username, email FROM users WHERE id = p_id;
END //
DELIMITER ;
# call it
CALL get_user_by_id(42);
# drop it
DROP PROCEDURE IF EXISTS get_user_by_id;
# show the definition
SHOW CREATE PROCEDURE get_user_by_id\GIN / OUT / INOUT 파라미터
파라미터는 IN(읽기 전용 입력), OUT(세션 변수로 호출자에게 쓰기 반환), INOUT(둘 다)입니다. SELECT ... INTO는 스칼라 쿼리 결과를 변수에 할당합니다. 예시는 SELECT FOR UPDATE로 행을 잠가 lost update를 막으면서 송금/입금을 트랜잭션으로 감쌉니다. 상태 코드 반환에는 OUT 파라미터를 쓰고, 프로시저 안에서 SELECT하면 결과 집합이 직접 반환됩니다.
DELIMITER //
CREATE PROCEDURE transfer(
IN p_from INT,
IN p_to INT,
IN p_amount DECIMAL(10,2),
OUT p_result VARCHAR(50)
)
BEGIN
DECLARE v_bal DECIMAL(10,2);
START TRANSACTION;
SELECT balance INTO v_bal FROM accounts WHERE id = p_from FOR UPDATE;
IF v_bal >= p_amount THEN
UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
COMMIT;
SET p_result = 'OK';
ELSE
ROLLBACK;
SET p_result = 'INSUFFICIENT_FUNDS';
END IF;
END //
DELIMITER ;
# call with a session variable for the OUT value
CALL transfer(1, 2, 50.00, @res);
SELECT @res;변수와 제어 흐름
저장 프로그램은 지역 변수(DECLARE, BEGIN...END 스코프), 사용자 변수(@var, 세션 스코프), 시스템 변수를 지원합니다. 제어 흐름에는 IF/ELSEIF/ELSE, CASE, 세 루프가 있습니다. LOOP(LEAVE/ITERATE와 함께), WHILE(조건 먼저), REPEAT(조건 나중). 중첩 시에는 루프에 이름을 붙여 LEAVE가 올바른 것을 겨냥하게 하세요. SET이 값을 할당하고, SELECT ... INTO가 쿼리 스칼라를 변수에 복사합니다.
DELIMITER //
CREATE PROCEDURE classify(IN p_age INT, OUT p_label VARCHAR(20))
BEGIN
DECLARE v_count INT DEFAULT 0;
# IF / ELSEIF / ELSE
IF p_age < 13 THEN
SET p_label = 'child';
ELSEIF p_age < 20 THEN
SET p_label = 'teen';
ELSE
SET p_label = 'adult';
END IF;
# simple loop with a counter
simple_loop: LOOP
SET v_count = v_count + 1;
IF v_count >= 5 THEN LEAVE simple_loop; END IF;
END LOOP;
# WHILE / REPEAT alternatives
# WHILE cond DO ... END WHILE;
# REPEAT ... UNTIL cond END REPEAT;
# CASE statement
CASE p_label
WHEN 'child' THEN SET p_label = CONCAT(p_label, '!');
ELSE SET p_label = CONCAT(p_label, '.');
END CASE;
END //
DELIMITER ;커서
커서는 저장 프로시저가 결과 집합을 행 단위로 순회하게 합니다. 커서를 먼저 선언하고, 고갈 감지를 위한 CONTINUE HANDLER FOR NOT FOUND를 선언하세요(플래그를 세우고 LEAVE로 루프 탈출). FETCH가 한 행을 변수로 가져옵니다. MySQL의 커서는 읽기 전용이고 전진 전용입니다. 행별 처리는 집합 기반 SQL보다 느리므로 가능하면 단일 UPDATE/INSERT ... SELECT를 선호하고, 논리가 정말 순차적일 때만 커서를 쓰세요.
DELIMITER //
CREATE PROCEDURE log_all_users()
BEGIN
DECLARE v_id INT;
DECLARE v_name VARCHAR(50);
DECLARE done INT DEFAULT 0;
# declare the cursor BEFORE handlers
DECLARE cur CURSOR FOR
SELECT id, username FROM users WHERE status='active';
# continue handler for end of cursor -> set done=1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
read_loop: LOOP
FETCH cur INTO v_id, v_name;
IF done THEN LEAVE read_loop; END IF;
INSERT INTO audit_log(user_id, action) VALUES (v_id, CONCAT('seen:', v_name));
END LOOP;
CLOSE cur;
END //
DELIMITER ;
CALL log_all_users();오류 핸들러
핸들러는 저장 프로그램 안의 오류를 잡습니다. CONTINUE는 핸들러 본문 실행 후 실패 문장 다음을 재개하고, EXIT는 본문 실행 후 BEGIN...END 블록을 빠져나가며, UNDO는 롤백합니다(폐기 예정). 임의 오류는 SQLEXCEPTION, 경고는 SQLWARNING, 특정 SQLSTATE/errno(예: 1062 중복 키)도 가능합니다. RESIGNAL은 정리 후 현재 오류를 다시 던집니다. 명명 조건(CONDITION FOR)은 핸들러를 자기 문서화하게 합니다.
DELIMITER //
CREATE PROCEDURE safe_insert(IN p_email VARCHAR(255))
BEGIN
# SQLEXCEPTION catches any error; SQLSTATE/errno catch specific ones
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL; -- re-raise so the caller sees the error
END;
DECLARE CONTINUE HANDLER FOR 1062 -- duplicate key
BEGIN
SELECT 'Duplicate skipped' AS msg;
END;
START TRANSACTION;
INSERT INTO users (username, email) VALUES ('x', p_email);
INSERT INTO stats (email) VALUES (p_email);
COMMIT;
END //
DELIMITER ;
# named conditions make handlers readable
DECLARE dup_key CONDITION FOR 1062;
DECLARE CONTINUE HANDLER FOR dup_key SELECT 'dup';프로시저 메타데이터
SHOW PROCEDURE STATUS가 프로시저를 나열하고, SHOW CREATE PROCEDURE가 전체 본문을 반환합니다. information_schema.routines는 결정성과 보안 유형을 포함한 풍부한 메타데이터를 줍니다. 출력이 입력에만 의존하는 함수는 DETERMINISTIC(READS SQL DATA / NO SQL)으로 표시하세요. 함수의 바이너리 로깅과 복제에 필요합니다. 프로시저에는 결정성 플래그가 필요 없습니다. 이 뷰들로 루틴을 감사·목록화하세요.
# list procedures in a database
SHOW PROCEDURE STATUS WHERE Db = 'mydb';
# show definition
SHOW CREATE PROCEDURE get_user_by_id\G
# query information_schema for routines
SELECT routine_name, routine_type, created, last_altered,
is_deterministic, security_type
FROM information_schema.routines
WHERE routine_schema = 'mydb';
# list stored functions too
SELECT routine_name, routine_type
FROM information_schema.routines
WHERE routine_schema = 'mydb' AND routine_type='FUNCTION';함수
문자열 함수
LENGTH는 바이트 수, CHAR_LENGTH는 문자 수입니다(멀티바이트 utf8mb4에서 중요). SUBSTRING은 1부터 시작하며 음수 시작은 끝에서 셉니다. CONCAT은 인자 중 하나라도 NULL이면 NULL을 반환하므로 NULL을 건너뛰려 CONCAT_WS를 쓰세요. SUBSTRING_INDEX는 구분자로 나눠 처음 N개를 남기고(음수 N은 오른쪽부터 유지) TRIM은 BOTH/LEADING/TRAILING 변형이 있으며 공백이 아니어도 임의 문자를 잘라냅니다.
# length & case
SELECT LENGTH('hello'), CHAR_LENGTH('héllo'); -- bytes vs chars
SELECT UPPER('abc'), LOWER('ABC');
# substring & position
SELECT SUBSTRING('MySQL', 3); -- 'SQL'
SELECT SUBSTRING('MySQL', 1, 2); -- 'My'
SELECT INSTR('MySQL', 'SQL'); -- 3
SELECT LOCATE('SQL', 'MySQL', 1); -- 3
# trim & pad
SELECT TRIM(' hi '), LTRIM(' hi'), RTRIM('hi ');
SELECT LPAD('5', 3, '0'), RPAD('5', 3, '-'); -- 005, 5--
# split & replace
SELECT REPLACE('a-b-c', '-', '/');
SELECT SUBSTRING_INDEX('a,b,c', ',', 2); -- 'a,b'
# concatenate with NULL-safe separator
SELECT CONCAT_WS(',', 'a', NULL, 'b'); -- 'a,b'숫자 함수
ROUND는 0에서 멀어지는 쪽으로 반올림하고, TRUNCATE는 반올림 없이 자릿수를 자릅니다. MOD와 %는 호환 가능합니다. RAND()는 [0,1)의 실수를 반환합니다. ORDER BY RAND()는 편리하지만 전체 결과 집합을 정렬해 비싸므로, 큰 테이블 샘플링에는 랜덤 PK 범위나 저수지 샘플링을 쓰세요. FORMAT은 천 단위 구분자를 더하고 주어진 소수 자릿수로 반올림한 문자열을 반환합니다.
# rounding
SELECT ROUND(2.567, 1); -- 2.6
SELECT CEIL(2.1), FLOOR(2.9); -- 3, 2
SELECT TRUNCATE(2.567, 1); -- 2.5
# modular & power
SELECT MOD(17, 5), 17 % 5; -- 2, 2
SELECT POWER(2, 10); -- 1024
SELECT SQRT(16); -- 4
# random
SELECT RAND(); -- 0..1 float
SELECT FLOOR(RAND() * 100); -- 0..99 integer
SELECT * FROM users ORDER BY RAND() LIMIT 5; -- random sample (slow!)
# formatting
SELECT FORMAT(1234567.891, 2); -- '1,234,567.89'
SELECT ABS(-5), SIGN(-5); -- 5, -1날짜와 시간 함수
NOW()는 한 번 호출로 현재 날짜/시간을 반환합니다(문장 내 일관). CURDATE/CURTIME은 날짜/시간 부분을 반환합니다. INTERVAL과 함께 쓰는 DATE_ADD/SUB가 정석 날짜 산술이며 월/년 넘김을 올바르게 처리합니다. DATEDIFF는 일 수(date1 - date2), TIMEDIFF는 TIME을 반환합니다. DATE_FORMAT과 STR_TO_DATE는 %Y %m %d %H %i %s 지정자를 씁니다(분은 소문자 i, 초는 s에 주의).
# current values
SELECT NOW(), CURDATE(), CURTIME(), UTC_TIMESTAMP();
# parts of a date
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW()), DAYNAME(NOW());
SELECT WEEK(NOW()), WEEKDAY(NOW()), DAYOFWEEK(NOW());
# arithmetic
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
SELECT DATE_SUB('2024-01-31', INTERVAL 1 MONTH); -- 2023-12-31
SELECT DATEDIFF('2024-12-31', '2024-01-01'); -- 365 days
SELECT TIMEDIFF('18:00:00', '09:30:00'); -- 08:30:00
# formatting
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
SELECT STR_TO_DATE('31/12/2024', '%d/%m/%Y');커스텀 함수
저장 함수는 단일 스칼라 값을 반환하며 표현식 안에서 쓸 수 있습니다(CALL하는 프로시저와 달리). 특성을 선언해야 합니다. DETERMINISTIC(같은 입력이 항상 같은 출력. 문장 기반 복제에 필요)과 READS SQL DATA / NO SQL / MODIFIES SQL DATA 중 하나. 함수는 결과 집합을 반환하거나 트랜잭션 제어를 쓸 수 없습니다. 순수 계산에는 NO SQL + DETERMINISTIC 조합이 가장 안전합니다.
DELIMITER //
CREATE FUNCTION full_name(p_first VARCHAR(50), p_last VARCHAR(50))
RETURNS VARCHAR(101)
DETERMINISTIC
READS SQL DATA
BEGIN
RETURN CONCAT_WS(' ', p_first, p_last);
END //
DELIMITER ;
# use it like a built-in
SELECT id, full_name(first_name, last_name) FROM users;
# drop it
DROP FUNCTION IF EXISTS full_name;
# functions MUST declare their nature for binary logging:
# DETERMINISTIC - same inputs -> same output (required for replication)
# READS SQL DATA - reads but doesn't modify
# NO SQL - no SQL at all제어 흐름 함수
IF()는 컴팩트한 삼항식입니다. IFNULL(a,b)는 a가 NULL이면 b(인자 두 개만), COALESCE는 N 인자로 일반화합니다. NULLIF(a,b)는 a=b일 때 NULL을 반환해 0 나눗셈 회피에 편리합니다(x / NULLIF(y,0)). ISNULL()은 값이 아니라 1/0을 반환하므로 IFNULL과 혼동하지 마세요. CASE가 가장 유연하고 이식성 있는 조건 표현식이며 어떤 SQL 방언에서도 동작합니다.
# IF(test, true_val, false_val) - ternary
SELECT IF(age >= 18, 'adult', 'minor') FROM users;
# IFNULL / NULLIF
SELECT IFNULL(email, 'no-email') FROM users; -- coalesce one
SELECT COALESCE(nick, email, username) FROM users; -- coalesce many
SELECT NULLIF(status, 'inactive'); -- NULL if equal, else status
# CASE as a function (see SELECT section for full CASE)
SELECT CASE WHEN x>0 THEN 'pos' WHEN x<0 THEN 'neg' ELSE 'zero' END;
# ISNULL() returns 1/0 (different from IFNULL!)
SELECT ISNULL(email) FROM users;캐스트와 변환
CAST와 CONVERT는 값의 타입을 바꿉니다. MySQL의 암시적 변환이 인덱스를 무력화할 때(예: 문자열과 숫자 컬럼 비교) 필수입니다. CAST(... AS DATE/DATETIME/SIGNED/UNSIGNED/CHAR/DECIMAL)가 흔한 요구를 커버합니다. 주의: CAST('123abc' AS SIGNED)는 오류가 아니라 경고와 함께 123을 반환합니다. 커스텀 날짜 형식 파싱에는 STR_TO_DATE를 쓰세요. CAST는 엄격한 ISO 형식을 기대합니다.
# CAST to a specific type
SELECT CAST('2024-01-15' AS DATE);
SELECT CAST(3.14 AS SIGNED); -- 3
SELECT CAST('123abc' AS SIGNED); -- 123 (warns in strict mode)
SELECT CAST(123 AS CHAR(10));
# CONVERT is the same thing with different syntax
SELECT CONVERT('2024-01-15', DATE);
SELECT CONVERT('hello' USING utf8mb4); -- charset conversion
# common: string -> date for comparison
SELECT * FROM orders
WHERE created_at >= CAST('2024-01-01' AS DATETIME);
# format a number as a zero-padded string
SELECT LPAD(CAST(id AS CHAR), 6, '0') FROM orders;트리거
BEFORE / AFTER 트리거
트리거는 INSERT/UPDATE/DELETE에서 자동 발동하며 BEFORE(NEW 값 수정, 검증 후 중단 가능) 또는 AFTER(로깅 같은 부수 효과)입니다. FOR EACH ROW는 본문이 영향받는 행마다 실행됨을 의미하며 행 트리거만 지원됩니다. BEFORE는 기본값 부여/정규화에, AFTER는 비핵심 연쇄 작업에 적합합니다. 트리거는 발동 문장과 같은 트랜잭션에서 실행됩니다.
DELIMITER //
CREATE TRIGGER trg_users_before_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
# normalize email to lowercase before saving
SET NEW.email = LOWER(NEW.email);
IF NEW.created_at IS NULL THEN
SET NEW.created_at = NOW();
END IF;
END //
DELIMITER ;
# AFTER INSERT trigger logs the event
CREATE TRIGGER trg_users_after_insert
AFTER INSERT ON users
FOR EACH ROW
INSERT INTO audit_log(table_name, row_id, action, at)
VALUES ('users', NEW.id, 'INSERT', NOW());감사 트리거 (UPDATE)
트리거의 흔한 용도는 변경 감사이며 OLD vs NEW를 히스토리 테이블에 기록합니다. 값 비교는 NULL을 고려해야 합니다(어느 쪽이든 NULL이면 OLD.email <> NEW.email은 NULL). IS NULL 검사로 가드하세요. AFTER DELETE는 OLD를 씁니다(삭제에는 NEW가 없음). 모든 행 변경에서 실행되므로 감사 로직은 가볍게 유지하세요. 대량 UPDATE/DELETE에도 트리거가 발동해 큰 작업에서 느려질 수 있습니다.
DELIMITER //
CREATE TRIGGER trg_users_after_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
IF OLD.email <> NEW.email OR (OLD.email IS NULL) <> (NEW.email IS NULL) THEN
INSERT INTO audit_log(table_name, row_id, field, old_val, new_val, at)
VALUES ('users', NEW.id, 'email', OLD.email, NEW.email, NOW());
END IF;
END //
DELIMITER ;
# a DELETE audit trigger
CREATE TRIGGER trg_users_after_delete
AFTER DELETE ON users
FOR EACH ROW
INSERT INTO archive_users(id, username, email, deleted_at)
VALUES (OLD.id, OLD.username, OLD.email, NOW());OLD와 NEW 값
NEW는 들어오는 행 값을, OLD는 이전 값을 담습니다. BEFORE INSERT/UPDATE에서 NEW를 수정해 데이터를 정규화하거나 기본값을 줄 수 있으며, AFTER 트리거와 DELETE에서는 읽기 전용입니다. SIGNAL SQLSTATE '45000'은 트리거나 저장 프로그램에서 커스텀 오류를 던지는 표준 방법입니다(45000은 '미처리 사용자 정의 예외'). 변경을 중단해야 하는 검증은 BEFORE, 부수 효과는 AFTER 트리거를 쓰세요.
# OLD: the row before the change (available on UPDATE/DELETE)
# NEW: the row after the change (available on INSERT/UPDATE)
# BEFORE INSERT: only NEW, you can modify it
# AFTER INSERT: only NEW, read-only
# BEFORE UPDATE: both OLD and NEW, you can modify NEW
# AFTER UPDATE: both OLD and NEW, read-only
# BEFORE DELETE: only OLD, read-only
# AFTER DELETE: only OLD, read-only
CREATE TRIGGER trg_balance_check
BEFORE UPDATE ON accounts
FOR EACH ROW
BEGIN
IF NEW.balance < 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Balance cannot be negative';
END IF;
END;여러 이벤트에 걸친 트리거 (FOLLOWS/PRECEDES)
5.7.2부터 한 테이블이 같은 이벤트와 시점에 여러 트리거를 가질 수 있으며 FOLLOWS/PRECEDES로 순서를 정합니다. 각 트리거는 여전히 정확히 하나의 이벤트(INSERT, UPDATE, DELETE)와 시점(BEFORE/AFTER)을 다룹니다. 여러 이벤트를 다루려면 트리거를 여러 개 만드세요. 트리거가 서로의 효과에 의존하면 순서가 중요합니다. MySQL에는 INSTEAD OF 트리거가 없습니다(뷰는 직접 갱신 가능).
# MySQL allows multiple triggers with the same event/timing on one table (5.7.2+)
# order them with FOLLOWS / PRECEDES
DELIMITER //
CREATE TRIGGER trg_users_after_insert_2
AFTER INSERT ON users
FOR EACH ROW
FOLLOWS trg_users_after_insert
BEGIN
UPDATE user_stats SET total = total + 1 WHERE user_id = NEW.id;
END //
DELIMITER ;
# events: INSERT | UPDATE | DELETE
# timing: BEFORE | AFTER
# a trigger cannot span multiple events; create one per event트리거 관리
SHOW TRIGGERS가 모든 트리거를 나열합니다('Table' 컬럼 이름이 예약어라 백틱 인용에 주의). SHOW CREATE TRIGGER는 전체 본문과 정의자를 반환합니다. information_schema.triggers는 감사용 구조화 메타데이터를 줍니다. 스크립트에는 DROP TRIGGER IF EXISTS가 안전합니다. 트리거는 테이블에 묶여 있어 테이블을 드롭하면 트리거도 사라집니다. 정의자를 주의하세요. 프로시저처럼 트리거는 기본적으로 정의자 권한으로 실행됩니다.
# list triggers
SHOW TRIGGERS\G
SHOW TRIGGERS WHERE `Table` = 'users';
# show definition
SHOW CREATE TRIGGER trg_users_before_insert\G
# query metadata
SELECT trigger_name, event_manipulation, action_timing,
event_object_table, created
FROM information_schema.triggers
WHERE trigger_schema = 'mydb';
# drop a trigger
DROP TRIGGER IF EXISTS trg_users_before_insert;
# triggers fire on the table's schema; definer is recorded트랜잭션과 잠금
트랜잭션 제어
MySQL은 기본 자동 커밋이며 각 문장이 자체 트랜잭션입니다. START TRANSACTION/BEGIN이 명시적 블록을 시작하며 COMMIT(저장)이나 ROLLBACK(취소)으로 끝납니다. 트랜잭션은 트랜잭션 엔진(InnoDB)만 지원하며 MyISAM은 지원하지 않습니다. 결정적으로 DDL 문장(CREATE/ALTER/DROP/TRUNCATE)은 현재 트랜잭션을 암시적으로 커밋하고 롤백할 수 없으므로, 원자성을 기대하며 DDL과 트랜잭션 DML을 섞지 마세요.
# explicit transaction (autocommit is on by default)
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK;
# alternative syntax
BEGIN;
INSERT INTO orders (user_id, amount) VALUES (1, 50);
UPDATE users SET balance = balance - 50 WHERE id = 1;
COMMIT;
# turn autocommit off for the session
SET autocommit = 0;
-- every statement is now part of a transaction until COMMIT/ROLLBACK
SET autocommit = 1;
# DDL (CREATE/ALTER/DROP) implicitly commits — cannot be rolled back세이브포인트
SAVEPOINT는 트랜잭션 안의 명명된 지점으로 전체 트랜잭션을 중단하지 않고 부분 롤백할 수 있게 합니다. ROLLBACK TO SAVEPOINT는 세이브포인트 이후 작업을 취소하고, RELEASE SAVEPOINT는 마커를 버립니다(작업은 유지). 세이브포인트로 선택적 단계를 시도하고 우아하게 회복할 수 있습니다. 트랜잭션 밖에서는 보이지 않고 COMMIT/ROLLBACK에 사라지며 중첩도 지원됩니다.
START TRANSACTION;
INSERT INTO orders (amount) VALUES (10);
SAVEPOINT sp_after_order;
INSERT INTO order_items (order_id) VALUES (LAST_INSERT_ID());
SAVEPOINT sp_after_items;
-- something risky
UPDATE inventory SET stock = stock - 1 WHERE sku='X';
-- undo just the last step, keep the order + items
ROLLBACK TO SAVEPOINT sp_after_items;
-- release a savepoint when no longer needed
RELEASE SAVEPOINT sp_after_order;
COMMIT;격리 수준
격리 수준은 일관성과 동시성의 절충입니다. MySQL의 기본 REPEATABLE READ는 더티 읽기와 반복 불가능 읽기를 막고, SQL 표준과 달리 next-key 잠금으로 대부분의 팬텀 읽기도 피합니다. READ COMMITTED는 잠금이 적지만 반복 불가능 읽기를 허용해 인기입니다. SERIALIZABLE은 모든 읽기를 잠금 읽기로 바꿉니다. InnoDB는 MVCC를 쓰므로 SERIALIZABLE을 제외한 어느 수준에서도 읽기가 쓰기를 막지 않습니다.
# four levels, from least to most strict
# READ UNCOMMITTED - dirty reads possible (rarely used)
# READ COMMITTED - no dirty reads; non-repeatable reads possible
# REPEATABLE READ - default; consistent reads within a transaction
# SERIALIZABLE - locks everything; safest, slowest
# set for the session
SET SESSION transaction_isolation = 'REPEATABLE-READ';
# MySQL 8.0+ name; older: tx_isolation
# set globally (new connections inherit it)
SET GLOBAL transaction_isolation = 'READ-COMMITTED';
# inspect
SELECT @@transaction_isolation;
SELECT @@GLOBAL.transaction_isolation;LOCK TABLES
LOCK TABLES는 테이블 수준 잠금을 걸며 대부분 MyISAM 시대의 기능입니다. InnoDB의 행 수준 잠금이 보통 낫습니다. READ 락은 다른 세션의 읽기를 허용하지만 쓰기를 막고, WRITE 락은 나머지 전부를 막습니다. 락을 쥐는 동안에는 잠긴 테이블에만 접근할 수 있습니다. LOCK TABLES는 활성 트랜잭션을 암시 커밋하므로 트랜잭션과 조합되지 않습니다. InnoDB 동시성 제어에는 SELECT ... FOR UPDATE를 선호하세요.
# explicit table locks (outside transactions; MyISAM-style)
LOCK TABLES users WRITE, orders READ;
-- only these tables are accessible; users is write-locked
SELECT * FROM users;
UNLOCK TABLES;
# READ lock: this session can read; others can read but not write
# WRITE lock: only this session can read/write; others blocked entirely
# in InnoDB prefer row-level locks (SELECT FOR UPDATE) over LOCK TABLES
# LOCK TABLES implicitly commits the active transactionSELECT ... FOR UPDATE / SHARE
SELECT ... FOR UPDATE는 배타적 행 락을 걸어 다른 트랜잭션이 같은 행을 갱신하거나 잠그려 하면 막힙니다. 이체 같은 읽기-수정-쓰기 시퀀스에 필수입니다. FOR SHARE는 공유 락을 겁니다(타인은 읽을 수 있지만 쓸 수 없음). 락은 COMMIT/ROLLBACK에서 해제됩니다. 8.0은 NOWAIT(대기 대신 오류)과 SKIP LOCKED(잠긴 행 건너뛰기)를 추가했으며 서로 막지 말아야 할 작업 큐 워커에 완벽합니다.
START TRANSACTION;
# lock the row so other transactions cannot modify it until commit
SELECT balance INTO @b FROM accounts WHERE id = 1 FOR UPDATE;
IF @b >= 100 THEN
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
END IF;
COMMIT; -- releases the lock
# FOR UPDATE: exclusive lock (write intent)
# FOR SHARE: shared lock (read intent, others can read)
# NOWAIT / SKIP LOCKED reduce waiting (8.0+)
SELECT * FROM jobs WHERE status='pending' LIMIT 1 FOR UPDATE SKIP LOCKED;교착과 감지
InnoDB는 교착을 자동 감지해 한 트랜잭션(피해자)을 롤백해 다른 쪽이 진행하게 하며 오류 1213을 받습니다. SHOW ENGINE INNODB STATUS가 마지막 교착을 보여 주고, innodb_print_all_deadlocks를 켜면 모든 교착이 로그됩니다. 예방 방법: 트랜잭션 간 일관된 순서로 행 잠금, 짧은 트랜잭션, 잠그는 컬럼의 인덱싱(없으면 잠금이 넓은 범위로 확대). 교착 가능 트랜잭션 주위에는 항상 재시도 로직을 두세요.
# deadlock: two transactions each hold a lock the other needs
# Tx1: locks A, wants B
# Tx2: locks B, wants A
# -> InnoDB detects it and rolls back the victim automatically
# see the last deadlock
SHOW ENGINE INNODB STATUS\G
# log all deadlocks to the error log
SET GLOBAL innodb_print_all_deadlocks = 1;
# mitigate by:
# - locking tables/rows in a consistent order across transactions
# - keeping transactions short
# - adding appropriate indexes (avoid gap locks on full scans)
# - using a lower isolation level where safe사용자 관리
사용자 생성과 인증
MySQL 계정은 항상 'user'@'host'입니다. 호스트가 어디서 연결할 수 있는지 제어하며 '%'는 모든 호스트 와일드카드입니다. MySQL 8.0은 기본으로 caching_sha2_password(더 안전)를 쓰는데 일부 오래된 클라이언트는 지원하지 않으므로 그들은 mysql_native_password로 폴백하세요. '%'보다 좁게 범위 지정된 호스트(10.0.0.%)를 선호하세요. CREATE USER가 8.0에서 제거된 오래된 GRANT ... IDENTIFIED BY 문법보다 바람직합니다.
# create a user that can connect from anywhere
CREATE USER 'app'@'%' IDENTIFIED BY 'StrongPass!2024';
# restrict to localhost only
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'localpass';
# MySQL 8.0 default plugin is caching_sha2_password
CREATE USER 'dev'@'10.0.%.%' IDENTIFIED WITH caching_sha2_password
BY 'devpass';
# legacy mysql_native_password (needed for some older clients)
CREATE USER 'legacy'@'%' IDENTIFIED WITH mysql_native_password
BY 'legacypass';
# rename or change host
RENAME USER 'app'@'%' TO 'appuser'@'10.0.0.%';
# drop a user
DROP USER IF EXISTS 'legacy'@'%';GRANT 권한
GRANT는 네 스코프로 권한을 부여합니다. 글로벌(*.*), 데이터베이스(db.*), 테이블(db.table), 컬럼/루틴. ALL PRIVILEGES는 GRANT OPTION을 제외한 모든 것입니다. 필요한 최소 권한만 주세요. 보고 사용자는 SELECT만 받아야 합니다. WITH GRANT OPTION은 사용자가 자기 권한을 타인에게 줄 수 있게 합니다(위험). GRANT는 메모리 테이블을 갱신하므로 FLUSH PRIVILEGES는 드뭅니다. mysql 테이블을 수동 편집한 뒤에만 쓰세요.
# grant all on a specific database
GRANT ALL PRIVILEGES ON mydb.* TO 'app'@'%';
# grant read-only on one table
GRANT SELECT ON mydb.users TO 'reporting'@'%';
# grant specific privileges
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'editor'@'localhost';
# grant the ability to grant to others (WITH GRANT OPTION)
GRANT SELECT ON mydb.* TO 'lead'@'%' WITH GRANT OPTION;
# grant at server level (use sparingly)
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor'@'10.0.0.5';
# reload privilege tables into memory (usually automatic)
FLUSH PRIVILEGES;REVOKE 권한
REVOKE는 이전에 부여된 권한을 제거하며 스코프를 정확히 맞춰야 합니다. GRANT OPTION까지 모든 것을 빼앗으려 REVOKE ALL PRIVILEGES, GRANT OPTION을 쓰세요. 권한 제거가 사용자를 드롭하지는 않습니다. 계정은 존재하고 연결할 수 있지만 다시 부여할 때까지 권한이 없습니다. SHOW GRANTS로 사용자의 실효 권한을 검사하고, 접근 변경 전 반드시 검토하세요. 완전 제거는 DROP USER입니다.
# remove specific privileges
REVOKE INSERT, UPDATE ON mydb.* FROM 'editor'@'localhost';
# remove all privileges (but keep the user)
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app'@'%';
# revoke at a specific scope
REVOKE SELECT ON mydb.users FROM 'reporting'@'%';
# see what a user currently has
SHOW GRANTS FOR 'app'@'%';
# show grants for the current user
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER();역할 (MySQL 8.0+)
역할(8.0+)은 권한을 그룹화해 한 번에 많은 사용자에게 부여하게 해 접근 관리를 단순화합니다. 역할을 부여받은 사용자는 역할이 활성화될 때까지 권한을 얻지 못하며, SET DEFAULT ROLE이 로그인 시 기본 활성화합니다. CURRENT_ROLE()이 활성 역할을 보여 줍니다. 역할을 한 번 바꾸면 모든 멤버 사용자가 변경을 물려받습니다. 같은 권한을 개별 사용자마다 부여하는 것보다 훨씬 깔끔합니다.
# a role is a named bundle of privileges
CREATE ROLE 'app_read', 'app_write';
GRANT SELECT ON mydb.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON mydb.* TO 'app_write';
# grant a role to users
GRANT 'app_read' TO 'alice'@'%', 'bob'@'%';
GRANT 'app_write' TO 'carol'@'%';
# a user must activate a role to use it (default: none)
SET DEFAULT ROLE 'app_read' TO 'alice'@'%';
SET DEFAULT ROLE ALL TO 'alice'@'%';
# see active roles
SELECT CURRENT_ROLE();비밀번호 관리
ALTER USER가 현대적인 비밀번호 관리 방법입니다(SET PASSWORD는 폐기 예정). PASSWORD EXPIRE는 다음 로그인 시 재설정을 강제합니다. 비밀번호 순환 정책에 유용합니다. validate_password 구성 요소는 MEDIUM(대소문자, 숫자, 특수 문자)과 STRONG(추가로 사전 단어 금지) 수준에서 길이/복잡도를 강제합니다. ACCOUNT LOCK/UNLOCK은 드롭 없이 계정을 임시 비활성화합니다. 조사 중 접근 중단에 편리합니다.
# change your own password
ALTER USER USER() IDENTIFIED BY 'NewPass!2024';
# change another user's password
ALTER USER 'app'@'%' IDENTIFIED BY 'NewAppPass!2024';
# require a password to be expired (user must reset on next login)
ALTER USER 'app'@'%' PASSWORD EXPIRE;
# enforce password policy (8.0+ validate_password component)
INSTALL COMPONENT 'file://component_validate_password';
SET GLOBAL validate_password.policy = 'MEDIUM'; -- 0/LOW,1/MEDIUM,2/STRONG
SET GLOBAL validate_password.length = 12;
# lock an account temporarily
ALTER USER 'app'@'%' ACCOUNT LOCK;
ALTER USER 'app'@'%' ACCOUNT UNLOCK;Show Grants와 사용자 조회
mysql.user는 모든 계정을 담는 시스템 테이블입니다. 사용자와 account_locked, password_expired 같은 플래그를 목록화하려 조회하세요. SHOW GRANTS는 한 사용자의 권한을 디코딩된 더 친절한 뷰로 보여 줍니다. CURRENT_USER()는 권한 검사에 쓰이는 신원으로(연결 문자열의 USER()와 다를 수 있음) 역할이나 프록시를 통해 연결할 때 중요합니다. 잠자거나 과도한 권한의 계정을 찾으려 정기적으로 감사하세요.
# list all users (MySQL 8.0+)
SELECT user, host, account_locked, password_expired
FROM mysql.user;
# grants for a specific user
SHOW GRANTS FOR 'app'@'%';
# grants for the current session
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER();
# who am I?
SELECT CURRENT_USER(), USER();
# active roles for the current session
SELECT CURRENT_ROLE();
# list global privileges
SELECT user, host, Super_priv, Process_priv, Reload_priv
FROM mysql.user;백업과 복구
mysqldump
mysqldump는 고전적 논리 백업 도구로 데이터베이스를 재생성하는 SQL 문을 출력합니다. --single-transaction은 단일 REPEATABLE READ 트랜잭션으로 잠금 없이 일관된 InnoDB 스냅샷을 줍니다. --routines/--triggers/--events는 저장 프로그램을 포함합니다(없으면 누락). 큰 데이터베이스에는 mysqldump보다 훨씬 빠른 Percona XtraBackup이나 MySQL Enterprise Backup(물리 백업)을 선호하세요.
# dump a single database to a file
mysqldump -u root -p mydb > mydb.sql
# dump all databases
mysqldump -u root -p --all-databases > alldb.sql
# dump with routines, triggers and events
mysqldump -u root -p --routines --triggers --events mydb > mydb_full.sql
# dump schema only (no data) or data only
mysqldump -u root -p --no-data mydb > schema.sql
mysqldump -u root -p --no-create-info mydb > data.sql
# consistent dump of InnoDB in one transaction
mysqldump -u root -p --single-transaction mydb > mydb.sql
# compress on the fly
mysqldump -u root -p mydb | gzip > mydb.sql.gz덤프에서 복구
복구는 덤프 파일을 mysql 클라이언트로 파이프하거나 클라이언트 안에서 SOURCE를 씁니다. 대상 데이터베이스가 이미 존재해야 합니다(CREATE DATABASE 후 USE). 더 빠른 복구를 위해 로드 중 외래 키와 유니크 검사, 자동 커밋을 끄고 나중에 다시 켜세요. 덤프 자체에 보통 이 가드들이 들어 있습니다. gunzip으로 압축 덤프를 복구하면 디스크에 풀지 않아도 됩니다. 항상 스테이징 서버에서 복구를 테스트하세요.
# restore a database dump (database must exist)
mysql -u root -p mydb < mydb.sql
# restore all databases (creates them)
mysql -u root -p < alldb.sql
# restore a compressed dump
gunzip < mydb.sql.gz | mysql -u root -p mydb
# restore from inside the mysql client
SOURCE /path/to/mydb.sql;
# speed up restores: disable checks and autocommit during load
SET foreign_key_checks = 0;
SET unique_checks = 0;
SET autocommit = 0;
SOURCE /path/to/mydb.sql;
COMMIT;
SET foreign_key_checks = 1;
SET unique_checks = 1;CSV 내보내기
SELECT ... INTO OUTFILE은 secure_file_priv 디렉터리로 제한된 서버 측 파일을 씁니다(secure_file_priv가 NULL이면 완전히 비활성). FILE 권한이 필요하며 기존 파일을 덮어쓸 수 없습니다. mysql 클라이언트의 --batch --raw는 그런 제한 없이 로컬에 탭 구분 파일을 만들어 cron 작업에 이상적입니다. FIELDS/LINES 절이 CSV 호환성을 위해 구분자, 인용, 이스케이프를 제어합니다.
# server-side export to a file (needs FILE privilege + secure_file_priv)
SELECT id, username, email, created_at
FROM users
INTO OUTFILE '/var/lib/mysql-files/users.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n';
# client-side export with the mysql client (no FILE privilege needed)
mysql -u root -p -e "SELECT id, username, email FROM users" \
--batch --raw mydb > users.tsv
# include column headers with a UNION
SELECT 'id','username','email' UNION
SELECT id, username, email FROM users
INTO OUTFILE '/var/lib/mysql-files/users.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';CSV 가져오기
LOAD DATA INFILE이 CSV 로드의 가장 빠른 방법이며 INSERT 문보다 훨씬 빠릅니다. 서버 측 형태는 secure_file_priv 아래 경로에서 읽고, LOCAL은 클라이언트 파일을 읽습니다(클라이언트와 서버 모두 local_infile 필요). IGNORE 1 LINES가 헤더를 건너뜁니다. 값 변환(예: 날짜 문자열 파싱)에는 @var로 컬럼을 받은 뒤 SET을 쓰세요. 매우 큰 로드에서는 키/외래 키를 끄고 단일 트랜잭션을 쓰세요.
# server-side import (needs FILE privilege)
LOAD DATA INFILE '/var/lib/mysql-files/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, username, email, @created_at)
SET created_at = STR_TO_DATE(@created_at, '%Y-%m-%d %H:%i:%s');
# client-side import (no FILE privilege, sends file from client)
LOAD DATA LOCAL INFILE 'C:/data/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES (id, username, email);
# enable LOCAL on the client: --local-infile=1
# and on the server: SET GLOBAL local_infile = 1;바이너리 로그 (mysqlbinlog)
바이너리 로그는 복제와 시점 복구를 위해 모든 데이터 변경 문장을 기록합니다. SHOW MASTER STATUS가 현재 파일과 위치(복제 좌표)를 줍니다. mysqlbinlog는 binlog 파일을 검사나 재생을 위해 SQL(또는 행 이벤트)로 되돌립니다. 시작/정지 시간이나 위치와 결합해 정확한 시점까지 복구하세요. PURGE BINARY LOGS가 디스크를 회수하며, expire_logs_days/binlog_expire_logs_seconds로 자동화하게 두세요.
# enable the binary log (my.cnf)
# [mysqld]
# log_bin = mysql-bin
# binlog_format = ROW
# server_id = 1
# show current binlog files and position
SHOW BINARY LOGS;
SHOW MASTER STATUS;
# view events in a binlog file
mysqlbinlog mysql-bin.000123
# filter by time / position
mysqlbinlog --start-datetime="2024-06-01 00:00:00" \
--stop-datetime="2024-06-01 12:00:00" \
mysql-bin.000123 > replay.sql
# replay events to recover
mysql -u root -p < replay.sql
# purge old binlogs
PURGE BINARY LOGS BEFORE '2024-06-01 00:00:00';Clone 플러그인과 복제
clone 플러그인(8.0+)은 논리 덤프보다 훨씬 빠르게 InnoDB 데이터 디렉터리를 물리 복사하며 레플리카 프로비저닝이나 스냅샷에 이상적입니다. 복제를 위해 CHANGE REPLICATION SOURCE TO(8.0+, 예전 CHANGE MASTER TO)로 레플리카를 소스에 연결하고 START REPLICA를 실행하세요. GTID(SOURCE_AUTO_POSITION=1)가 복제 위치를 견고하게 만듭니다. SHOW REPLICA STATUS가 지연과 오류를 보여 주며, 레플리카 건강을 위해 Seconds_Behind_Source를 모니터링하세요.
# install the clone plugin (8.0+) for fast physical copy
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
# clone a local data directory to another path
CLONE LOCAL DATA DIRECTORY = '/var/lib/mysql-clone';
# clone from a remote donor (good for provisioning replicas)
CLONE INSTANCE FROM 'donor'@'10.0.0.2':3306
IDENTIFIED BY 'donorpass';
# set up replication from a donor
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='10.0.0.2', SOURCE_PORT=3306,
SOURCE_USER='repl', SOURCE_PASSWORD='replpass',
SOURCE_AUTO_POSITION=1;
START REPLICA; -- 8.0+ (was START SLAVE)
SHOW REPLICA STATUS\G성능 최적화
EXPLAIN
EXPLAIN은 쿼리 플랜을 보여 줍니다. 어느 인덱스가 쓰이는지, 행 수 추정치, filesort나 임시 테이블이 필요한지. 'type' 컬럼이 접근 방식을 순위 매김합니다. const/eq_ref가 최선(단일 행 조회), ref/range는 인덱스 사용, index는 인덱스 전체 스캔, ALL은 테이블 전체 스캔(보통 나쁨). 'Using index'는 커버링 인덱스 전용 스캔이며 'Using filesort'/'Using temporary'는 최적화할 비싼 연산의 경고입니다.
# show the execution plan for a query
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
# extended: extra info + warnings
EXPLAIN EXTENDED SELECT ...;
SHOW WARNINGS;
# format as a tree (8.0+) — more readable
EXPLAIN FORMAT=TREE SELECT ...;
# the key columns to read:
# type - access method (const > eq_ref > ref > range > index > ALL)
# key - the index chosen
# rows - estimated rows scanned
# Extra - 'Using index' (good) / 'Using filesort' / 'Using temporary'EXPLAIN ANALYZE (8.0+)
EXPLAIN ANALYZE(8.0.17+)는 실제로 쿼리를 실행하고 이터레이터별 실제 행 수와 시간을 보고해 시간이 어디에 쓰이는지 드러냅니다. 일반 EXPLAIN의 추정치보다 훨씬 정확합니다. 스테이징의 SELECT 쿼리에 쓰세요. SHOW WARNINGS와 함께 쓰면(뷰 병합, 상수 폴딩 후) 옵티마이저가 재작성한 쿼리를 볼 수 있습니다. 쓰기 쿼리는 읽기 전용 등가물에 EXPLAIN을 쓰거나 performance_schema로 검사하세요.
# actually runs the query and reports real per-step timing
EXPLAIN ANALYZE
SELECT u.username, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;
# output shows actual rows, loops and per-iterator ms:
# -> Table scan on users (actual rows=1000, loops=1)
# -> Covering index lookup on o (actual rows=2.4, loops=1000)
# unlike EXPLAIN, this executes the statement (read-only queries only)
# also useful: SHOW WARNINGS after EXPLAIN to see the rewritten query
EXPLAIN SELECT ...;
SHOW WARNINGS\G옵티마이저 힌트
옵티마이저 힌트(/*+ ... */)는 단일 문장에 대해 플래너를 무시합니다. SET GLOBAL보다 목표가 분명하고 문장 재시작에도 살아남습니다. 나쁜 플랜을 고치는 데 쓰되 근본 원인(오래된 통계, 누락 인덱스, 치우친 데이터)도 다루세요. 데이터가 바뀌면 힌트는 썩습니다. MAX_EXECUTION_TIME은 읽기 전용 쿼리를 제한해 무모한 SELECT로부터 서버를 보호합니다. SET_VAR는 단일 문장에 한해 세션 변수를 조정합니다.
# index hints (8.0+ style, after SELECT)
SELECT /*+ INDEX(u idx_email) */ *
FROM users u
WHERE email = '[email protected]';
SELECT /*+ NO_RANGE_OPTIMIZATION(u) */ *
FROM users u WHERE id BETWEEN 1 AND 1000;
# join order / algorithm hints
SELECT /*+ JOIN_ORDER(a,b,c) */ * FROM a JOIN b ON ... JOIN c ON ...;
SELECT /*+ HASH_JOIN(b) */ * FROM a JOIN b ON a.id=b.aid;
SELECT /*+ NLJ(b) */ * FROM a JOIN b ON a.id=b.aid;
# resource hints
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM big_table;
# aborts the query after 1000 ms (read-only)
# SET_VAR hint changes a variable for one statement
SELECT /*+ SET_VAR(sort_buffer_size=16M) */ ... FROM ...;Slow Query Log
slow query log는 long_query_time(초, 소수 허용)을 초과하는 쿼리를 포착합니다. log_queries_not_using_indexes를 켜면 빠르더라도 전체 스캔을 잡습니다. mysqldumpslow가 유사 쿼리를 집계해 시간/횟수/행으로 순위 매기므로 최악을 우선순위화할 수 있습니다. Percona의 pt-query-digest는 지문, 백분위 같은 훨씬 풍부한 보고를 주는 slow log 분석의 표준 도구입니다. 로그를 정기적으로 로테이션하고 청소하세요.
# enable in my.cnf
# [mysqld]
# slow_query_log = 1
# slow_query_log_file = /var/log/mysql/slow.log
# long_query_time = 2 # log queries slower than 2s
# log_queries_not_using_indexes = 1
# log_slow_admin_statements = 1
# enable at runtime (persists until restart)
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;
# analyze the slow log with mysqldumpslow
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# -s t sort by total time, -t 10 top 10
# -s c count, -s r rows, -s l lock time
# pt-query-digest (Percona Toolkit) gives richer analysis
pt-query-digest /var/log/mysql/slow.logSHOW PROFILE과 Performance Schema
SHOW PROFILE은 쿼리를 단계로 나눕니다(테이블 열기, 정렬, 데이터 전송). 편리하지만 폐기 예정입니다. 현대적 등가물은 performance_schema로, 8.0에서 기본 켜진 저오버헤드 계측 프레임워크입니다. events_statements_summary_by_digest를 조회해 서버 전체에서 가장 많은 누적 시간을 소비하는 쿼리를 찾고, events_stages_history_long으로 단계별 상세를 파고드세요. 단일 느린 쿼리가 아니라 시스템적 병목을 찾는 방법입니다.
# profiling (deprecated but quick) - per-statement stage timings
SET profiling = 1;
SELECT COUNT(*) FROM huge_table;
SHOW PROFILE;
SHOW PROFILE FOR QUERY 1;
# preferred: performance_schema (8.0+, enabled by default)
# top queries by total latency
SELECT digest_text, count_star, sum_timer_wait/1e9 AS total_s
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;
# which stage of which query took longest
SELECT event_name, format_time(timer_wait) AS took
FROM performance_schema.events_stages_history_long
ORDER BY timer_wait DESC LIMIT 10;인덱스 최적화 팁
인덱스 설계가 성능 싸움의 대부분을 결정합니다. leftmost-prefix 규칙을 따르고, 선행 와일드카드를 피하며, 인덱스 컬럼을 함수로 감싸지 마세요(또는 generated column + 인덱스 사용). 핫 쿼리에는 커버링 인덱스를 겨냥하세요. WHERE 조건을 sargable(검색 인자 가능)하게 유지해 인덱스가 쓰이게 하세요. created_at >= x AND created_at < y가 DATE(created_at)=x를 이깁니다. ANALYZE TABLE은 옵티마이저가 의존하는 카디널리티 통계를 갱신합니다.
# 1. index columns used in WHERE / JOIN / ORDER BY
# with the most selective equality column first
CREATE INDEX idx_user_status ON orders(user_id, status, created_at);
# 2. avoid leading wildcards (defeats the index)
SELECT * FROM users WHERE name LIKE 'A%'; -- OK
SELECT * FROM users WHERE name LIKE '%A'; -- full scan
# 3. don't wrap indexed columns in functions
SELECT * FROM orders WHERE DATE(created_at)='2024-01-01'; -- bad
SELECT * FROM orders WHERE created_at >= '2024-01-01'
AND created_at < '2024-01-02'; -- good
# 4. use a covering index so the query is index-only
EXPLAIN SELECT user_id, status FROM orders WHERE user_id=5;
-- 'Using index' in Extra means no table lookup
# 5. refresh statistics after big loads
ANALYZE TABLE orders;JSON 연산
JSON 컬럼과 삽입
JSON 타입은 검증된 바이너리 형식으로 JSON을 저장합니다. JSON 문자열 리터럴로 삽입하거나 JSON_OBJECT()(키/값 쌍), JSON_ARRAY()로 값을 만드세요. 잘못된 JSON은 삽입 시 거부됩니다. JSON은 파싱된 트리로 저장되어 멤버 접근이 빠르지만 전체 문서가 함께 읽힙니다. 자주 조회되는 스칼라 값은 generated column으로 추출해 인덱싱하면 두 세계의 장점을 모두 얻습니다.
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
attrs JSON
);
# insert JSON literals
INSERT INTO products VALUES
(1, 'Widget',
'{"color":"red","size":42,"tags":["new","sale"],"stock":{"ny":5,"la":3}}'),
(2, 'Gadget', '{"color":"blue","in_stock":true}');
# build JSON with functions
INSERT INTO products VALUES (3, 'Thing',
JSON_OBJECT('color','green','tags',JSON_ARRAY('new')));JSON_EXTRACT / -> / ->>
->는 JSON 멤버를 추출하고(따옴표가 붙은 JSON 반환) ->>는 따옴표 없는 텍스트로 반환합니다(비교에는 이것이 일반적). 경로는 루트가 $., 멤버가 $.key, 배열 인덱스가 $.arr[0], 중첩이 $.a.b입니다. JSON_CONTAINS_PATH는 키 존재를 검사합니다. JSON_EXTRACT는 ->의 함수 형태입니다. 멤버 값으로 문서를 효율적으로 찾으려 attrs->>'$.color'에 generated column + 인덱스를 추가하세요.
# extract a member (returns JSON)
SELECT JSON_EXTRACT(attrs, '$.color') FROM products WHERE id=1; -- "red"
SELECT attrs->'$.color' FROM products WHERE id=1; -- "red"
# extract as text (unquoted)
SELECT attrs->>'$.color' FROM products WHERE id=1; -- red
# array element access
SELECT attrs->>'$.tags[0]' FROM products WHERE id=1; -- new
# nested paths
SELECT attrs->>'$.stock.ny' FROM products WHERE id=1; -- 5
# filter rows by a JSON value
SELECT id, name FROM products
WHERE attrs->>'$.color' = 'red';
# exists check
SELECT id FROM products
WHERE JSON_CONTAINS_PATH(attrs, 'one', '$.stock');JSON_SET / INSERT / REPLACE / REMOVE
이 함수들은 수정된 JSON 문서를 반환하므로 컬럼을 갱신하려면 결과를 다시 할당해야 합니다. JSON_SET은 upsert(추가 또는 갱신), INSERT는 없을 때만 추가, REPLACE는 있을 때만 갱신, REMOVE는 삭제입니다. JSON_MERGE_PATCH(RFC 7396)는 두 문서를 병합하며 null로 설정된 키를 제거합니다. 부분 패치 적용의 표준 방법입니다. JSON_MERGE_PRESERVE는 교체 대신 기존 배열/객체를 유지합니다.
# SET updates or adds a member (returns the modified document)
UPDATE products
SET attrs = JSON_SET(attrs, '$.color', 'green', '$.weight', 1.2)
WHERE id = 1;
# INSERT adds only if the path doesn't exist
UPDATE products
SET attrs = JSON_INSERT(attrs, '$.code', 'W-100')
WHERE id = 1;
# REPLACE updates only if the path exists
UPDATE products
SET attrs = JSON_REPLACE(attrs, '$.size', 99)
WHERE id = 1;
# REMOVE deletes a member
UPDATE products
SET attrs = JSON_REMOVE(attrs, '$.tags[0]')
WHERE id = 1;
# MERGE_PATCH merges two JSON documents (8.0+)
SET attrs = JSON_MERGE_PATCH(attrs, '{"size":50,"color":null}')JSON_TABLE (8.0+)
JSON_TABLE(8.0+)은 JSON과 관계형을 잇는 다리입니다. JSON 문서를 어떤 테이블처럼 조인, 필터, 집계할 수 있는 행으로 분쇄합니다. columns 절이 JSON 경로를 타입이 지정된 컬럼으로 매핑합니다. NESTED PATH는 중첩 배열을 한 번에 풉니다. JSON 컬럼을 반구조 데이터에 실용적으로 만듭니다. 유연한 JSON을 저장하고, SQL 분석이 필요할 때 JSON_TABLE로 관계형으로 분쇄하고, 핫 경로에는 generated column을 인덱싱하세요.
# turn a JSON array of objects into relational rows
SELECT jt.*
FROM products p,
JSON_TABLE(p.attrs, '$.tags[*]'
COLUMNS (
tag VARCHAR(50) PATH '$' -- each element as a column
)
) AS jt
WHERE p.id = 1;
# flatten an array of objects
SELECT p.id, jt.color, jt.size
FROM products p,
JSON_TABLE(p.attrs, '$'
COLUMNS (
color VARCHAR(20) PATH '$.color',
size INT PATH '$.size'
)
) AS jt;
# nested arrays: NESTED PATH
JSON_TABLE(j, '$.stock.*'
COLUMNS (warehouse VARCHAR(5) PATH '$.wh', qty INT PATH '$.qty'))JSON 집계 함수
JSON_ARRAYAGG는 그룹별 값을 JSON 배열로 모으고, JSON_OBJECTAGG는 key->value 객체를 만듭니다(나중 키가 중복을 덮어씀). JSON_OBJECT와 결합하면 API 응답용 객체 배열을 SQL에서 바로 만들 수 있습니다. 앱에서 후처리 없이 중첩 페이로드를 조립할 때 편리합니다. NULL 입력은 둘 다 건너뜁니다. 결과 크기를 주의하세요. 거대 그룹은 거대 JSON 값을 만듭니다.
# JSON_ARRAYAGG: collect values into a JSON array
SELECT user_id,
JSON_ARRAYAGG(email) AS emails
FROM contacts
GROUP BY user_id;
-- ["[email protected]","[email protected]"]
# JSON_OBJECTAGG: key/value pairs into a JSON object
SELECT user_id,
JSON_OBJECTAGG(type, value) AS prefs
FROM user_prefs
GROUP BY user_id;
-- {"theme":"dark","lang":"en"}
# build a JSON array of objects with JSON_OBJECT + ARRAYAGG
SELECT JSON_ARRAYAGG(
JSON_OBJECT('id', id, 'name', username)
) AS users
FROM users WHERE status='active';JSON 검증과 스키마
JSON_VALID는 잘 형성됨을 검사합니다(삽입 시 이미 강제). JSON_PRETTY는 가독성을 위해 다시 서식을 지정하고, JSON_STORAGE_SIZE는 바이트를 보고합니다. JSON_SCHEMA_VALID(8.0.17+)는 CHECK 제약이 JSON Schema(필수 키, 타입)를 강제하게 합니다. 유연성을 잃지 않고 JSON 컬럼에 구조를 주는 가벼운 방법입니다. JSON_TABLE과 JSON_EXTRACT는 경로가 없거나 유효하지 않을 때 동작을 제어하는 ON EMPTY / ON ERROR 절을 받습니다.
# validate a string is JSON
SELECT JSON_VALID('{"a":1}'); -- 1
SELECT JSON_VALID('{bad}'); -- 0
# pretty-print and minify
SELECT JSON_PRETTY(attrs) FROM products WHERE id=1;
SELECT JSON_STORAGE_SIZE(attrs) FROM products WHERE id=1; -- bytes
# JSON_SCHEMA_VALID (8.0.17+) enforces a schema with CHECK
CREATE TABLE events (
id INT PRIMARY KEY,
data JSON,
CHECK (JSON_SCHEMA_VALID(
'{"type":"object","required":["ts","type"],
"properties":{"ts":{"type":"string"},
"type":{"type":"string"}}}', data))
);
# JSON_TABLE with ERROR ON ERROR for strict validation
SELECT * FROM JSON_TABLE(j, '$' COLUMNS(v INT PATH '$.x')
DEFAULT NULL ON EMPTY ERROR ON ERROR) AS t;윈도우 함수
ROW_NUMBER()
ROW_NUMBER()는 파티션 내 각 행에 지정된 순서로 고유한 연속 정수를 부여합니다. 순위 매기기, 중복 제거, 그룹별 top-N의 정석입니다. RANK/DENSE_RANK와 달리 동률이 절대 없습니다. CTE를 통한 DELETE 패턴은 키당 한 행을 남기며 중복을 제거합니다(CTE에서 직접 삭제할 수 없으므로 삭제할 ID를 먼저 선택). 윈도우 함수는 MySQL 8.0+가 필요합니다.
# unique sequential number per row within a partition
SELECT username, country,
ROW_NUMBER() OVER (PARTITION BY country ORDER BY created_at) AS rn
FROM users;
# top 3 oldest users per country
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY country ORDER BY birth_date) AS rn
FROM users
)
SELECT * FROM ranked WHERE rn <= 3;
# deduplicate: keep the latest row per email
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id DESC) AS rn
FROM users
)
DELETE FROM users WHERE id IN (
SELECT id FROM ranked WHERE rn > 1
);RANK()와 DENSE_RANK()
RANK()는 동률에 같은 순위를 주고 다음 순위를 건너뜁니다(1,1,4,5). DENSE_RANK()는 동률에 같은 순위를 주되 갭이 없습니다(1,1,3,4). ROW_NUMBER()는 동률이 없습니다(1,2,3,4). 의도로 고르세요. 리더보드식 순위는 보통 RANK 또는 DENSE_RANK를 원합니다. DENSE_RANK가 그룹별 N번째 최고를 찾는 고전적 방법입니다(rank=2면 두 번째 고유 급여 반환). 셋 모두 같은 OVER() 문법을 공유합니다.
# all three ranking functions on the same data
SELECT name, score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
RANK() OVER (ORDER BY score DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY score DESC) AS drnk
FROM players;
# with scores 100,100,90,80 the functions return:
# rn: 1,2,3,4 (always sequential)
# rnk:1,2,4,5 (ties share a rank, next rank skips)
# drnk:1,2,3,4 (ties share a rank, no gap)
# find the 2nd-highest salary per department
WITH ranked AS (
SELECT dept, salary,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dr
FROM employees
)
SELECT DISTINCT dept, salary FROM ranked WHERE dr = 2;LAG()와 LEAD()
LAG()와 LEAD()는 self join 없이 현재 행 기준 다른 행을 엿봅니다. LAG는 뒤를, LEAD는 앞을 봅니다. 선택적 오프셋(기본 1)과 오프셋이 파티션 밖일 때의 기본값(기본 NULL)을 받습니다. 기간 간 차이, 이동 비교, 시퀀스의 연속/중단 감지에 완벽합니다. '이전'이 잘 정의되도록 항상 ORDER BY를 지정하세요.
# compare each row to the previous/next row
SELECT day, sales,
LAG(sales) OVER (ORDER BY day) AS prev_day,
sales - LAG(sales) OVER (ORDER BY day) AS diff,
LEAD(sales) OVER (ORDER BY day) AS next_day
FROM daily_sales;
# with an explicit offset and default value
SELECT day, sales,
LAG(sales, 7, 0) OVER (ORDER BY day) AS same_day_last_week
FROM daily_sales;
# year-over-year comparison
SELECT year, region, revenue,
LAG(revenue) OVER (PARTITION BY region ORDER BY year) AS prev_year,
revenue - LAG(revenue) OVER (PARTITION BY region ORDER BY year) AS yoy
FROM annual_revenue;윈도우 집계 (SUM / AVG OVER)
OVER()가 붙은 집계 함수는 행을 축소하는 대신 이동하는 행 윈도우 위에서 집계를 계산합니다. 프레임이 없으면 SUM OVER (ORDER BY x)는 누계이고, ROWS BETWEEN 6 PRECEDING AND CURRENT ROW이면 뒤 7행 이동 평균입니다. 빈 OVER()는 모든 행의 총합을 계산합니다. PARTITION BY는 그룹별로 윈도우를 리셋합니다. 프레임은 ROWS(물리) 또는 RANGE(값 기반) 경계를 씁니다.
# running total over time
SELECT day, sales,
SUM(sales) OVER (ORDER BY day) AS running_total
FROM daily_sales;
# moving average over a 7-day window
SELECT day, sales,
AVG(sales) OVER (
ORDER BY day
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS ma7
FROM daily_sales;
# each row's share of its group total
SELECT user_id, amount,
amount / SUM(amount) OVER (PARTITION BY user_id) AS share
FROM orders;
# grand total alongside detail rows
SELECT username, balance,
SUM(balance) OVER () AS total_balance
FROM users;FIRST_VALUE / LAST_VALUE / NTH_VALUE
FIRST_VALUE/NTH_VALUE는 프레임 내 특정 행의 값을 반환합니 다. LAST_VALUE는 흔한 함정입니다. 기본 프레임이 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW이므로 'last'는 현재 행이지 파티션의 마지막이 아닙니다. 진짜 마지막 값을 얻으려 프레임을 UNBOUNDED FOLLOWING으로 늘리세요. NTH_VALUE는 N번째 행이 나타나기 전까지 NULL입니다. 모호함을 없애려 명시적 프레임을 항상 지정하는 것이 좋습니다.
# first value in each partition (per ordering)
SELECT day, region, sales,
FIRST_VALUE(sales) OVER (PARTITION BY region ORDER BY day) AS first_sale
FROM daily_sales;
# LAST_VALUE needs an explicit frame to include current row!
SELECT day, region, sales,
LAST_VALUE(sales) OVER (
PARTITION BY region ORDER BY day
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS latest_so_far
FROM daily_sales;
# the Nth value within a partition
SELECT day, region, sales,
NTH_VALUE(sales, 3) OVER (
PARTITION BY region ORDER BY day
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS third
FROM daily_sales;NTILE과 PERCENT_RANK
NTILE(n)은 정렬된 행을 n개 버킷으로 최대한 균등히 나눕니다(나누어 떨어지지 않으면 앞 버킷이 추가 행을 받음). 보고서의 사분위/십분위에 편리합니다. PERCENT_RANK()는 0..1 분수로 상대 순위이며, CUME_DIST()는 누적 분포(현재 값 이하 행의 비율)입니다. 버킷화에는 NTILE, 백분위식 분석에는 PERCENT_RANK/CUME_DIST를 쓰세요.
# divide rows into N equal buckets (1..N)
SELECT name, salary,
NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;
-- quartile 1 = top 25%, quartile 4 = bottom 25%
# relative rank as a fraction 0..1
SELECT name, salary,
PERCENT_RANK() OVER (ORDER BY salary) AS pct,
CUME_DIST() OVER (ORDER BY salary) AS cum
FROM employees;
-- pct = (rank-1)/(N-1), 0 = lowest, 1 = highest
-- cum = (rows up to and including current)/N
# CUME_DIST: fraction of rows with a value <= current날짜와 시간 처리
현재 날짜/시간 함수
NOW()/CURRENT_TIMESTAMP는 문장 내내 같은 값을 반환합니다(일관). SYSDATE()는 호출 시점의 실제 시각을 반영하 며 피하세요. 비결정적이고 복제를 깨뜨립니다. 타임스탬프는 UTC로 저장하고(UTC_TIMESTAMP) 표시할 때 현지 시간으로 변환하세요. NOW(6)는 마이크로초 정밀도입니다. UNIX_TIMESTAMP는 에포크 초로/에서 변환하며 에포크 시간을 쓰는 언어와의 상호운용에 편리합니다.
# current timestamp (date + time) in one call
SELECT NOW(), SYSDATE(), CURRENT_TIMESTAMP, LOCALTIME;
# current date / time separately
SELECT CURDATE(), CURRENT_DATE; -- '2024-07-02'
SELECT CURTIME(), CURRENT_TIME; -- '14:30:00'
# UTC equivalents (store UTC, display local)
SELECT UTC_TIMESTAMP(), UTC_DATE(), UTC_TIME();
# microsecond precision with (6)
SELECT NOW(6), CURRENT_TIMESTAMP(6);
# UNIX timestamp (seconds since epoch) and back
SELECT UNIX_TIMESTAMP(); -- 1719912600
SELECT FROM_UNIXTIME(1719912600); -- '2024-07-02 12:30:00'
SELECT FROM_UNIXTIME(1719912600, '%Y-%m-%d');DATE_FORMAT과 STR_TO_DATE
DATE_FORMAT은 지정자로 날짜/시간을 문자열로 서식화합니다. 소문자 %i(분)와 %s(초)에 주의하세요. 흔한 함정입니다. STR_TO_DATE는 그 역으로 문자열을 DATE/TIME/DATETIME으로 파싱합니다. 형식 설정에 의존하는 취약한 암시적 문자열→날짜 변환 대신 사용자 입력 파싱에는 항상 STR_TO_DATE를 쓰세요. 날짜는 문자열이 아니라 DATE/DATETIME 타입으로 저장하세요.
# format a date/time to a string
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- 2024-07-02 14:30:00
SELECT DATE_FORMAT(NOW(), '%d/%m/%Y'); -- 02/07/2024
SELECT DATE_FORMAT(NOW(), '%W, %M %e %Y'); -- Tuesday, July 2 2024
# parse a string into a date
SELECT STR_TO_DATE('02/07/2024', '%d/%m/%Y'); -- 2024-07-02
SELECT STR_TO_DATE('Jul 2, 2024 2:30 PM', '%b %e, %Y %h:%i %p');
# common specifiers:
# %Y 4-digit year %y 2-digit year
# %m month(01-12) %c month(1-12) %M month name %b abbreviated
# %d day(01-31) %e day(1-31) %j day of year
# %H hour(00-23) %h hour(01-12) %i minutes %s seconds
# %W weekday name %a abbreviated %p AM/PMDATE_ADD / DATE_SUB와 INTERVAL
INTERVAL과 함께 쓰는 DATE_ADD/DATE_SUB가 정석 날짜 산술이며 + INTERVAL / - INTERVAL 축약이 더 깔끔합니다. INTERVAL은 월/년 넘김을 올바르게 처리합니다(윤년의 1월 31일 + 1개월 = 2월 29일). LAST_DAY는 인자 월의 마지막 날을 반환하며 월말 로직에 편리합니다. YEAR_MONTH 같은 복합 단위는 '1-2'(1년 2개월)를 받습니다. 날짜에 문자열 산술을 쓰지 마세요. 경계 사례에서 깨집니다.
# add an interval to a date
SELECT DATE_ADD('2024-01-31', INTERVAL 1 MONTH); -- 2024-02-29
SELECT DATE_ADD('2024-01-31', INTERVAL 1 DAY); -- 2024-02-01
SELECT '2024-01-31' + INTERVAL 1 MONTH; -- shorthand
# subtract
SELECT DATE_SUB(NOW(), INTERVAL 7 DAY);
SELECT NOW() - INTERVAL 1 HOUR;
# interval units: MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR
# SECOND_MICROSECOND MINUTE_MICROSECOND ... YEAR_MONTH (compound)
# add to a datetime (keeps the time component)
SELECT DATE_ADD(NOW(), INTERVAL 30 MINUTE);
# last day of next month
SELECT LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH));DATEDIFF / TIMESTAMPDIFF
DATEDIFF는 온 일을 반환합니다(date1 - date2, 시간 무시). TIMESTAMPDIFF는 고른 단위의 차이를 반환하며(unit, from, to) 0 쪽으로 잘라냅니다. 따라서 TIMESTAMPDIFF(YEAR, ...)는 올해 생일이 지나갔을 때만 나이를 주고, 정확한 나이는 월/일이 아직 안 왔으면 1을 빼세요. 인자 순서를 주의하세요. DATEDIFF는 (a,b) = a-b이고 TIMESTAMPDIFF는 (unit, from, to) = to-from입니다.
# DATEDIFF: difference in DAYS (date1 - date2)
SELECT DATEDIFF('2024-12-31', '2024-01-01'); -- 365
SELECT DATEDIFF(NOW(), birth_date) AS days_alive FROM users;
# TIMESTAMPDIFF: difference in a chosen unit (date1, date2) -> date2 - date1
SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age;
SELECT TIMESTAMPDIFF(MONTH, '2024-01-15', '2024-07-02');
SELECT TIMESTAMPDIFF(MICROSECOND, '2024-07-02 09:00:00', NOW());
# units: MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR
# (TIMESTAMPDIFF argument order is (unit, from, to))
# TIME values: TIMEDIFF returns a TIME
SELECT TIMEDIFF('18:00:00', '09:30:00'); -- 08:30:00EXTRACT와 날짜 부분
EXTRACT(unit FROM date)는 단일 숫자 구성 요소를 반환하고, YEAR()/MONTH()/DAY() 등은 흔한 것의 축약이며, DAYNAME/MONTHNAME은 이름을 반환합니다. 월간 보고서를 만드는 표준 방법은 타임스탬프의 YEAR()/MONTH()로 그룹화하는 것입니다. DAYOFWEEK는 1=일요일..7=토요일이고 WEEKDAY는 0=월요일..6=일요일이니 관례에 맞는 것을 고르세요. WEEK()에는 주 시작 요일과 카운팅의 모드가 있습니다.
# pull out a single component
SELECT EXTRACT(YEAR FROM created_at) AS yr,
EXTRACT(MONTH FROM created_at) AS mon,
EXTRACT(DAY FROM created_at) AS dy
FROM orders;
# dedicated functions for each part
SELECT YEAR(created_at), MONTH(created_at), DAY(created_at),
HOUR(created_at), MINUTE(created_at), SECOND(created_at),
DAYOFWEEK(created_at), DAYOFYEAR(created_at),
WEEK(created_at), QUARTER(created_at);
# names instead of numbers
SELECT DAYNAME(created_at), MONTHNAME(created_at);
# group orders by month for a report
SELECT YEAR(created_at) AS yr, MONTH(created_at) AS mon,
COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
GROUP BY yr, mon
ORDER BY yr, mon;시간대 변환
세션 time_zone을 설정하면 TIMESTAMP 컬럼이 현지 시간으로 표시됩니다. 명명된 존(Asia/Shanghai)은 mysql_tzinfo_to_sql로 시스템 시간대 테이블을 로드해야 합니다. CONVERT_TZ는 리터럴 datetime을 존 간 변환합니다. 베스트 프랙티스: 모든 시간은 UTC로 저장하고(TIMESTAMP 또는 UTC의 DATETIME), 표시를 위해 각 세션의 time_zone을 설정하며, 필요하면 CONVERT_TZ로 변환하세요. 다른 지역 사용자 간 모호함을 피합니다.
# see the session time zone
SELECT @@session.time_zone, @@global.time_zone;
# set the session time zone (UTC offset or named, needs tz data loaded)
SET time_zone = '+08:00'; # Asia/Shanghai offset
SET time_zone = 'Asia/Shanghai'; # named zone (load tz tables first)
# convert a datetime between time zones
SELECT CONVERT_TZ('2024-07-02 09:00:00', '+00:00', '+08:00');
-- 2024-07-02 17:00:00
# named zones require the timezone tables to be loaded:
# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
# TIMESTAMP columns auto-convert to the session time zone on display;
# DATETIME columns do not -- they store the literal value
# store all times in UTC and convert on the way out
SELECT CONVERT_TZ(created_at, '+00:00', @@session.time_zone)
FROM orders;관련 MySQL 스니펫
Copy-paste ready code for common tasks.
SELECT with JOINs
Combine rows from multiple tables using JOINs.
Subqueries
Use scalar, IN, EXISTS, and derived-table subqueries.
Indexes
Create single, composite, unique, and fulltext indexes.
Transactions
Use BEGIN, COMMIT, ROLLBACK, and isolation levels.
Stored Procedures
Define reusable procedural routines with parameters.
Triggers
Run logic automatically on INSERT, UPDATE, or DELETE.
Views
Create virtual tables to simplify and secure queries.
Query Optimization
Diagnose and optimize slow queries with EXPLAIN and indexes.
Was this helpful?