Mysql usage Summary: (1) stored procedures, loops, and the exec method for implementing the Mssql Server Function

I recently started to summarize my experience with Mysql and will share it with you here. I hope you can make a decision. Thank you.

 

Let's talk about the reason why I want to learn the Mysql stored procedure, because Mysql does not support loops in simple scripts. You can refer to the relevant documents.

 

First, provide the Code:

DELIMITER $

Drop procedure if exists 'domain' $

Create procedure 'domain '()

DETERMINISTIC

BEGIN

DECLARE v1 int default 1000;
Declare tablename varchar (10) default 'testtable ';
Declare createString varchar (1000 );

While v1 <> 0 DO
Set @ nextTable = concat (tablename, v1 );
Set createString = concat ("create table", @ nextTable );
Set createstring = Concat (createstring, "'('id' int null, 'name' varchar (200) null );");
Set @ mytable = createstring;
Select @ mytable;
Prepare stmt_name from @ mytable;
Execute stmt_name;
Deallocate prepare stmt_name;

Set V1 = V1-1;
End while;

End $

Delimiter;

 

 

Step 1: Create a MySQL stored procedure. Note that the keyword delimiter must be used. Otherwise, errornum: 1064 is generated. Both the command line and MySQL tools (Workbench) must be followed.

Specific reference: http://dev.mysql.com/doc/refman/5.1/en/stored-programs-defining.html, once again proved, carefully read the document is very important.

Http://dev.mysql.com/doc/refman/5.1/en/stored-routines.html for more information

 

Step 2: To use a loop, you must use the Mysql stored procedure and hope that the new Mysql version can be improved.

 

OriginalBe used in the body of stored programs: Stored procedures and funures, triggers, and events. These objects are defined in terms of SQL code that is stored on the server for later invocation

 

 

Reference: http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-compound-statements.html

 

Step 3: run the PREPARE command to open a Stmt. Do not forget to close it.

Reference: http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-prepared-statements.html

 

Summary:

 

I use a loop here, but the table creation process is actually completed only 300 times. Is there a limit on loops in the stored procedure?

Exploration 1: the number of cycles in a while loop is set to 100000, no problem. Time: 0.406 seconds. (Dual core 2.47)

Exploration 2: the number of cycles is 10000000, no problem. Time: 27.753 seconds.

Explore 3: the number of cycles is 20000000, no problem. Time: 48.578 seconds.

More than 30 seconds.

 

The connection is disconnected after more than 10 minutes. The main analysis showed that workbench had a database connection time of 10 minutes.

Through reading, we found that the MySQL timeout setting is interactive_timeout = wait_timeout: 28800 S (480 min) for 6 hours.