CODEWITHSUNDEEP
mysqlmariadb
Narktor
Narktor

Reputation: 1037

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

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?

Upvotes: 0

Views: 2538

Answers (2)

slaakso
slaakso

Reputation: 9080

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

Upvotes: 1

linh nguyenvan
linh nguyenvan

Reputation: 121

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

Upvotes: 0

Related Questions