Changing a Column Definition or Name

8.3.1 Problem

You want to change how a column is defined.

8.3.2 Solution

Use MODIFY or CHANGE. MODIFY is simpler, but cannot change the column name. CHANGE is more confusing to use, but can change both the name and the definition.

8.3.3 Discussion

To change a column's definition, use MODIFY or CHANGE.[1] Of the two, MODIFY has the simpler syntax: name the column, then specify its new definition. For example, to change column c from CHAR(1) to CHAR(10), do this:

[1] MODIFY requires MySQL 3.22.16 or later.

ALTER TABLE mytbl MODIFY c CHAR(10);

With CHANGE, the syntax is a bit different. After the CHANGE keyword, you name the column you want to change, then specify the new definition, which includes the new name. The second column name is required, because CHANGE also allows you to rename the column, not just change its definition. For example, to change i from INT to BIGINT and rename it to j at the same time, the statement looks like this:

ALTER TABLE mytbl CHANGE i j BIGINT;

If you now use CHANGE to convert j from BIGINT back to INT without changing the column name, the statement may look a bit odd:

ALTER TABLE mytbl CHANGE j j INT;

At first glance, the statement seems incorrectthe column name appears to be given one too many times. However, it's correct as written. The fact that the CHANGE syntax requires two column names (even if they're both the same) is simply something you have to get used to. This is especially important to remember if your version of MySQL is old enough that you can't use MODIFY. Any ALTER TABLE statement that uses MODIFY col_name can be replaced by one that uses CHANGE col_name col_name. That is, the following two statements are equivalent:

ALTER TABLE tbl_name MODIFY col_name ... ; ALTER TABLE tbl_name CHANGE col_name col_name ... ;

It would be nice to have a form of the ALTER TABLE statement that renamed a column without the need to repeat the definition, especially for working with ENUM and SET columns that have many member values. Unfortunately, there is no such statement, which makes these column types somewhat difficult to work with when using ALTER TABLE. Suppose you add to mytbl an ENUM column e that has several members:

ALTER TABLE mytbl ADD e ENUM('hardware','software','books','office supplies', 'telecommunications','furniture','utilities', 'shipping','tax');

If you want to rename the column from e to e2, you use CHANGE to indicate the new name. But you must also repeat the column definition as well:

ALTER TABLE mytbl CHANGE e e2 ENUM('hardware','software','books','office supplies', 'telecommunications','furniture','utilities', 'shipping','tax');

Ugh. That's too much typing. Manually entering the proper ALTER TABLE statement for this kind of operation is quite a lot of work, not to mention error-prone. One way to avoid retyping the definition is to capture the current definition in a file and edit the file to produce the proper ALTER TABLE statement:

• Run mysqldump to get the CREATE TABLE statement that contains the column definition:

  % mysqldump --no-data cookbook mytbl > test.txt

  The resulting file, test.txt, should contain this statement:

  CREATE TABLE mytbl ( c char(10) default NULL, j bigint(20) NOT NULL default '100', e enum('hardware','software','books','office supplies','telecommunications', 'furniture','utilities','shipping','tax') default NULL ) TYPE=MyISAM;

  The --no-data option tells mysqldump not to dump the data from the table; it's used here because only the table creation statement is needed.

• Edit the test.txt file to remove everything but the definition for the e column:

  e enum('hardware','software','books','office supplies','telecommunications', 'furniture','utilities','shipping','tax') default NULL

• Modify the definition to produce an ALTER TABLE statement with a semicolon at the end:

  ALTER TABLE mytbl CHANGE e e2 enum('hardware','software','books','office supplies','telecommunications', 'furniture','utilities','shipping','tax') default NULL;

• Write test.txt back out to save it, then get out of the editor and feed test.txt as a batch file to mysql:

  % mysql cookbook < test.txt

For simple columns, this procedure is more work than just typing the ALTER TABLE statement manually, of course. But for ENUM and SET columns with long and ungainly definitions, using an editor to create a mysql batch file from mysqldump output makes a lot of sense. You can also use this technique to make it easier to reorder the items in an ENUM or SET column, or to add or delete members from the column definition. For another approach to column manipulation, see Recipe 9.9, which develops a utility script that makes it trivial to add member values. The script examines the table structure and uses that information to figure out the proper ALTER TABLE statement for modifying an ENUM or SET column.