MySQL: breaking stereotypes

Recently, I often began to come across various arguments of people about the fact that MySQL is bad, it is very bad - because ... but then there are descriptions of various MySQL features that are clearly documented, but the user simply does not know them. Someone adds data without validation to the database 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 is sucks - which cannot be used in real projects. It is impossible to read all the documentation, and yes - I absolutely agree with this, but believe me, we have a bunch of other undocumented and no less 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 (there NULL is equal to an empty string), everything is much cooler here. On the one hand, NULL is zero. This is easy to prove. Create a simple table null_equals_zeroand fill it with 4 values ​​with a unique column number 2, by 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;

As you know, this query will return to us the unique values ​​of the first column of which, as we know, two: zero and NULL
but what will be the result? 0, NULL, or both?
+-----------+
| int_value |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

as you would expect, this is NULL because zero and NULL in this case are the same

This example is only one part of the behavior of NULL in contentious situations, because although it is equal to zero, it can also be easily proved that NULL is more than one. Let's look at 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 read this post, has questions about how they work, but just in case, for the purity of the experiment, we will execute 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)

So far 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;

I think a smart reader has already guessed what the answer will be.
+----------------+
| null_is_to_big |
+----------------+
| 1 < null       |
+----------------+
1 row in set (0.00 sec)

as required, although I have to notice that there are 2 outputs, either NULL is greater than 1, or you can get an array element at the NULL index, which is kind of NULL, which is written above, but who knows ...

Now let's do the math, I think for the first grade the most. And so the question is, what sign does the number 0. Take the time to answer, you already realized that the MySQL developers are terrible trolls. Let's check it out better. So. Let's create a table and insert into it two values ​​close to zero - 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);

it remains to find out what the sign is at zero according to MySQL developers
+----------------------+
| signed_zero          |
+----------------------+
| -0 не равно 0        |
+----------------------+
1 row in set (0.00 sec)

Well, actually nothing surprising - they have not yet decided

Well, let's digress from mathematics and move on to impossible objects. It turns out that MySQL has objects that cannot be created (dedicated to lovers of quotes). Let's try to make a table called 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 that everything is going as it should. Now - add another column referencing 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 correctly
but the server response will disappoint you
ERROR 1050 (42S01): Table './test/already_exists' already exists
1 row in set (0.00 sec)

says there is already such a table, and all why - the name is incorrect, it’s written in detail if you show the status of the InnoDB
Error engine in foreign key constraint creation for table `test`.`already_exists`.
A foreign key constraint of name `trans`.`Already_exists_ibfk_1` already exists. (yeah! I called the constrain Already_exists_ibfk_2)
Workaround: name your constraints explicitly with unique names. (yes, I’m watching CEP is not asleep)
InnoDB: Renaming table `test`.` # sql-37fc_3` to` test`.`already_exists` failed!
but why? the use of quotation marks does not lead to good - somewhere in the engine they were taken into account and somewhere not. So it’s not our fate to create a table named already_exists because it already exists

I remember with the introduction of IPv6 in all forums the question was thundering. What type to use to store IP addresses? There were different assumptions: DECIMAL(39), 2хbigint(20), binary, varchar. But why do we need compromises? After all, everyone knows that it is bigintnot 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';


Well, let's find out how many characters we have
+---------------+
| column_type   |
+---------------+
| bigint(65535) |
+---------------+
1 row in set (0.00 sec)

cool? now you can say goodbye to replication and all subsequent dumps, and bigint as it was bigint it will remain so

The fact that the query result should not depend on the sequence of adding data to the table is kind of obvious. Obvious to everyone, but not to us. We're not looking for easy ways. Let's try the following: we write only 2 rows in the table. At the beginning, in the direct sequence, then in the reverse, and 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)

While everything is true and without fraud, really only one line satisfies our condition. Now in reverse order.
delete from data_ordering;
insert into data_ordering
     values ('string'), ('');
select *
  from data_ordering
 where 'string' regexp varchar_value;

same lines - same request
it remains to find out how many rows of 2 satisfy the same criterion
+---------------+
| varchar_value |
+---------------+
| string        |
|               |
+---------------+
2 rows in set (0.00 sec)

about! exactly two ... and the first time? ... one? ahhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh


So what am I doing all this for? Believe me, 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!

Also popular now: