0

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?

Narktor
  • 977
  • 14
  • 34
  • 1
    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 Aug 29 '19 at 09:01

2 Answers2

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

slaakso
  • 8,331
  • 2
  • 16
  • 27
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
linh nguyenvan
  • 111
  • 1
  • 5