There exists in this world a nice little SQL statement know as the SELECT INTO statement, one that works beautifully well in most database systems for when you want to create a backup of an existing table. In MySQL however this doesn’t work straight out of the box, which of course is a pain in the ass.
So how does one duplicate this functionality in MySQL then?
Well funnily enough, it’s actually pretty damn simple. The CREATE TABLE IF NOT EXISTS phrase is key here and combining this with a select statement will in fact create a copy of your existing table, taking all the data from your source table and dumping it into your newly created clone table. (Note that you can control what data gets copied into the new table by modifying the SELECT statement with an appropriate WHERE statement as well as the columns that get created by specifying column names in the first part of the SELECT statement in place of the asterisk).
So for example: “CREATE TABLE IF NOT EXISTS `my_backup_table` SELECT * FROM `my_table`”
…will create the `my_backup_table` if it doesn’t exist and copy over all data currently contained in `my_table`.
Pretty useful, no?
It is however important to note that this process does come with a few drawbacks. Firstly, attribute data like primary keys, comments, etc. gets lost in the process. Also, don’t expect any associated triggers to make the trip either. Finally, certain default values like CURRENT_TIMESTAMP gets converted to 0000-00-00 00:00:00 as well, just to add insult to injury.
But then, if this backup table really is just about keeping the existing data safe, then I guess this really shouldn’t matter all that much to you in the first place! :)
You might also enjoy:
-
To check if a column exists in a table with SQL using a MySQL database is pretty easy, thanks to the nifty SHOW COLUMNS command. To find if a specific co ... -
A simple question for today: how does one go about preventing duplicate rows based on the value held by more than one column being inserted into an existing ... -
To get a list of user accounts on a MySQL server instance, we need to make a SELECT call to the mysql.user table. First, fire up MySQL in your terminal ...
-
To check if a table exists with SQL in a MySQL database is pretty easy, thanks to the nifty SHOW TABLES command. To find if a specific table exists, we s ...
-
Sometimes it is quite handy to see which records based on a particular key appear more than once in a database. The trick here is that we are focusing on a ...
