Mysql usage summary: (1) exec_MySQL with stored procedures and loops to implement the MssqlServer function

Mysql usage summary: (1) the storage process, loop, and exec method for implementing the MssqlServer function recently began to summarize the Mysql experience I learned. I will share 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.