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

I do not know if you have any understanding of the similar MYSQL floor error report how to solve the article, today I am here to tell you briefly. If you are interested, let's take a look at the text. I believe you will gain something after reading MYSQL's floor error report how to solve it.

Use the sentence to search a lot on the Internet, I will not go into details, first summarize the picture according to a sentence:

Let's talk about the rand function first, which will be used next.

What is the meaning of floor (rand (0) * 2) in the statement? Is to produce a number that is not unique and can be repeated. The number generated by the rand () function randomly produces a decimal from 0 to 1, and when the argument becomes rand (0), a definite decimal (as I understand it) may be somewhat circled, so let's take a look at the execution statement:

The rand () function executes the result twice, and:

The result of executing 2 times with a parameter:

So, rand (0) is to get a definite number, that is, what the number of execution is (my own understanding), and * 2 floor is to get the only integer data, otherwise the decimal is too long, it is too difficult to find a repeated number. no, no, no. In addition, you can multiply any number greater than or equal to 2, but certainly not too large, such as 9999999999999. To be greater than or equal to 2 is because less than 2 rand (0) * 2 will become 0, it will not be unique, the number is too large, and there is not so much data for you to find duplicates.

Besides, if you report an error, what is wrong? it is the repetition of the primary key. why is the primary key repeated? This is about group by and count (*). Let's first look at the effect of the combination of count (*) and group by.

The result will show the number of times each column, and the contents of this column are the only primary key. In the query process, the first is to create a virtual table, a row to insert, and the insert has a duplicate primary key, then an error will be reported. Of course, you may ask, when repeating, count (*) is added by 1. Why does the primary key repeat? This is related to the rand () function. It is mentioned in the official document that the rand () function will be calculated many times when performing a GROUP BY query. Here is an explanation of the reference document. I think it is very clear:

1. An empty virtual table is created by default before the query.

two。 Take the first record, execute floor (rand (0) * 2), find that the result is 0 (the first calculation), query the virtual table, and find that the key value of 0 does not exist, then floor (rand (0) * 2) will be calculated again, the result is 1 (the second calculation), insert the virtual table, and the query of the first record is completed.

3. Query the second record, calculate floor (rand (0) * 2) again, and find that the result is 1 (the third calculation). After querying the virtual table, it is found that the key value of 1 exists, so floor (rand (0) * 2) will not be calculated for the second time, directly count (*) plus 1, and the second record query is completed.

4. Query the third record, calculate floor (rand (0) * 2) again, find that the result is 0 (4th calculation), query the virtual table, and find that the key value does not have 0, then the database attempts to insert a new piece of data. When inserting the data, floor (rand (0) * 2) is calculated again as the primary key of the virtual table, whose value is 1 (the fifth calculation). However, the primary key 1 already exists in the virtual table. The newly calculated value is also 1 (the primary key value must be unique), so the insertion is directly misreported.

5. The entire query process floor (rand (0) * 2) is calculated five times and the original data table is queried three times, so this is why three pieces of data are needed in the data table and this statement is used to report an error.

Add 5 here. Why do you need 3 pieces of data? take a look at the implementation result of floor (rand (0) * 2).

Although there are only 2 values, there will be no repetition until you find the third item.

Well, finally, we can sum up, SELECT distinct concat (0x23dPersonID report0x3aPasswordPower0x23) FROM person limit 0meme1 is to check the desired data, floor (rand (0) * 2) is to generate a non-unique and repeatable number, so that the primary key can be repeated later.

Concat ((SELECT distinct concat (0x23 PersonID instruction 0x23) FROM person limit 0Power1), rand (0) * 2), is to aggregate into the appearance of # 1:c4d7b26adbecfc3fedbc895f30099f4b#1, SELECTcount (*), concat ((SELECT distinct concat (0x23 PersonID 0x23) FROM person limit 0jig1), floor (rand (0) * 2)) xFROMinformation_schema. The purpose of TABLES GROUP BY x is to put rand count (*) and group by together, because they are calculated many times and there are repeated primary keys. The outermost floor select 1 can be left out.

After reading MYSQL's floor error report how to solve this article, what do you think? If you want to know more about it, you can continue to follow our industry information section.

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.