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.

117 Responses to “Add a column to an existing MySQL table”

  1. November 18, 2008 at 6:44 am, nilesh said:

    Thanks. it helped me.

    Reply

    • January 27, 2011 at 6:59 pm, Prasath said:

      THANKS

      Reply

  2. November 18, 2008 at 11:26 pm, Philip said:

    Wow, that’s kind of stupid that there’s no before.

    Reply

    • December 14, 2008 at 5:36 pm, James said:

      Why do you need a before? If its the first one, you use first, if its the second one, you use after first.

      Reply

      • April 03, 2010 at 11:34 pm, Better Reader said:

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

        Reply

        • November 18, 2010 at 1:48 am, Marcus Longwell said:

          Except there is no need for it anyways, obviously.

          Reply

          • February 18, 2011 at 11:57 am, Spam Banjo said:

            Um, yes there is.

    • April 16, 2009 at 5:19 pm, ally said:

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

      Reply

    • May 27, 2011 at 9:21 pm, liftari said:

       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

      Reply

      • July 27, 2012 at 6:13 am, MEIAM said:

        > 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

        Reply

        • August 06, 2013 at 8:49 am, kat said:

          Totally agree!

          Reply

  3. December 12, 2008 at 8:57 pm, fubar said:

    nice

    Reply

  4. January 12, 2009 at 8:12 am, Mike said:

    Thus why philip will never design anything worth while. Positive thinking is needed to create great things …. :P

    Reply

  5. January 16, 2009 at 7:50 am, Parkash said:

    Wow what a solution!

    Reply

  6. January 16, 2009 at 6:43 pm, KG Old Wolf said:

    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 :(

    Reply

    • January 28, 2009 at 7:58 pm, BaZ said:

      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?

      Reply

  7. March 01, 2009 at 5:30 pm, bob said:

    This is just perfect, I use it all the time … thx!

    Reply

  8. March 20, 2009 at 8:43 pm, gb said:

    Thanks!

    Reply

  9. April 29, 2009 at 9:48 am, gnujack said:

    Thanks, exactly what I was looking for!

    Reply

  10. May 02, 2009 at 6:20 pm, mike said:

    “…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.

    Reply

  11. May 23, 2009 at 10:36 am, andy said:

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

    Reply

  12. July 23, 2009 at 2:50 am, Anonymous said:

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

    Reply

  13. August 30, 2009 at 7:34 pm, English said:

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

    Reply

  14. September 08, 2009 at 9:05 am, Anonymous said:

    good example

    Reply

  15. October 26, 2009 at 12:08 pm, Anonymous said:

    thanks

    Reply

  16. November 16, 2009 at 4:34 am, sasimkumar said:

    Thanks for the information..

    Reply

  17. December 08, 2009 at 6:21 pm, Anonymous said:

    How do you add a value to the new column?

    Reply

    • February 08, 2011 at 10:41 am, James Saunderson said:

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

      Reply

  18. December 15, 2009 at 7:10 pm, Anonymous said:

    First time in this site. It seems helpful

    Reply

  19. December 22, 2009 at 11:10 am, Anonymous said:

    This is helped me a lot!!!

    Reply

  20. December 22, 2009 at 11:13 am, Anonymous said:

    This site is helpful i like it!

    Reply

  21. December 22, 2009 at 11:15 am, Anonymous said:

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

    Reply

  22. December 27, 2009 at 3:39 pm, Anonymous said:

    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!

    Reply

  23. January 07, 2010 at 11:43 pm, Anonymous said:

    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

    Reply

  24. February 03, 2010 at 4:25 pm, dani said:

    very useful article. Thanks for writing it.

    Reply

  25. February 03, 2010 at 6:07 pm, Danny said:

    Very useful one! Thanks a lot

    Reply

  26. March 03, 2010 at 7:00 am, Anonymous said:

    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

    Reply

    • February 08, 2011 at 10:44 am, James Saunderson said:

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

      Reply

  27. March 03, 2010 at 7:02 am, Anonymous said:

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

    Reply

    • April 08, 2010 at 9:07 am, maddin said:

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

      Reply

  28. March 30, 2010 at 8:59 am, test said:

    not getting

    Reply

    • December 15, 2010 at 11:57 am, Viji_jyotika said:

      then go

      Reply

  29. March 31, 2010 at 12:06 pm, Anonymous said:

    good, i used and it’s correct

    Reply

  30. April 30, 2010 at 5:00 pm, 3rlend said:

    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?

    Reply

    • April 30, 2010 at 5:23 pm, 3rlend said:

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

      Reply

  31. May 07, 2010 at 2:57 pm, Rein said:

    thanks

    Reply

    • July 07, 2010 at 6:06 am, Kinathukadavuu said:

      thanks

      Reply

  32. May 27, 2010 at 9:13 am, Chandru said:

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

    Reply

  33. May 27, 2010 at 1:11 pm, yusuf said:

    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.

    Reply

  34. June 02, 2010 at 8:02 am, Raj said:

    Thanks it good for new person in mysql

    Reply

  35. June 04, 2010 at 8:45 am, beyo said:

    thanks a lot

    Reply

  36. July 12, 2010 at 1:15 pm, Shariqnittt said:

    Thanks

    Reply

  37. July 13, 2010 at 10:10 am, Dheerajchouhan85 said:

    thanks bro

    Reply

  38. July 19, 2010 at 2:06 pm, Jhanine_0692 said:

    hauhuhu

    Reply

  39. July 29, 2010 at 2:57 am, Mackensz1 said:

    Thanks.. IT helps me

    Reply

  40. August 10, 2010 at 11:10 am, Guest said:

    Great, thank you

    Reply

  41. August 31, 2010 at 7:10 am, Manoj said:

    thanks alot

    Reply

  42. September 02, 2010 at 3:23 pm, Thomas said:

    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)

    Reply

  43. September 03, 2010 at 6:57 am, vik said:

    thank, it helped me.

    Reply

  44. September 13, 2010 at 7:34 am, chanderpal soni said:

    its a realy good example for beginers…
    thanks a lot

    Reply

  45. September 27, 2010 at 9:42 am, MMAMail said:

    nice and quick to the point. thank

    Reply

  46. October 03, 2010 at 4:08 am, silent_sinner said:

    thank for that info..

    Reply

  47. October 07, 2010 at 1:57 pm, vishal said:

    thanks for help

    Reply

  48. October 20, 2010 at 4:28 pm, Fernando Silva said:

    Danke Schön!

    Reply

  49. October 23, 2010 at 12:56 pm, erald mariano said:

    thanks! it helped!

    Reply

  50. November 04, 2010 at 6:08 pm, Vtmathi@googlemail.com said:

    Fantastic explanation

    Reply

  51. January 12, 2011 at 10:38 am, Binnu said:

    Mei k aaj ko hu…Binnu..

    Reply

  52. January 12, 2011 at 11:48 am, Manu_ash0147 said:

    thanks

    Reply

  53. January 20, 2011 at 6:30 pm, Mcheche Mugune said:

    How to add a column in the existing database table?

    Reply

  54. February 03, 2011 at 5:26 am, Aliraza4167 said:

    Thanks alot. it was of great help

    Reply

  55. February 15, 2011 at 6:01 pm, Randy said:

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

    Reply

  56. March 09, 2011 at 1:58 am, Karan Sachdeva86 said:

    thank you

    Reply

  57. March 12, 2011 at 3:02 pm, Anonymous said:

    Thank you for this.

    Reply

  58. April 07, 2011 at 4:07 pm, Lara Web Works said:

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

    Reply

  59. May 04, 2011 at 11:18 am, supriya said:

    thank you.. it helped me

    Reply

  60. May 21, 2011 at 11:09 am, Pamidipadudravid said:

    thanks  alot

    Reply

  61. May 23, 2011 at 4:15 pm, Sdfds said:

    thanks, good info.

    Reply

  62. July 05, 2011 at 9:42 am, Rizwan khan said:

    so easy thnks.

    Reply

  63. October 14, 2011 at 5:20 am, LAKS said:

    its very simple… thanks….

    Reply

  64. October 17, 2011 at 10:37 pm, Yogesh said:

    Thanks for help

    Reply

  65. December 26, 2011 at 4:21 am, Shairyar said:

    great thanks

    Reply

  66. December 28, 2011 at 6:53 am, vickky said:

    thank you very much

    Reply

  67. February 14, 2012 at 2:22 am, Avinash said:

    thanks man its really helpfull

    Reply

  68. February 14, 2012 at 2:24 am, Avinash said:

    thanks man

    Reply

  69. March 13, 2012 at 5:36 am, prakash said:

    COOL………

    Reply

  70. March 25, 2012 at 1:51 am, abdul kitindi said:

    thanks,it works

    Reply

  71. March 25, 2012 at 11:33 am, Karthick said:

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

    Reply

  72. April 04, 2012 at 6:37 am, Dominic said:

    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.

    Reply

  73. April 05, 2012 at 6:33 am, ivo said:

    Exactly what i was looking for.

    Reply

  74. April 25, 2012 at 9:15 am, Kaushik said:

    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… :(

    Reply

  75. April 27, 2012 at 7:21 pm, tim said:

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

    Reply

  76. May 10, 2012 at 10:24 am, anju said:

    thanks

    Reply

  77. May 12, 2012 at 5:52 pm, yogesh said:

    alter table tbl_fb_token add date_time(datetime)

    Reply

  78. May 15, 2012 at 2:19 pm, jerome said:

    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.

    Reply

  79. July 02, 2012 at 10:44 am, jAKEER said:

    Thanks allot ..

    helped me allot .

    Reply

  80. July 13, 2012 at 8:48 am, allan said:

    Helped me out a great deal that did. TVM !

    Reply

  81. July 17, 2012 at 10:39 am, shivanshu said:

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

    Reply

  82. July 17, 2012 at 5:26 pm, Ganesh Babu H.C said:

    Thank you !
    it helpsme a lot……….

    Reply

  83. August 08, 2012 at 9:40 am, Ivan said:

    Thanks, exactly what I was looking for!

    Reply

  84. August 09, 2012 at 7:10 am, pankaj said:

    it helped me, thanks a lot.

    Reply

  85. August 21, 2012 at 8:00 am, dutch said:

    thanx bro

    Reply

  86. October 06, 2012 at 4:42 am, joshi said:

    Thanks!!!!!!!!!

    Reply

  87. October 24, 2012 at 1:51 pm, micki said:

    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.

    Reply

  88. January 09, 2013 at 9:59 am, Poonam said:

    It’s help.

    Reply

  89. January 16, 2013 at 8:53 pm, Dean Kamali said:

    Thanks

    Reply

  90. January 23, 2013 at 7:59 pm, Gabi said:

    GRACIAS

    Reply

  91. March 11, 2013 at 6:15 am, grijesh said:

    Good theory, lack of examples with outputs

    Reply

  92. March 14, 2013 at 9:57 am, Lohith said:

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

    Reply

  93. April 02, 2013 at 5:03 pm, Jorge said:

    Thanks!

    Reply

  94. April 08, 2013 at 11:57 am, mohammad said:

    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

    Reply

  95. April 08, 2013 at 12:02 pm, mohammad said:

    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

    Reply

  96. April 10, 2013 at 10:18 am, Russell English said:

    Thanks very much! Worked perfectly!

    Reply

  97. April 30, 2013 at 11:56 pm, Aaron St. Clair said:

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

    Reply

    • May 17, 2013 at 9:25 am, David Kirk said:

      :)

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

      Reply

  98. September 20, 2013 at 7:39 am, senthil said:

    yeah it works..:-*

    Reply

  99. September 24, 2013 at 7:26 am, Abhishek said:

    Thanks. its great.

    Reply

  100. October 25, 2013 at 9:38 am, sanjay said:

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

    Reply

Leave a Reply