MySQL: разрушаем стереотипы

create table null_equals_zero(int_value     int,                               group_value   int                              ) engine = innodb;  insert into null_equals_zero      values (null, 1), (0, 2), (NULL, 3), (0, 4);  select   distinct int_value     from null_equals_zero group by group_value; 

Как вы понимаете данный запрос вернет нам уникальные значения первой колонки которых как мы знаем два: ноль и NULL

+-----------+ | int_value | +-----------+ |      NULL | +-----------+ 1 row in set (0.00 sec) 

Данный пример это лишь одна часть поведения NULL в спорных ситуациях, ибо хоть он и равен нулю, но так же легко можно доказать что NULL — больше единицы. Давайте рассмотрим две функции: least — которая возвращает минимальное значение из перечисленных аргументов, и elt — которая возвращает значение по индексу указанному первым аргументом. Думаю ни у кого, из тех кто читает этот пост, не возникает вопросов как именно они работают, но на всякий случай для чистоты эксперимента выполним 2 запроса:

select least(1, null) cmp_res; +---------+ | cmp_res | +---------+ |    NULL | +---------+ 1 row in set (0.00 sec)  select elt(null, 'Поле с индексом NULL') null_index_field; +------------------+ | null_index_field | +------------------+ | NULL             | +------------------+ 1 row in set (0.00 sec) 

Пока как мы видим все идет по плану, NULL несравним с одной стороны, и по индексу NULL нет элементов, но давайте попробуем узнать чему равна суперпозиция данных функций?

select elt(least(1, null), '1 < null') null_is_to_big; 

+----------------+ | null_is_to_big | +----------------+ | 1 < null       | +----------------+ 1 row in set (0.00 sec) 

Теперь займемся математикой, думаю за первый класс самое то. И так вопрос, какой знак имеет число 0. Не торопитесь с ответом, вы же уже поняли, что разработчики MySQL жуткие тролли. Давайте лучше проверим. И так. Создадим таблицу и вставим в нее два, близких к нулю значения — положительное и отрицательное.

create table signed_zero (float_value float);  insert into signed_zero(float_value)      values (-0.1), (0.1);  select group_concat(round(float_value) separator ' не равно ') signed_zero from signed_zero group by round(float_value); 

+----------------------+ | signed_zero          | +----------------------+ | -0 не равно 0        | +----------------------+ 1 row in set (0.00 sec) 

Ну пожалуй отвлечемся от математики и перейдем к невозможным объектам. Оказывается в MySQL есть объекты, которые нельзя создать (любителям кавычек посвящается). Давайте попробуем сделать таблицу с именем already_exists.
Начнем со справочника (что за таблица без внешнего ключа).

create table `dictionary_one` (`dict_id` int(10) primary key) engine = innodb;  create table `already_exists`(   `pk_id`             int(10) primary key,   `ref_dict_one_id`   int(10),   constraint `Already_exists_ibfk_1` foreign key(`ref_dict_one_id`) references `dictionary_one`(`dict_id`) ); 

Вроде все пока идет как надо. Теперь — добавим ещё одну колонку ссылающуюся на другую таблицу.

create table `dictionary_two` (`dict_id` int(10) primary key) engine = innodb;  alter table `already_exists` add column `ref_dict_two_id` int(10), add foreign key `Already_exists_ibfk_2`(`ref_dict_two_id`) references `dictionary_two`(`dict_id`); 

Ошибок синтаксиса нет, все сделано верно

ERROR 1050 (42S01): Table './test/already_exists' already exists 1 row in set (0.00 sec) 

Помнится с введением IPv6 на всех форумах гремел вопрос. Какой тип использовать для хранения IP адреса? Звучали разные предположения: DECIMAL(39), 2хbigint(20), binary, varchar. Но для чего нам компромиссы? Ведь все знают что bigint не ограничивается лишь 20 знаками. Как вы не знали? ну что ж это тоже легко доказать.

create table new_unlimited_table as   select cast(substr(repeat(' ', 21848), 10) as signed integer) new_bigint_field;  select column_type   from information_schema.columns  where table_name = 'new_unlimited_table' and table_schema = database() and column_name = 'new_bigint_field'; 

+---------------+ | column_type   | +---------------+ | bigint(65535) | +---------------+ 1 row in set (0.00 sec) 

То что результат запроса не должен зависеть от последовательности добавления данных в таблицу — это вроде очевидно. Очевидно для всех, но не для нас. Мы не ищем легких путей. Попробуем сделать следующее: запишем в таблицу всего 2 строки. В начале в прямой последовательности потом в обратной, и попробуем их 2 раза выбрать один и тем же запросом:

create table data_ordering (varchar_value varchar(10));  insert into data_ordering      values (''), ('string');  select *   from data_ordering  where 'string' regexp varchar_value; +---------------+ | varchar_value | +---------------+ | string        | +---------------+ 1 row in set (0.00 sec)  

Пока все верно и без обмана, действительно только одна строка удовлетворяет нашему условию. Теперь в обратном порядке.

delete from data_ordering;  insert into data_ordering      values ('string'), ('');  select *   from data_ordering  where 'string' regexp varchar_value; 

те же строки — тот же запрос

+---------------+ | varchar_value | +---------------+ | string        | |               | +---------------+ 2 rows in set (0.00 sec) 

В общем к чему я все это? Поверьте подвоха от разработчиков можно ждать откуда угодно, и то что поведение MySQL соответствует документации — это хорошо, гораздо хуже когда все наоборот. С наступающим!