Add a column to an existing MySQL table
Contributed by qmchenry 
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;
You may also like -
Describe the column structure of a MySQL table
Delete a column from an existing MySQL table
Create a basic MySQL table
Use regular expressions in MySQL SELECT statements
Create a MySQL table with a primary keyMySQL: Use CONCAT to include text in SELECT results
Rename or change name of MySQL table
Import CSV file directly into MySQL
nilesh said on November 18, 2008
Thanks. it helped me.
Philip said on November 18, 2008
Wow, that’s kind of stupid that there’s no before.
fubar said on December 12, 2008
nice
James said on December 14, 2008
Why do you need a before? If its the first one, you use first, if its the second one, you use after first.
Mike said on January 12, 2009
Thus why philip will never design anything worth while. Positive thinking is needed to create great things …. :P
Parkash said on January 16, 2009
Wow what a solution!
KG Old Wolf said on January 16, 2009
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 said on January 28, 2009
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 said on March 1, 2009
This is just perfect, I use it all the time … thx!
gb said on March 20, 2009
Thanks!
ally said on April 16, 2009
wow, you have no idea what you’re talking ’bout
gnujack said on April 29, 2009
Thanks, exactly what I was looking for!
mike said on May 2, 2009
“…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.
andy said on May 23, 2009
@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 said on July 23, 2009
I want to learn about the space that data take in mysql
English said on August 30, 2009
Thanks for that info, that really helps me to create my first ALTER ADD COLUMN =)
Anonymous said on September 8, 2009
good example
Anonymous said on October 26, 2009
thanks
sasimkumar said on November 16, 2009
Thanks for the information..
Anonymous said on December 8, 2009
How do you add a value to the new column?
Anonymous said on December 15, 2009
First time in this site. It seems helpful
Anonymous said on December 22, 2009
This is helped me a lot!!!
Anonymous said on December 22, 2009
This site is helpful i like it!
Anonymous said on December 22, 2009
Does this site have a Facebook group i would to join it coz it helped me big tym!
Anonymous said on December 27, 2009
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 said on January 7, 2010
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
dani said on February 3, 2010
very useful article. Thanks for writing it.
Danny said on February 3, 2010
Very useful one! Thanks a lot
Anonymous said on March 3, 2010
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
Anonymous said on March 3, 2010
how to add multiple columns using single query to a existing databae table
test said on March 30, 2010
not getting
Anonymous said on March 31, 2010
good, i used and it’s correct
Better Reader said on April 3, 2010
Because it increases the inherent flexibility that (My)SQL is supposedly famous for, obviously.
3rlend said on April 30, 2010
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?
3rlend said on April 30, 2010
I just found it out.
ALTER TABLE table1 ADD column4 Default ‘jeje’;
Rein said on May 7, 2010
thanks
Chandru said on May 27, 2010
please give the suggestion for upload a file into the mysql database
yusuf said on May 27, 2010
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.
Raj said on June 2, 2010
Thanks it good for new person in mysql
beyo said on June 4, 2010
thanks a lot
Kinathukadavuu said on July 7, 2010
thanks
Shariqnittt said on July 12, 2010
Thanks
Dheerajchouhan85 said on July 13, 2010
thanks bro
Jhanine_0692 said on July 19, 2010
hauhuhu
Mackensz1 said on July 29, 2010
Thanks.. IT helps me
Guest said on August 10, 2010
Great, thank you
Manoj said on August 31, 2010
thanks alot
Thomas said on September 2, 2010
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 said on September 3, 2010
thank, it helped me.
chanderpal soni said on September 13, 2010
its a realy good example for beginers…
thanks a lot
MMAMail said on September 27, 2010
nice and quick to the point. thank
silent_sinner said on October 3, 2010
thank for that info..
vishal said on October 7, 2010
thanks for help
Fernando Silva said on October 20, 2010
Danke Schön!
erald mariano said on October 23, 2010
thanks! it helped!
Vtmathi@googlemail.com said on November 4, 2010
Fantastic explanation
Marcus Longwell said on November 18, 2010
Except there is no need for it anyways, obviously.
Viji_jyotika said on December 15, 2010
then go
Binnu said on January 12, 2011
Mei k aaj ko hu…Binnu..
Manu_ash0147 said on January 12, 2011
thanks
Mcheche Mugune said on January 20, 2011
How to add a column in the existing database table?
Prasath said on January 27, 2011
THANKS
Aliraza4167 said on February 3, 2011
Thanks alot. it was of great help
James Saunderson said on February 8, 2011
ALTER TABLE `mydb`.`mytable` ADD COLUMN `col2` TINYINT(1) DEFAULT ‘0′ AFTER `col1`
James Saunderson said on February 8, 2011
ALTER TABLE `mydb`.`mytable` ADD COLUMN `col2` VARCHAR(50) AFTER `col1`, ADD COLUMN `col3` TINYINT(1) DEFAULT ‘0′ AFTER `col2`;
Randy said on February 15, 2011
It would be helpful to show an MYSQL example along with the SQL example
Spam Banjo said on February 18, 2011
Um, yes there is.
Karan Sachdeva86 said on March 9, 2011
thank you
Anonymous said on March 12, 2011
Thank you for this.
Lara Web Works said on April 7, 2011
Thank you, son simple yet it looks complicated in the manual!
supriya said on May 4, 2011
thank you.. it helped me
Pamidipadudravid said on May 21, 2011
thanks alot
Sdfds said on May 23, 2011
thanks, good info.
liftari said on May 27, 2011
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
Rizwan khan said on July 5, 2011
so easy thnks.
LAKS said on October 14, 2011
its very simple… thanks….
Yogesh said on October 17, 2011
Thanks for help
Shairyar said on December 26, 2011
great thanks
vickky said on December 28, 2011
thank you very much