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)

The game starts the second time and starts processing the operation log. Start by putting the logs in the same table and discovering the time of day, averaging 100 players online. Operation Record of more than 130,000, decided to split the table. Save the log according to the date. Daily logs are stored in a table, and the old data is regularly exported for backup and then deleted.

the detailed idea is to write the log, according to the current time to decide to insert into the day's surface. Create a new table if the table does not exist. The table name contains 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, just the main grammar, such advanced features are required to surf the internet, hehe.

The very beginning of the idea. is to define a string variable, put the table name to create a table, and found that the created table name is a defined variable name, just a good time to find a way.

After checking the data, and testing the very many times, finally found the implementation of the method, you need to first put SQL statements. Then you can use prepare to handle it. SQL statements such as the following:

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 SQL statement is spelled first. The scale is as follows: (note: RID, etc. is the number of parameters passed in by the stored procedure)

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;   

Run the time to find an error, can not find rid this field. The web said that the rId should be added with an argument such as (' rId ') does not work, continue to error data type mismatch.

Think about it. Rid of these descendants should be declared as a local parameter, again tested success, but also need to note that the table field in the string need to add (' xxx '). 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