Add a column to an existing MySQL table

Contributor Icon Contributed by qmchenry Date Icon February 8, 2004  
Tag Icon Tagged: MySQL

MySQL tables are easy to extend with additional columns.


To add a column called email to the contacts table created in Create a basic MySQL table with a datatype of VARCHAR(80), use the following SQL statement:

ALTER TABLE contacts ADD email VARCHAR(60);

This first statement will add the email column to the end of the table. To insert the new column after a specific column, such as name, use this statement:

ALTER TABLE contacts ADD email VARCHAR(60) AFTER name;

If you want the new column to be first, use this statement:

ALTER TABLE contacts ADD email VARCHAR(60) FIRST;

Previous recipe | Next recipe |
 
  • Thanks. it helped me.
  • Philip
    Wow, that's kind of stupid that there's no before.
  • James
    Why do you need a before? If its the first one, you use first, if its the second one, you use after first.
  • ally
    wow, you have no idea what you're talking 'bout
  • fubar
    nice
  • Thus why philip will never design anything worth while. Positive thinking is needed to create great things .... :P
  • Parkash
    Wow what a solution!
  • KG Old Wolf
    Actually "HEAD FIRST SQL" (book by O'Rielly publications) states there is "BEFORE, AFTER, FIRST, SECOND, THIRD" but I can only get FIRST and AFTER to work using MySQL 5.1

    That's why I came here for a different opinion.

    I think the book is wrong :(
  • BaZ
    Try looking at MySQL refman page:
    http://dev.mysql.com/doc/refman/5.1/en/alter-ta...
    The location, if present, must be "FISRT" or "AFTER column", there is no other option.

    If the book is about MySQL 5.1 its really wrong, but isn t it about ANSI SQL or anything else?
  • bob
    This is just perfect, I use it all the time ... thx!
  • gb
    Thanks!
  • Thanks, exactly what I was looking for!
  • mike
    "...with a datatype of VARCHAR(80), use the..." then you say "...ADD email VARCHAR(60)" what i'm getting at here is 80 then 60. small typo.
  • @KG - SQL comes in many flavors. PregreSQL and MySQL and MS SQLServer all use slightly different variations on SQL, with varying support for various language constructs. I can only assume they are all derived from some original SQL language specification.
  • kannika
    I want to learn about the space that data take in mysql
  • Thanks for that info, that really helps me to create my first ALTER ADD COLUMN =)
  • grvijaya
    good example
  • milnd
    thanks
blog comments powered by Disqus