Altering Structures | 215

mysql> ALTER TABLE artist MODIFY artist_name CHAR) DEFAULT "Unknown";
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates 0 Warnings You can also do this with the
CHANGE
keyword, but by specifying the same column name twice:
mysql> ALTER TABLE artist CHANGE artist_name artist_name CHAR) DEFAULT "Unknown";
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates 0 Warnings Be careful when you’re modifying types Don’t use incompatible types, since you’re relying on MySQL to successfully convert data from one format to another (for example, converting an
INT
column to a
DATETIME
column isn’t likely to do what you hoped Don’t truncate the data unless that’s what you want. If you reduce the size of a type, the values will be edited to match the new width, and you can lose data.
Suppose you want to add an extra column to an existing table. Here’s how to do it with the ALTER TABLE
statement:
mysql> ALTER TABLE artist ADD formed YEAR;
Query OK, 6 rows affected (0.02 sec)
Records: 6 Duplicates 0 Warnings You must supply the
ADD
keyword, the new column name, and the column type and clauses. This example adds the new column, formed, as the last column in the table, as shown with the SHOW COLUMNS
statement:
mysql> SHOW COLUMNS FROM artist;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| artist_id | smallint(5) | | PRI | 0 | |
| artist_name | char) | YES | | Unknown | |
| formed | year) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
3 rows inset sec)
If you want it to instead be the first column, use the
FIRST
keyword as follows:
mysql> ALTER TABLE artist ADD formed YEAR FIRST;
Query OK, 6 rows affected (0.04 sec)
Records: 6 Duplicates 0 Warnings 0
mysql> SHOW COLUMNS FROM artist;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| formed | year) | YES | | NULL | |
| artist_id | smallint(5) | | PRI | 0 | |
| artist_name | char) | YES | | Unknown | |
+-------------+-------------+------+-----+---------+-------+
3 rows inset sec)

Download 4.24 Mb.

Share with your friends: