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

Задача «найновіший рядок у кожній групі» - класика співбесід. Кілька способів:

1. Віконна функція - працює і в PostgreSQL, і в MySQL 8+:

SELECT *
FROM (
    SELECT o.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders o
) ranked
WHERE rn = 1;

2. DISTINCT ON - коротко, але лише PostgreSQL:

SELECT DISTINCT ON (user_id) *
FROM orders
ORDER BY user_id, created_at DESC;

3. LATERAL (PostgreSQL, MySQL 8.0.14+) - зручно, коли потрібні кілька останніх, а не одне:

SELECT u.id, last.*
FROM users u
CROSS JOIN LATERAL (
    SELECT * FROM orders o WHERE o.user_id = u.id ORDER BY created_at DESC LIMIT 3
) last;

Чого уникати: GROUP BY user_id разом із MAX(created_at) і рештою колонок у SELECT - MySQL у старому режимі поверне колонки з довільного рядка групи, а не з останнього.

Для швидкодії потрібен індекс (user_id, created_at DESC): з ним LATERAL бере кілька рядків з індексу, не сортуючи всю таблицю. У Laravel цю задачу розв'язує зв'язок latestOfMany().

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

• IN (підзапит) - перевіряє, чи значення є серед результатів підзапиту.
• EXISTS (підзапит) - перевіряє, чи підзапит повернув хоч один рядок. Значення не важливі, тож пишуть SELECT 1.

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

Для позитивних перевірок сучасні оптимізатори PostgreSQL і MySQL зазвичай перетворюють обидва варіанти на однаковий план (semi-join), тож різниця частіше в читабельності.

Головна пастка - NOT IN і NULL. Якщо підзапит повертає хоч один NULL, NOT IN не поверне жодного рядка:

SELECT * FROM users WHERE id NOT IN (SELECT manager_id FROM teams);
-- якщо в teams є рядок з manager_id = NULL - порожній результат

Причина в тризначній логіці: 5 NOT IN (1, NULL) - це 5 <> 1 AND 5 <> NULL, а 5 <> NULL дає NULL, не true. Рядок не проходить фільтр.

Тому для «немає відповідності» використовують NOT EXISTS або LEFT JOIN ... WHERE x.id IS NULL. Вони поводяться передбачувано з NULL і добре оптимізуються.

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

FLOAT і DOUBLE зберігають числа у двійковій формі з плаваючою комою. Більшість десяткових дробів так точно не записати: 0.1 + 0.2 дає 0.30000000000000004. На мільйонах операцій копійки «губляться», а сума в звіті не сходиться з сумою платежів.

Два правильні варіанти:

1. NUMERIC / DECIMAL - точний десятковий тип:

amount NUMERIC(12, 2) NOT NULL

2. Ціле число в найменших одиницях - копійках чи центах (BIGINT). Так роблять Stripe та більшість платіжних систем: 1 999 копійок замість 19.99 грн.

Що ще важливо:

• Валюта поруч із сумою. 100 без валюти нічого не означає. Окрема колонка currency CHAR(3) (ISO 4217).
• У PHP теж не float: суму тримають як int у копійках або як рядок і рахують через bcmath чи бібліотеки на кшталт brick/money.
• Округлення - явне й в одному місці. Розподіл 100 грн на три частини має дати 33.34 + 33.33 + 33.33, а не загубити копійку.
• Тип money у PostgreSQL не радять: його формат залежить від налаштувань локалі сервера.

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

Правило: зберігати момент часу в UTC (або в типі, що зберігає саме момент), а в часовий пояс користувача перетворювати лише під час показу.

PostgreSQL:

• timestamptz (timestamp with time zone) зберігає момент часу. Під час запису значення переводиться в UTC, під час читання - у пояс сесії (TimeZone). Назва вводить в оману: сам пояс не зберігається.
• timestamp (без поясу) - просто дата й час «на годиннику», без прив'язки до поясу. Годиться для «зустріч о 10:00 за місцевим часом», але не для моментів подій.

MySQL:

• TIMESTAMP перетворюється в UTC за поясом сесії й назад, але має межу 2038 року.
• DATETIME зберігає значення як є, без перетворень. Застосунок сам відповідає за те, щоб писати в UTC.

Типові проблеми:

• Сервер БД, PHP і застосунок мають різні пояси, і час «з'їжджає» на кілька годин. Явно налаштований пояс на кожному шарі прибирає цей клас багів.
• Перехід на літній час: «щодня о 02:30» за київським часом раз на рік не існує, а раз на рік буває двічі.
• Порівняння DATE(created_at) = '2026-10-04' у UTC і в місцевому часі дає різні множини рядків.

Для майбутніх подій (запис до лікаря через пів року) інколи зберігають місцевий час і назву поясу окремо: правила поясів можуть змінитися до того моменту.

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

Корельований підзапит посилається на колонки зовнішнього запиту, тож логічно виконується для кожного рядка зовнішнього запиту:

SELECT u.id, u.name,
       (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS orders_count,
       (SELECT MAX(created_at) FROM orders o WHERE o.user_id = u.id) AS last_order_at
FROM users u;

Звичайний (некорельований) підзапит не залежить від зовнішнього і виконується один раз: WHERE id IN (SELECT user_id FROM vip_list).

Чим корельовані підзапити можуть бути погані: на 100 000 користувачів - 200 000 виконань підзапитів. З індексом на orders.user_id кожне швидке, але сумарно це багато роботи. Без індексу - катастрофа.

Як переписують:

-- JOIN з агрегованою похідною таблицею: orders проходиться один раз
SELECT u.id, u.name,
       COALESCE(s.orders_count, 0) AS orders_count,
       s.last_order_at
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) AS orders_count, MAX(created_at) AS last_order_at
    FROM orders
    GROUP BY user_id
) s ON s.user_id = u.id;

Але не завжди варто переписувати:

• Оптимізатор MySQL сам перетворює багато підзапитів з IN / EXISTS на semi-join і виконує їх як з'єднання.
• Для сторінки з 20 рядків корельований підзапит виконається 20 разів - це дешевше, ніж агрегувати всю таблицю замовлень.
• Корельований EXISTS з індексом зупиняється на першому знайденому рядку - часто найефективніший спосіб перевірки.

Правило: дивитися EXPLAIN і час на реальних даних. Підзапит у SELECT для кожного рядка великої вибірки - підозрілий; EXISTS у WHERE з індексом - зазвичай у порядку.

У Laravel withCount('orders') генерує саме корельований підзапит - для пагінованих списків це нормально.

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

Похідна таблиця (derived table) - підзапит у FROM, результат якого використовується як тимчасова таблиця:

SELECT c.name, t.revenue
FROM categories c
JOIN (
    SELECT category_id, SUM(total) AS revenue
    FROM orders
    GROUP BY category_id
) t ON t.category_id = c.id;

Звичайна похідна таблиця не бачить таблиць зовнішнього запиту - вона обчислюється сама по собі.

LATERAL (MySQL 8.0.14+) дозволяє похідній таблиці посилатися на колонки таблиць, що стоять лівіше в FROM. Вона обчислюється для кожного рядка зовнішньої таблиці - як корельований підзапит, але може повертати кілька рядків і колонок.

Класична задача - «топ-3 замовлення кожного користувача»:

SELECT u.name, top.id, top.total
FROM users u
JOIN LATERAL (
    SELECT o.id, o.total
    FROM orders o
    WHERE o.user_id = u.id
    ORDER BY o.total DESC
    LIMIT 3
) AS top ON TRUE;

З індексом (user_id, total) кожен підзапит бере три рядки прямо з індексу.

Альтернатива - віконна функція:

SELECT * FROM (
    SELECT o.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) AS rn
    FROM orders o
) ranked
WHERE rn <= 3;

Вона нумерує всі замовлення, а потім відкидає зайві. На великій таблиці, коли користувачів у вибірці небагато, LATERAL з індексом зазвичай швидший; коли потрібні всі користувачі - віконна функція простіша.

Що варто знати:

• LEFT JOIN LATERAL ... ON TRUE - залишити користувачів без замовлень.
• Оптимізатор MySQL може «злити» просту похідну таблицю з зовнішнім запитом (derived merge) або матеріалізувати її. Поведінку видно в EXPLAIN (DERIVED).
• MySQL до 8.0.14 LATERAL не підтримує - там цю задачу розв'язували змінними чи корельованими підзапитами.

Докладніше в документації: LATERAL-похідні таблиці

MySQL підтримує багатотабличні UPDATE і DELETE з JOIN:

-- позначити замовлення заблокованих користувачів
UPDATE orders o
JOIN users u ON u.id = o.user_id
SET o.status = 'cancelled'
WHERE u.is_banned = 1 AND o.status = 'pending';

-- видалити товари з кошиків, яких більше немає в продажу
DELETE ci
FROM cart_items ci
JOIN products p ON p.id = ci.product_id
WHERE p.is_active = 0;

У DELETE перед FROM вказують, з якої таблиці видаляти (DELETE ci), - інакше можна видалити з обох.

Синтаксис відрізняється між СУБД:

-- PostgreSQL
UPDATE orders o SET status = 'cancelled'
FROM users u
WHERE u.id = o.user_id AND u.is_banned;

DELETE FROM cart_items ci USING products p
WHERE p.id = ci.product_id AND NOT p.is_active;

Переносимий варіант - підзапит: WHERE user_id IN (SELECT id FROM users WHERE is_banned = 1).

Обмеження MySQL: не можна змінювати таблицю й одночасно читати з неї в підзапиті:

DELETE FROM orders WHERE id IN (SELECT id FROM orders WHERE ...);
-- ERROR 1093: You can't specify target table 'orders' for update in FROM clause

Обхід - обгорнути підзапит у ще одну похідну таблицю (MySQL матеріалізує її) або переписати через JOIN.

Обережно з масовими змінами:

• Спершу виконати той самий запит як SELECT і перевірити кількість рядків.
• Великі оновлення - порціями з LIMIT (у багатотабличних UPDATE/DELETE LIMIT не дозволений - порції роблять за діапазонами id).
• Блокування: UPDATE ... JOIN блокує рядки, які читає, і в об'єднаній таблиці теж - під навантаженням можливі очікування й deadlock'и.

У Laravel: DB::table('orders')->join('users', ...)->where(...)->update([...]) генерує багатотабличний UPDATE для MySQL.

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

Знайти дублікати - групування за колонками, що мали б бути унікальними:

SELECT email, COUNT(*) AS copies, MIN(id) AS keep_id
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Побачити всі рядки-дублікати з порядковим номером - віконна функція:

SELECT *
FROM (
    SELECT u.*,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at, id) AS rn
    FROM users u
) t
WHERE rn > 1;    -- усе, крім першого запису кожного email

ORDER BY визначає, який запис залишити: найстаріший, найновіший, найповніший.

Видалити дублікати:

DELETE u
FROM users u
JOIN (
    SELECT id
    FROM (
        SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at, id) AS rn
        FROM users
    ) ranked
    WHERE rn > 1
) dup ON dup.id = u.id;

Подвійне вкладення обходить обмеження MySQL на читання з тієї ж таблиці, з якої видаляють.

Перш ніж видаляти:

• Злити пов'язані дані: замовлення, коментарі, підписки дублікатів перепризначити на запис, що залишається. Інакше вони або зникнуть каскадом, або залишаться «сиротами».
• Нормалізувати значення: Olia@Example.com і olia@example.com - теж дублікати, якщо порівнювати з урахуванням регістру й пробілів.
• Зробити бекап або перенести дублікати в окрему таблицю, а не видаляти одразу.

Головне - не допустити повторення: додати унікальний індекс після очищення (ALTER TABLE users ADD UNIQUE (email)). Без нього дублікати з'являться знову - через гонку двох одночасних реєстрацій чи імпорт.

Докладніше в документації: Використання віконних функцій

Поруч з UNION у SQL є ще дві операції над множинами рядків:

• INTERSECT - рядки, що є в обох результатах;
• EXCEPT - рядки з першого результату, яких немає в другому.

У MySQL вони з'явилися у версії 8.0.31 (у PostgreSQL є давно).

-- клієнти, що купували і в 2025, і в 2026 році
SELECT user_id FROM orders WHERE YEAR(created_at) = 2025
INTERSECT
SELECT user_id FROM orders WHERE YEAR(created_at) = 2026;

-- зареєстровані, але жодного разу не купували
SELECT id FROM users
EXCEPT
SELECT user_id FROM orders;

Як і UNION, обидві за замовчуванням прибирають дублікати; INTERSECT ALL і EXCEPT ALL - зберігають.

Чим замінювали раніше:

-- INTERSECT
SELECT DISTINCT o1.user_id
FROM orders o1
WHERE YEAR(o1.created_at) = 2025
  AND EXISTS (SELECT 1 FROM orders o2 WHERE o2.user_id = o1.user_id AND YEAR(o2.created_at) = 2026);

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

Чим EXCEPT кращий за NOT IN: порівняння в операціях над множинами вважає NULL рівними між собою, тож немає пастки NOT IN з NULL у підзапиті.

Коли брати що:

• INTERSECT/EXCEPT - коли порівнюються цілі рядки кількох колонок і важлива читабельність («є тут, але немає там»).
• EXISTS/NOT EXISTS - коли потрібні колонки лише з першої таблиці, а умова зв'язку складна; часто оптимізатор виконує їх ефективніше, особливо з індексами.

Пастка з YEAR(created_at): функція над колонкою не дає використати індекс на created_at. Для великих таблиць - діапазон: created_at >= '2025-01-01' AND created_at < '2026-01-01'.

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

WITH ROLLUP додає до результату групування рядки підсумків: для кожного рівня групування і загальний підсумок.

SELECT country, city, SUM(total) AS revenue
FROM orders
GROUP BY country, city WITH ROLLUP;

country | city   | revenue
UA      | Київ   | 120000
UA      | Львів  | 45000
UA      | NULL   | 165000     <- підсумок по країні
PL      | Krakow | 30000
PL      | NULL   | 30000
NULL    | NULL   | 195000     <- загальний підсумок

В одному запиті - і деталі, і проміжні суми, і загальна сума. Без ROLLUP довелося б об'єднувати кілька запитів через UNION ALL чи рахувати підсумки в застосунку.

Як відрізнити рядок підсумку від справжнього NULL: функція GROUPING() (MySQL 8.0.12+) повертає 1 для колонок, «згорнутих» у підсумок:

SELECT
    IF(GROUPING(country), 'Усі країни', country) AS country,
    IF(GROUPING(city), 'Усього', city) AS city,
    SUM(total) AS revenue
FROM orders
GROUP BY country, city WITH ROLLUP;

Без цього рядок з city = NULL (замовлення без міста) неможливо відрізнити від підсумку по країні.

Що варто знати:

• Порядок колонок у GROUP BY визначає ієрархію: country, city дає підсумки по країнах, але не по містах окремо від країн.
• ORDER BY з ROLLUP дозволений з MySQL 8.0.12; раніше підсумки йшли лише в природному порядку групування.
• PostgreSQL має ROLLUP, а також CUBE (підсумки за всіма комбінаціями) і GROUPING SETS (довільні набори групувань). MySQL підтримує лише ROLLUP.

Коли це доречно: звіти й вивантаження для бухгалтерії, де підсумки потрібні прямо в даних. Для веб-інтерфейсу підсумки часто зручніше рахувати окремим запитом чи в застосунку, щоб не змішувати рядки різного типу.

Докладніше в документації: Модифікатори GROUP BY

Історично utf8 у MySQL - це utf8mb3: до 3 байтів на символ. Справжній UTF-8 використовує до 4 байтів, і 4-байтові символи (емодзі, частина ієрогліфів, математичні символи) в utf8mb3 не вміщуються.

Що відбувається на практиці: користувач пише коментар з емодзі, і вставка падає з Incorrect string value: '\xF0\x9F\x98\x80', а в нестрогому режимі - текст обрізається на першому емодзі без помилки.

utf8mb4 - повний UTF-8. У MySQL 8 це кодування за замовчуванням (з collation utf8mb4_0900_ai_ci), а utf8mb3 застаріло.

Переведення наявних таблиць:

ALTER DATABASE app CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

ALTER TABLE comments CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

CONVERT TO перекодовує дані всіх текстових колонок - це перебудова таблиці: на великих таблицях довго, з блокуванням запису. Для продакшену - через gh-ost чи pt-online-schema-change.

Підводні камені переходу:

• Ліміт довжини ключа індексу. У старих версіях і форматах рядка (COMPACT) ключ обмежений 767 байтами: VARCHAR(255) в utf8mb4 - це 1020 байтів, і індекс не створюється. Звідси Schema::defaultStringLength(191) у старих Laravel-проєктах. З форматом DYNAMIC (за замовчуванням у MySQL 8) ліміт - 3072 байти, і проблеми немає.
• Зміна collation змінює порівняння: нечутливість до регістру й діакритики (ai_ci) може зробити «різні» значення однаковими - і унікальний індекс не створиться, поки дублікати не прибрати.
• Розмір: тексти, що містять 4-байтові символи, займуть більше; для кирилиці розмір не зміниться (2 байти на символ).
• З'єднання теж має бути utf8mb4: charset у конфігурації підключення ('charset' => 'utf8mb4' у Laravel) - інакше дані перекодуються в utf8mb3 ще по дорозі.

Правило для нових проєктів: лише utf8mb4 скрізь - сервер, база, таблиці, з'єднання.

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

CHECK - умова, яку має задовольняти кожен рядок. База перевіряє її при кожній вставці й оновленні.

CREATE TABLE products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    price DECIMAL(10, 2) NOT NULL,
    discount_price DECIMAL(10, 2),
    stock INT NOT NULL,
    CONSTRAINT price_positive CHECK (price > 0),
    CONSTRAINT stock_not_negative CHECK (stock >= 0),
    CONSTRAINT discount_below_price CHECK (discount_price IS NULL OR discount_price < price)
);

INSERT INTO products (price, stock) VALUES (-5, 10);
-- ERROR 3819: Check constraint 'price_positive' is violated.

Важлива історія версій: до MySQL 8.0.16 синтаксис CHECK приймався, але ігнорувався - обмеження не перевірялися зовсім. Код, написаний для старих версій з «перевірками», насправді нічого не перевіряв. З 8.0.16 обмеження працюють.

Що вміє CHECK:

• умови на значення однієї колонки і на кілька колонок того самого рядка;
• NOT ENFORCED - оголосити, але не перевіряти (для поступового впровадження).

Обмеження:

• Лише в межах рядка: не можна посилатися на інші рядки чи таблиці («сума залишків не більше ліміту складу») і використовувати недетерміновані функції (NOW(), RAND()) чи підзапити.
• Колонки з AUTO_INCREMENT у CHECK не беруть участі.
• Додавання до наявної таблиці перевіряє всі рядки: якщо хоч один порушує умову, ALTER TABLE не виконається - спершу виправити дані.

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

У Laravel-міграціях окремого методу для CHECK немає - додають сирим SQL через DB::statement('ALTER TABLE ... ADD CONSTRAINT ... CHECK (...)').

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

Згенерована колонка обчислюється з інших колонок того самого рядка за виразом.

CREATE TABLE order_items (
    price DECIMAL(10, 2) NOT NULL,
    quantity INT NOT NULL,
    total DECIMAL(12, 2) AS (price * quantity) STORED
);

ALTER TABLE users
    ADD COLUMN email_domain VARCHAR(255) AS (SUBSTRING_INDEX(email, '@', -1)) VIRTUAL,
    ADD INDEX users_email_domain_idx (email_domain);

Два види:

• VIRTUAL (за замовчуванням у MySQL) - не зберігається, обчислюється при читанні.
• STORED - обчислюється при записі й зберігається, як звичайна колонка.

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

Поле JSON:

ALTER TABLE products
    ADD COLUMN brand VARCHAR(100) AS (attributes ->> '$.brand') VIRTUAL,
    ADD INDEX products_brand_idx (brand);

SELECT * FROM products WHERE brand = 'Bosch';   -- використовує індекс

Вираз для пошуку без урахування регістру, дату з часу, нормалізоване значення.

Обмеження:

• Вираз - детермінований: без NOW(), RAND(), змінних, підзапитів і посилань на інші таблиці.
• Не можна записати значення напряму.
• Первинний ключ - лише на STORED.
• Зміна виразу STORED-колонки - перебудова таблиці.

Функціональні індекси (MySQL 8.0.13+) - коротший запис того самого: індекс за виразом без явної колонки.

CREATE INDEX users_lower_email_idx ON users ((LOWER(email)));

Під капотом MySQL створює приховану віртуальну колонку. Щоб індекс спрацював, вираз у запиті має збігатися з виразом в індексі.

У Laravel-міграціях: $table->string('brand')->virtualAs("attributes->>'$.brand'") і ->storedAs(...).

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

sql_mode визначає, наскільки суворо MySQL поводиться з некоректними даними. Ключовий прапорець - STRICT_TRANS_TABLES (строгий режим), увімкнений за замовчуванням з MySQL 5.7.

Без строгого режиму MySQL «виправляє» дані мовчки:

-- name VARCHAR(10), age TINYINT UNSIGNED, created_at DATE
INSERT INTO users (name, age, created_at) VALUES ('Олександра Іваненко', 300, '2026-02-30');
-- вставлено з попередженнями:
-- name = 'Олександра', age = 255, created_at = '0000-00-00'

• рядок обрізано до довжини колонки;
• число поза діапазоном замінено на межу;
• неіснуюча дата - на нульову;
• значення не того типу - на 0 чи порожній рядок.

Застосунок отримує «успіх», а в базі - зіпсовані дані, які виявляють через місяці.

Зі строгим режимом ті самі вставки - помилки, і застосунок дізнається про проблему одразу.

Інші важливі прапорці режиму за замовчуванням у MySQL 8:

• ONLY_FULL_GROUP_BY - заборона неоднозначних GROUP BY;
• NO_ZERO_DATE, NO_ZERO_IN_DATE - заборона «нульових» дат 0000-00-00;
• ERROR_FOR_DIVISION_BY_ZERO - ділення на нуль при записі - помилка, а не NULL;
• NO_ENGINE_SUBSTITUTION - помилка замість тихої заміни рушія таблиці.

Де режим вимикають (і не варто):

• 'strict' => false у config/database.php Laravel - встановлює м'який режим для з'єднання застосунку. Інколи так «лікують» помилки старого коду при оновленні MySQL - і повертають тихе псування даних.
• Старі дампи з 0000-00-00 не імпортуються в строгому режимі - їх треба виправити, а не вимикати режим глобально.

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

SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;
SHOW WARNINGS;   -- після операції, якщо режим м'який

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

Докладніше в документації: Режими SQL сервера

Автоматичні мітки часу - для TIMESTAMP і DATETIME:

CREATE TABLE posts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

• DEFAULT CURRENT_TIMESTAMP - значення при вставці, якщо колонку не передали.
• ON UPDATE CURRENT_TIMESTAMP - автоматичне оновлення при будь-якій зміні рядка.
• Дробові секунди - з точністю: DATETIME(6) ... DEFAULT CURRENT_TIMESTAMP(6).

Нюанси ON UPDATE:

• Спрацьовує, лише якщо значення якоїсь колонки справді змінилося. UPDATE posts SET title = title мітку не оновить.
• Спрацьовує й на технічні зміни (лічильник переглядів, службовий прапорець) - «оновлено» перестає означати «змінено автором». Тоді краще керувати міткою в застосунку.

Значення за замовчуванням виразом (MySQL 8.0.13+) - вираз у дужках:

CREATE TABLE api_tokens (
    id BINARY(16) NOT NULL DEFAULT (UUID_TO_BIN(UUID(), 1)) PRIMARY KEY,
    expires_at DATETIME NOT NULL DEFAULT (CURRENT_TIMESTAMP + INTERVAL 30 DAY),
    settings JSON NOT NULL DEFAULT (JSON_OBJECT()),
    notes TEXT DEFAULT ('')
);

До 8.0.13 дозволялися лише літерали й CURRENT_TIMESTAMP; TEXT, BLOB і JSON не могли мати значення за замовчуванням узагалі.

Обмеження виразів: без підзапитів, змінних, збережених функцій і посилань на AUTO_INCREMENT-колонку.

Laravel і мітки часу: Eloquent сам заповнює created_at / updated_at у застосунку (з часовим поясом застосунку) і не покладається на ON UPDATE. Але масові оновлення через Query Builder (DB::table()->update()) Eloquent не бачить - там updated_at не зміниться, якщо в базі немає ON UPDATE. $table->timestamp('updated_at')->useCurrentOnUpdate() додає його в міграції.

Часові пояси: CURRENT_TIMESTAMP обчислюється в поясі сесії MySQL. Якщо він не збігається з поясом застосунку, мітки, поставлені базою й застосунком, розійдуться на кілька годин.

Докладніше в документації: Ініціалізація TIMESTAMP і DATETIME