MySQL: How to set default value if provded value is null-CodePudding

I have a column in my database table where the default needs to be 'int 0' but also null can be provided due to legacy.

I have attached a picture to show what I am trying to do.

The problem I am having is that when I untick 'Not Null' and have a default of 0, and a null value is provided then null is added.

But if I tick the box and null is provided it does not add 0, my code throws an exception instead stating the value for this column must not be null.

I want it do that if null is provided then it defaults to 0.

Appreciate any help on this. Thanks

Note: I am using Datagrip

I am assuming something in 'default expression' but I am not sure what

CodePudding user response：

You could create a BEFORE INSERT trigger:

CREATE TRIGGER mytable BEFORE INSERT ON mytable FOR EACH ROW 
   SET NEW.testfield = COALESCE(NEW.testfield, 0);

More information on triggers can be found in the documentation.

CodePudding user response：

Check Data on controller before sending it to database

if(testfield == null) { tedtfield = 0;}