Databases Reference
In-Depth Information
If you want it added in a specific position, use the
AFTER
keyword:
mysql>
ALTER TABLE artist ADD formed YEAR AFTER artist_id;
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql>
SHOW COLUMNS FROM artist;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| artist_id | smallint(5) | | PRI | 0 | |
| formed | year(4) | YES | | NULL | |
| artist_name | char(64) | YES | | Unknown | |
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
To remove a column, use the
DROP
keyword followed by the column name. Here's how
to get rid of the newly added
formed
column:
mysql>
ALTER TABLE artist DROP formed;
Query OK, 6 rows affected (0.02 sec)
Records: 6 Duplicates: 0 Warnings: 0
This removes both the column structure and any data contained in that column. It also
removes the column from any index it was in; if it's the only column in the index, the
index is dropped, too. You can't remove a column if it's the only one in a table; to do
this, you drop the table instead as explained later in “Deleting Structures.” Be careful
when dropping columns; you discard both the data and the structure of your table.
When the structure of a table changes, you will generally have to modify any
INSERT
statements that you use to insert values in a particular order. We described
INSERT
statements in “The INSERT Statement” in Chapter 5.
MySQL allows you to specify multiple alterations in a single
ALTER TABLE
statement by
separating them with commas. Here's an example that adds a new column and adjusts
another:
mysql>
ALTER TABLE artist ADD formed YEAR, MODIFY artist_name char(256);
Query OK, 6 rows affected, 1 warning (0.08 sec)
Records: 6 Duplicates: 0 Warnings: 0
It's very efficient to join multiple modifications in a single operation, as it potentially
saves the cost of creating a new table, copying data from the old table to the new table,
dropping the old table, and renaming the new table with the name of the old table for
each modification individually.
Adding, Removing, and Changing Indexes
As we discussed previously, it's often hard to know what indexes are useful before the
application you're building is used. You might find that a particular feature of the
application is much more popular than you expected, causing you to evaluate how to
improve performance for the associated queries. You'll therefore find it useful to be
