2

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.)

0

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.

  • Thanks, that sounds great! Sorry what is volumetry? – Eilidh Apr 28 '14 at 9:19
  • The volume of data in your table. – Maxime Fouilleul Apr 28 '14 at 9:20
  • Okay. Thank you. I thought so but just wanted to check. How does it depend on the volume of data in my table? At what point would it be a good idea to do something else? It is a rather large table (~4 million rows) and will likely be split into three tables of ~1-~1.5 million rows each. – Eilidh Apr 28 '14 at 9:21
  • Response in my post – Maxime Fouilleul Apr 28 '14 at 9:27
  • Thank you very much for your help, I really appreciate it. I will select your answer once the site allows me to do so. Thank you! – Eilidh Apr 28 '14 at 9:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.