SQL: NULL, типи й обмеження
20 питань · ~20 хв · Версія v3.0
Увійдіть, щоб продовжити
Тризначна логіка NULL, вибір типів для грошей, дат, тексту й JSON, кодування й порівняння рядків, обмеження цілісності - питання для MySQL і PostgreSQL, від junior до senior.
- За спробу
- 20
- У пулі
- 70
- Проходжень
- 2
- Середній бал
- 48%
- Пройшли на 70%+
- 50%
Питання для підготовки
25 питань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» просто не виникає.
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.
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 '- істина). Collationsutf8mb4_0900_*у MySQL 8 -NO PAD, і там пробіли значущі. - Обмеження
n:VARCHARдо 65 535 байтів, але всі колонки рядка разом не можуть перевищити 65 535 байтів - довгі тексти виносять уTEXT.
VARCHAR(255) за звичкою - нормально як верхня межа, але для полів з відомим обмеженням (телефон, код) варто ставити реальну довжину: це й документація, і захист від сміття. Валідація довжини все одно потрібна й у застосунку, щоб користувач отримав зрозуміле повідомлення.
Обидва вирішують «вставити або замінити», але REPLACE робить це грубо: при конфлікті унікального ключа він видаляє старий рядок і вставляє новий.
REPLACE INTO settings (user_id, theme) VALUES (1, 'dark');
Наслідки, що роблять REPLACE небезпечним:
- Нове значення
AUTO_INCREMENT. Якщо ключ конфлікту - неid, рядок отримає новийid. Посилання на старийidз інших таблиць ламаються. - Каскадні видалення. Зовнішні ключі з
ON DELETE CASCADEвидалять залежні рядки - «оновлення» налаштувань може знищити пов'язані дані. - Тригери
DELETEспрацьовують, хоча логічно це оновлення. - Втрата колонок. Колонки, яких немає в
REPLACE, отримують значення за замовчуванням: усе, що було в старому рядку, зникає. - Два рядки замість одного при конфлікті за кількома ключами:
REPLACEвидалить усі рядки, з якими конфліктує.
INSERT ... ON DUPLICATE KEY UPDATE оновлює наявний рядок на місці: id той самий, інші колонки не змінюються, каскади не спрацьовують, змінюється рівно те, що вказано.
INSERT INTO settings (user_id, theme) VALUES (1, 'dark') AS new
ON DUPLICATE KEY UPDATE theme = new.theme;
Ще варіант - INSERT IGNORE: вставити, якщо немає, і нічого не робити, якщо є. Але IGNORE перетворює на попередження й інші помилки (обрізання значень, неправильні типи), тож ховає баги.
Правило: INSERT ... ON DUPLICATE KEY UPDATE за замовчуванням. REPLACE - лише для таблиць без залежностей, де повна заміна рядка - саме те, що потрібно (кеш-таблиці, денормалізовані знімки).
Первинний ключ (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 і каскадне видалення будуть повільними.
Спершу почитати
Прочитати - ще не значить знати
20 питань, по одному на екран, ~20 хв. Після завершення - розбір кожної помилки з посиланням на питання.