"Learning note" MySQL Floor error Principle Analysis Summary

Reference Link: http://mp.weixin.qq.com/s?__biz=MzA5NDY0OTQ0Mw==&mid=403404979&idx=1&sn= 27d10b6da357d72304086311cefd573e&scene=1&srcid=04131x3lqlrdmyocntcqwf6n#wechat_redirect

There are many articles on the floor error, but are the use of statements, the introduction of the principle of the article most of a pen, but I finally found a decent article of principle, so reference to understand the principle, refer to This link said very detailed, but I still have some unclear white places, So the study of a bit, and finally generally understand, although there are some doubts, but still have to tear down the record of their own understanding ... Writing slag as far as possible to remember the orderly, (say ashamed, contact security for a year, now only to understand.) ）

Use the statement online a search a lot of, I will no longer elaborate, first based on a statement to summarize the Picture it:

Say the RAND function first, and it will be used below.

What does the floor (rand (0)) mean in the statement? is to produce a number that is not unique and can repeat itself. The number generated by the rand () function randomly generates 0 to 1 decimals, and when the parameter becomes rand (0), a definite decimal point (my understanding) may be a bit around, so let's take a look at the execution statement:

The rand () function performs 2 results:

The results of the parameter execution 2 times are added:

So rand (0) is in order to get a definite number, that is, what data (my own understanding) is executed, and the floor is to get the unique integer data, otherwise the decimal is too long, it is too difficult to find a duplicate number. In addition, you multiply by more than 2 of what number is OK, certainly not too big, for example 9999999999999. To be greater than or equal to 2 is because, less than 2,floor (rand (0) * *) will become 0, will not be unique, the number is too large, and not so much data can let you find duplicates ...

Again error, reported what is wrong, is the primary key duplicate, why will the primary key repeat? This is about group by and COUNT (*), and we'll look at the effect of Count (*) and group by combination.

The results show the number of times for each column, and the contents of the column are unique primary keys. And in the query process, is to create a virtual table, a row of inserted, and the insertion has been repeated the primary key, then, will be an error. Of course, you might ask, when you repeat, COUNT (*) adds 1, why does the primary key repeat? This is related to the rand () function, which is mentioned in the official document that the rand () function will be counted multiple times for a group by query, and here is an explanation of the reference document, which I think is very clear:

1. Empty virtual tables are established by default before querying

2. Take the first record, execute floor (rand (0) * * , found that the result is 0 (first calculation), query the virtual table, found that 0 of the key value does not exist, then Code style= "margin:0px; padding:0px; " >floor (rand (0) * *) will be recalculated once, the result is 1 (second calculation), insert the virtual table, then the first record query is complete

3. Query the second record, and then compute the floor (rand (0) * * , found the result is 1 (third calculation), query the virtual table, found that 1 of the key value exists, so Code style= "margin:0px; padding:0px; " >floor (rand (0) * *) will not be computed the second time, directly count (*) plus 1, second record query completed

4. Query the third record and calculate the again. floor (rand (0) * * floor (rand (0) *) is computed again as the primary key of the virtual table with a value of 1 (5th time calculation) , however, 1 this primary key already exists in the virtual table, and the newly computed value is also 1 (the primary key key value must be unique), so the insertion time will be directly error.

5. The entire query process floor(rand(0)*2) was calculated 5 times, querying the original data table 3 times, so this is why the data table requires 3 data, the use of the statement will be the cause of the error.

To add 5, why do I need 3 data to see the results of the floor (rand (0) * *)?

Although there are only 2 values, but you have to find out the third one will be repeated AH ~~~~~

Well, finally, to summarize,SELECT distinct concat (0x23,personid,0x3a,password,0x23) from the person limit 0,1 is to check the desired data, floor (rand (0) * 2) is in order to generate a non-unique and repeatable number, so that the back can be the primary key repeat

Concat ((SELECT distinct concat (0x23,personid,0x3a,password,0x23) from person limit 0,1), rand (0) * 2), is for aggregation into # #: C4d7b26adbecfc3fedbc895f30099f4b#1 's appearance, SelectCount (*), concat ((SELECT distinct concat (0x23,personid,0x3a,password, 0x23) from the person limit 0,1), floor (rand (0) * 2)) Xfrominformation_schema. TABLES GROUP by X is to put Rand count (*), group by together, because it is counted more than once, there is a primary key repetition. The outermost layer of the Select 1 can not.

Well, that's pretty much the way it is, it's really a good thing to write about this statement, and MySQL must be playing very well. This blog is added to their own understanding of things, if there is wrong, or there is a different view, we have more exchanges ~ ~ ~ and slag slag for guidance.

"Learning note" MySQL Floor error Principle Analysis Summary