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

+-----------+
| 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 соответствует документации — это хорошо, гораздо хуже когда все наоборот. С наступающим!

Автор: mcshadow

Источник