Changing columns in MySQL

From Shrubbery

Cheat sheet for changing columns in MySQL

[edit] Rename a Column

alter table {table_name} change {old_col_name} {new_col_name} [col_definition]

[edit] Change column type

alter table {table_name} modify {col_name} {new_col_definition}

or

alter table {table_name} change {col_name} {col_name} {new_col_definition}

[edit] Adding a 'milliseconds' column

If you have a table that has a TIMESTAMP column, and you want to add milliseconds (e.g. Java System.currentTimeMillis()) you might need to back-populate the new column for the existing rows. The UNIX_TIMESTAMP function will do the job of converting the TIMESTAMP values into 'millis since the epoch'.

alter table {table_name} add column ({col_name} BIGINT(20));
update {table_name} set {col_name} = UNIX_TIMESTAMP({old_col_name});

Now, a not-null constraint can be added if necessary:

alter table {table_name} modify {col_name} BIGINT(20) not null;