Learning MySQL

Altering Structures

We’ve shown you all the basics you need for creating databases, tables, indexes, and columns. In this section, you’ll learn how to add, remove, and change columns, databases, tables, and indexes in structures that already exist.

Adding, Removing, and Changing Columns

You can use the ALTER TABLE statement to add new columns to a table, remove existing columns, and change column names, types, and lengths.

Let’s begin by considering how you modify existing columns. Consider an example in which we rename a table column. The played table has a column—also called played—that contains the time the track was played. To change the name of this column to last_played, you would write:

mysql> ALTER TABLE played CHANGE played last_played TIMESTAMP;
Query OK, 12 rows affected (0.03 sec)
Records: 12  Duplicates: 0  Warnings: 0

You can see that MySQL processes and alters each row. What actually happens behind the scenes is that MySQL creates a new table with the new structure, copies the data into that table, removes the original played table, and renames the table to played. You can check the result with the SHOW COLUMNS statement:

mysql> SHOW COLUMNS FROM played; +----------------+-------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+-------------+------+-----+-------------------+-------+ | artist_id | smallint(5) | | PRI | 0 | | | album_id | smallint(4) | | PRI | 0 | | | track_id | smallint(3) | | PRI | 0 | | ...