MySQL "Invalid use of NULL value" workaround

1. Description of the problem

Use database query statement "ALTER TABLE information modify ' number ' varchar (255) NOT NULL ' because you want to change the" number "column in the" Information "table to non-null You can change the other columns, but only in the numbered column, the error message is Invalid use of NULL value.

It is strange that the other columns use this command to find out why only numbered columns cannot be checked.

2. Cause of Error

This error can occur because the ' number ' column of the existing data is NULL, and the setting for not null conflicts. (The reason for the search).

I think it's because the table structure of the column has been set in the beginning of the new data table, and the subsequent changes will be in conflict with the previous settings (as to why this change cannot be changed, and I don't understand it) because I set the structure property of the numbering column before I created the table.

Because it cannot be set to non-null, you can succeed by creating a new column and setting not null.

3. Workaround

1) Add a new column to set the structure properties of the column.

ALTER TABLE Information add column ' yyy ' varchar (255) not NULL first; -Newly added columns yyy to the first column.

2) Copy the column contents of the error to the new column and delete the error column

Update information set yyy= ' number '; ---Copy all values of the ' number ' column to the YYY column.

ALTER TABLE information drop ' number '; ---Delete the faulted column.

3) Modify the column name to which the new column name is faulted

ALTER TABLE information change ' yyy ' number ' varchar (255) not null;

MySQL "Invalid use of NULL value" workaround