Set default value if empty string is passed

Question

I am trying to find a solution to achieve the following result.

If MySQL query has empty string '', than use default value defined in a table schema.

For example

INSERT INTO `users`(`name`,`nickname`,`balance`) 
VALUES ('hello','world','');

In this case if the balance column's default value is 0 then it should be set instead of the empty string.

Is it possible to achieve this ?

score 0 · Answer 1 · answered Jun 23 '17 at 09:34

A better way would be to create a trigger. Read more about the triggers on Mysql documentation. Similar answer for more details can be found here.

For this question here is my implementation of the trigger.

delimiter #
create trigger nonEmptyString
before insert
on users
for each row

BEGIN
IF NEW.balance = '' THEN
   SELECT COLUMN_DEFAULT INTO @def
   FROM information_schema.COLUMNS
   WHERE
     table_schema = 'Schema Name'
     AND table_name = 'users'
     AND column_name = 'balance';
   SET NEW.balance = @def;
END IF;

end#

delimiter ;

Note : Replace Schema Name with the name of your schema.

You can use the [DATABASE()](https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_database) function instead of `Schema Name`. — wchiquito, Jun 23 '17 at 11:26

score 0 · Answer 2 · answered Jun 26 '17 at 05:07

use a variable (such as $balance) instead of '' (which I expect you'd be doing anyway).

On the line before your query:

if(strlen(trim($balance))==0){ $balance='0'; }

You could also use the mysql case syntax, which is a bit clunky, but very much on point for your description.

MySQL update CASE WHEN/THEN/ELSE

Set default value if empty string is passed

2 Answers2

Linked