Что такое SQL режим STRICT_ALL_TABLES (strict mode) в MySQL?

Question

Что такое SQL режим STRICT_ALL_TABLES (strict mode) в MySQL?

Тип вопроса: Базы данных Вероятность: 24% Категория: PHP Разработчик

Ответ

В контексте баз данных, strict mode (строгий режим) в MySQL — это настройка сервера SQL, которая заставляет его более строго проверять входящие данные и отклонять недопустимые операции, вместо того чтобы молча их обрезать или подставлять значения по умолчанию.

Основные эффекты включения строгого режима (STRICT_ALL_TABLES или STRICT_TRANS_TABLES):

Ошибка при вставке некорректных данных: Если вы пытаетесь вставить строку, длина которой превышает объявленный VARCHAR(255), операция завершится ошибкой, а не обрежет строку.
Ошибка при вставке NULL в NOT NULL поле: Вместо подстановки значения по умолчанию (например, пустой строки или нуля) произойдёт ошибка.
Ошибка при неверных значениях даты: Некорректные даты (вроде '2023-02-30') вызовут ошибку, а не будут преобразованы в '0000-00-00'.
Ошибка деления на ноль: При обычных настройках результат 1/0 будет NULL. В строгом режиме (с включённой опцией ERROR_FOR_DIVISION_BY_ZERO) это вызовет ошибку.

Как проверить и установить:

-- Показать текущие SQL режимы
SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;

-- Установить строгий режим для текущей сессии (рекомендуемый набор)
SET SESSION sql_mode = 'STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- Установить глобально (требует привилегий SUPER)
SET GLOBAL sql_mode = 'STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

Практический пример:

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    age TINYINT UNSIGNED
);

-- Со строгим режимом ВКЛЮЧЕН:
INSERT INTO users (id, email, age) VALUES (1, NULL, 300);
-- Ошибка: Column 'email' cannot be null. И ошибка: Out of range value for column 'age'.

-- Со строгим режимом ВЫКЛЮЧЕН:
INSERT INTO users (id, email, age) VALUES (1, NULL, 300);
-- Предупреждения. В email попадёт пустая строка, в age — 255 (макс. для TINYINT UNSIGNED).

Зачем это нужно:

Целостность данных: Гарантирует, что в БД попадают только корректные данные, соответствующие схеме.
Раннее обнаружение ошибок: Проблемы выявляются на этапе записи, а не при последующем анализе "битых" данных.
Предсказуемость: Поведение СУБД становится более явным и соответствует ожиданиям разработчика.

Ответ 18+ 🔞

Э, слушай, давай про строгий режим в MySQL поговорим, а то народ иногда нихуя не понимает, что за зверь такой.

Представь себе, что твоя база данных — это твой дом. А строгий режим — это такой бдительный, блядовитый консьерж, который стоит на входе и проверяет всех входящих по полной программе. Без него — полный распиздяй, кто угодно может зайти: и с пустыми руками (NULL куда не надо), и с чемоданами, которые в дверь не влазят (данные длиннее поля), и вообще с просроченными паспортами (неправильные даты). База молча это всё проглатывает, обрезает, подсовывает дефолтные значения — и живёт с этим говном внутри. А потом ты сидишь и думаешь: "Ёпта, а откуда у меня в поле age значение 255 взялось? Человеку-то 300 лет!" Волнение ебать, удивление пиздец.

Так вот, строгий режим (STRICT_ALL_TABLES) этому консьержу даёт инструкцию: "Чувак, доверия ебать ноль. Видишь хоть малейшую хуйню — сразу в пизду, ошибку в лицо и не пущать". И это правильно, ядрёна вошь!

Что конкретно он делает, этот зануда:

Длинные данные. Пытаешься запихнуть текст на 500 символов в поле VARCHAR(255)? Раньше он молча обрезал до 255, и ты мог даже не заметить. Теперь — получи ошибку, сабака сука, иди чини свой запрос.
NULL куда нельзя. Объявил поле email как NOT NULL, а сам шлёшь туда NULL? Раньше MySQL мог подсунуть пустую строку. Теперь — ошибка, чувак. Сам дурак.
Бредовые даты. Дата '2023-02-30' — это что за пиздопроебина? Такого дня не существует! Раньше могло превратиться в '0000-00-00', теперь — будет тебе хиросима, операция отменится.
Деление на ноль. В обычной жизни 1 / 0 даст тебе NULL. Со строгим режимом и включённой опцией ERROR_FOR_DIVISION_BY_ZERO — получишь по ебалу ошибкой. Справедливо, ёб твою мать.

Как этого сторожа включить или посмотреть, не спит ли он:

-- Смотришь, какие у него сейчас правила в голове
SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;

-- Ставишь ему в уши самый правильный набор команд для сессии
SET SESSION sql_mode = 'STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- Или для всех на постоянку (нужны права админа)
SET GLOBAL sql_mode = 'STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

Пример из жизни, чтобы совсем понятно стало:

Создаём простую табличку:

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    age TINYINT UNSIGNED
);

Со строгим режимом (наш консьерж на посту):
```
INSERT INTO users (id, email, age) VALUES (1, NULL, 300);
```
Что будет? ДВЕ ОШИБКИ, Карл! Сразу две! Первая: "Ты чё, мудила, email не может быть NULL!". Вторая: "Возраст 300? Да ты что, охуел? TINYINT только до 255!". И НИЧЕГО не добавится. Чисто.
Без строгий режима (консьерж ушёл бухать):
```
INSERT INTO users (id, email, age) VALUES (1, NULL, 300);
```
Что будет? Тишина. Может, парочку предупреждений. А в таблице появится запись: (1, '', 255). Пустая строка вместо NULL и максимально возможное число вместо 300. Пиздец тихий. Потом будешь эту кашу разгребать.

Так зачем это всё, спросишь? Да затем, ёпта!

Чистота. В базе лежат только те данные, которые ты ожидаешь. Никакого скрытого мусора.
Ранний отлов. Ошибки всплывают сразу, в момент записи, а не через полгода, когда отчёты кривые пошли. Терпения ноль ебать — лучше сразу знать.
Предсказуемость. Ты точно знаешь, что система не будет за тебя "додумывать" и "исправлять". Она ведёт себя прямолинейно и честно. Либо данные правильные, либо — идите вы все нахуй с такими запросами.

Короче, включай этот режим всегда, если не хочешь потом охуевать от того, что натворили твои же скрипты. Это не паранойя, это здравый смысл.