MySQL stored procedures dynamically create tables and insert data

MySQL stored procedures dynamically create tables and insert data

Recently done a project, the database is using MySQL, for MySQL is not very skilled, but will be simple application, after all, simple SQL statements are still interlinked, but as the project's deep and complex SQL statement began to slowly, one of the small difficulty is to create a table dynamically based on the date of the day, and insert the data into it.

The specific logic can be understood as, first go to the database query there is no day's record table, if not, create the table, and then perform the day's log write.

MySQL Visual Tools We use is navicat, relatively speaking or relatively good, of course, no SQL Server so humane (many shortcuts do not support, cumbersome operation). Let me just say it. Use navicat how to create a database table dynamically and insert data.

One: Create a stored procedure

Two: Using the CONCAT function stitching dynamically create the statement of the table and execute

Three: Also use the CONCAT function to splice the statement that writes the data to the dynamically created table and execute

Because the database table is a fixed prefix plus the current date form, so whether to create a table or write data is based on dynamic tables, so you can not write to the same fixed table as the simple INSERT statement, in addition, the parameter ID passed to the stored procedure is not directly passed to the stored insert statement, will report the SQL statement error, so need to introduce local parameters, may not be the best way, but at present the author has not found a better wording, if you have any better or feel where can improve the hope of more exchanges.

For MySQL, we can not ignore its strong, MySQL has a lot to learn, and then since the daily dynamic generation of record tables, then must be a job, to follow a certain time rule to clear or back up the database table, the next is to study MySQL job, slowly, There's a lot to learn.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MySQL stored procedures dynamically create tables and insert data