MySQL - How to modify column default value?

Question

How do I change my column's default value from None to something else? For example, I want my dates to have a default value of 0000-00-00 if I don't specify one when I create the row.

I understand this in phpMyAdmin, but I'm not sure how to do it via command prompt.

I also understand how to do this when adding a column. But all of my columns are made and have data in some of them.

ALTER TABLE table1 ADD COLUMN foo INT DEFAULT 0;

From searching, I found this line, but I'm not sure if that's what I want?

ALTER TABLE foobar_data MODIFY COLUMN col VARCHAR(255) NOT NULL DEFAULT '{}';

Careful about the 0000-00-00 as that is not within the valid date range. dev.mysql.com/doc/refman/5.7/en/datetime.html — Kellen Stuart
– Kellen Stuart, Commented Jul 2, 2016 at 23:24

Saharsh Shah · Accepted Answer · 2014-01-04 05:29:59Z

13

Use ALTER TABLE to CHANGE or MODIFY the DEFAULT value of column. Check this link ALTER TABLE SYNTAX

ALTER TABLE `tableName` CHANGE `columnName` `columnName` DATE DEFAULT '0000-00-00'; 
ALTER TABLE `tableName` MODIFY `columnName` DATE DEFAULT '0000-00-00';

edited Jan 4, 2014 at 5:29

answered Jan 4, 2014 at 5:01

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

User Over a year ago

Will this apply 0000-00-00 to all my old rows that currently have None, or do I need to run a query to change those?

Saharsh Shah Over a year ago

@macdonjo It will apply 0000-00-00 to all rows where the field is null in your column.

Satish Sharma · Accepted Answer · 2014-01-04 05:04:53Z

0

try this

ALTER TABLE foobar_data CHANGE COLUMN col VARCHAR(255) NOT NULL DEFAULT '{}';

user CHANGE to alter a existing column

see Link

answered Jan 4, 2014 at 5:04

Satish Sharma

9,6356 gold badges31 silver badges52 bronze badges

Comments

user9139688 · Accepted Answer · 2018-04-13 12:53:11Z

0

In my case, it worked

ALTER TABLE `table_name` CHANGE `col_name` `col_name_again` VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'anydefault_text';

Hope it helps you too.

answered Apr 13, 2018 at 12:53

user9139688

Collectives™ on Stack Overflow

MySQL - How to modify column default value?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related