Add a column to an existing MySQL table
Posted by Quinn McHenry in 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;
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
View more articles by Quinn McHenry
The Conversation
Follow the reactions below and share your own thoughts.
November 18, 2008 at 6:44 am, nilesh said:
Thanks. it helped me.
January 27, 2011 at 6:59 pm, Prasath said:
THANKS
November 18, 2008 at 11:26 pm, Philip said:
Wow, that’s kind of stupid that there’s no before.
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.
April 03, 2010 at 11:34 pm, Better Reader said:
Because it increases the inherent flexibility that (My)SQL is supposedly famous for, obviously.
November 18, 2010 at 1:48 am, Marcus Longwell said:
Except there is no need for it anyways, obviously.
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
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
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
December 12, 2008 at 8:57 pm, fubar said:
nice
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 ….
January 16, 2009 at 7:50 am, Parkash said:
Wow what a solution!
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
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?
March 01, 2009 at 5:30 pm, bob said:
This is just perfect, I use it all the time … thx!
March 20, 2009 at 8:43 pm, gb said:
Thanks!
April 29, 2009 at 9:48 am, gnujack said:
Thanks, exactly what I was looking for!
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.
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.
July 23, 2009 at 2:50 am, Anonymous said:
I want to learn about the space that data take in mysql
August 30, 2009 at 7:34 pm, English said:
Thanks for that info, that really helps me to create my first ALTER ADD COLUMN =)
September 08, 2009 at 9:05 am, Anonymous said:
good example
October 26, 2009 at 12:08 pm, Anonymous said:
thanks
November 16, 2009 at 4:34 am, sasimkumar said:
Thanks for the information..
December 08, 2009 at 6:21 pm, Anonymous said:
How do you add a value to the new column?
February 08, 2011 at 10:41 am, James Saunderson said:
ALTER TABLE `mydb`.`mytable` ADD COLUMN `col2` TINYINT(1) DEFAULT ’0′ AFTER `col1`
December 15, 2009 at 7:10 pm, Anonymous said:
First time in this site. It seems helpful
December 22, 2009 at 11:10 am, Anonymous said:
This is helped me a lot!!!
December 22, 2009 at 11:13 am, Anonymous said:
This site is helpful i like it!
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!
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!
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
February 03, 2010 at 4:25 pm, dani said:
very useful article. Thanks for writing it.
February 03, 2010 at 6:07 pm, Danny said:
Very useful one! Thanks a lot
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
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`;
March 03, 2010 at 7:02 am, Anonymous said:
how to add multiple columns using single query to a existing databae table
March 30, 2010 at 8:59 am, test said:
not getting
December 15, 2010 at 11:57 am, Viji_jyotika said:
then go
March 31, 2010 at 12:06 pm, Anonymous said:
good, i used and it’s correct
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?
April 30, 2010 at 5:23 pm, 3rlend said:
I just found it out.
ALTER TABLE table1 ADD column4 Default ‘jeje’;
May 07, 2010 at 2:57 pm, Rein said:
thanks
July 07, 2010 at 6:06 am, Kinathukadavuu said:
thanks
May 27, 2010 at 9:13 am, Chandru said:
please give the suggestion for upload a file into the mysql database
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.
June 02, 2010 at 8:02 am, Raj said:
Thanks it good for new person in mysql
June 04, 2010 at 8:45 am, beyo said:
thanks a lot
July 12, 2010 at 1:15 pm, Shariqnittt said:
Thanks
July 13, 2010 at 10:10 am, Dheerajchouhan85 said:
thanks bro
July 19, 2010 at 2:06 pm, Jhanine_0692 said:
hauhuhu
July 29, 2010 at 2:57 am, Mackensz1 said:
Thanks.. IT helps me
August 10, 2010 at 11:10 am, Guest said:
Great, thank you
August 31, 2010 at 7:10 am, Manoj said:
thanks alot
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)
September 03, 2010 at 6:57 am, vik said:
thank, it helped me.
September 13, 2010 at 7:34 am, chanderpal soni said:
its a realy good example for beginers…
thanks a lot
September 27, 2010 at 9:42 am, MMAMail said:
nice and quick to the point. thank
October 03, 2010 at 4:08 am, silent_sinner said:
thank for that info..
October 07, 2010 at 1:57 pm, vishal said:
thanks for help
October 20, 2010 at 4:28 pm, Fernando Silva said:
Danke Schön!
October 23, 2010 at 12:56 pm, erald mariano said:
thanks! it helped!
November 04, 2010 at 6:08 pm, Vtmathi@googlemail.com said:
Fantastic explanation
January 12, 2011 at 10:38 am, Binnu said:
Mei k aaj ko hu…Binnu..
January 12, 2011 at 11:48 am, Manu_ash0147 said:
thanks
January 20, 2011 at 6:30 pm, Mcheche Mugune said:
How to add a column in the existing database table?
February 03, 2011 at 5:26 am, Aliraza4167 said:
Thanks alot. it was of great help
February 15, 2011 at 6:01 pm, Randy said:
It would be helpful to show an MYSQL example along with the SQL example
March 09, 2011 at 1:58 am, Karan Sachdeva86 said:
thank you
March 12, 2011 at 3:02 pm, Anonymous said:
Thank you for this.
April 07, 2011 at 4:07 pm, Lara Web Works said:
Thank you, son simple yet it looks complicated in the manual!
May 04, 2011 at 11:18 am, supriya said:
thank you.. it helped me
May 21, 2011 at 11:09 am, Pamidipadudravid said:
thanks alot
May 23, 2011 at 4:15 pm, Sdfds said:
thanks, good info.
July 05, 2011 at 9:42 am, Rizwan khan said:
so easy thnks.
October 14, 2011 at 5:20 am, LAKS said:
its very simple… thanks….
October 17, 2011 at 10:37 pm, Yogesh said:
Thanks for help
December 26, 2011 at 4:21 am, Shairyar said:
great thanks
December 28, 2011 at 6:53 am, vickky said:
thank you very much
February 14, 2012 at 2:22 am, Avinash said:
thanks man its really helpfull
February 14, 2012 at 2:24 am, Avinash said:
thanks man
March 13, 2012 at 5:36 am, prakash said:
COOL………
March 25, 2012 at 1:51 am, abdul kitindi said:
thanks,it works
March 25, 2012 at 11:33 am, Karthick said:
Thanks. Nice tutorial.. It is really helpful..
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.
April 05, 2012 at 6:33 am, ivo said:
Exactly what i was looking for.
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…
April 27, 2012 at 7:21 pm, tim said:
Thank you very much! I looked everywhere for this syntax!
May 10, 2012 at 10:24 am, anju said:
thanks
May 12, 2012 at 5:52 pm, yogesh said:
alter table tbl_fb_token add date_time(datetime)
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.
July 02, 2012 at 10:44 am, jAKEER said:
Thanks allot ..
helped me allot .
July 13, 2012 at 8:48 am, allan said:
Helped me out a great deal that did. TVM !
July 17, 2012 at 10:39 am, shivanshu said:
Thank you !
I’ll like to work under your guidence !
July 17, 2012 at 5:26 pm, Ganesh Babu H.C said:
Thank you !
it helpsme a lot……….
August 08, 2012 at 9:40 am, Ivan said:
Thanks, exactly what I was looking for!
August 09, 2012 at 7:10 am, pankaj said:
it helped me, thanks a lot.
August 21, 2012 at 8:00 am, dutch said:
thanx bro
October 06, 2012 at 4:42 am, joshi said:
Thanks!!!!!!!!!
January 09, 2013 at 9:59 am, Poonam said:
It’s help.
January 16, 2013 at 8:53 pm, Dean Kamali said:
Thanks
January 23, 2013 at 7:59 pm, Gabi said:
GRACIAS
March 11, 2013 at 6:15 am, grijesh said:
Good theory, lack of examples with outputs
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..?
April 02, 2013 at 5:03 pm, Jorge said:
Thanks!
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
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
April 10, 2013 at 10:18 am, Russell English said:
Thanks very much! Worked perfectly!
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!
May 17, 2013 at 9:25 am, David Kirk said:
Thanks, Aaron. TR wouldn’t be TR without brillant authors like you…