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

от автора

В последнее время часто стал натыкаться на различные рассуждения людей, по поводу того, что MySQL — это плохо, это очень плохо — потому что… а вот дальше идут описания различных фич MySQL, которые четко документированы, но пользователь их просто не знает. Кто-то добавляет в БД данные без валидации и удивляется почему они сохранились в неверном формате, а кто-то описывает кучу особенностей myIsam движка, и на этих основаниях делает вывод, что MySQL это отстой — который невозможно использовать в реальных проектах. Всю документацию прочитать невозможно, и да — я с этим абсолютно согласен, но поверьте у нас есть куча других недокументированных и не менее интересных особенностей. Давайте начнем с малого, к примеру докажем, что NULL равно нулю.

NULL это сложная структура, при чем каждая БД трактует его по своему. В MySQL нет таких извращений как в Oracle (там NULL равен пустой строке) у нас все гораздо круче. С одной стороны NULL равен нулю. Это легко доказать. Создадим простую таблицу null_equals_zero и заполним её 4 значениями с уникальной колонкой номер 2, по которой проведем группировку.

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

но каков будет результат? 0, NULL или оба значения одновременно?

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

как и следовало ожидать это NULL ибо ноль и NULL в данном случае это одно и то же

Данный пример это лишь одна часть поведения 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) 

что и требовалось доказать, хотя вынужден заметить что тут есть 2 вывода, либо NULL больше 1 либо можно получить элемент массива по индексу NULL, который вроде как NULL, что и написано выше, но как знать…

Теперь займемся математикой, думаю за первый класс самое то. И так вопрос, какой знак имеет число 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); 

осталось узнать какой же у нуля знак по мнению разработчиков MySQL

+----------------------+ | 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) 

говорит уже есть такая таблица, а все почему — имя неправильное, так и написано в деталях если показать статус движка InnoDB
Error in foreign key constraint creation for table `test`.`already_exists`.
A foreign key constraint of name `trans`.`Already_exists_ibfk_1` already exists. (да ну! я же назвал констрейнт Already_exists_ibfk_2)
Workaround: name your constraints explicitly with unique names. (да-да я смотрю КЭП не дремлет)
InnoDB: Renaming table `test`.`#sql-37fc_3` to `test`.`already_exists` failed!
а все почему? правильно использование кавычек до добра не доводит — где-то в движке их учли а где-то нет. Так что не судьба нам создать таблицу с имененм already_exists ибо она already exists

Помнится с введением 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) 

ну уж 65535 десятичных цифр — нам точно хватит

То что результат запроса не должен зависеть от последовательности добавления данных в таблицу — это вроде очевидно. Очевидно для всех, но не для нас. Мы не ищем легких путей. Попробуем сделать следующее: запишем в таблицу всего 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; 

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

осталось выяснить сколько строк из 2-х удовлетворяют тому же критерию

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

о! точно две… а первый раз?… одна? аааа ну да я же данные в другом порядке вставил, больше не буду, извините…

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

ссылка на оригинал статьи http://habrahabr.ru/post/164085/


Комментарии

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *