MySQL - Remove default value for Datetime field

Question

An existing MySQL table has a DateTime field which is not null and having a Default Value set as '0001-00-00 00:00:00'. Is it possible to Alter this table to remove the default value for the DateTime field ?

Answer 1

Yes, you can drop the default using an ALTER TABLE statement like this:

alter table your_table 
  alter column your_column drop default;

Answer 2

To drop the default from multiple datetime columns in a table:

ALTER TABLE your_table 
   ALTER COLUMN columnname1 DROP DEFAULT,
   ALTER COLUMN columnname2 DROP DEFAULT, 
   ALTER COLUMN columnname3 DROP DEFAULT,
   ....

Answer 3

You can simply change the column and exclude default clause using following query. Here T1 is table containing default value for column updated and subject. You can simply remove default value as given below:

ALTER TABLE `T1` 
CHANGE `updated` `updated` DATETIME NOT NULL,
CHANGE `subject` `subject` VARCHAR(100)