MySQL - How to modify column default value?

mysql sql default alter
User picture User · Jan 4, 2014 · Viewed 25.8k times · Source

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 '{}';

Answer

Saharsh Shah picture Saharsh Shah · Jan 4, 2014

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';

Related questions

How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More
What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN in MySQL?

Read More