С чего начать
Подключение к MySQL
Клиент mysql — стандартный CLI для MySQL. Всегда используйте -p (без пробела после), чтобы пароль запрашивался безопасно и не попадал в историю оболочки. -h задаёт хост, -P (заглавная) — порт. Команда \s выводит статус соединения. Флаг -e удобен для разовых запросов в скриптах.
# connect to local server (prompt for password)
mysql -u root -p
# connect to a remote host on a custom port
mysql -h 192.168.1.100 -P 3307 -u admin -p
# connect directly to a specific database
mysql -u root -p mydb
# non-interactive: run a query and exit
mysql -u root -p -e "SELECT VERSION();"
# show server status inside the client
\s
SELECT VERSION(), CURRENT_USER, DATABASE();Управление базами данных
Используйте utf8mb4 (а не utf8), чтобы поддерживать полный Unicode, включая эмодзи: utf8 в MySQL — это 3-байтное подмножество, не вмещающее все символы. utf8mb4_unicode_ci — рекомендуемая коллация для корректной сортировки. IF EXISTS/IF NOT EXISTS предотвращают ошибки в скриптах. DROP DATABASE мгновенно уничтожает все таблицы и данные.
# list all databases
SHOW DATABASES;
# create a database with charset and collation
CREATE DATABASE mydb
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
# switch to a database
USE mydb;
# show the current database
SELECT DATABASE();
# drop a database (irreversible)
DROP DATABASE IF EXISTS mydb;
# alter a database's charset
ALTER DATABASE mydb CHARACTER SET utf8mb4;Просмотр объектов
Команды SHOW — собственные инструменты интроспекции MySQL. DESCRIBE даёт краткий обзор колонок; SHOW CREATE TABLE показывает полный DDL, который можно переиспользовать. Добавляйте \G вместо ; для вертикального вывода — он гораздо читаемее для широких строк вроде SHOW CREATE TABLE.
# list tables in the current database
SHOW TABLES;
# show table structure (columns, types, keys)
DESCRIBE users;
# equivalent
SHOW COLUMNS FROM users;
# show create statement for a table
SHOW CREATE TABLE users\G
# list indexes on a table
SHOW INDEX FROM users;
# show stored procedures / functions
SHOW PROCEDURE STATUS WHERE Db = 'mydb';
SHOW FUNCTION STATUS WHERE Db = 'mydb';
# list triggers
SHOW TRIGGERS\GСтатус сервера и переменные
У переменных MySQL есть области SESSION (текущее соединение) и GLOBAL. SET GLOBAL меняет работающий сервер, но сбрасывается при перезапуске — сохраняйте настройки в my.cnf. @@var читает значения. SHOW STATUS показывает счётчики времени выполнения (соединения, uptime, пропускную способность), полезные для мониторинга.
# view a system variable
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'version%';
# view session vs global variable
SELECT @@session.sql_mode, @@global.sql_mode;
# set a variable for the current session
SET sql_mode = 'STRICT_TRANS_TABLES';
SET @@session.foreign_key_checks = 0;
# set globally (needs SUPER/privilege, persists until restart)
SET GLOBAL max_connections = 200;
# show server status counters
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Uptime';Комментарии и основы операторов
MySQL поддерживает три стиля комментариев: -- (требуется пробел после), блоки /* */ и # до конца строки. /*! ... */ — особый исполняемый комментарий: код внутри выполняется только в MySQL и игнорируется другими SQL-движками, что полезно для переносимых файлов схемы. \G выполняет запрос и выводит результат вертикально.
-- single-line comment (note the space after --)
SELECT 1; -- inline comment
/* multi-line
comment block */
SELECT 2;
# hash-style comment (MySQL-specific, to end of line)
SELECT 3;
# MySQL executable comments: run only on MySQL
SELECT 1 /*!50100 , 2 */; /* the part runs on MySQL >= 5.1 */
# statements end with semicolon; \G ends and prints vertically
SELECT * FROM users\GФайл конфигурации (my.cnf)
my.cnf (Linux) / my.ini (Windows) хранит постоянные настройки сервера и клиента по секциям: [mysqld] для сервера, [client]/[mysql] для клиента. innodb_buffer_pool_size — важнейший параметр настройки InnoDB (обычно 50–75% RAM). После правки перезапустите сервер. Проверяйте результат через mysql --help или SHOW VARIABLES.
# /etc/my.cnf or ~/.my.cnf (Linux/macOS)
# C:\ProgramData\MySQL\MySQL Server 8.0\my.ini (Windows)
[mysqld]
port = 3306
datadir = /var/lib/mysql
max_connections = 200
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
innodb_buffer_pool_size = 2G
slow_query_log = 1
long_query_time = 2
[client]
default-character-set = utf8mb4
[mysql]
prompt = \u@\h [\d]>\_DDL-операции
Создание таблицы
CREATE TABLE определяет колонки, типы, ограничения и опции таблицы. InnoDB — движок по умолчанию и рекомендуемый (поддерживает транзакции, блокировки на уровне строк, внешние ключи). BIGINT UNSIGNED для AUTO_INCREMENT защищает от переполнения. DECIMAL(p,s) точен для денег. ENUM проверяет значение по фиксированному списку. TIMESTAMP DEFAULT CURRENT_TIMESTAMP заполняется автоматически при вставке.
CREATE TABLE users (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL,
birth_date DATE NULL,
status ENUM('active','inactive','banned') NOT NULL DEFAULT 'active',
balance DECIMAL(10,2) NOT NULL DEFAULT 0.00,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;ALTER TABLE
ALTER TABLE развивает схему без пересоздания данных. ADD COLUMN с AFTER ставит колонку в конкретную позицию (порядок колонок в MySQL косметический). MODIFY меняет тип/значение по умолчанию; RENAME COLUMN (8.0+) чище, чем CHANGE. Большие ALTER могут пересобирать и блокировать таблицу — для крупных таблиц используйте pt-online-schema-change или online DDL. RENAME TABLE атомарен.
# add a column
ALTER TABLE users
ADD COLUMN phone VARCHAR(20) NULL AFTER email;
# add multiple columns
ALTER TABLE users
ADD COLUMN first_name VARCHAR(50) NULL,
ADD COLUMN last_name VARCHAR(50) NULL;
# modify a column type
ALTER TABLE users
MODIFY COLUMN phone VARCHAR(30) NOT NULL;
# rename a column (MySQL 8+ preserves data)
ALTER TABLE users
RENAME COLUMN phone TO phone_number;
# rename a table
RENAME TABLE users TO members;
ALTER TABLE users RENAME TO members;DROP и TRUNCATE
DROP TABLE удаляет таблицу целиком; TRUNCATE очищает её, сохраняя структуру и сбрасывая AUTO_INCREMENT к начальному значению. TRUNCATE быстрее DELETE, потому что пропускает построчное удаление и логирование, но это DDL (не откатывается, не вызывает триггеры). Отключайте foreign_key_checks, когда очищаете таблицы, на которые ссылаются другие.
# drop a table (removes structure and data)
DROP TABLE IF EXISTS old_logs;
# drop multiple tables
DROP TABLE IF EXISTS temp1, temp2;
# truncate: empty the table, keep structure, reset AUTO_INCREMENT
TRUNCATE TABLE session_data;
# truncate cannot be rolled back in some engines
# (InnoDB: TRUNCATE is DDL, implicitly commits)
SET foreign_key_checks = 0;
TRUNCATE TABLE child_table;
SET foreign_key_checks = 1;AUTO_INCREMENT
AUTO_INCREMENT генерирует последовательные номера для первичного ключа — в таблице может быть только один такой столбец. LAST_INSERT_ID() возвращает id, сгенерированный последним INSERT в текущей сессии (на соединение, безопасно при параллельной работе). Пропуски появляются после удалений, откатов и вставок с явными значениями. Для сброса используйте ALTER TABLE ... AUTO_INCREMENT = n.
CREATE TABLE orders (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
amount DECIMAL(10,2)
);
# set the next auto-increment value
ALTER TABLE orders AUTO_INCREMENT = 1000;
# insert without specifying id
INSERT INTO orders (amount) VALUES (99.50);
# get the last inserted id
SELECT LAST_INSERT_ID();
# show current AUTO_INCREMENT value
SHOW TABLE STATUS LIKE 'orders'\GОграничения таблиц
Ограничения обеспечивают целостность данных на уровне базы. InnoDB поддерживает первичные ключи, UNIQUE, NOT NULL, CHECK (проверяется с 8.0.16) и внешние ключи. FOREIGN KEY ... ON DELETE CASCADE удаляет дочерние строки при удалении родителя; SET NULL обнуляет их; RESTRICT/NO ACTION блокируют удаление. Явно именуйте ограничения для удобства управления. Внешние ключи требуют индексов на обеих колонках.
CREATE TABLE orders (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED NOT NULL,
total DECIMAL(10,2) NOT NULL,
status VARCHAR(20) NOT NULL,
# unique constraint
CONSTRAINT uq_order_no UNIQUE (user_id, status),
# check constraint (MySQL 8.0.16+ enforced)
CONSTRAINT chk_total CHECK (total >= 0),
# foreign key with cascading actions
CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE
ON UPDATE RESTRICT
) ENGINE=InnoDB;
# add a constraint later
ALTER TABLE orders
ADD CONSTRAINT chk_status CHECK (status IN ('paid','shipped','cancelled'));Временные таблицы
TEMPORARY-таблицы существуют только в текущей сессии и удаляются автоматически при её закрытии. Они могут затенять реальную таблицу с тем же именем — полезно для безопасного рефакторинга или промежуточных данных. Две сессии могут создать временные таблицы с одинаковым именем без конфликта. Они не видны другим соединениям и по умолчанию не пишутся в binlog.
# session-scoped, auto-dropped on disconnect
CREATE TEMPORARY TABLE tmp_active
SELECT id, username FROM users WHERE status = 'active';
# identical name shadowing a real table
CREATE TEMPORARY TABLE users
(id INT, name VARCHAR(50));
# only this session sees the temp table
SELECT * FROM users;
# explicit cleanup
DROP TEMPORARY TABLE IF EXISTS tmp_active;Типы данных
Числовые типы
Для денежных значений, где важна точность, используйте DECIMAL: FLOAT/DOUBLE приблизительны и накапливают ошибки округления. UNSIGNED удваивает положительный диапазон, но запрещает отрицательные значения. Ширина отображения (например, INT(11)) в MySQL 8.0 устарела и игнорируется — она никогда не ограничивала хранимый диапазон. Для AUTO_INCREMENT рекомендуется BIGINT UNSIGNED во избежание переполнения. SERIAL — удобное сокращение для суррогатного ключа.
# integers (with optional UNSIGNED)
TINYINT -- 1 byte, -128..127 or 0..255 (UNSIGNED)
SMALLINT -- 2 bytes
MEDIUMINT -- 3 bytes
INT -- 4 bytes
BIGINT -- 8 bytes (use for large AUTO_INCREMENT)
# fixed-point (exact, for money)
DECIMAL(10,2) -- 10 digits total, 2 after decimal
NUMERIC(8,4)
# floating-point (approximate)
FLOAT -- 4 bytes
DOUBLE -- 8 bytes
# other
BIT(8) -- bit-field
SERIAL -- alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUEСтроковые и текстовые типы
VARCHAR почти всегда предпочтительнее CHAR (который дополняет пробелами), кроме коротких кодов фиксированной длины. В utf8mb4 каждый символ занимает до 4 байт: колонке VARCHAR(255) нужно до 1020 байт плюс длина, что важно для лимита ключа индекса (3072 байта в InnoDB). Семейства TEXT/BLOB хранят крупные данные вне страницы; они не могут иметь значение по умолчанию, а для их индексации нужна длина префикса.
# fixed vs variable length
CHAR(10) -- fixed 10 chars, padded with spaces
VARCHAR(255) -- variable, up to 255 chars (stores actual length)
# large text
TINYTEXT -- up to 255 bytes
TEXT -- up to 64 KB
MEDIUMTEXT -- up to 16 MB
LONGTEXT -- up to 4 GB
# binary counterparts
BINARY(16), VARBINARY(255), BLOB, MEDIUMBLOB, LONGBLOB
# national character set
NATIONAL VARCHAR(100) -- same as VARCHAR with utf8mb4
# VARCHAR with charset counts characters, not bytes
VARCHAR(100) CHARACTER SET utf8mb4Типы даты и времени
DATETIME хранит буквальные дату и время без привязки к часовому поясу; TIMESTAMP хранит UTC и при выводе конвертирует в time_zone сессии, поэтому лучше подходит для приложений с несколькими часовыми поясами, но н а 32-битных сборках ограничен 2038 годом. Для микросекундной точности используйте DATETIME(6)/TIMESTAMP(6). CURRENT_TIMESTAMP работает как значение по умолчанию для обоих начиная с MySQL 5.6.5. Задавайте часовой пояс сессии: SET time_zone = '+08:00';.
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.
# 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(). Если несколько ключей запрашиваются часто, извлеките их в генерируемые колонки и индексируйте их для лучшей производительности.