MySQL alter table add column syntax examples

MySQL alter table FAQ: Can you share some examples of how to use the MySQL ALTER TABLE syntax, especially the ADD COLUMN syntax.

A MySQL 'ALTER TABLE ADD COLUMN' example

Sure, here's a MySQL ALTER TABLE ADD COLUMN example I just ran through. I started with a MySQL database table named ideas:

mysql> desc ideas;
+------------------------+--------------+------+-----+-------------------+----------------+
| Field                  | Type         | Null | Key | Default           | Extra          |
+------------------------+--------------+------+-----+-------------------+----------------+
| id                     | int(11)      | NO   | PRI | NULL              | auto_increment |
| category_id            | int(11)      | NO   | MUL | NULL              |                |
| idea                   | varchar(200) | NO   |     | NULL              |                |
| comments               | text         | YES  |     | NULL              |                |
| date_created           | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
+------------------------+--------------+------+-----+-------------------+----------------+

This table already exists, and I wanted to add a new table column named "priority". I could have added it like this:

alter table ideas add priority int;

but I wanted it to appear right after the existing "idea" column, so instead I ran my MySQL ALTER TABLE ADD COLUMN query like this:

mysql> alter table ideas add priority int after idea;
Query OK, 70 rows affected (0.52 sec)
Records: 70  Duplicates: 0  Warnings: 0

As you can see it updated 70 rows in the table. Now my MySQL database table looks like this:

mysql> desc ideas;
+------------------------+--------------+------+-----+-------------------+----------------+
| Field                  | Type         | Null | Key | Default           | Extra          |
+------------------------+--------------+------+-----+-------------------+----------------+
| id                     | int(11)      | NO   | PRI | NULL              | auto_increment |
| category_id            | int(11)      | NO   | MUL | NULL              |                |
| idea                   | varchar(200) | NO   |     | NULL              |                |
| priority               | int(11)      | YES  |     | NULL              |                |
| comments               | text         | YES  |     | NULL              |                |
| date_created           | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
+------------------------+--------------+------+-----+-------------------+----------------+

As you can see, the new "priority" column is right where I want it.

(Note that I ran all those MySQL commands after logging into my MySQL server and then accessing the database I wanted to alter. You can also run these commands through a GUI or web interface, like the PHPMySqlAdmin tool.)

Other MySQL ALTER TABLE ADD COLUMN examples

Before I go back to work, here are some other MySQL ALTER TABLE ADD COLUMN examples, showing different approaches. First, my original idea:

alter table ideas add priority int;

Next, making sure I put the new column exactly where I want it:

alter table ideas add priority int after idea;

Here's a similar alter table add column query, but this time specifying a default value for my integer field:

alter table ideas add priority int default 5 after idea;

Here's how you'd add a text field (varchar):

alter table ideas add short_desc varchar(32) after priority;

And although I wouldn't do it for this example, here's how you'd make a new column the first column in a table with the "first" keyword:

alter table ideas add foobar int first;

For more information on the MySQL ALTER TABLE ADD COLUMN syntax, see the MySQL ALTER TABLE documentation.

Post new comment

The content of this field is kept private and will not be shown publicly.