About MySQL stored procedures creating dynamic table names and parameter handling

 Reprint Please indicate the source: The Curtain Roll westerly column (Http://blog.csdn.net/ljxfblog)

Recently the game began the second beta, began to process the operation log, the first to put the log into the same table, found a day, the average 100 players online, the operation record of more than 130,000, decided to split the table, according to the date to save the log, the daily log into a table, and then regularly export the old data to backup and delete.

the specific idea is to write the log, according to the current time to decide to insert into the day's table, such as the table does not exist to create a new table, the table name inside the date of the day. This involves the need to dynamically create a date-related table within the stored procedure. MySQL is not very familiar with, only the basic grammar, this advanced features need to surf the internet, hehe.

The first idea was to define a string variable, put the table name together and create the table, and find that the created table name is a defined variable name, so you have to try again.

After checking the data and experimenting many times, finally found the implementation of the method, you need to first put the SQL statement, and then use prepare to deal with it. The SQL statements are as follows:

Set @sql_create_table = concat (' CREATE table IF not EXISTS operrecord_ ', Date_format (Curdate (), '%y%m%d '), "(' oper_id ' int () not NULL auto_increment, ' oper_role ' int (one) not null, ' oper_type ' varchar (+) NOT null DEFAULT ' ', ' oper_content ' Varc Har (+) NOT null default ' ', ' oper_cls ' int (ten) NOT null default ' 0 ', ' oper_date ' datetime NOT NULL, ' Oper_serverid ' int (11 ) Not NULL default ' 1 ', PRIMARY KEY (' oper_id ')) engine=innodb auto_increment=1 default Charset=utf8 "); PREPARE sql_create_table from @sql_create_table;   

After creating the table, you also need to insert the data, but the INSERT statement also uses the dynamic table name, there is no way to do with the same method as above, the first to spell the SQL statement, the example is as follows: (note: RID, etc. is stored procedure parameters passed in)

Set @sql_oper_revcord = Concat ("INSERT into Operrecord_", Date_format (Curdate (), '%y%m%d '), "(' Oper_role ', ' Oper_type ', ' Oper_content ', ' oper_cls ', ' Oper_serverid ', ' oper_date ') VALUES (rId, type, content, CLS, ServerID, Now ())); PREPARE Sql_oper_revcord from @sql_oper_revcord;   

Execution of the time to find an error, can not find rid this field, the net said should give RId quotes such as (' RID ') also do not, continue error data type mismatch.

Think, should be rid of these descendants of the parameters declared as local parameters, again test success, it is also important to note that the table field in the string need to add (' xxx ') only line. The correct SQL statements are as follows:

Set @rId = rId, @type = type, @content = content, @cls = cls, @serverid = Serverid;set @sql_oper_revcord = concat ("INSERT Into Operrecord_ ", Date_format (Curdate (), '%y%m%d ')," (' Oper_role ', ' oper_type ', ' oper_content ', ' oper_cls ', ' Oper_ ServerID ', ' oper_date ') VALUES (@rId, @type, @content, @cls, @serverid, Now ()) "); PREPARE Sql_oper_revcord from @sql_oper_revcord;   

Write down this article to make a memo. I also hope to help other students who have encountered this problem.

About MySQL stored procedures creating dynamic table names and parameter handling