How to change MySQL column definition?

Question

I have a mySQL table called test:

create table test(
    locationExpect varchar(120) NOT NULL;
);

I want to change the locationExpect column to:

create table test(
    locationExpect varchar(120);
);

How can it be done quickly?

score 299 · Accepted Answer · edited May 26 '16 at 16:45

299

Do you mean altering the table after it has been created? If so you need to use alter table, in particular:

ALTER TABLE tablename MODIFY COLUMN new-column-definition

e.g.

ALTER TABLE test MODIFY COLUMN locationExpect VARCHAR(120);

edited May 26 '16 at 16:45

Benjamin W.

answered Oct 20 '09 at 15:19

mikej

6

So... MySQLWorkbench has a syntax checking bug that rejects this command. But, it's the right command. – Steven Lu May 14 '15 at 16:31
Beware: The above example will also change COLUMN to Null=Yes, Default=Null. You need to specify different values if you don't want Null. – Guy Gordon Jan 13 '20 at 16:52
@GuyGordon in the original question [Mask](https://stackoverflow.com/users/179364/mask) wanted to change a column from `NOT NULL` to allowing nulls so I think this example is still OK. Agree that a slight variation would be needed if a different default was wanted though. – mikej Jan 13 '20 at 17:21

score 43 · Answer 2 · edited Jan 02 '14 at 09:20

43

Syntax to change column name in MySql:

alter table table_name change old_column_name new_column_name data_type(size);

Example:

alter table test change LowSal Low_Sal integer(4);

edited Jan 02 '14 at 09:20

Rohan Kandwal

answered Jan 02 '14 at 09:00

Niranjan Vaddi

score 10 · Answer 3 · answered Oct 20 '09 at 15:19

10

This should do it:

ALTER TABLE test MODIFY locationExpert VARCHAR(120)

answered Oct 20 '09 at 15:19

Daniel Rikowski

3 Answers3