Learning Mysql

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 You can see that MySQL processes and alters each row. What actually happens behind the scenes is that MySQL creates anew 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 | |
| last_played | timestamp | YES | PRI | CURRENT_TIMESTAMP | |
+----------------+-------------+------+-----+-------------------+-------+
4 rows inset sec)
In the previous example, you can see that we provided four parameters to the
ALTER
TABLE
statement with the
CHANGE
keyword:
1. The table name, played. The original column name, played. The new column name, last_played
4. The column type,
TIMESTAMP
You must provide all four that means you need to respecify the type and any clauses that go with it. In the previous example, it just happens that the
TIMESTAMP
type defaults to:
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
If you want to rename the artist_name column to artist-name
, you would write:
ALTER TABLE artist CHANGE artist_name artist-name CHAR) DEFAULT NULL;
If you want to modify the type and clauses of a column, but not its name, you can use the
MODIFY
keyword:

Download 4.24 Mb.

Share with your friends: