MySQL-procedure (loop, repeat) _ MySQL

In MySQL-procedure (cursor, loop), all the text in the spam_keyword table is divided into the t table, and the words in each row are not repeated. the t table can be used as a small dictionary, it only contains 1000 characters. this time, table t is used as the character source, and a character in table t is written in MySQL-procedure (cursor, loop) split all the text in the spam_keyword table into the t table, and the words in each row are not repeated. the t table can be used as a small dictionary and only contains 1000 characters, this time, we use table t as the character source and write a procedure statement that uses table t as the character Library to generate random characters with an indefinite length.

1. table t uses the InnoDB engine. in order to make a comparison, create a new table t2, use the MyISAM engine and copy the data of table t. A total of 1023 rows of records are recorded.

Create table t2 like t; alter table t2 engine = myisam; insert into t2 select * from t; create table 'T2' ('id' int (11) default null, -- row number id, which is unique. a total of 1023 rows 'T' varchar (3) default null, 'cnt 'int (11) default null, KEY 'idx _ id' ('id') ENGINE = MyISAM default charset = utf8

2. create another table and store the generated random data. The table name is tx and the column x. each row contains 0 to 10 characters.

Create table 'tx '(

'X' varchar (10) DEFAULT NULL

) ENGINE = InnoDB default charset = utf8

3. Stored Procedure: use rand () to generate a random number of less than 10 to determine the length of each inserted string, insert 50000 times

Drop procedure if exists proctx; create procedure proctx () begin declare xid int; -- random source id declare xstr varchar (1); -- the word declare oid int corresponding to the source id; -- string random growth count declare xostr varchar (10); -- string result declare txid int; -- all cycle count set txid = 0; truncate tx; loop1: loopset txid = txid + 1; set oid = ceil (rand () * 10); -- use the number of randomly generated loops to determine the character length set xostr = ''; -- it must be set to a null string before entering the growth loop; otherwise, the default value is null. the result of concat's null connection is null repeatset xid = ceil (rand () * 1023 ); select t into xstr from t where id = xid; set xostr = CONCAT (xstr, xostr); -- the string result set oid = oid-1 to insert to the tx table; until oid = 0 end repeat; insert into tx set x = xostr; -- insert the table if txid> = 50000 then leave loop1; end if; end loop; end;

At the beginning of the write process, you didn't need to pay attention to set xostr, and set an initial value for this string variable so that all the results in the subsequent concat functions are converted to null. The result is that all values inserted in the tx table are null.

4. check the result after calling proctx.

From the t table, InnoDB, 441.677 s, 7 min 21 s, 113.205 lines per sec

[SQL] call proctx ()

Affected rows: 1

Time: 441.677 s

Mysql> select * from tx;

+ ---------------------- +

| X |

+ ---------------------- +

| Disaster tolerance # Zhongyu zhidianlang |

| No downgrading |

| U song Opera |

| Flat |

| Try-on ratio of purchase cabinets |

| Example |

| Hundreds of millions |

| Longshi weekly price insurance |

| {Waxy |

| Q-door mounting and mailing department's wife |

... Omitted ......

Modification process, from table t2, MyISAM, 439.339 s, 7 min 19 s, 113.895 lines per sec

[SQL] call proctx ()

Affected rows: 1

Time: 439.339 s

Mysql> select * from tx;

+ ---------------------- +

| X |

+ ---------------------- +

| Li, like the dead Palace scene |

| P South exhibition |

| Sub-card |

| The banner liquor z weak music is revealed |

| Search and contraction of Haofu zhengqiyin Park |

| The wind G starts the flag. Shiya Yu |

| W |

| Zheng Ma bond Kong Yi Hai men box enrollment |

| Difference |

| You are not recognized by Dr. Gao. |

... Omitted ......

There is no difference between the two execution results, which cannot be explained. In addition, because the actual number of cyclic operations generated during the two execution processes is different, because the random numbers are different, in fact, the comparability of the two results is also related to the nature of the random number. In addition, it takes more than seven minutes to use a random number of 5 million records, which is a little slow. If we can complete the insertion of 5 kW in a few minutes, we may also be able to reduce the proportion of the impact of random numbers on time. "You have to check whether this random is true or false ", the two engines should objectively have different query performance characteristics, but this test should use the wrong test object, and the t table has fewer records, procedure statement execution time focuses on loop and random. Although the characteristics of the two engines are not verified, the objective of generating random strings is achieved, that is, the performance needs to be optimized.