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 ...