2

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?

d-_-b
  • 21,536
  • 40
  • 150
  • 256
  • This solution does it in an update, you can probably apply the concept: http://stackoverflow.com/questions/3238319/how-do-i-change-all-empty-strings-to-null-in-a-table – Jeff Wooden Oct 24 '12 at 03:31
  • Can anything be set up table-wise, or is this a case of `do it right the first time and you won't have this problem`ology – d-_-b Oct 24 '12 at 03:37
  • Why don't you do it the other way around, disallow null and set the default to empty string? – sn00k4h Oct 24 '12 at 05:45
  • 1
    Are you using PDO? If so, you can use `PDO::NULL_EMPTY_STRING` attribute as a means to fix your script. – Ja͢ck Oct 24 '12 at 05:46
  • @Jack Thanks I will check this out. It sounds like what I'm looking for! – d-_-b Oct 24 '12 at 17:12
  • @sn00k4h You can't have multiple empty strings in a column set as unique/primary. – d-_-b Oct 24 '12 at 17:13

2 Answers2

5

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.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
0

try something like

SET sn=NULLIF(sn,NULL);

or

UPDATE Table
  SET demo = CASE demo = ' ' THEN NULL ELSE demo END,
  ...
NullPoiиteя
  • 56,591
  • 22
  • 125
  • 143
  • Wouldn't you have to drop the unique index, insert the data, do your update, then re-add the unique index for this to work? – mu is too short Oct 24 '12 at 03:39
  • @muistooshort well no you wouldn't even be able to have multiple empty strings if the unique index was in place.... – d-_-b Oct 24 '12 at 03:40