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 PhpMyAdmin 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 add a text field (varchar):

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

And although I wouldn’t do it for this specific example, here’s how you 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.