Mysql – Moving data from an old table into a newly created table with different field names / number of fields

MySQLphpmyadmin

I've been handed over management of a database, and I have had very little exposure to MySQL (or databases in general) so far.

I would like to do two things, both of which are (I expect) fairly simple, however it is the combination of the two which is causing me difficulty.

At present, I have one very large table containing all data. It can easily be divided into three smaller tables, as one of the fields effectively has only three distinct values, by which I wish to separate it. I assume writing a query to find entries of each value and copy only the correct one across to a new table would be very simple.

However, my problem is that the fields in this table are very, very poorly named and misspelled. So, when I create my new tables I would like to rename many of the field names. I will also potentially need to add fields.

Is there a way to achieve this so that I may create new tables with correctly named fields and then copy the data across without any intermediary steps?

The two obvious (though very clumsy) ways to do this that I can see would be to –

  1. Copy the current table, rename all the fields to match the newly designed tables, and then copy the data across since the field names match. (This is not ideal as the current table is very large, and the issue of additional fields in the new tables may still cause issues?)

  2. Create the new tables with the same poor field names as the original. Copy the data across. Change the new table's field names.

Both these potential methods seem very clumsy. Is there a way to do this that simply lets me create a new table with correct field names and then transfer the old data easily at any time?

(Please forgive me if this is an obvious or awkward question, I'm very, very new to this. Thank you.)

Best Answer

It depends of your volumetry but you can use a simple INSERT SELECT statement.

You old table:

mysql> desc old_table;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| fname | varchar(255) | YES  |     | NULL    |       |
| lname | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Imagine you want to replace fname by firstname, lname by lstname and add a birthdate field.

Create your new structure:

CREATE TABLE new_table (
    id int(11), 
    firstname varchar(255), 
    lastname varchar(255), 
    birthdate DATETIME DEFAULT NULL
);

Now, transfert the old data in the new table, note that the new fields (here Birthdate) will be empty:

INSERT INTO new_table (id, firstname, lastname) SELECT id, fname, lname FROM old_table;

It's just a generic example, if you want for specific one, please add your tables structures in your question.

Max.

Edit for question "At what point would it be a good idea to do something else? ": The INSERT SELECT statement locks (write lock) your table to enforce data consitency during operation. I can't answered to the question "at what point" it depends of your server, configs... But imagine your have a table with 1 Million rows, the operation could take some seconds, we'll say 4 seconds for example, so for 4 seconds, your users will not write in your table.

Related Question