Pull to refresh

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

Reading time 5 min
Views 59K
В последнее время часто стал натыкаться на различные рассуждения людей, по поводу того, что 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)

круто? теперь можете попращаться с репликацей и всеми последующими дампами, а bigint как был bigint'ом так им и останется

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

Articles