Junior: питання на співбесіді з теми «Типи й обмеження»

NULL у SQL означає «невідоме значення». Будь-яке порівняння з невідомим дає теж невідоме - NULL, а не true чи false. А WHERE пропускає лише рядки, де умова true.

SELECT * FROM users WHERE deleted_at = NULL;   -- завжди порожньо
SELECT * FROM users WHERE deleted_at IS NULL;  -- правильно
SELECT * FROM users WHERE deleted_at IS NOT NULL;

Де ще NULL поводиться несподівано:

• NULL = NULL - теж NULL. Для «рівні, з урахуванням NULL» є IS NOT DISTINCT FROM у PostgreSQL і оператор <=> у MySQL.
• COUNT(*) рахує всі рядки, COUNT(column) - лише ті, де значення не NULL.
• SUM, AVG ігнорують NULL. AVG з трьох значень, одне з яких NULL, ділить на 2.
• Арифметика з NULL дає NULL: price * NULL. Підставити значення за замовчуванням допомагає COALESCE(discount, 0).
• WHERE status <> 'banned' не поверне рядки зі status IS NULL.

Порада: колонки, де значення обов'язкове, позначайте NOT NULL. Тоді питання «а що, як тут NULL» просто не виникає.

Докладніше в документації: Оператори порівняння

Первинний ключ (PRIMARY KEY) однозначно ідентифікує рядок: значення унікальне й не може бути NULL. Під нього база автоматично створює унікальний індекс.

Зовнішній ключ (FOREIGN KEY) гарантує, що значення в колонці посилається на існуючий рядок іншої таблиці.

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    total NUMERIC(10, 2) NOT NULL
);

Що дає зовнішній ключ:

• Неможливо створити замовлення для неіснуючого користувача - помилка буде одразу, а не через місяць у звіті.
• Видалення батьківського рядка керується явно: CASCADE (видалити й залежні), SET NULL, RESTRICT (заборонити, поки є залежні).

Чому інколи без FK: при шардингу чи в дуже навантажених системах цілісність перевіряють у коді. Але для звичайного застосунку обмеження в базі - найнадійніший захист: код може мати баги, кілька сервісів можуть писати в одну базу, а обмеження перевіряється завжди.

Нюанс MySQL: зовнішні ключі підтримує лише InnoDB. У PostgreSQL під FK індекс на колонці, що посилається, не створюється автоматично - його варто додати, інакше JOIN і каскадне видалення будуть повільними.

Докладніше в документації: Обмеження

UNSIGNED - лише невід'ємні значення, зате вдвічі більший верхній діапазон.

Як обирати:

• Первинні ключі - BIGINT UNSIGNED. INT закінчується на 2,1 мільярда (4,29 з UNSIGNED), і в таблицях подій, логів чи лічильників це трапляється раніше, ніж очікують. Змінити тип ключа на великій таблиці - довга й ризикована операція. Laravel $table->id() створює саме BIGINT UNSIGNED.
• Зовнішні ключі - того самого типу, що й ключ, на який вони посилаються, включно з UNSIGNED. Інакше обмеження не створиться.
• Прапорці й маленькі перелічення - TINYINT. BOOLEAN у MySQL - це синонім TINYINT(1).

Пастки:

• INT(11) - не обмеження розміру. Число в дужках - лише «ширина відображення» для старого ZEROFILL, на діапазон не впливає й застаріло з MySQL 8.0.17.
• Віднімання з UNSIGNED: SELECT a - b для UNSIGNED-колонок при від'ємному результаті дає помилку «out of range» (а в нестрогому режимі - величезне число). Для арифметики, де можливі від'ємні значення, - CAST(a AS SIGNED) - b.
• Гроші не в INT гривнях, а в копійках (BIGINT) чи DECIMAL.
• Переповнення: у строгому режимі (STRICT_TRANS_TABLES, за замовчуванням) вставка значення поза діапазоном - помилка; без нього значення тихо обрізається до межі.

Докладніше в документації: Цілочисельні типи

• CHAR(n) - фіксованої довжини: значення доповнюється пробілами до n символів при збереженні, а при читанні кінцеві пробіли прибираються.
• VARCHAR(n) - змінної довжини: зберігається рівно стільки символів, скільки записано, плюс 1-2 байти на довжину.

CREATE TABLE codes (
    country CHAR(2),        -- завжди 2 символи: 'UA', 'PL'
    email VARCHAR(255)      -- від 0 до 255 символів
);

Коли CHAR: значення завжди однакової довжини - коди країн і валют, хеші фіксованої довжини, коди статусів. Тоді немає зайвих байтів на довжину.

Коли VARCHAR: усе інше - імена, email, адреси, заголовки.

Нюанси:

• n - у символах, а не байтах. В utf8mb4 символ займає до 4 байтів, тож VARCHAR(255) - до 1020 байтів даних. Це важливо для обмежень розміру рядка таблиці й індексу.
• CHAR в utf8mb4 у InnoDB займає змінну кількість байтів, тож перевага «фіксованої довжини» для багатобайтових кодувань майже зникає.
• Кінцеві пробіли: CHAR втрачає їх при читанні; для VARCHAR вони зберігаються, але порівняння в collations з PAD SPACE їх ігнорують ('a' = 'a ' - істина). Collations utf8mb4_0900_* у MySQL 8 - NO PAD, і там пробіли значущі.
• Обмеження n: VARCHAR до 65 535 байтів, але всі колонки рядка разом не можуть перевищити 65 535 байтів - довгі тексти виносять у TEXT.

VARCHAR(255) за звичкою - нормально як верхня межа, але для полів з відомим обмеженням (телефон, код) варто ставити реальну довжину: це й документація, і захист від сміття. Валідація довжини все одно потрібна й у застосунку, щоб користувач отримав зрозуміле повідомлення.

Докладніше в документації: Типи CHAR і VARCHAR

Розміри типів TEXT:

• TINYTEXT - до 255 байтів;
• TEXT - до 64 КБ;
• MEDIUMTEXT - до 16 МБ;
• LONGTEXT - до 4 ГБ.

Ліміти - у байтах: в utf8mb4 TEXT вміщує від ~16 до 64 тисяч символів залежно від мови.

Відмінності від VARCHAR:

• Ліміт рядка таблиці. Усі колонки рядка разом - не більше 65 535 байтів, і VARCHAR враховується повністю. TEXT займає в цьому ліміті лише 9-12 байтів (вміст зберігається окремо), тож довгі тексти - лише TEXT.
• Індекси. Індекс на TEXT можливий лише за префіксом (INDEX (body(100))), повний унікальний індекс - ні. VARCHAR індексується цілком (в межах ліміту ключа 3072 байти).
• Значення за замовчуванням. Для TEXT - лише як вираз у дужках (з MySQL 8.0.13); VARCHAR приймає звичайний літерал.
• Зберігання. InnoDB з форматом рядка DYNAMIC виносить великі значення на окремі сторінки. Запит, що вибирає такі колонки (SELECT *), читає ці сторінки - звідси повільні списки.

Як обирати:

• Обмежений короткий текст (імена, заголовки, email, URL) - VARCHAR з розумною довжиною.
• Довгий текст без жорсткої межі (статті, коментарі, описи, сирий HTML) - TEXT / MEDIUMTEXT.
• Великі бінарні дані (файли) - у сховище (S3), а в базі - шлях; BLOB лише для невеликих бінарних значень.

Практичні наслідки:

• Не вибирайте TEXT-колонки в списках: select(['id', 'title']) замість SELECT *.
• Для пошуку по тексту LIKE '%...%' на TEXT - повний перегляд таблиці. Потрібен FULLTEXT-індекс або пошуковий рушій.
• У Laravel $table->string() - VARCHAR(255), $table->text() - TEXT, $table->longText() - LONGTEXT.

Докладніше в документації: Типи BLOB і TEXT

ENUM - колонка, що приймає одне значення з фіксованого списку. SET - кілька значень з фіксованого списку одночасно.

CREATE TABLE orders (
    status ENUM('new', 'paid', 'shipped', 'cancelled') NOT NULL DEFAULT 'new',
    flags SET('gift', 'urgent', 'fragile')
);

Всередині ENUM зберігається як номер значення в списку (1-2 байти) - компактно й з перевіркою допустимих значень.

Підводні камені ENUM:

• Сортування за номером, а не за абеткою. ORDER BY status упорядкує в порядку оголошення (new, paid, shipped, cancelled). Інколи це зручно, частіше - несподівано.
• Числа в контексті чисел. WHERE status = 1 порівнює з номером, а не з рядком '1'. Для ENUM('0', '1', '2') це джерело плутанини.
• Зміна списку - ALTER TABLE. Додати значення в кінець у MySQL 8 можна миттєво (лише метадані). Додати в середину, перейменувати чи видалити - перебудова таблиці.
• Нестрогий режим: недопустиме значення перетворюється на порожній рядок '' з номером 0 замість помилки. У строгому режимі (за замовчуванням у MySQL 8) - помилка.
• Перенесення між СУБД: це нестандартний тип MySQL.

SET - ще специфічніший: значення зберігаються як бітова маска (до 64 елементів), шукати за ними незручно (FIND_IN_SET), а індекси майже не допомагають. Майже завжди краще окрема таблиця зв'язку.

Альтернативи ENUM:

• VARCHAR + CHECK (MySQL 8.0.16+): status VARCHAR(20) CHECK (status IN ('new', 'paid', ...)) - зміна переліку не чіпає дані.
• Таблиця-довідник із зовнішнім ключем - коли значення змінюються без деплою чи мають атрибути (назва, колір, порядок).
• PHP enum у застосунку + рядок у базі - логіка значень живе в коді, а база зберігає просте значення.

$table->enum() у Laravel-міграціях на MySQL створює саме нативний ENUM.

Докладніше в документації: Тип ENUM