set default value to field if empty string is inserted through query

Question

So I have table A which contains a column Z with default value set to string "diverse". The queries come in from a php script which takes the data from a jqueryAJAX post request.

I would like to have my DB set the respective field of column Z to default value if the query received an empty string for insertion into/update of this column.

I really would like to accomplish this using mysql functionality, without using any more custom coded php logic. I already read about using WHEN/THEN logic here: Set default value if empty string is passed MySQL and here MySQL update CASE WHEN/THEN/ELSE

but these don't explain how I permanently configure a table/column in a way that it exposes this "default" behavior not just on receiving a NULL value, but also on receiving an empty string.

Besides: If I set a column to NOT NULL and also add a default value for the column, would the query just fail if I tried to insert a/update to a NULL value, or would the DB instead flip to the default value?

Some things are meant to be as they are due to valid reasons. If MySQL column is set to 'diverse' as default value and during INSERT/UPDATE user wants to use the blank/empty value then the engine has to work that way. There is no valid reason on why you do not want to remove or set default value 'diverse' at PHP side. That's the easiest way to solve your problem. — Samir Selia
– Samir Selia, Commented Aug 29, 2019 at 9:01

slaakso · Accepted Answer · 2019-08-30 10:40:56Z

1

MySQL/MariaDB will put the value to a column you specify in the insert/update-statement. If you do not provide a value, the default (if specified) will be used.

If you want to use the default value even if the insert/update-statement does provide a value (NULL / empty string), you will have to have the logic somewhere. The options are that you put the logic in your application code (PHP) or if you want to do it in MySQL/MariaDB, you can use a trigger to check the new value and act accordingly.

CREATE TRIGGER ins_A BEFORE INSERT ON A
FOR EACH ROW
BEGIN
  IF NEW.Z is null or NEW.Z='' THEN
    SET NEW.Z = 'diverse';
  END IF;
END;

And do the same for UPDATE

answered Aug 30, 2019 at 10:40

slaakso

9,1452 gold badges19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

linh nguyenvan · Accepted Answer · 2019-08-29 09:42:04Z

0

Please follow 2 case bellow:

create table Test
(
    a varchar(400)  not null default 'test',
    b varchar(10)
)
    collate = utf8mb4_unicode_ci;

Case 1: 
INSERT INTO Test (a, b) VALUES (NULL, 'case1');
=> resul: error because column a required not null;

Case 2:
INSERT INTO Test (b) VALUES ('case1');
=> OK, and result of column a = defaul value = test

edited Aug 29, 2019 at 9:42

answered Aug 29, 2019 at 8:57

linh nguyenvan

1211 silver badge5 bronze badges

Collectives™ on Stack Overflow

set default value to field if empty string is inserted through query

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related