Home » Database » How to modify the structure of a MySQL table

How to modify the structure of a MySQL table



Need to modify or alter the structure of a MySQL table that you created a while ago? Before you carry out the changes you must know that if you haphazardly change the structure of your MySQL table you might lose your data. So make sure you know what you are doing.

You may lose your data while making the following changes in your MySQL table:

  • By altering the type — from integer to text or some other type
  • By deleting the column

Now coming back to altering the structure.

Suppose a while ago you created a MySQL table called “tourists” with the following structure.

name varchar(255)
destination varchar(255)
id integer not null auto_increment

Then you realized the name should in fact be in two separate fields, viz. first_name and last_name. Now whatever data is there in the current name field you can have it in the new first_name field and in the new last_name field you can manually enter the new values. But let us see how you can change the name to first_name and last_name:

ALTER TABLE ‘tourists’ CHANGE ‘name’ ‘first_name’ VARCHAR(255);
ALTER TABLE ‘tourists’ ADD COLUMN ‘last_name’ VARCHAR(255);

We have in fact executed here two commands simultaneously to change the structure of our MySQL table.

This is highly improbable but you can also change the type of a field. Let’s say you want name to be an integer. You change it like this:

ALTER TABLE ‘tourists’ CHANGE ‘name’ ‘name’ INTEGER NOT NULL

You can also use MODIFY to change a column definition:

ALTER TABLE ‘tourists’ MODIFY ‘name’ INTEGER NOT NULL