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;