Changing a Column Definition or Name

Problem

You want to change how a column is defined.

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.

Discussion

To change a column’s definition, use MODIFY or CHANGE.[35] 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:

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 incorrect—the 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 ...

Get MySQL Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.