Null поля в mysql

вступ

Часто на форумах і навіть в підручниках пишуть про те, що краще не використовувати NULL поля в MySQL. У цих твердженнях бентежить той факт, що ніхто не спромагається пояснити, чому NULL - це зло. Ця замітка покликана розібратися, що таке NULL в MySQL і так чи страшний чорт, як його малюють.

Що таке NULL?

Один приклад. Таблиця сайти містить поле PR INT UNSIGNED NULL. PR - цілочисельне значення, яке може бути дорівнює нулю. Яке значення вставити в разі, коли PR ще не розрахований і, відповідно, невідомий? NULL.

Чим небезпечний NULL?

Порівняння NULL з будь-яким іншим значенням, навіть з родинним (в більшості мов програмування, зокрема, в PHP null, 0, false це одне і те ж, якщо не застосовувати суворого порівняння, яке включає порівняння типів) йому FALSE поверне NULL. Звідси випливає перша пастка.
Припустимо, у нас є таблиця:

Додамо в неї 2 записи:

INSERT INTO users (name, family) VALUES ( 'Андрій', 'Романов'), ( 'Іван', NULL);

У разі, якщо ви захочете об'єднати ім'я і прізвище, отримавши ПІБ одним полем, наприклад, таким запитом:

SELECT CONCAT (name, '', family) FROM `users`

MySQL не виправдає ваших очікувань. Ви отримаєте NULL замість Івана.

Трохи несподівано. правда?
На ділі ж нічого несподіваного немає, якщо ви пам'ятаєте, що будь-яка операція з NULL поверне NULL, крім спеціальних операцій, призначених для роботи з NULL: IS NULL, IS NOT NULL, IFNULL ()

Сортування по NULL

Всього лише хочу спростувати деякі фрази з українського мануала MySQL про те, що при сортуванні по стовпчику, що містить NULL значення, ці самі NULL значіння всега виявляються нагорі. Це не так .

SELECT name, family FROM `users` ORDER BY family ASC

SELECT name, family FROM `users` ORDER BY family DESC

Як бачимо, NULL вважається найменшим значенням, і порядок сортування на нього діє.

Угруповання і NULL

Все просто.
MySQL групує по NULL так само як і з будь-якого іншого полю.

Додамо нашій таблиці users стовпець score INT UNSIGNED NULL;

Підрахуємо скільки всього користувачів набрали ту чи іншу кількість очок, тобто згрупуємо вибірку по полю score
SELECT COUNT (*), score FROM `users` GROUP BY score

Як видно, MySQL згрупувала 2 рядки з score = NULL

Індекси і NULL

Звідкись ходить оману про те, що MySQL не використовує індекси, якщо стовпець може приймати значення NULL.
Це не так!

Проведемо кілька експериментів.

Виберемо всі записи, де score = NULL. Не забуваємо, що ми для цього повинні використовувати конструкцію IS NULL
EXPLAIN SELECT * FROM `users` WHERE score IS NULL

Виберемо всі записи, де кількість очок більше, наприклад, п'яти.
EXPLAIN SELECT * FROM `users` WHERE` score`> 5

Знайдемо користувача, у якого рівно 7 очок
EXPLAIN SELECT * FROM `users` WHERE` score` = 7

Можна зробити висновок: оману дійсно таким і виявилося.
Можна сміливо використовувати NULL-поля при створенні індексу і індекс буде працювати.

Окремо варто відзначити індекс типу UNIQUE.
Ми розглянули вище, що MySQL групує по NULL як за звичайним значенням і всі рядки з score = NULL були згруповані в один кортеж. З цього може послідувати помилковий висновок про те, що не вийде використовувати UNIQUE індекс разом з NULL полем. Однак це не так. Для UNIQUE індексу NULL є цілком собі унікальним значенням, і MySQL без будь-яких перешкод дозволить вставити кілька рядків з NULL значеннями поля, що представляє UNIQUE індекс.

Коли слід використовувати NULL?

Раніше я навів приклад таблиці сайтів, яка містить поле PR.
PR - це цілочисельне значення, яке може приймати значення 0, до того ж воно може бути в змозі «не вважав за». Як реалізувати зберігання такого властивості в таблиці?

Ті, хто боїться використовувати NULL, і створюють pr як INT UNSIGNED NOT NULL DEFAULT 0; змушені додавати ще одне поле в таблицю, наприклад, pr_calculated TINYINT (1) DEFAULT 0. По ньому вони перевіряють, пораховано чи PR чи ні. Відповідно, якщо pr = 0 і pr_calculated = 0. значить, що pr не дійсно дорівнює нулю, а всього тільки не пораховано. Чи не занадто багато умов?

Навіщо відмовлятися від того, що мова пропонує тобі «з коробки»?
Значення NULL для такого поля підходить ідеально. NULL - означає «немає значення», тобто воно ще не пораховано. Якщо значення дорівнює нулю, значить воно дійсно дорівнює нулю. Все просто.

Другий приклад слушної нагоди для використання NULL - це поле-нащадок.

Поле-нащадок вказує на id записи з іншої (або ж цієї ж таблиці). Прикладом такого поля може бути parent_id.

Ті, хто боїться використовувати NULL, створюють стовпець parent_id INT UNSIGNED NOT NULL DEFAULT 0. Якщо parent_id нікуди не вказує - воно містить значення, рівне нулю. При цьому, архітектор передбачає, що в таблиці не буде записів з значенням id = 0. Погоджуся з тим, що MySQL за замовчуванням, починає інкремент з одиниці, але це не означає, що в таблиці не може з'явитися записи з id = 0. запис з'явитися може, тому що БД цього не забороняє.

Коли ми створюємо поле parent_id як INT UNSIGNED NULL. то ми зводимо ймовірність того, що рядок в результаті якихось дій буде вказувати через parent_id на реально існуючу рядок, до нуля! В MySQL немає можливості створити запис з id = NULL. коли id є автоїнкрементальний полем.

Немає причин боятися створення NULL полів. Треба добре розуміти, що NULL в MySQL це не нуль і не false - це відсутність значення. Треба знати, як MySQL працює з NULL: нюанси є, але їх не багато. У MySQL немає проблем з індексацією NULL полів.

Ведущий программист, Катин Сергій

Хочете отримати безкоштовний SEO-аудит сайту?