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.

Add new comment

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

Anonymous format

  • Allowed HTML tags: <em> <strong> <cite> <code> <ul type> <ol start type> <li> <pre>
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.