MySql stored procedure dynamically creates a table and inserts data _ MySQL-mysql tutorial

The MySql stored procedure dynamically creates a table and inserts data to a recently created Project. The database uses MySql, which is not very skillful in MySql, but can be simply used, after all, simple SQL statements are still the same, but as the project goes deeper into complicated SQL statements, MySql stored procedures are gradually used to dynamically create tables and insert data.

In a recent project, the database uses MySql, which is not very skilled in MySql, but simple application. after all, simple SQL statements are still the same, however, as the project goes deeper into complex SQL statements, one of the minor difficulties is to dynamically create a table based on the date of the day and insert data into it.

The specific logic can be understood as: first go to the database to check whether there is a record table of the day, if not, create a table, and then execute the log writing of the day.

Mysql visualization tools we use navicat, which is relatively easy to use. of course, it is not as user-friendly as sqlserver (many shortcut keys are not supported, and the operation is cumbersome ). Next I will talk about how to use navicat to dynamically create database tables and insert data.

I. create a stored procedure

II. use the concat function to concatenate and execute statements for dynamic table creation

III. use the concat function to concatenate and execute a statement to write data to a dynamically created table.

Because database tables are in the form of a fixed prefix and the current date, no matter whether you create a table or write data, it is based on the dynamic table, therefore, you cannot write simple insert statements like the original fixed table. In addition, the parameter id passed to the stored procedure cannot be directly passed to the stored insert statement, and an SQL statement error will be reported, therefore, it may not be the best way to introduce local parameters, but the author has not yet found a better way to write the code. if you have any better ideas or want to improve your skills, we hope to talk more.

For mysql, we can't ignore its power. mysql still has many things to learn. since the record table is generated dynamically every day, a job is required, to clear or back up database tables according to certain time rules. next we will study mysql job.