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.