eharetea

Add or modify columns of a table using the ALTER statement in MySQL.

Modify the structure of a table in MySQL.

Date Created:Friday December 29th, 2006 03:41 AM
Date Modified:Friday August 01st, 2008 01:15 AM

# rename a table
ALTER TABLE comment RENAME AS comments

# same as:
RENAME TABLE comment TO comments

# add column
ALTER TABLE comments ADD COLUMN comment_type VARCHAR(25)

# add column2 after column1
ALTER TABLE comments ADD COLUMN column2 VARCHAR(25) AFTER column1;

# change the engine of a table:
ALTER TABLE comments ENGINE = MYISAM
ALTER TABLE comments ENGINE = INNODB
ALTER TABLE comments ENGINE = MEMORY # no auto increment

# add column
ALTER TABLE folders ADD COLUMN date_created DATETIME;

# modify column
ALTER TABLE folders MODIFY COLUMN date_created DATETIME NOT NULL;
ALTER TABLE users MODIFY COLUMN status tinyint(1) NOT NULL;

#delete a column
ALTER TABLE folders DROP COLUMN status;