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.