Skip to content

MySQL Шпаргалка

Популярная открытая реляционная система управления базами данных.

01

С чего начать

Подключение к MySQL

Клиент mysql — стандартный CLI для MySQL. Всегда используйте -p (без пробела после), чтобы пароль запрашивался безопасно и не попадал в историю оболочки. -h задаёт хост, -P (заглавная) — порт. Команда \s выводит статус соединения. Флаг -e удобен для разовых запросов в скриптах.

mysql
# 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();

Управление базами данных

Используйте utf8mb4 (а не utf8), чтобы поддерживать полный Unicode, включая эмодзи: utf8 в MySQL — это 3-байтное подмножество, не вмещающее все символы. utf8mb4_unicode_ci — рекомендуемая коллация для корректной сортировки. IF EXISTS/IF NOT EXISTS предотвращают ошибки в скриптах. DROP DATABASE мгновенно уничтожает все таблицы и данные.

mysql
# 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.

mysql
# 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 показывает счётчики времени выполнения (соединения, uptime, пропускную способность), полезные для мониторинга.

mysql
# 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 выполняет запрос и выводит результат вертикально.

mysql
-- 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 (обычно 50–75% RAM). После правки перезапустите сервер. Проверяйте результат через mysql --help или SHOW VARIABLES.

mysql
# /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]>\_
02

DDL-операции

Создание таблицы

CREATE TABLE определяет колонки, типы, ограничения и опции таблицы. InnoDB — движок по умолчанию и рекомендуемый (поддерживает транзакции, блокировки на уровне строк, внешние ключи). BIGINT UNSIGNED для AUTO_INCREMENT защищает от переполнения. DECIMAL(p,s) точен для денег. ENUM проверяет значение по фиксированному списку. TIMESTAMP DEFAULT CURRENT_TIMESTAMP заполняется автоматически при вставке.

mysql
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

ALTER TABLE развивает схему без пересоздания данных. ADD COLUMN с AFTER ставит колонку в конкретную позицию (порядок колонок в MySQL косметический). MODIFY меняет тип/значение по умолчанию; RENAME COLUMN (8.0+) чище, чем CHANGE. Большие ALTER могут пересобирать и блокировать таблицу — для крупных таблиц используйте pt-online-schema-change или online DDL. RENAME TABLE атомарен.

mysql
# 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 (не откатывается, не вызывает триггеры). Отключайте foreign_key_checks, когда очищаете таблицы, на которые ссылаются другие.

mysql
# 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() возвращает id, сгенерированный последним INSERT в текущей сессии (на соединение, безопасно при параллельной работе). Пропуски появляются после удалений, откатов и вставок с явными значениями. Для сброса используйте ALTER TABLE ... AUTO_INCREMENT = n.

mysql
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 обнуляет их; RESTRICT/NO ACTION блокируют удаление. Явно именуйте ограничения для удобства управления. Внешние ключи требуют индексов на обеих колонках.

mysql
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.

mysql
# 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;
03

Типы данных

Числовые типы

Для денежных значений, где важна точность, используйте DECIMAL: FLOAT/DOUBLE приблизительны и накапливают ошибки округления. UNSIGNED удваивает положительный диапазон, но запрещает отрицательные значения. Ширина отображения (например, INT(11)) в MySQL 8.0 устарела и игнорируется — она никогда не ограничивала хранимый диапазон. Для AUTO_INCREMENT рекомендуется BIGINT UNSIGNED во избежание переполнения. SERIAL — удобное сокращение для суррогатного ключа.

mysql
# 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

Строковые и текстовые типы

VARCHAR почти всегда предпочтительнее CHAR (который дополняет пробелами), кроме коротких кодов фиксированной длины. В utf8mb4 каждый символ занимает до 4 байт: колонке VARCHAR(255) нужно до 1020 байт плюс длина, что важно для лимита ключа индекса (3072 байта в InnoDB). Семейства TEXT/BLOB хранят крупные данные вне страницы; они не могут иметь значение по умолчанию, а для их индексации нужна длина префикса.

mysql
# 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). CURRENT_TIMESTAMP работает как значение по умолчанию для обоих начиная с MySQL 5.6.5. Задавайте часовой пояс сессии: SET time_zone = '+08:00';.

mysql
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 range

Типы ENUM и SET

ENUM компактно (1–2 байта) хранит одно значение из фиксированного списка строк и в строгом режиме отклоняет неизвестные. SET хранит любую комбинацию до 64 членов как битовую маску. Оба делают изменения схемы дорогими (добавление или переупорядочивание членов требует ALTER TABLE) и неудобны для запросов — многие команды предпочитают справочную таблицу или VARCHAR с CHECK. Для запросов к SET помогает FIND_IN_SET.

mysql
# 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 в двоичном формате, проверяемом при записи, и обеспечивает быстрый доступ к членам. Он предпочтительнее хранения JSON в TEXT, потому что позволяет индексировать и запрашивать внутренние значения. Для сборки значений используйте JSON_OBJECT(), JSON_ARRAY() и JSON_MERGE_PATCH(). Если несколько ключей запрашиваются часто, извлеките их в генерируемые колонки и индексируйте их для лучшей производительности.

mysql
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 хранит произвольные двоичные данные (изображения, файлы), но неудобен для запросов и не может иметь значение по умолчанию. Для крупных файлов лучше хранить путь в БД, а файл — на диске или в объектном хранилище. Храните UUID как BINARY(16) (16 байт) вместо CHAR(36) (36 байт) ради эффективности индексов — в MySQL 8.0 есть UUID_TO_BIN()/BIN_TO_UUID() с опциональной перестановкой для дружественности к индексам.

mysql
# 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;
04

DML (INSERT / UPDATE / DELETE)

INSERT

Многострочный INSERT гораздо быстрее повторных одиночных вставок: он амортизирует разбор, сетевые обходы и обновление индексов. INSERT ... SELECT копирует данные между таблицами. Синтаксис SET специфичен для MySQL и удобен для одной строки. Всегда перечисляйте целевые колонки явно, чтобы оператор переживал изменения схемы. Для идемпотентных вставок используйте INSERT IGNORE или ON DUPLICATE KEY UPDATE.

mysql
# 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 MySQL обновляет существующую строку вместо ошибки. VALUES(col) ссылается на значение, которое было бы вставлено; в MySQL 8.0.19+ этот способ устарел в пользу псевдонимов строк (AS new). Это эффективнее отдельного SELECT с последующим INSERT/UPDATE, потому что операция атомарна. INSERT IGNORE вместо этого молча пропускает конфликты.

mysql
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 поддерживает многотабличный UPDATE с JOIN — удобно для денормализации вычисляемых значений. ORDER BY + LIMIT позволяет безопасно обновлять пачками, избегая долгих блокировок огромных таблиц. В режиме safe-updates клиент отказывается выполнять UPDATE/DELETE без ключа в WHERE — защита от случайных массовых изменений.

mysql
# 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.

mysql
# 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, но имеет побочные эффекты: срабатывают триггеры DELETE, затем INSERT (а не UPDATE), сбрасывается AUTO_INCREMENT, а не указанные колонки получают значения по умолчанию. В большинстве случаев предпочитайте INSERT ... ON DUPLICATE KEY UPDATE.

mysql
# 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 listed

LOAD DATA INFILE

LOAD DATA INFILE — самый быстрый способ массовой загрузки CSV, в 20 раз быстрее операторов INSERT, поскольку обходит разбор SQL. Сервер читает файлы из безопасного каталога (secure_file_priv); LOCAL заставляет клиент отправить файл, но по умолчанию отключён ради безопасности. Используйте захват колонок в @var и SET для преобразования значений при загрузке. SELECT ... INTO OUTFILE — симметричный экспорт.

mysql
# 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';
05

Запросы SELECT

Базовый SELECT

Избегайте SELECT * в продакшене: он передаёт лишние данные, ломается при добавлении или удалении колонок и мешает оптимизатору использовать покрывающие индексы. Перечисляйте нужные колонки. LIMIT с OFFSET реализует пагинацию, но медленен на больших смещениях (он всё равно сканирует пропущенные строки); keyset-пагинация (WHERE id > last_id LIMIT n) масштабируется гораздо лучше.

mysql
# 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. NULL в списке IN/NOT IN может дать неожиданный пустой результат — используйте NOT EXISTS или коррелированный подзапрос. BETWEEN включает границы. MySQL вычисляет AND раньше OR, поэтому расставляйте скобки в смешанных условиях. Для производительности ставьте самое селективное условие первым и держите индексированные колонки слева от оператора.

mysql
# 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 result

ORDER BY и LIMIT

ORDER BY сортирует результат; несколько ключей применяются слева направо. FIELD() позволяет задать собственный порядок по перечисленному списку. Для больших результатов ORDER BY ... LIMIT всё равно может сканировать и сортировать весь набор, если индекс не поддерживает порядок, — добавьте индекс на колонки ORDER BY. Паттерн с оконной функцией (ROW_NUMBER OVER PARTITION BY) — каноническое решение top-N по группе в MySQL 8.0+.

mysql
# 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) считает уникальные не-NULL значения. DISTINCT по сути является GROUP BY по всем выбранным колонкам. Чтобы убрать дубликаты, сохранив представительную строку (например, самую новую), используйте ROW_NUMBER() OVER (PARTITION BY ...). DISTINCT может быть дорогим — он строит временную таблицу, поэтому лучше обойтись индексом.

mysql
# 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. Для полнотекстового поиска используйте FULLTEXT-индекс с MATCH ... AGAINST.

mysql
# 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 для простых случаев.

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

JOIN

INNER JOIN

INNER JOIN возвращает строки, только когда есть совпадение в обеих таблицах: несовпавшие строки с любой стороны отбрасываются. JOIN — сокращение для INNER JOIN. USING (col) — краткая альтернатива ON a.col = b.col, когда имя колонки одинаково и она появляется в результате один раз. INNER JOIN — тип соединения по умолчанию и самый частый для связывания нормализованных таблиц.

mysql
# 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 (анти-join) находит левые строки без пары справа — часто понятнее и быстрее NOT IN, особенно с NULL. RIGHT JOIN симметричен; обычно таблицы переставляют и используют LEFT JOIN ради читаемости. NULL в ключе соединения никогда не совпадает.

mysql
# 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. Рекурсивный CTE — стандартный способ сгенерировать последовательность или таблицу измерений в MySQL 8.0+.

mysql
# 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.

mysql
# 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 ALL со стратегией дедупликации, так как неявный DISTINCT в UNION дорог. Если нужны только несовпавшие строки, используйте два анти-join.

mysql
# 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 branch

NATURAL JOIN и USING

NATURAL JOIN хрупок: он соединяет по всем колонкам с одинаковыми именами, поэтому добавление колонки может незаметно изменить результат; избегайте его в продакшене. USING (col) безопаснее и оставляет в выводе одну копию колонки соединения. STRAIGHT_JOIN принудительно задаёт порядок таблиц для оптимизатора и служит ручным обходом плохих планов; убирайте его, когда исправлена настоящая причина. Для ясности предпочитайте явный ON.

mysql
# 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;
07

Агрегация и группировка

Агрегатные функции

Агрегатные функции сворачивают много строк в одну: COUNT, SUM, AVG, MIN, MAX. COUNT(*) считает строки; COUNT(col) — не-NULL значения col; COUNT(DISTINCT col) — уникальные. AVG игнорирует NULL (а не считает их нулями), что может искажать результат — используйте COALESCE(col,0), если NULL должен считаться нулём. Агрегаты без GROUP BY возвращают одну строку даже для пустой таблицы.

mysql
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() от метки времени.

mysql
# 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 BY

HAVING

HAVING фильтрует результат агрегации, а WHERE фильтрует входные строки до группировки, поэтому HAVING может ссылаться на агрегаты (SUM, COUNT), а WHERE нет. Строчные фильтры ставьте в WHERE ради эффективности: они уменьшают число строк до группировки. HAVING в MySQL принимает псевдонимы (HAVING users > 100). Запрос может использовать оба: WHERE сужает строки, GROUP BY раскладывает по корзинам, HAVING сужает корзины.

mysql
# 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 — эмулируйте через UNION ALL нескольких ROLLUP-запросов, если нужны все комбинации измерений.

mysql
# 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 байта); увеличьте его для длинных списков. В других СУБД это LISTAGG/STRING_AGG — GROUP_CONCAT является эквивалентом в MySQL.

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 против оконных агрегатов

ROLLUP уменьшает результат, добавляя строки подытогов, а оконный агрегат (OVER) сохраняет то же число строк и добавляет агрегат отдельной колонкой — так можно видеть детали и итоги одновременно. Используйте ROLLUP для сводных отчётов, а оконные функции — когда нужен контекст по каждой строке вместе с агрегатом. Накопительные итоги — классический случай SUM OVER (ORDER BY ...).

mysql
# 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;
08

Подзапросы и CTE

Скалярный подзапрос

Скалярный подзапрос возвращает одно значение (одна строка, одна колонка) и может использоваться везде, где допустимо выражение: в списке SELECT, WHERE, HAVING. Если он не возвращает строк, значение равно NULL. Оптимизатор часто превращает коррелированный скалярный подзапрос в соединение (материализация подзапроса). Для ясности и иногда для производительности лучше CTE или JOIN, когда один и тот же подзапрос используется многократно.

mysql
# 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 column

Подзапросы IN / ANY / ALL

IN совпадает с любым значением, которое вернул подзапрос; NOT IN — дополнение, но его отравляют NULL: если подзапрос вернул NULL, NOT IN не вернёт ни одной строки, поэтому всегда исключайте NULL или используйте NOT EXISTS. ALL истинно, когда сравнение выполняется для всех значений; ANY/SOME — когда хотя бы для одного. Оптимизатор часто переписывает их в semi-join.

mysql
# 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 в semi-join.

mysql
# 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) вместо встроенной производной таблицы.

mysql
# 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+.

mysql
# 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.

mysql
# 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);
09

Индексы

Создание индекса

Индекс — структура данных (B+Tree в InnoDB), ускоряющая поиск и сортировку ценой накладных расходов на запись и места на диске. Создавайте индексы на колонках из WHERE, JOIN и ORDER BY. UNIQUE-индексы дополнительно обеспечивают ограничение уникальности. Префиксные индексы на длинных текстовых колонках экономят место, но не годятся для покрывающих сканирований и ORDER BY. Каждый первичный ключ в InnoDB кластерный: вторичные индексы хранят значение PK как указатель на строку.

mysql
# 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'). Не индексируйте лишнее — каждый индекс замедляет запись.

mysql
# 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

Полнотекстовый индекс

FULLTEXT-индексы включают естественно-языковой и булев поиск по тексту, гораздо лучше LIKE '%word%'. MATCH ... AGAINST возвращает оценку релевантности. Булев режим поддерживает операторы: +обязательно, -исключить, *усечение, >усилить, ~ослабить. Требует InnoDB (5.6+) или MyISAM и минимальной длины слова (ft_min_word_len / innodb_ft_min_token_size, по умолчанию 3). Для серьёзного поиска в масштабе рассмотрите отдельный движок вроде Elasticsearch.

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

Пространственный индекс

Пространственные (R-tree) индексы ускоряют географические запросы по колонкам GEOMETRY/POINT/POLYGON. MySQL 8.0 стандартизировал SRID 4326 (координаты GPS WGS 84) и добавил ST_Distance_Sphere для расстояний в метрах. Колонка должна быть NOT NULL. Для фильтрации используйте ST_Within/ST_Contains/ST_Distance. Всегда задавайте координаты как (долгота, широта), чтобы соответствовать соглашению X/Y. Пространственные индексы делают запросы по радиусу значительно быстрее.

mysql
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+) позволяет избежать удаления и пересоздания. OPTIMIZE TABLE пересобирает таблицу, чтобы вернуть фрагментированное место и обновить статистику индексов, — это блокирующая операция, планируйте её на низкую нагрузку. Устаревшая статистика вводит оптимизатор в заблуждение; после больших загрузок запускайте ANALYZE TABLE.

mysql
# 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 и влияют только на помеченный оператор. Используйте подсказки умеренно — это пластырь при плохом распределении данных или устаревшей статистике.

mysql
# 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]';
10

Представления

Создание представления

Представление — сохранённый запрос, ведущий себя как виртуальная таблица: он упрощает сложные запросы, абстрагирует изменения схемы и управляет видимостью строк и колонок. CREATE OR REPLACE обновляет определение за один шаг. Представления не хранят данные (запрос выполняется каждый раз, если представление не материализовано), поэтому не добавляют затрат на запись, но и не ускоряют чтение. При возможности представления вливаются в запрос.

mysql
# 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 передаются в базовую таблицу. По умолчанию UPDATE, выводящий строку за пределы WHERE представления, завершается успешно (строка просто исчезает из представления); WITH CHECK OPTION блокирует такие изменения, чтобы строки не могли покинуть область представления. Представления с JOIN, GROUP BY, DISTINCT или подзапросами обычно не обновляемы.

mysql
# 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 — только когда вы намеренно хотите ослабить фильтр базового представления.

mysql
# 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 раскрывает определение каждого представления, обновляемость и тип безопасности — полезно для аудита. Представление зависит от базовых таблиц; удаление базовой таблицы оставляет его недействительным до пересоздания.

mysql
# 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 выбрать.

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';
11

Хранимые процедуры

Создание и вызов процедуры

Хранимая процедура — именованный предкомпилированный блок SQL. Поскольку процедуры содержат точки с запятой, вы меняете DELIMITER, чтобы весь CREATE выполнился как один оператор, а затем возвращаете его обратно. CALL выполняет процедуру. Процедуры не возвращают значение напрямую, но могут возвращать наборы результатов и изменять параметры OUT. Они работают на сервере, сокращая сетевые обходы для многошаговой логики, но их сложнее версионировать и тестировать, чем код приложения.

mysql
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\G

Параметры IN / OUT / INOUT

Параметры бывают IN (вход только для чтения), OUT (записывается обратно вызывающему через переменную сессии) или INOUT (оба). SELECT ... INTO присваивает скалярный результат запроса переменной. В примере дебет и кредит обёрнуты в транзакцию с SELECT FOR UPDATE, чтобы заблокировать строки и предотвратить потерянные обновления. Используйте OUT-параметры для кодов статуса; наборы результатов возвращаются напрямую, если внутри процедуры выполняется SELECT.

mysql
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 копирует скаляр запроса в переменную.

mysql
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; курсоры нужны только при действительно последовательной логике.

mysql
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) делают обработчики самодокументируемыми.

mysql
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), если её результат зависит только от входных данных: это требуется для бинарного логирования и репликации функций. Процедурам флаг детерминизма не нужен. Используйте эти представления для аудита и инвентаризации процедур.

mysql
# 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';
12

Функции

Строковые функции

LENGTH считает байты, CHAR_LENGTH — символы (важно для многобайтного utf8mb4). SUBSTRING индексируется с 1; отрицательное начало отсчитывается с конца. CONCAT возвращает NULL, если любой аргумент NULL, — используйте CONCAT_WS, чтобы пропускать NULL. SUBSTRING_INDEX делит по разделителю и оставляет первые N частей (отрицательное N — с конца). У TRIM есть варианты BOTH/LEADING/TRAILING, и он может удалять любой символ, а не только пробелы.

mysql
# 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 округляет половину от нуля; TRUNCATE отбрасывает цифры без округления. MOD и % взаимозаменяемы. RAND() возвращает float в [0,1); ORDER BY RAND() — удобный, но дорогой способ случайной выборки строк, поскольку он сортирует весь набор; для больших таблиц используйте случайный диапазон PK или резервуарную выборку. FORMAT добавляет разделители тысяч и округляет до заданного числа знаков, возвращая строку.

mysql
# 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 возвращают части. DATE_ADD/SUB с INTERVAL — каноническая арифметика дат: корректно обрабатывает переходы месяцев и лет. DATEDIFF возвращает дни (date1 - date2); TIMEDIFF возвращает TIME. DATE_FORMAT и STR_TO_DATE используют спецификаторы %Y %m %d %H %i %s (обратите внимание: строчная i — минуты, s — секунды).

mysql
# 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 безопаснее всего для чистых вычислений.

mysql
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, если a не NULL, иначе b (только два аргумента); COALESCE обобщает до N аргументов. NULLIF(a,b) возвращает NULL, когда a=b, — удобно для защиты от деления на ноль (x / NULLIF(y,0)). ISNULL() возвращает 1/0, а не значение, — не путайте с IFNULL. CASE — самое гибкое и переносимое условное выражение, работающее в любом диалекте SQL.

mysql
# 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-формат.

mysql
# 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;
13

Триггеры

Триггеры BEFORE / AFTER

Триггер срабатывает автоматически на INSERT/UPDATE/DELETE — либо BEFORE (может менять значения NEW, проверять и прерывать), либо AFTER (для побочных эффектов вроде логирования). FOR EACH ROW означает, что тело выполняется для каждой затронутой строки: MySQL поддерживает только построчные триггеры. BEFORE удобны для нормализации и значений по умолчанию, AFTER — для каскадных некритичных действий. Триггеры работают в той же транзакции, что и вызвавший оператор.

mysql
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 и NEW в таблицу истории. При сравнении значений нужно учитывать NULL (OLD.email <> NEW.email даёт NULL, если любая сторона NULL), поэтому защищайтесь проверками IS NULL. AFTER DELETE использует OLD (при удалении NEW нет). Держите логику аудита лёгкой, так как она выполняется при каждом изменении строки. Помните, что триггеры срабатывают и при массовых UPDATE/DELETE, что может быть медленно.

mysql
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 — для побочных эффектов.

mysql
# 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
# 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 возвращает полное тело и definer. information_schema.triggers даёт структурированные метаданные для аудита. DROP TRIGGER IF EXISTS безопасен для скриптов. Триггеры привязаны к таблице: удаление таблицы удаляет и её триггеры. Следите за definer: как и процедуры, триггеры по умолчанию работают с привилегиями definer.

mysql
# 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
14

Транзакции и блокировки

Управление транзакциями

MySQL по умолчанию работает в режиме автокоммита: каждый оператор — отдельная транзакция. START TRANSACTION/BEGIN открывает явный блок, завершаемый COMMIT (сохранить) или ROLLBACK (отменить). Транзакции поддерживают только транзакционные движки (InnoDB); MyISAM — нет. Критично: DDL-операторы (CREATE/ALTER/DROP/TRUNCATE) неявно фиксируют текущую транзакцию и не откатываются, поэтому не смешивайте DDL с транзакционным DML в расчёте на атомарность.

mysql
# 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. Вложенные точки сохранения поддерживаются.

mysql
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 locking. READ COMMITTED популярен из-за меньшего числа блокировок, но допускает неповторяющееся чтение. SERIALIZABLE превращает все чтения в блокирующие. InnoDB использует MVCC, поэтому читатели не блокируют писателей на любом уровне, кроме SERIALIZABLE.

mysql
# 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.

mysql
# 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 transaction

SELECT ... FOR UPDATE / SHARE

SELECT ... FOR UPDATE берёт эксклюзивную блокировку строки, поэтому другие транзакции блокируются при попытке обновить или заблокировать ту же строку, — необходимо для последовательностей «чтение-изменение-запись» вроде переводов. FOR SHARE берёт разделяемую блокировку (другие могут читать, но не писать). Блокировки снимаются при COMMIT/ROLLBACK. В 8.0 добавлены NOWAIT (ошибка вместо ожидания) и SKIP LOCKED (пропускать заблокированные строки) — идеально для воркеров очередей задач, которые не должны блокировать друг друга.

mysql
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, чтобы логировать каждый. Предотвращайте дедлоки, блокируя строки в одном и том же порядке во всех транзакциях, делая транзакции короткими и индексируя блокируемые колонки (иначе блокировки расширяются до больших диапазонов). Всегда предусматривайте повтор транзакций, которые могут попасть в дедлок.

mysql
# 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
15

Управление пользователями

Создание пользователя и аутентификация

Учётная запись MySQL всегда имеет вид 'user'@'host': host определяет, откуда пользователь может подключаться; '%' — шаблон для любого хоста. MySQL 8.0 по умолчанию использует caching_sha2_password (безопаснее), который поддерживают не все старые клиенты — для них переключитесь на mysql_native_password. Предпочитайте узкие хосты (10.0.0.%) вместо '%'. CREATE USER предпочтительнее старого синтаксиса GRANT ... IDENTIFIED BY, удалённого в 8.0.

mysql
# 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 позволяет пользователю передавать свои привилегии другим (опасно). FLUSH PRIVILEGES нужен редко, поскольку GRANT обновляет таблицы в памяти; используйте его только после ручного редактирования таблиц mysql.

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.

mysql
# 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() показывает активные роли. Измените роль один раз — и все пользователи-члены получат изменение. Это гораздо чище, чем выдавать одинаковые привилегии каждому пользователю отдельно.

mysql
# 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 временно отключает учётную запись, не удаляя её, — удобно для приостановки доступа во время расследования.

mysql
# 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;

Просмотр привилегий и пользователей

mysql.user — системная таблица всех учётных записей; запрашивайте её для инвентаризации пользователей и флагов вроде account_locked и password_expired. SHOW GRANTS — более дружелюбное, расшифрованное представление привилегий одного пользователя. CURRENT_USER() — идентификатор, по которому проверяются привилегии (может отличаться от USER() из строки подключения), что важно при подключении через роль или прокси. Регулярно проверяйте эти данные на спящие и избыточно привилегированные учётные записи.

mysql
# 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;
16

Бэкап и восстановление

mysqldump

mysqldump — классический инструмент логического бэкапа: он выдаёт SQL-операторы, воссоздающие базу. --single-transaction даёт согласованный снимок InnoDB без блокировок, используя одну транзакцию REPEATABLE READ. --routines/--triggers/--events включают хранимые программы (иначе они пропускаются). Для больших баз предпочитайте Percona XtraBackup или MySQL Enterprise Backup (физические бэкапы), которые гораздо быстрее mysqldump.

mysql
# 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). Для ускорения восстановления отключите проверки внешних и уникальных ключей и autocommit на время загрузки, затем включите обратно — обычно дамп уже содержит эти защиты. Восстановление сжатого дампа через gunzip избавляет от распаковки на диск. Всегда тестируйте восстановление на staging-сервере.

mysql
# 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; существующий файл перезаписать нельзя. --batch --raw в клиенте mysql создаёт локальный файл с табуляцией без этих ограничений — идеально для cron-задач. Секции FIELDS/LINES управляют разделителями, кавычками и экранированием для совместимости с CSV.

mysql
# 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 для преобразования значений (например, разбора строки даты). Для очень больших загрузок отключайте ключи и внешние ключи и используйте одну транзакцию.

mysql
# 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.

mysql
# 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 для здоровья реплики.

mysql
# 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
17

Оптимизация производительности

EXPLAIN

EXPLAIN раскрывает план запроса: какой индекс используется, сколько строк оценено и нужны ли filesort или временная таблица. Колонка 'type' ранжирует методы доступа: const/eq_ref лучшие (поиск одной строки), ref/range используют индекс, index сканирует весь индекс, ALL сканирует всю таблицу (обычно плохо). 'Using index' означает покрывающее сканирование только по индексу; 'Using filesort'/'Using temporary' предупреждают о дорогих операциях, которые стоит оптимизировать.

mysql
# 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 на staging. Вместе с SHOW WARNINGS можно увидеть переписанный оптимизатором запрос (после вливания представлений и свёртки констант). Для запросов на запись используйте EXPLAIN на эквивалентном read-only запросе или смотрите performance_schema.

mysql
# 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 ограничивает read-only запросы, защищая сервер от неуправляемых SELECT. SET_VAR меняет переменную сессии только для одного оператора.

mysql
# 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 ...;

Лог медленных запросов

Лог медленных запросов фиксирует запросы дольше long_query_time (в секундах, допускаются доли). Включите log_queries_not_using_indexes, чтобы ловить полные сканирования даже быстрых запросов. mysqldumpslow агрегирует похожие запросы и ранжирует их по времени, числу и строкам, позволяя приоритизировать худших. pt-query-digest от Percona даёт гораздо более богатые отчёты (отпечатки, перцентили) — стандартный инструмент анализа slow-log. Регулярно ротируйте и очищайте лог.

mysql
# 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.log

SHOW PROFILE и performance_schema

SHOW PROFILE разбивает запрос на стадии (открытие таблиц, сортировка, отправка данных) — удобно, но устарело. Современный эквивалент — performance_schema, фреймворк инструментирования с низкими накладными расходами, включённый по умолчанию в 8.0. Запрашивайте events_statements_summary_by_digest, чтобы найти запросы, потребляющие больше всего суммарного времени по серверу, затем углубляйтесь в events_stages_history_long для деталей по стадиям. Так находят системные узкие места, а не только отдельные медленные запросы.

mysql
# 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;

Практика оптимизации индексов

Дизайн индексов выигрывает большинство битв за производительность. Соблюдайте правило левого префикса, избегайте ведущих шаблонов и не оборачивайте индексируемые колонки в функции (или используйте генерируемую колонку + индекс). Стремитесь к покрывающим индексам для горячих запросов. Держите условия WHERE sargable (пригодными для поиска по индексу): created_at >= x AND created_at < y лучше, чем DATE(created_at)=x. ANALYZE TABLE обновляет статистику кардинальности, на которую опирается оптимизатор.

mysql
# 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;
18

Операции с JSON

Колонка JSON и вставка

Тип JSON хранит JSON в двоичном проверенном формате. Вставляйте строковый литерал JSON или собирайте значения через JSON_OBJECT() (пары ключ/значение) и JSON_ARRAY(). Неверный JSON отклоняется при вставке. Поскольку JSON хранится как разобранное дерево, доступ к членам быстрый, но документ читается целиком — для часто запрашиваемых скалярных значений извлеките их в генерируемую колонку и индексируйте её, получив лучшее из двух миров.

mysql
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'.

mysql
# 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 обновляет или добавляет; INSERT только добавляет отсутствующее; REPLACE только обновляет существующее; REMOVE удаляет. JSON_MERGE_PATCH (RFC 7396) объединяет два документа и удаляет ключи, установленные в null, — стандартный способ применить частичный патч. JSON_MERGE_PRESERVE сохраняет существующие массивы и объекты вместо их замены.

mysql
# 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, разворачивайте в реляционную форму через JSON_TABLE для SQL-аналитики и индексируйте генерируемую колонку для горячих путей.

mysql
# 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 строит объект ключ->значение (более поздние ключи перезаписывают ранние дубликаты). Вместе с JSON_OBJECT можно собрать массив объектов для ответа API прямо в SQL — удобно для формирования вложенных payload без постобработки в приложении. NULL-входы пропускаются обеими функциями. Следите за размером результата: огромная группа даёт огромное значение JSON.

mysql
# 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, управляя поведением при отсутствующем или неверном пути.

mysql
# 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;
19

Оконные функции

ROW_NUMBER()

ROW_NUMBER() присваивает каждой строке уникальный последовательный номер внутри её раздела в заданном порядке — канонический способ ранжирования, дедупликации и выбора top-N по группе. В отличие от RANK/DENSE_RANK он никогда не даёт одинаковых номеров. Паттерн удаления через CTE убирает дубликаты, сохраняя по одной строке на ключ (напрямую удалять из CTE нельзя, поэтому сначала выбирают ID для удаления). Оконные функции требуют MySQL 8.0+.

mysql
# 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().

mysql
# 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() заглядывают в другую строку относительно текущей — LAG назад, LEAD вперёд — без self join. Они принимают необязательное смещение (по умолчанию 1) и значение по умолчанию, если смещение выходит за пределы раздела (по умолчанию NULL). Идеально для сравнений период к периоду, скользящих сравнений и обнаружения серий или разрывов в последовательностях. Всегда задавайте ORDER BY, чтобы «предыдущая» была определена однозначно.

mysql
# 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 (по значениям).

mysql
# 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, поэтому «последняя» означает текущую строку, а не последнюю в разделе, — расширьте рамку до UNBOUNDED FOLLOWING, чтобы получить настоящее последнее значение. NTH_VALUE возвращает NULL, пока не появится N-я строка. Явные рамки устраняют неоднозначность, и их стоит задавать всегда.

mysql
# 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 — для аналитики в стиле перцентилей.

mysql
# 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
20

Работа с датой и временем

Текущие дата и время

NOW()/CURRENT_TIMESTAMP возвращают одно и то же значение в пределах оператора (согласованно); SYSDATE() отражает реальное время каждого вызова (избегайте его: он недетерминирован и ломает репликацию). Храните метки времени в UTC (UTC_TIMESTAMP) и конвертируйте в местное время при отображении. NOW(6) даёт микросекундную точность. UNIX_TIMESTAMP конвертирует в эпоху и обратно — удобно для взаимодействия с языками, использующими epoch.

mysql
# 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, а не строки.

mysql
# 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/PM

DATE_ADD / DATE_SUB и INTERVAL

DATE_ADD/DATE_SUB с INTERVAL — каноническая арифметика дат; сокращение + INTERVAL / - INTERVAL чище. INTERVAL корректно обрабатывает переходы месяцев и лет (31 января + 1 месяц = 29 февраля в високосный год). LAST_DAY возвращает последний день месяца аргумента — удобно для логики конца месяца. Составные единицы вроде YEAR_MONTH принимают '1-2' (1 год 2 месяца). Никогда не считайте даты строковой арифметикой — она ломается на граничных случаях.

mysql
# 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) и усекает к нулю: TIMESTAMPDIFF(YEAR, ...) даст возраст, только если день рождения в этом году уже прошёл; для точного возраста вычтите 1, если месяц и день ещё не наступили. Следите за порядком аргументов: DATEDIFF это (a,b) = a-b; TIMESTAMPDIFF это (unit, from, to) = to-from.

mysql
# 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:00

EXTRACT и части даты

EXTRACT(unit FROM date) возвращает один числовой компонент; YEAR()/MONTH()/DAY() и т. п. — сокращения для частых случаев; DAYNAME/MONTHNAME возвращают имена. Группировка по YEAR()/MONTH() от метки времени — стандартный способ строить месячные отчёты. Учтите: DAYOFWEEK считает 1=воскресенье..7=суббота, а WEEKDAY — 0=понедельник..6=воскресенье; выбирайте под свою конвенцию. У WEEK() есть режимы для первого дня недели и подсчёта.

mysql
# 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 или DATETIME в UTC), задавать time_zone каждой сессии для отображения и при необходимости конвертировать через CONVERT_TZ. Это избавляет от неоднозначности между пользователями из разных регионов.

mysql
# 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;

Was this helpful?