About mysql stored procedure creation dynamic table name and parameter processing, mysql Stored Procedure

About mysql stored procedure creation dynamic table name and parameter processing, mysql Stored Procedure
Reprinted please indicate the source: curtain roll west wind column (http://blog.csdn.net/ljxfblog)

Recently, the game started its second internal test, started to process operation logs, and first put the logs in the same table. It was found that on average, 100 gamers were online in a day, with more than 0.13 million Operation Records, we decided to split the table and save the logs by date. Every day, the logs are stored in a table, and old data is regularly exported for backup and deleted.

The specific idea is to insert the log into the table of the current day according to the current time. If the table does not exist, create a new table with the date in the table name. This involves dynamically creating a date-related table in the stored procedure. Mysql is not very familiar with it. It only has basic syntax. This advanced function requires online query.

The first thought was to define a string variable, put the table name together, and then create a table. We found that the created table name is the defined variable name, so we had to try again.

After checking the data and experimenting many times, we finally found the implementation method. We need to first spell out the SQL statement and then use PREPARE to process it. The SQL statement is as follows:

set @sql_create_table = concat('CREATE TABLE IF NOT EXISTS operrecord_', date_format(curdate(),'%y%m%d'),"(`oper_id` int(10) NOT NULL AUTO_INCREMENT,`oper_role` int(11) NOT NULL, `oper_type` varchar(30) NOT NULL DEFAULT '',`oper_content` varchar(1000) NOT NULL DEFAULT '',`oper_cls` int(10) 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;   EXECUTE sql_create_table; 

After creating a table, you still need to insert data, but the insert statement also needs to use the dynamic table name. There is no way to deal with it in the same way as above. First, fight the SQL statement, example: (Note: rId and so on are 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;   EXECUTE sql_oper_revcord; 

An error will be reported during execution. If the rId field cannot be found, a quotation mark ('rid ') should be added to the rId field on the Internet. The error data type does not match.

After thinking about it, we should declare the parameters of the people who pass the rId as local parameters. The test is successful again. In addition, note that the table field must be added to the string ('xxx ') only. The correct SQL statement is 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;   EXECUTE sql_oper_revcord; 

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