[UPHPU] mysql modify vs. change

[Brandon Stout]

Wade Preston Shearer wrote:

> It appears that you can change a MySQL column's data type with both 
> 'modify' and 'change'. What is the difference between the two? In my 
> notes, I had 'modify' down to use for altering a data type and 
> 'change' for renaming a column. phpMyAdmin used 'change' to change a 
> column's data type though. Are the two synonymous?

I believe you are talking about ALTER TABLE, MODIFY, CHANGE, and ALTER
are each ALTER specifications.  ALTER TABLE syntax:

ALTER [IGNORE] TABLE tbl_name
  alter_specification [, alter_specification] ...

alter_specification also includes ADD, DROP, ENABLE or DISABLE KEYS,
RENAME, ORDER BY, and character set conversion clauses, but I'll just
list the ALTER, CHANGE, and MODIFY clause syntax here, with reference at
the bottom:

| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name new_col_name column_definition
  [FIRST|AFTER col_name]
| MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

CHANGE col_name, DROP col_name, and DROP INDEX are MySQL extensions to
standard SQL.
MODIFY is an Oracle extension to ALTER TABLE.

It appears you can use both ALTER and CHANGE to modify a column data
type (called column_definition in the above clauses).  It's odd that
phpMyAdmin used CHANGE to change the data type because you must still
specify both old and new column names even though both are the same.  A
human should probably use MODIFY to change the column data type without
accidentally renaming it.

Reference:

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

Brandon Stout
http://mscis.org