O'Reilly logo

Learning MySQL by Hugh E. Williams, Seyed M.M. Tahaghoghi

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required