Shulou(Shulou.com)06/01 Report--

In this issue, the editor will bring you about the reasons for the errors in floor () in MySQL. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Select count (*) from users group by concat (database (), floor (rand (0) * 2)); select count (*), concat (database (), floor (rand (0) * 2)) x from users group by x

They have the same meaning, and the as x in the second statement is actually a synonym (alias) for concat (database (), floor (rand (0) * 2)), and there is no difference between the two SQL statements. Come on, let's see what it says wrong: ERROR 1062 (23000): Duplicate entry 'security1' for key' group_key'.

It says' group_key' 's primary key 'security1' repeats, huh? Where did security1' come from? Which table has a duplicate primary key?

Although we don't know the principle at first, we can see that the database function in the error prompt statement has been executed. As I said before, I am useful to sqli-labs 's database, so it's normal to be 'security'' after database () is executed.

Floor (rand (0) * 2)

The 1 in security1 comes from floor (rand (0) * 2), which says security1 repeats, which means the primary key is already in the previous table. Because database () is fixed, let's move on to the floor (rand (0) * 2) that produces 1. Rand () is also a mathematical function that returns a random floating-point value [0Magne1].

If you specify an integer parameter N, it is applied to the seed value (also known as a random factor), (rand () will be randomly generated according to this seed value) to generate repetitive sequences, that is, the value of rand (0) is fixed.

After it, * 2, is selected to obtain the range of data [0pr 2], which is actually multiplied by 2.

Floor () is also a mathematical function that returns the maximum integer value not greater than x. For example, floor (3. 3) returns 3-4.

Now let's look at the number of times the users table data is calculated, the value of floor (rand (0) * 2).

You can see that the value of rand (0) is indeed fixed. At the same time, 1 also appeared. Concat () is a string concatenation function that concatenates multiple strings. If the string contains NULL, the result is NULL. From this point of view, the result after concat is' security0' or 'security1','security1'.

At this point, we don't have much to say in the second half. Rand () has a very important feature that we'll talk about later with group by.

Group by and count (*)

Let's talk about this count (*), which is an aggregate function that returns the number of values. The difference between it and count () is that it does not exclude NULL.

Let's use the query statement select count (*) from users group by username; to understand how group by works.

When group by executes, it fetches the records from the query table in turn and creates a temporary table, and the object of group by is the primary key of the temporary table. If the primary key already exists in the temporary table, add 1 to the count (*) value, and if it does not exist, insert the primary key into the temporary table, note that it is inserted! The empty temporary table created before the query.

Take the first record, username is Dumb, and find that there is no primary key in the temporary table, then insert Dumb into the primary key, the count (*) value counts 1, and take the second record.

Similarly, if the second record, username, is Angelina, and there is also no primary key, Angelina is inserted into the primary key, and the count (*) value counts as 1.

When the eighth admin in the original table is taken, the admin is also inserted into the temporary table as the primary key, and the count (*) is counted as 1. When you take item 15 of the data and find that there is already an admin as the primary key in the temporary table, then directly count (*) plus 1. End result:

Although the results displayed on the command line are not quite the same as ours, the idea is correct (it seems to sort the results alphabetically, or before inserting the temporary table).

At this point, according to the above logic, the error statement should be select count (*) from users group by 'security0' or' security1';? Then create a temporary table when group by, the first is security0, and find that there is no primary key, then insert security0 into the location of the primary key, count 1, and then take the next record in from's table.

After taking a record, group by is the primary key of which security1 does not exist in the security1', temporary table, then insert security1 into the primary key position, count 1, and then take the next record.

After that, there is only security0 or security1 for group by, which should be just a change in count. In the end, it should be:

So why not this result, but reported a repeated error of the primary key?

Because there is also one of the most important features, that is, when group by is used with rand (), if the primary key is not in the temporary table, rand () will be calculated again before insertion (that is, twice, but some blogs write multiple times. I don't know how many times this is, and I haven't found relevant materials to support it, but it makes sense to understand the following experiments twice). It is this feature that causes the primary key to repeat and report an error. Let's see:

When group by fetches the table record of the first from, the primary key of group by is' security0', found that there is no 'security0'' in the temporary table. Note that rand (0) * 2 will calculate again. After floor (), the primary key that is first inserted into the temporary table is not security0, but security1, and count 1.

Then take the second record, and 01 in the key of the second record group by is still calculated by floor (rand (0) * 2), that is, security1. At this point, the temporary table already has the primary key of security1, so count (*) can be added directly by 1.

Continue to take the third record from the from table and calculate floor (rand (0) * 2) again. The result is 0 and spliced with database () as security0. The primary key of the temporary table does not exist. Before insertion, floor (rand (0) * 2) is calculated again, after splicing with secruity1, but it is inserted directly, even if there is already a primary key security1 in the temporary table, resulting in repeated error reports of the primary key. That is: ERROR 1062 (23000): Duplicate entry 'security1' for key' group_key'.

This is the end of the principle of reporting errors. Do you feel echoed by the audience?

Optimize

Let's go on to see that we took a total of three records from the from table (which is why floor (rand (0) * 2) needs at least three records in the table), because the value of floor (rand (0) * 2) is 011011. But in fact, the third calculation of 1 can not be used (changed to 0101).

If a floor (rand (x) * 2) satisfies 0101 or 1010, then two pieces of data in the from table can be reported as errors. After many experiments, I found that the value of floor (rand (14) * 2) is 101000. So let's create a table with two pieces of data and try Kangkang.

Create a test table with only two pieces of data.

Rand (0) * 2 and rand (14) * 2 were used in the experiment.

These are the reasons for the floor () error report in the MySQL shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.