CODEWITHSUNDEEP
mysql
Zombified Head
Zombified Head

Reputation: 3

How do I create a table name in MySQL using a select?

I'm building a MySQL event to make a copy of a table in the database with a timestamp in the name. 

CREATE  TABLE  `db_name`.`tbl_prefix_(SELECT TO_SECONDS(NOW()))` ( [the rest...]

Obviously this isn't working. What should I do to make it work?

Any suggestions are welcome.

Thanks

Upvotes: 0

Views: 62

Answers (2)

spencer7593
spencer7593

Reputation: 108370

In order to do this, you would need to use "dynamic SQL". That is, make use of the MySQL PREPARE statement.

What you'd need to do is populate a variable with a string that contains the SQL text you want to execute. Doing variable substitution into string is trivial.

The "trick" is to take that dynamic string and execute it like it was a SQL statement.

And that's what the PREPARE statement does for us, takes in a variable, and reads the contents of that variable like it were a SQL statement.

With that said, rather than give an example code that demonstrates this in more detail, I'm going to suggest that you re-think this idea of creating a table with timestamp value as part of the name.

What problem is that designed to solve? And carefully consider whether the proposed design for a solution will introduce a bigger problem than it solves.

Upvotes: 0

kba
kba

Reputation: 19466

This is a bad architecture. Generating tables on the fly is not something you should do.

Instead, create a single table with a timestamp column. For instance, if you would before have 3 tables with three timestamps A, B, and C, you now have one table with a timestamp column containing the values A, B, and C, respectively.

Upvotes: 1

Related Questions