Add a column to an existing MySQL table

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;

 

About Quinn McHenry

Quinn was one of the original co-founders of Tech-Recipes. He is currently crafting iOS applications as a senior developer at Small Planet Digital in Brooklyn, New York.
View more articles by Quinn McHenry

The Conversation

Follow the reactions below and share your own thoughts.

  • http://ynilesh.blogspot.com nilesh

    Thanks. it helped me.

    • Prasath

      THANKS

  • 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.

      • Better Reader

        Because it increases the inherent flexibility that (My)SQL is supposedly famous for, obviously.

        • http://www.facebook.com/profile.php?id=1523937551 Marcus Longwell

          Except there is no need for it anyways, obviously.

          • http://www.forkoff.co.uk/ Spam Banjo

            Um, yes there is.

    • ally

      wow, you have no idea what you’re talking ’bout

    • liftari

       I agree with you,  I’m not an expert in mysql, but I’m working on a database and needed to add a new column and “before” googling I tried with it xD

      • MEIAM

        > Um, if you have an AFTER then you can ADD a column AFTER the first – last and if you want it at the first position then you use FIRST.
        Why is that so hard for people to get? :s

        • kat

          Totally agree!

  • fubar

    nice

  • http://www.kazby.com/ Mike

    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-table.html
      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!

  • http://www.ubooboo.net/ gnujack

    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.

  • http://www.siliconrockstar.com/ andy

    @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.

  • Anonymous

    I want to learn about the space that data take in mysql

  • http://www.englishelp.ru/ English

    Thanks for that info, that really helps me to create my first ALTER ADD COLUMN =)

  • Anonymous

    good example

  • Anonymous

    thanks

  • http://www.mioot.com sasimkumar

    Thanks for the information..

  • Anonymous

    How do you add a value to the new column?

    • James Saunderson

      ALTER TABLE `mydb`.`mytable` ADD COLUMN `col2` TINYINT(1) DEFAULT ’0′ AFTER `col1`

  • Anonymous

    First time in this site. It seems helpful

  • Anonymous

    This is helped me a lot!!!

  • Anonymous

    This site is helpful i like it!

  • Anonymous

    Does this site have a Facebook group i would to join it coz it helped me big tym!

  • Anonymous

    Can we then at least have a “between” if we can’t have a before … ;-) ;-), wow I like more prepositions …

    Quick ‘n easy explanation, qmchenry, thanks!

  • Anonymous

    Hey

    Any of you guys know how to change existing column with string type to binary format including data?

    Sam
    System Admin
    flip minohd camcorder

  • http://anglopolish.com/ dani

    very useful article. Thanks for writing it.

  • http://www.reviewsofregistrycleaners.com/ Danny

    Very useful one! Thanks a lot

  • Anonymous

    how to add two colomns in a existing database table?
    For example i want to add first_name,last_name column after serial_no column. it should be added in a single script

    • James Saunderson

      ALTER TABLE `mydb`.`mytable` ADD COLUMN `col2` VARCHAR(50) AFTER `col1`, ADD COLUMN `col3` TINYINT(1) DEFAULT ’0′ AFTER `col2`;

  • Anonymous

    how to add multiple columns using single query to a existing databae table

    • maddin

      ALTER TABLE contacts ADD email VARCHAR(60) , ADD email_2 VARCHAR(60) , ADD email_3 VARCHAR(60) ………

  • test

    not getting

    • Viji_jyotika

      then go

  • Anonymous

    good, i used and it’s correct

  • http://3rlend.com/ 3rlend

    Helped me, but if you have a table with users, many users, how to chose the default value for all the users?
    For instance, I add a column “logged in”, but I want it to be “false” in every row.
    How do I do that?

    • http://3rlend.com/ 3rlend

      I just found it out.
      ALTER TABLE table1 ADD column4 Default ‘jeje’;

  • Rein

    thanks

    • Kinathukadavuu

      thanks

  • Chandru

    please give the suggestion for upload a file into the mysql database

  • yusuf

    thanks for that recipe, you know what? the web doesn’t stink after all… By the way Philip you don’t need a before because you’ve got a first… Get it “first”.. thanks tech-recipes.com you’re not a life safer but you’re cool.

  • http://www.rhythmgraphic.com Raj

    Thanks it good for new person in mysql

  • beyo

    thanks a lot

  • Shariqnittt

    Thanks

  • Dheerajchouhan85

    thanks bro

  • Jhanine_0692

    hauhuhu

  • Mackensz1

    Thanks.. IT helps me

  • Guest

    Great, thank you

  • Manoj

    thanks alot

  • Thomas

    hello, thanks but how can I alter multiple tables at once using wildcard, tables ends with _test (001_test, 002_test, 003_test, ..).

    I trie this but doesnt work

    ALTER TABLE *_test ADD image VARCHAR(255)

  • vik

    thank, it helped me.

  • chanderpal soni

    its a realy good example for beginers…
    thanks a lot

  • MMAMail

    nice and quick to the point. thank

  • silent_sinner

    thank for that info..

  • vishal

    thanks for help

  • Fernando Silva

    Danke Schön!

  • http://eraldmariano.com erald mariano

    thanks! it helped!

  • Vtmathi@googlemail.com

    Fantastic explanation

  • Binnu

    Mei k aaj ko hu…Binnu..

  • Manu_ash0147

    thanks

  • http://www.facebook.com/people/Mcheche-Mugune/100001418757624 Mcheche Mugune

    How to add a column in the existing database table?

  • Aliraza4167

    Thanks alot. it was of great help

  • Randy

    It would be helpful to show an MYSQL example along with the SQL example

  • Karan Sachdeva86

    thank you

  • Anonymous

    Thank you for this.

  • http://twitter.com/larawebworks Lara Web Works

    Thank you, son simple yet it looks complicated in the manual!

  • supriya

    thank you.. it helped me

  • Pamidipadudravid

    thanks  alot

  • Sdfds

    thanks, good info.

  • Rizwan khan

    so easy thnks.

  • LAKS

    its very simple… thanks….

  • Yogesh

    Thanks for help

  • http://www.placingjobs.com Shairyar

    great thanks

  • vickky

    thank you very much

  • Avinash

    thanks man its really helpfull

  • Avinash

    thanks man

  • prakash

    COOL………

  • abdul kitindi

    thanks,it works

  • Karthick

    Thanks. Nice tutorial.. It is really helpful..

  • Dominic

    I seem to have a mental block when it comes to the syntax for adding another column in MYSQL, I’ve actually used this page quite a lot. Kudos on well written article.

  • ivo

    Exactly what i was looking for.

  • Kaushik

    thanx but i am not able to insert any values in the new column that i have created. I tried INSERT INTO) but it is not working… :(

  • tim

    Thank you very much! I looked everywhere for this syntax!

  • anju

    thanks

  • yogesh

    alter table tbl_fb_token add date_time(datetime)

  • jerome

    This article is spot on. Not only did it answer my query
    Precisely, but also contains other details which are
    Relevant to my work. Thanks Quinn.

  • jAKEER

    Thanks allot ..

    helped me allot .

  • allan

    Helped me out a great deal that did. TVM !

  • shivanshu

    Thank you !
    I’ll like to work under your guidence !

  • Ganesh Babu H.C

    Thank you !
    it helpsme a lot……….

  • Ivan

    Thanks, exactly what I was looking for!

  • pankaj

    it helped me, thanks a lot.

  • dutch

    thanx bro

  • joshi

    Thanks!!!!!!!!!

  • micki

    OK – I see how to add a column. My problem is that this column needs to calculate a running balance.
    ie
    Newcol1: if period = 0, then + beginning bal
    Newcol2: if period = 1, then periodbal + Newcol1
    Newcol3: if period = 2, then periodbal + Newcol2
    …all the way to Newcol13 (13 accounting periods)

    Can this be done? (I’m very new to MYSQL)

    I want balances for each account and each period in the end.

  • Poonam

    It’s help.

  • Dean Kamali

    Thanks

  • Gabi

    GRACIAS

  • grijesh

    Good theory, lack of examples with outputs

  • Lohith

    Hi I need to alter table with image attribute so that i can insert images in that database how to do that..?

  • Jorge

    Thanks!

  • http://shneler.com/vb/forum.php mohammad

    can you please add to me Colum to fix this error

    Action Required
    Unexpected Text:

    Database error in vBulletin 4.2.0:

    Invalid SQL:
    INSERT INTO widget (`title`,`template`,`icon`,`isthirdparty`,`category`,`cloneable`,`guid`,`canbemultiple`)
    VALUES(‘Static HTML’,’widget_statichtml’,’module-icon-html.png’,’0′,’Generic’,’1′,’vbulletin-widget_1-4eb423cfd68cd8.93414606′,’1′)
    /**widget**/;

    MySQL Error : Unknown column ‘canbemultiple’ in ‘field list’
    Error Number : 1054
    Request Date : Sunday, February 17th 2013 @ 02:17:40 PM
    Error Date : Sunday, February 17th 2013 @ 02:17:40 PM
    Script : http:///vb5/core/install/upgrade.php
    Referrer :
    IP Address : 79.173.199.121
    Username :
    Classname : vB_Database_MySQL
    MySQL Version : 5.5.23-55

  • http://shneler.com/vb/forum.php mohammad

    can you please fix to me this error in Vb upgrade
    Thanks

    Action Required
    Unexpected Text:

    Database error in vBulletin 4.2.0:

    Invalid SQL:
    INSERT INTO widget (`title`,`template`,`icon`,`isthirdparty`,`category`,`cloneable`,`guid`,`canbemultiple`)
    VALUES(‘Static HTML’,’widget_statichtml’,’module-icon-html.png’,’0′,’Generic’,’1′,’vbulletin-widget_1-4eb423cfd68cd8.93414606′,’1′)
    /**widget**/;

    MySQL Error : Unknown column ‘canbemultiple’ in ‘field list’
    Error Number : 1054
    Request Date : Sunday, February 17th 2013 @ 02:17:40 PM
    Error Date : Sunday, February 17th 2013 @ 02:17:40 PM
    Script : http:///vb5/core/install/upgrade.php
    Referrer :
    IP Address : 79.173.199.121
    Username :
    Classname : vB_Database_MySQL
    MySQL Version : 5.5.23-55

  • Russell English

    Thanks very much! Worked perfectly!

  • http://www.tech-recipes.com/ Aaron St. Clair

    Oh man, googling my problem and ending up on TR for the first hit, that’s what I’m talking about!

    • David Kirk

      :)

      Thanks, Aaron. TR wouldn’t be TR without brillant authors like you…

  • senthil

    yeah it works..:-*

  • Abhishek

    Thanks. its great.

  • sanjay

    after adding the new column for that column i need to insert data into that column. Pls can u help me