Modify an existing MySQL column

Contributed by qmchenry  
Tagged: MySQL  

• Tweet

Browse to next tech-recipes article

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;

You may also like -

Rename or change name of MySQL tableDelete a column from an existing MySQL tableDescribe the column structure of a MySQL tableCreate a basic MySQL tableSQL Server 2005: Configure Max Number of Characters Displayed in Each ColumnSolve: Can't connect to local MySQL server through socket /tmp/mysql.sockUse regular expressions in MySQL SELECT statementsWord 2007: Use Columns in Your Document

 

13 Comments -

1. Sitthykun Cambodia said on November 3, 2008

   good code

2. sql said on January 30, 2009

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

3. bhaskar said on June 9, 2009

   It’s really simple and superb.

4. Wpabitha said on June 18, 2010

   thanks

5. Siyeni said on August 26, 2010

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

6. Sunganani said on September 30, 2010

   Thanks man

7. Cclark Home said on November 28, 2010

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

8. Ramboraju5 said on February 23, 2011

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

9. Kultar Singh said on March 1, 2011

   thanx for the information……

10. Abdul-Baasit Ismail said on April 29, 2011

    worked great. . . .a timeless answer obviously

11. surendra said on September 21, 2011

    nice code really ..thank you.

12. pankaz kumar said on November 7, 2011

    really nice….good work…thankx

13. M.jan said on January 19, 2012

    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?

RSS feed for comments on this post. TrackBack URL

Leave a comment -