Insert new column into table

[fowlerlfc]

In mysql, How would I go about inserting a new column into an existing table?

 

[sleipnir214]

The command format is

ALTER TABLE <tablename> ADD <column declaration> [FIRST | AFTER <existing column name>]

An example:

mysql> describe foo;
+---------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+-------+
| id | tinyint(3) unsigned | | PRI | 0 | |
| keyword | varchar(25) | YES | | NULL | |
+---------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table foo add id2 int unsigned after id;
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> describe foo;
+---------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+-------+
| id | tinyint(3) unsigned | | PRI | 0 | |
| id2 | int(10) unsigned | YES | | NULL | |
| keyword | varchar(25) | YES | | NULL | |
+---------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

 

[fowlerlfc]

Thanks for the awesome and descriptive post!

Worked brilliantly!

 

[fowlerlfc]

Two more questions, I have a table that has an unneeded column in it. How would I remove it?

I also have a field that is set at 7 characters long, but I need to change it to 8 characters long.

Any help would be greatly appreciated!

 

[piti]

hi
so removing a column is almost the same as adding one:

ALTER TABLE <table_name> DROP [COLUMN] <column_name>

and the sec. prob

ALTER TABLE <table_name> MODIFY [COLUMN] <column definition>
or
ALTER TABLE <table_name> CHANGE [COLUMN] <column_name> <column definition>

in your case <column definition> == column_name varchar(8) (or something similar)

hope that helps