Scenario: At the end of the year, each App will have a personal annual bill. In the project, in order to speed up the process and save interface coordination between each system module. Direct labor statistics (😳 😳 😳 😳 😳 😳 😳 😳 😳 😳 😳 😳 😳 😳), the JSON data structure bill.

Words do not say, the old man picked up the keyboard is dry.

The data is not provided by other systems groups, but some of the data is randomly generated descriptive text from a fixed text array. The front end display effect will be random text split into several parts.

Normal SQL concatenation into JSON is also convenient.

CONCAT('\"app\":{\"time\":\"', DATE_FORMAT(time, "%Y-%m-%d %T"), '\",\"days\":', days, 
case when typeTime is null then '' else CONCAT(',\"typeTime\":\"', DATE_FORMAT(typeTime, "%Y-%m-%d %T"), '\"') end,'}') 
Copy the code

Here comes random text

Educated, again beautiful temperament, also can cover up the your talent Resourcefulness: mental use activists, only you Brave words: your insights, comprehensive, thorough popular feeling Drawn: unique insight, always can learn everything well informed: the most beautiful experience, bear responsibility is the scenery along the way, all the sorrow and the sorrow of the first, after the world of joy but joy

As you can see from the top, they correspond to each other.

Only the random text really thought for a moment. Let’s break it down.

Intercepting text

How to get random text from an array in mysql see substring_index. In two steps, the first step is to count backwards and start counting backwards, and the second step is to take the first digit from the new array.

Substring_index (substring_index(array, 'coincidence interval ', -n),' coincidence interval ', 1)Copy the code

Constructed random factor

Now look at the random factor, refer to the rand() function.

Because it’s a random number between -1 and -7. So the end result is this.

-floor(rand()*7+1)
Copy the code

To take the values in the random range you can refer to this formula

FLOOR(start_num + RAND() * (end_num - start_num + 1))
Copy the code

Start the first intercept

Random string interception method, random number has, let’s go.

select user_id, CONCAT('\"desc\": {\"label\":\"', 
substring_index(substring_index('Well-educated; Resourceful; Speak out; An eye of gold; Be well informed; Responsibility; Be positive '.'; ', -FLOOR(rand(*)7+1)),'; '.1),'\ "'.',\"description\": \"', substring_index(substring_index('No matter how beautiful you are, you can't cover up the essence of your talent. You're a brain use activist. Your opinions, comprehensive, deep into the hearts of people; Unique insight, always know everything; The most beautiful experience, is the scenery along the road; The first world of sorrow and sorrow, after the world of joy and joy; Love to laugh you will not be bad luck '.'; ', -FLOOR(rand(*)7+1)),'; '.1),
'\ "}'
) as report_content from data; 
Copy the code

The result shows that it is not right. [Responsibility] should correspond to [the sorrow of the world before, the joy of the world after], and the result shows that [no matter how beautiful the temperament is, it cannot cover up the essence of your talent].

Try for a second intercept

This time considering the last time we are the random factor in THE SQL no sentence, so each time we executed 2 times, the generation is a random factor is not the same, the intercept results do not correspond, that, we first in the sub-query the random factor generated first, and then in the outer layer of the random factor should be different.

select temp.user_id, CONCAT('\"desc\": {\"label\":\"', 
substring_index(substring_index('Well-educated; Resourceful; Speak out; An eye of gold; Be well informed; Responsibility; Be positive '.'; ',temp.desc_index),'; '.1),'\ "'.',\"description\": \"', substring_index(substring_index('No matter how beautiful you are, you can't cover up the essence of your talent. You're a brain use activist. Your opinions, comprehensive, deep into the hearts of people; Unique insight, always know everything; The most beautiful experience, is the scenery along the road; The first world of sorrow and sorrow, after the world of joy and joy; Love to laugh you will not be bad luck '.'; ',temp.desc_index),'; '.1),
'\ "}'
) as report_content from (select user_id, -FLOOR(rand(*)7+1) as desc_index from data) temp

Copy the code

Spitting blood pictures in the search, from the beginning to see see out, not corresponding, the first few on the front corresponding [love to laugh your luck will not be bad], love to laugh I, luck is so bad.

Change the following statement to print out the random factor

select temp.user_id, desc_index, CONCAT(desc_index, '\"desc\": {\"label\":\"', 
substring_index(substring_index('Well-educated; Resourceful; Speak out; An eye of gold; Be well informed; Responsibility; Be positive '.'; ',temp.desc_index),'; '.1),'\ "'.',\"description\": \"', substring_index(substring_index('No matter how beautiful you are, you can't cover up the essence of your talent. You're a brain use activist. Your opinions, comprehensive, deep into the hearts of people; Unique insight, always know everything; The most beautiful experience, is the scenery along the road; The first world of sorrow and sorrow, after the world of joy and joy; Love to laugh you will not be bad luck '.'; ',temp.desc_index),'; '.1),
'\ "}'
) as report_content from (select user_id, -FLOOR(rand(*)7+1) as desc_index from data) temp

Copy the code

********, the front of these asterisks, are dirty words, ignore it, random factor, obviously in the subquery are generated, why is not the same.

Finally racked their brains to intercept

We wanted to use rand() in the subquery to figure out the random factor, but rand() recomputed the TMM in the outer query.

A reference to look for information, accidentally found the problem

Mysql certainly has this bug

In 2017, a buddy mentioned such an issue, which is the link above.

In mysql5.6, random numbers are the same, 5.7 random numbers are different. If the subquery does not use tables, the same is true for random numbers. Just right mysql is 5.7, just right.

Following the advice of the author and Roy Lyseng, I used limit. Unexpectedly, I was overwhelmed with tears.

A successful solution will also be explained in the issue.

The final look after the transformation.

select temp.user_id, desc_index, CONCAT(desc_index, '\"desc\": {\"label\":\"', 
substring_index(substring_index('Well-educated; Resourceful; Speak out; An eye of gold; Be well informed; Responsibility; Be positive '.'; ',temp.desc_index),'; '.1),'\ "'.',\"description\": \"', substring_index(substring_index('No matter how beautiful you are, you can't cover up the essence of your talent. You're a brain use activist. Your opinions, comprehensive, deep into the hearts of people; Unique insight, always know everything; The most beautiful experience, is the scenery along the road; The first world of sorrow and sorrow, after the world of joy and joy; Love to laugh you will not be bad luck '.'; ',temp.desc_index),'; '.1),
'\ "}'
) as report_content from (select user_id, -FLOOR(rand(*)7+1) as desc_index from data limit 100000) temp


Copy the code