'SQL: Set default value to NULL for all columns without default value

The webhosting I use has enabled StrictMode for the Databases. All my php scripts now stopped working because they report I haven't defined a default value for some columns.

As I have a lot of columns in a lot of tables, is there a way to set all the columns with "default value = none" with "default value = NULL" ? In this way it won't report me the error anymore.

Of course, If there's another (better) way, I am available for it.

I tried looking on the net, but I couldn't find anything suitable for this case.

Solution 1:^[1]

A general approach here which should work for each column causing an error would be to set a default value, and then maybe do an update to backfill records missing a value.

ALTER TABLE yourTable ALTER some_text_column SET DEFAULT 'None';

And here is the update:

UPDATE yourTable SET some_text_column = 'None' WHERE some_text_column IS NULL;

You are not required to do this update, but it might make sense to bring older records missing values in line with what newer records would look like.

Solution 2:^[2]

you can alter column

ALTER TABLE table_name
 MODIFY COLUMN col datatype  DEFAULT null

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1
Solution 2

'SQL: Set default value to NULL for all columns without default value

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]