MySQL: питання на співбесіді рівня Junior

• INNER JOIN повертає лише ті рядки, для яких знайшлася пара в обох таблицях.
• LEFT JOIN повертає всі рядки лівої таблиці; де пари в правій немає, її колонки заповнюються NULL.

-- Лише користувачі, які мають замовлення
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id;

-- Усі користувачі; без замовлень - з NULL у o.total
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

Типова задача: «користувачі без жодного замовлення» - LEFT JOIN і фільтр на NULL з правого боку:

SELECT u.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

Пастка: умова на праву таблицю в WHERE (WHERE o.status = 'paid') відкидає рядки з NULL і непомітно перетворює LEFT JOIN на INNER JOIN. Якщо треба зберегти всіх користувачів, умову ставлять в ON: LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid'.

Ще є RIGHT JOIN (дзеркальний до LEFT, на практиці рідкісний) і FULL JOIN - усі рядки з обох боків.

Докладніше в документації: Табличні вирази: з'єднання

Різниця в моменті, коли вони спрацьовують:

• WHERE фільтрує рядки до групування. Агрегатів у ньому ще немає.
• HAVING фільтрує групи після GROUP BY і може використовувати агрегати.

SELECT user_id, COUNT(*) AS orders, SUM(total) AS spent
FROM orders
WHERE status = 'paid'          -- лише оплачені замовлення
GROUP BY user_id
HAVING SUM(total) > 10000;     -- лише ті, хто витратив понад 10 000

Порядок виконання запиту: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Звідси й правило: умову на звичайну колонку ставлять у WHERE, бо так база відкидає зайві рядки раніше й групує менше даних.

Типові помилки:

• WHERE COUNT(*) > 5 - помилка: на етапі WHERE груп ще немає.
• HAVING status = 'paid' без status у GROUP BY - помилка і в PostgreSQL, і в MySQL з режимом ONLY_FULL_GROUP_BY (він увімкнений за замовчуванням). А навіть там, де такий запит проходить, він фільтрує пізніше, ніж міг би.
• Посилання в WHERE на псевдонім з SELECT (WHERE spent > 100) не працює саме через порядок виконання.

Докладніше в документації: Агрегатні функції

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 і каскадне видалення будуть повільними.

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

Обидва об'єднують результати кількох SELECT в один набір, один під одним.

• UNION - прибирає дублікати: результат містить лише унікальні рядки.
• UNION ALL - залишає всі рядки як є, з повторами.

SELECT email FROM customers
UNION
SELECT email FROM newsletter_subscribers;      -- кожен email один раз

SELECT 'order' AS type, id, created_at FROM orders
UNION ALL
SELECT 'refund', id, created_at FROM refunds   -- усі події, повтори неможливі за змістом
ORDER BY created_at DESC;

Чому UNION ALL за замовчуванням краще: щоб прибрати дублікати, UNION змушений сортувати чи хешувати весь об'єднаний результат - на великих наборах це дорого (тимчасова таблиця, можливо, на диску). Якщо дублікатів бути не може (різні таблиці, різні типи подій) або вони потрібні, - UNION ALL.

Правила:

• Кількість колонок у всіх SELECT однакова; типи - сумісні.
• Імена колонок результату беруться з першого SELECT.
• ORDER BY і LIMIT в кінці стосуються всього результату. Щоб відсортувати окрему частину, її беруть у дужки з власним LIMIT.

Типові застосування: стрічка подій з кількох таблиць, пошук по кількох сутностях («знайти серед користувачів і компаній»), заміна складного OR на кілька простих запитів, кожен з яких використовує свій індекс:

SELECT * FROM users WHERE email = ?
UNION
SELECT * FROM users WHERE phone = ?;

У Laravel - ->union($query) і ->unionAll($query).

Докладніше в документації: UNION

У запиті з GROUP BY кожна колонка в SELECT має бути або в GROUP BY, або всередині агрегатної функції (COUNT, SUM, MAX...). Інакше незрозуміло, яке значення показати для групи з кількох рядків.

SELECT user_id, status, COUNT(*)
FROM orders
GROUP BY user_id;
-- ERROR 1055: Expression #2 of SELECT list is not in GROUP BY clause
-- and contains nonaggregated column 'orders.status'...

У користувача кілька замовлень з різними статусами - який із них показати?

Режим ONLY_FULL_GROUP_BY (увімкнений за замовчуванням з MySQL 5.7) забороняє такі запити. Старі версії MySQL виконували їх, повертаючи значення з довільного рядка групи - і код роками працював з випадковими даними. Після оновлення MySQL такі запити раптом починають падати.

Як виправити - вирішити, що насправді потрібно:

-- групувати й за статусом
SELECT user_id, status, COUNT(*) FROM orders GROUP BY user_id, status;

-- агрегат: останній статус за датою - вже інша задача (віконна функція)
SELECT user_id, MAX(created_at) AS last_order_at FROM orders GROUP BY user_id;

-- значення справді однакове для групи (валюта клієнта), а MySQL цього не знає
SELECT user_id, ANY_VALUE(currency), COUNT(*) FROM orders GROUP BY user_id;

Функціональна залежність: MySQL дозволяє колонки, однозначно визначені колонками GROUP BY. Якщо групують за первинним ключем users.id, можна вибирати users.name - вона залежить від ключа.

Чого не робити: вимикати режим ('strict' => false у config/database.php Laravel чи прибирати його з sql_mode), щоб «запрацювало». Так помилка ховається, а результат лишається недетермінованим. PostgreSQL такі запити не дозволяє взагалі.

Докладніше в документації: Обробка GROUP BY у MySQL

DISTINCT прибирає однакові рядки результату. GROUP BY збирає рядки в групи, щоб порахувати для кожної агрегати.

Без агрегатів вони дають однаковий результат, і MySQL часто виконує їх однаково:

SELECT DISTINCT city FROM customers;
SELECT city FROM customers GROUP BY city;

Різниця з'являється, коли потрібні агрегати:

SELECT city, COUNT(*) AS customers, MAX(created_at) AS last_signup
FROM customers
GROUP BY city;

DISTINCT порахувати нічого не вміє - лише прибрати повтори.

DISTINCT діє на весь рядок, а не на першу колонку:

SELECT DISTINCT city, name FROM customers;   -- унікальні пари (місто, ім'я)

Поширена помилка - очікувати «унікальні міста з будь-яким ім'ям».

COUNT(DISTINCT ...) - кількість унікальних значень:

SELECT COUNT(DISTINCT user_id) AS buyers FROM orders WHERE created_at >= '2026-10-01';

Запах у запитах - DISTINCT для «лікування» дублікатів після JOIN:

SELECT DISTINCT u.* FROM users u JOIN orders o ON o.user_id = u.id;

JOIN з таблицею «багато» розмножує рядки, а DISTINCT їх потім прибирає - база спершу робить зайву роботу, а потім ще й сортує результат. Правильно сформулювати, що потрібно: «користувачі, у яких є замовлення»:

SELECT u.* FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Швидкодія: обидва можуть використати індекс за колонками групування; без нього - тимчасова таблиця. У EXPLAIN це видно як Using temporary.

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

Upsert - вставити рядок, а якщо він уже є, - оновити. У MySQL «вже є» визначається порушенням унікального ключа (первинного чи UNIQUE).

INSERT INTO product_stats (product_id, views)
VALUES (42, 1)
ON DUPLICATE KEY UPDATE views = views + 1;

Перший виклик вставить рядок, наступні - збільшать лічильник. Атомарно, без гонки «перевірити - вставити».

Посилання на значення, яке намагалися вставити - через псевдонім рядка (MySQL 8.0.19+):

INSERT INTO prices (sku, price, updated_at)
VALUES ('A1', 199.00, NOW()), ('B2', 349.00, NOW()) AS new
ON DUPLICATE KEY UPDATE price = new.price, updated_at = new.updated_at;

Стара функція VALUES(price) в UPDATE-частині працює, але застаріла.

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

• Потрібен унікальний ключ саме на тих колонках, за якими визначається «той самий» рядок. Без нього буде звичайна вставка дубліката.
• Кілька унікальних ключів: якщо рядок конфліктує одразу з двома різними рядками за різними ключами, оновиться лише один - результат несподіваний. Upsert краще робити по таблиці з одним унікальним ключем, крім первинного.
• Автоінкремент «з'їдається»: невдала вставка часто все одно забирає значення AUTO_INCREMENT - звідси дірки в ID.
• Кількість змінених рядків: 1 - вставлено, 2 - оновлено, 0 - значення ті самі.
• Блокування: при конфлікті InnoDB бере блокування на запис індексу - при великій конкуренції можливі deadlock'и, і їх варто повторювати.

У Laravel: Model::upsert($rows, uniqueBy: ['sku'], update: ['price', 'updated_at']) генерує саме цей запит для MySQL (і ON CONFLICT для PostgreSQL).

Докладніше в документації: INSERT ... ON DUPLICATE KEY UPDATE

Обидва вирішують «вставити або замінити», але 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 - лише для таблиць без залежностей, де повна заміна рядка - саме те, що потрібно (кеш-таблиці, денормалізовані знімки).

Докладніше в документації: REPLACE

Таблиця в SQL - це множина рядків без визначеного порядку. Якщо в запиті немає ORDER BY, база повертає рядки в тому порядку, в якому їй зручно: за індексом, яким скористалася, за фізичним розташуванням, за порядком паралельного виконання.

SELECT * FROM products LIMIT 10;

Сьогодні це «перші 10 вставлених», а завтра - після оновлення MySQL, нового індексу, OPTIMIZE TABLE чи іншого плану запиту - інші 10. Код, що на це покладався, ламається без жодних змін.

Ще підступніше - пагінація з неповним ORDER BY:

SELECT * FROM products ORDER BY created_at DESC LIMIT 20 OFFSET 20;

Якщо багато товарів мають однаковий created_at (масовий імпорт), порядок серед них не визначений. Одні товари з'являтимуться на двох сторінках, інші - на жодній.

Правило: ORDER BY має задавати однозначний порядок - додайте унікальну колонку останньою:

SELECT * FROM products ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 20;

Коли порядок справді не важливий: «будь-які 10 записів для перевірки», EXISTS, вибірка для обробки порціями, де кожен рядок однаково підходить.

Швидкодія LIMIT з ORDER BY: якщо є індекс, що віддає рядки в потрібному порядку, MySQL читає лише перші N і зупиняється. Без індексу - сортує всі рядки (Using filesort у EXPLAIN), хоча й оптимізує сортування для невеликого LIMIT.

OFFSET на далеких сторінках все одно читає й відкидає всі попередні рядки. Для глибокої пагінації - keyset: WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20.

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

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

MySQL зберігає дані через рушії зберігання (storage engines). Один сервер може мати таблиці на різних рушіях. Від MySQL 5.5 рушій за замовчуванням - InnoDB, а MyISAM лишився спадщиною.

Що дає InnoDB:

• Транзакції (ACID). COMMIT, ROLLBACK, відновлення після збою. У MyISAM транзакцій немає взагалі: DB::transaction() у Laravel на MyISAM-таблиці нічого не відкотить.
• Блокування рядків. Два запити, що оновлюють різні рядки однієї таблиці, не заважають один одному. MyISAM блокує всю таблицю на кожен запис - під навантаженням на запис усі запити стають у чергу.
• Узгоджене читання (MVCC). Читачі не чекають на записувачів: SELECT бачить знімок даних, а не блокується через чужий UPDATE.
• Зовнішні ключі. MyISAM синтаксис FOREIGN KEY приймає, але ігнорує.
• Стійкість до збоїв. Після аварійного вимкнення InnoDB відновлюється за журналом повтору (redo log). MyISAM-таблиці після збою часто доводиться «лагодити» (REPAIR TABLE) з ризиком втратити дані.
• Кластерний індекс. Рядки фізично впорядковані за первинним ключем, тож пошук за ним дуже дешевий.

Чим MyISAM колись приваблював і чому це вже неактуально:

• точний COUNT(*) без умов миттєво (лічильник у метаданих) - InnoDB рахує рядки, бо через MVCC різні транзакції бачать різну кількість;
• повнотекстовий пошук - InnoDB має його від 5.6;
• менший розмір на диску - різниця не варта втрати транзакцій.

Як перевірити й перевести:

SELECT table_name, engine FROM information_schema.tables
WHERE table_schema = DATABASE() AND engine <> 'InnoDB';

ALTER TABLE legacy_logs ENGINE = InnoDB;

ALTER ... ENGINE перебудовує таблицю повністю, тож на великих таблицях це варто планувати.

Інші рушії мають вузьке застосування: MEMORY для тимчасових даних (зникають при перезапуску), ARCHIVE для рідко читаних логів, CSV для обміну. Для таблиць застосунку відповідь майже завжди одна - InnoDB.

Докладніше в документації: Вступ до InnoDB