Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from MySQL Cookbook

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

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata