mysql 字符串为空字符串,MySQL中是否有一种方法可以将空字符串自动转换为NULL值？...

I have a table I'm trying to add a unique index to. Problem is, the way the script is set up that inserts data, there will be on occasion, some empty strings instead of NULL.

So, aside from changing the script (which I foresee being necessary unless someone saves me!).... is there any setting in mysql that can automatically use NULL value if an empty string is passed through to the unique key?

解决方案

If you can't fix the import script then you should be able to use a trigger to patch things up before INSERTs or UPDATEs get to the table. Something like this should do the trick:

delimiter $$

create trigger nullify_blanks_ins before insert on your_table

for each row begin

if new.string = '' then

set new.string = null;

end if;

end;

$$

create trigger nullify_blanks_upd before update on your_table

for each row begin

if new.string = '' then

set new.string = null;

end if;

end;

$$

delimiter ;

new here refers to what the row will look like after the INSERT or UPDATE completes so you can look for the garbage values and fix them. Then, since these are BEFORE triggers, your patched rows will go into the database. You would, of course, have to change the table (your_table) and column (string) names to suit your situation.