MySQL in a Nutshell [54]
ALTER TABLE: CHANGE clauses
ALTER [IGNORE] TABLE table
ALTER [COLUMN] column {SET DEFAULT value|DROP DEFAULT} |
CHANGE [COLUMN] column column definition [FIRST|AFTER column] |
MODIFY [COLUMN] column definition [FIRST|AFTER column]
These three clauses are used to alter an existing column in a table. The first syntax structure is used either to set the default value of a column to a particular value or to reset it back to its default value for its column type (usually NULL or 0). The other two syntax structures are used primarily to change the column definitions. The COLUMN keyword is optional and has no effect.
To change the column’s character set, add CHARACTER SET to the end of the column definition for the CHANGE or MODIFY clauses, followed by the character set name to use. Here’s an example of the first clause:
ALTER TABLE clients
ALTER COLUMN city SET DEFAULT 'New Orleans';
This statement sets the default value of the city column in the clients table to a value of New Orleans, because that’s where most of the clients are located.
The clauses that change column definitions are roughly synonymous; they follow the standards of different SQL systems for the sake of compatibility (e.g., MODIFY is used with Oracle). They can also be used to relocate the column within the table schema with the FIRST or the AFTER keywords. If a column’s data type is changed, MySQL attempts to adjust the data to suit the new data type. If a column width is shortened, MySQL truncates the data and generates warning messages for the affected rows. Indexes related to changed columns will be adjusted automatically for the new lengths.
In the CHANGE clause, the current column name must be specified first, followed by either the same column name if the name is to remain the same, or a new column name if the name is to be changed. The full column definition for the column must be given as well, even if it’s not to be changed.
The MODIFY clause cannot be used to change a column’s name, so the column name appears only once with it.
The following SQL statement shows the columns in the clients table, where the column name begins with a c and contains an i to list the columns that begin with either client or city. After viewing these limited results, we change one column using each of the clauses for changing column definitions:
SHOW COLUMNS FROM clients LIKE 'c%i%';
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| client_id | int(11) | NO | PRI | NULL | auto_increment |
| client_name | varchar(255) | YES | MUL | NULL | |
| city | varchar(255) | YES | | NULL | |
| client_zip | char(10) | YES | | NULL | |
| client_state | char(2) | YES | | NULL | |
+--------------+--------------+------+-----+---------+----------------+
ALTER TABLE clients
CHANGE COLUMN city client_city VARCHAR(100) CHARACTER SET 'latin2',
MODIFY COLUMN client_state CHAR(4) AFTER client_city;
After looking at the current columns, we’ve decided to change the name of the city column to client_city to match the other related columns, and to enlarge the client_state column and move it before the column for the postal ZIP code. To do this, the CHANGE clause is used to change the name of the city column, but not its column type and size. The second clause changes the column type and size and relocates the client_state column to a position after the client_city column.
When a column is changed, MySQL will attempt to preserve the data. If a column size is reduced, the data won’t be completely deleted, but it may be truncated, in which case the results will show a number of warnings. Use the SHOW WARNINGS statement to view them.
ALTER TABLE: DROP column clause