1. Description
When it comes to using data, we need a lot of databases, and we want to use time to make table names to differentiate. However, MySQL does not support the use of variable names for table names or column names in stored procedures.
For example, there is a table we want to name "2015-07-16 12:00:00".
2. Solution
If you just change a normal table name, it's easy to use the following SQL statement:
alter table old_table_name rename new_table_name
But to use time as the table name, the dynamic naming of the words will not be. First we can get the current time of the system with the now () function. To find a way to support dynamic database operations in the form of variables, the prepare statement can support such operations in versions above MySQL5.1.
We can use set @var=...
the set variable, and then use prepare stml from @var
the dynamic SQL statement, and finally EXECUTE stml;
execute the statement.
The following is a SQL execution process that dynamically modifies a table with a time table name:
@asql=concat(‘alter table old_table_name rename `‘,NOW(),‘`‘);@asql;EXECUTE stml;
Here we use the concat function in MySQL, the function of which is string concatenation. I believe that the students who know how to program are not unfamiliar with it.
How to use:
Concat (STR1,STR2,...)
Returns the string that results from the connection parameter. Note If any one of the arguments is NULL, the return value is NUL.
Attached with a knot running Fruit chart:
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
MySQL Dynamic SQL statement execution time table name