CODEWITHSUNDEEP
mysqlsql
Sherin Jose
Sherin Jose

Reputation: 2526

Set default value to all columns in a database

I am working in a PHP + MySQL application. The application is working fine for me. But when I hosted it in another server, I got a MySQL error:

Error Code: 1364. Field 'field' doesn't have a default value

I know this is a problem with the MySQL version and we should setup default values for all columns. But currently I have more than 100 tables. So I need to set default value to NULL for all columns in all tables that has no default value yet.

I can't make use of the strict mode option, because the server is a shared one. Is it possible to setup in a single step rather than setting for each and every table ? If not possible tell me the easiest way to setup it.

Any help would be greatly appreciated

Upvotes: 1

Views: 6228

Answers (2)

Ostaff
Ostaff

Reputation: 21

For anyone else with this problem, it will take a bit of coding to perform automatically, but the following would be how you would do so:

First run the following query:

SELECT table_schema,table_name,column_name,data_type FROM information_schema.columns WHERE IS_NULLABLE='NO' AND column_default is null AND column_key=''

Next, for each row returned from the above query perform the following:

If data_type contains 'int' set default to 0
else if data_type='datetime' set default to '0000-00-00 00:00:00'
else if data_type='date' set default to '0000-00-00'
else if data_type='time' set default to '00:00:00'
else set default to ''

create and run the following query with all [[...]] variables replaced with their proper values:

ALTER TABLE `[[table_schema]]`.`[[table_name]]` ALTER COLUMN `[[column_name]]` SET DEFAULT '[[default]]'

This should replace the default values for all databases, all tables, all columns that are set to be NOT NULL and are not primary keys and have no default value set.

Upvotes: 2

Shailender Ahuja
Shailender Ahuja

Reputation: 312

Another solution that i found is like:-

Get all column name put it in array... Now push values in column array for inserting -- with ZERO value for all those arrays we do not have values. FOR EXAMPLE: in a table we have COLUMN NAME LASTNAME COMPNAME PHONO EMAIL ADDRESS ALTERPERSON ALTERPHONE ALTEREMAIL Now after migration we see the eeror

Error Code: 1364. Field 'field' doesn't have a default value

if we run a INSERT QUERY LIKE

mysqli_query($con,'insert into table (NAME,LASTNAME,COMPNAME,PHONO,EMAIL,ADDRESS) values (NAME,LASTNAME,COMPNAME,PHONO,EMAIL,ADDRESS)')

now it will give error...

So just turn the table get all the column value from DB.TABLE put it in an array or do it like one by one using while loop or for loop....

check insert values for each column put condition if insert value is equal to ZERO or NULL then insert ZERO it will solve all issues.

WHY ZERO --

because it will work for VARCHAR,TEXT,INT,BIGINT and in many Data Types except time or date function and DATE/TIME data type got ZERO values by default...

=============================== Another option...

run a PHP code get all TABLE NAME then for each TABLE NAME get all COLUMN NAME

and run this command as in function under loop

ALTER TABLE DB.TABLEnAME CHANGE columnNAME_A columnNAME_A VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;

=======================

And its DONE

Upvotes: 0

Related Questions