MySQL: we destroy stereotypes

Recently, people often began to stumble upon various people's reasoning about the fact that MySQL is bad, this is very bad - because ... but then come the descriptions of various MySQL features that are clearly documented, but the user simply does not know them. Someone adds data to the database without validation and wonders why they were saved in the wrong format, and someone describes a bunch of features of the myIsam engine, and on these grounds concludes that MySQL sucks - which cannot be used in real projects. It is impossible to read all the documentation, and yes - I absolutely agree with that, but believe us, we have a bunch of other undocumented and equally interesting features. Let's start small, for example, prove that NULL is zero.

NULL is a complex structure, with each database interpreting it in its own way. In MySQL, there are no such perversions as in Oracle (where NULL is equal to the empty string), everything is much cooler. On the one hand, NULL is zero. It is easy to prove. Create a simple table null_equals_zero and fill it with 4 values ​​with a unique column number 2, according to which we will group.

 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; 

How do you understand this query will return us the unique values ​​of the first column of which we know two: zero and NULL

This example is only one part of NULL behavior in controversial situations, for although it is zero, it can just as easily be proved that NULL is more than one. Let's consider two functions: least — which returns the minimum value of the listed arguments, and elt — which returns the value at the index specified by the first argument. I think no one, from those who reads this post, have any questions how exactly they work, but just in case for the purity of the experiment we will fulfill 2 queries:

 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) 

As long as we see everything goes according to plan, NULL is incomparable on the one hand, and there are no elements by the NULL index, but let's try to find out what the superposition of these functions is equal to?

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

Now let's do math, I think for the first class thing. And so the question is what sign has the number 0. Take your time with the answer, you already understood that the MySQL developers are terrible trolls. Let's check it out. So. Create a table and insert two close to zero values ​​into it - positive and negative.

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

Well, let's digress from mathematics and move on to impossible objects. It turns out that there are objects in MySQL that cannot be created (dedicated to lovers of quotes). Let's try to make a table with the name already_exists .
Let's start with the directory (what a table without a foreign key).

 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`) ); 

It seems everything is as it should be. Now - we will add one more column referring to another table.

 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`); 

There are no syntax errors, everything is done right

I remember with the introduction of IPv6 in all forums the question thundered. What type to use to store IP addresses? There were various assumptions: DECIMAL(39) , 2bigint(20) , binary , varchar . But why do we need compromises? After all, everyone knows that bigint not limited to only 20 characters. How did you not know? Well, this is also easy to prove.

 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'; 

The fact that the result of the query should not depend on the sequence of adding data to the table is obvious. Obviously for everyone, but not for us. We're not looking for easy ways. Let's try to do the following: we write only 2 rows in the table. At the beginning, in a direct sequence, then in the reverse, and we will try to select them 2 times with the same query:

 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) 

As long as everything is true and without cheating, only one line is really satisfying our condition. Now in the reverse order.

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

same lines - same query


So what am I doing all this? Believe the trick from the developers can be expected from anywhere, and the fact that the behavior of MySQL corresponds to the documentation is good, much worse when the opposite is true. Holiday greetings!