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 ?
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.
Schema Name.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.
