Modify an existing MySQL column

The best laid plans of mice and DBAs oft go awry, so it is sometimes necessary to change the characteristics of a column after it exists and contains data. Beware whenever you make changes to your database — always make a backup first.


After a week of using the contacts table created in Create a basic MySQL table, we may find that 40 characters for the column name doesn’t cut it. To increase the size of the name column to 80 characters:

ALTER TABLE contacts CHANGE name name VARCHAR(80);

The first part of this statement (ALTER TABLE contacts CHANGE name) identifies that we want to change the column name in the table contacts. The second part of this statement (name VARCHAR(80)) redefines the column name. We could further define this column as NOT NULL, for example, with

ALTER TABLE contacts CHANGE name name VARCHAR(80) NOT NULL;

 

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.

15 Responses to “Modify an existing MySQL column”

  1. November 03, 2008 at 6:44 am, Sitthykun Cambodia said:

    good code

    Reply

  2. January 30, 2009 at 1:34 pm, sql said:

    thanks…but i m not getting result….sorry..:’-(

    Reply

    • August 06, 2012 at 3:48 pm, nutharsh said:

      > One important pre-requisite before running the second SQL command to make a column to hold not-null values is that it should not contain any null values populated previously. so, run a query to remove null values and then try the second SQL command.

      Reply

  3. June 09, 2009 at 6:53 am, bhaskar said:

    It’s really simple and superb.

    Reply

  4. June 18, 2010 at 5:22 am, Wpabitha said:

    thanks

    Reply

  5. August 26, 2010 at 12:40 am, Siyeni said:

    Thanks, I have no regrets hitting this page. Good job.

    Reply

  6. September 30, 2010 at 3:32 am, Sunganani said:

    Thanks man

    Reply

  7. November 28, 2010 at 3:59 pm, Cclark Home said:

    THANK YOU! I’m just learning mysql and have done a lot of drop tables due to not being able to figure this out.

    Reply

  8. February 23, 2011 at 5:34 am, Ramboraju5 said:

    CHANGE didn’t work for me.. but the below code worked for me on MySQL
    ALTER TABLE contacts MODIFY name VARCHAR(80);

    Reply

  9. March 01, 2011 at 4:06 pm, Kultar Singh said:

    thanx for the information……

    Reply

  10. April 29, 2011 at 2:24 pm, Abdul-Baasit Ismail said:

    worked great. . . .a timeless answer obviously

    Reply

  11. September 21, 2011 at 4:42 am, surendra said:

    nice code really ..thank you.

    Reply

  12. November 07, 2011 at 9:40 pm, pankaz kumar said:

    really nice….good work…thankx

    Reply

  13. January 19, 2012 at 11:03 am, M.jan said:

    hi dear :
    although i write this command alter table [name of table]modify name varcahr(65); i can’t get the result
    name is existed in my table kindly help me what can i do?

    Reply

  14. June 18, 2012 at 7:09 am, vipul said:

    A simple query was made very difficult in mysql original documentation, thanx a lot mahn!!

    Reply

Leave a Reply