Урок 04

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

Базовый синтаксис

CREATE TABLE users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Ключевое:

  • ENGINE=InnoDB — транзакции, FK.
  • CHARSET=utf8mb4 — полный UTF-8.
  • COLLATE=utf8mb4_unicode_ci — регистронезависимо.

Полезные опции

CREATE TABLE IF NOT EXISTS users (...) ENGINE=InnoDB;

Что даёт: не упадёт, если таблица уже есть.

Primary Key

Одна колонка:

id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)

Составной:

PRIMARY KEY (user_id, role_id)

Что даёт:

  • Уникальность.
  • NOT NULL.
  • Автоиндекс.
  • Цель для FK.

⚠️ В MySQL каждая InnoDB-таблица должна иметь PK. Если не указать — создастся скрытый.

UNIQUE KEY

UNIQUE KEY uq_users_email (email)

Или инлайн:

email VARCHAR(255) NOT NULL UNIQUE

Составной:

UNIQUE KEY uq_user_course (user_id, course_id)

Отличие от PRIMARY KEY: может быть NULL (если колонка nullable).

AUTO_INCREMENT

Только одна колонка на таблицу, только с индексом.

id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

Узнать последнее значение:

SELECT LAST_INSERT_ID();

Сбросить:

ALTER TABLE users AUTO_INCREMENT = 1;

⚠️ AUTO_INCREMENT может «прыгать» — при откате транзакции значение не вернётся.

FOREIGN KEY

CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE

Действия:

  • CASCADE — каскадом.
  • SET NULL — обнулить.
  • RESTRICT — запретить (по умолчанию).
  • NO ACTION — как RESTRICT.
  • SET DEFAULT — по умолчанию (в InnoDB не работает).

⚠️ В MySQL нужен индекс на FK-колонку. InnoDB создаёт автоматически.

Удалить FK:

ALTER TABLE orders DROP FOREIGN KEY fk_orders_user;

Отключить проверку (для bulk load):

SET FOREIGN_KEY_CHECKS = 0;
-- bulk insert
SET FOREIGN_KEY_CHECKS = 1;

⚠️ Только для миграций и SEED. В проде — никогда.

CHECK

MySQL 8.0.16+ поддерживает:

price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
status VARCHAR(20) NOT NULL CHECK (status IN ('draft', 'published'))

Named:

CONSTRAINT chk_products_price CHECK (price >= 0)

⚠️ В MariaDB CHECK работают с 10.2. В MySQL до 8.0.16 — игнорировались.

DEFAULT

created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
status VARCHAR(20) NOT NULL DEFAULT 'draft',
metadata JSON DEFAULT (JSON_OBJECT()),
is_active TINYINT(1) NOT NULL DEFAULT 1

Автообновление:

updated_at TIMESTAMP NOT NULL
    DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP

Что даёт: не нужен код для updated_at.

Индексы в CREATE TABLE

CREATE TABLE orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    KEY idx_orders_user_id (user_id),
    KEY idx_orders_status_created (status, created_at DESC),
    KEY idx_orders_created (created_at),

    CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

KEY = INDEX в MySQL.

Названия ключей

Правило:

  • PK — PRIMARY KEY.
  • UNIQUE — uq_<table>_<col>.
  • FK — fk_<table>_<ref>.
  • INDEX — idx_<table>_<col>.
  • CHECK — chk_<table>_<col>.

Что даёт: понятные ошибки и миграции.

ENGINE

ENGINE=InnoDB   -- всегда

Другие:

  • MyISAM — старый, без транзакций.
  • MEMORY — в RAM.
  • CSV — данные в CSV.

⚠️ MyISAM не даёт транзакций, FK, row locking. Только InnoDB.

Проверить:

SHOW TABLE STATUS FROM myapp LIKE 'users';

CHARSET и COLLATE

DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Коллации:

  • utf8mb4_unicode_ci — старый, но совместимый.
  • utf8mb4_0900_ai_ci — MySQL 8, быстрее и правильнее.
  • utf8mb4_general_ci — быстрый, но неточный.
  • utf8mb4_bin — побайтово, регистрозависимый.

Правило: MySQL 8 — utf8mb4_0900_ai_ci. Совместимость — utf8mb4_unicode_ci.

Row format

ROW_FORMAT=DYNAMIC

Что даёт: лучшее хранение VARCHAR / TEXT с компрессией.

Правило: в MySQL 8 — по умолчанию DYNAMIC.

ALTER TABLE

Добавить колонку:

ALTER TABLE users ADD COLUMN last_login_at TIMESTAMP NULL;
ALTER TABLE users ADD COLUMN age INT UNSIGNED NOT NULL DEFAULT 0 AFTER email;

Удалить:

ALTER TABLE users DROP COLUMN last_login_at;

Переименовать:

ALTER TABLE users RENAME COLUMN name TO full_name;
ALTER TABLE users RENAME TO accounts;

Изменить тип:

ALTER TABLE users MODIFY COLUMN age INT UNSIGNED NOT NULL;
ALTER TABLE users CHANGE age age TINYINT UNSIGNED NOT NULL;   -- с переименованием

Добавить / удалить индекс:

ALTER TABLE users ADD INDEX idx_users_created (created_at);
ALTER TABLE users DROP INDEX idx_users_created;

Добавить / удалить FK:

ALTER TABLE orders ADD CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE orders DROP FOREIGN KEY fk_orders_user;

⚠️ ALTER TABLE в MySQL пересоздаёт таблицу (копирует данные). На больших таблицах — часы. Есть pt-online-schema-change и gh-ost для онлайн-изменений.

DROP TABLE

DROP TABLE users;
DROP TABLE IF EXISTS users;
DROP TABLE users, orders;   -- несколько

Порядок важен, если есть FK. Или:

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE users, orders;
SET FOREIGN_KEY_CHECKS = 1;

TRUNCATE

TRUNCATE TABLE users;

Быстрее DELETE. Сбрасывает AUTO_INCREMENT.

⚠️ TRUNCATE нельзя откатить (DDL-операция).

TEMPORARY TABLE

CREATE TEMPORARY TABLE tmp_users (
    id BIGINT UNSIGNED,
    email VARCHAR(255)
) ENGINE=InnoDB;

Что даёт: видна только в текущей сессии, удалится при закрытии.

Когда: сложные отчёты, промежуточные вычисления.

CREATE TABLE LIKE

CREATE TABLE users_backup LIKE users;
INSERT INTO users_backup SELECT * FROM users;

Что даёт: копия структуры (индексы, колонки). Данные — отдельно.

Правило: для быстрого бэкапа перед массовым апдейтом.

Пример: интернет-магазин

CREATE TABLE users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE categories (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    parent_id BIGINT UNSIGNED NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_categories_name (name),
    KEY idx_categories_parent (parent_id),
    CONSTRAINT fk_categories_parent FOREIGN KEY (parent_id)
        REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE products (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    category_id BIGINT UNSIGNED NULL,
    sku VARCHAR(64) NOT NULL,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    UNIQUE KEY uq_products_sku (sku),
    KEY idx_products_category (category_id),
    CONSTRAINT fk_products_category FOREIGN KEY (category_id)
        REFERENCES categories(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    total DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_orders_user (user_id),
    KEY idx_orders_status_created (status, created_at),
    CONSTRAINT fk_orders_user FOREIGN KEY (user_id)
        REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE order_items (
    order_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    quantity INT UNSIGNED NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    KEY idx_order_items_product (product_id),
    CONSTRAINT fk_order_items_order FOREIGN KEY (order_id)
        REFERENCES orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_order_items_product FOREIGN KEY (product_id)
        REFERENCES products(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Просмотр структуры

SHOW TABLES;
SHOW TABLES FROM myapp;
DESCRIBE users;                 -- или DESC users
SHOW CREATE TABLE users;        -- полный DDL
SHOW INDEX FROM users;          -- индексы
SHOW TABLE STATUS LIKE 'users';

Из information_schema:

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'myapp' AND table_name = 'users';

Комментарии

CREATE TABLE users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Первичный ключ',
    email VARCHAR(255) NOT NULL COMMENT 'Email',
    PRIMARY KEY (id)
) ENGINE=InnoDB COMMENT='Пользователи системы';

Просмотр:

SHOW FULL COLUMNS FROM users;

Частые ошибки

1. Нет PRIMARY KEY:

InnoDB создаст скрытый, будет медленнее

Решение: всегда явный PK.

2. MyISAM вместо InnoDB:

Нет транзакций, FK, MVCC

Решение: ENGINE=InnoDB.

3. latin1 вместо utf8mb4:

Эмодзи и кириллица бьются

Решение: CHARSET=utf8mb4.

4. Длинный ALTER TABLE на большой таблице:

Копирует всю таблицу

Решение: pt-online-schema-change, gh-ost.

5. FK без индекса:

InnoDB создаст, но неоптимально

Решение: явно указать KEY.

6. VARCHAR(255) и utf8mb4:

255 × 4 = 1020 байт, может превысить лимит

Решение: знать про лимит 65 535.

7. TRUNCATE в проде:

Нельзя откатить

Решение: DELETE в транзакции.

8. Забыли COLLATE:

Разные коллации при JOIN — медленно

Решение: одинаковые на всех таблицах.

Практика

1. Создайте таблицы блога:

CREATE TABLE authors (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_authors_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE posts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    author_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    published_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_posts_author (author_id),
    KEY idx_posts_published (published_at),
    CONSTRAINT fk_posts_author FOREIGN KEY (author_id)
        REFERENCES authors(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE comments (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    post_id BIGINT UNSIGNED NOT NULL,
    author_name VARCHAR(100) NOT NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_comments_post (post_id),
    CONSTRAINT fk_comments_post FOREIGN KEY (post_id)
        REFERENCES posts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. Добавьте updated_at в posts:

ALTER TABLE posts
ADD COLUMN updated_at TIMESTAMP NOT NULL
    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

3. Проверьте структуру:

SHOW CREATE TABLE posts\G

4. Проверьте charset:

SELECT table_name, table_collation
FROM information_schema.tables
WHERE table_schema = 'myapp';

Итог

  • ENGINE=InnoDB — всегда.
  • CHARSET=utf8mb4 — всегда.
  • AUTO_INCREMENT — на PK.
  • FK + ON DELETE — осознанно.
  • Индексы на FK — обязательно.
  • ALTER TABLE — может быть тяжёлым.
  • TRUNCATE — не откатывается.
  • Naming: idx_, fk_, uq_, chk_.

Дальше — CRUD и запросы.