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.