MySQL and a NULL index walk into a bar….
June 9th, 2009
Did you know that in MySQL, if you have a NULL field in your “unique” index, you can have multiple duplicates of that row and MySQL will turn a blind eye? Oh yes, that is a pain. Example: If you have a unique index that consists of 2 fields (ID and secondID), you “could” end up with (1, NULL), (1, NULL), (1,NULL). Sure, it looks to be identical, but all 2 identical rows can exists in perfect harmony. This is particularly problematic when realying on a unique key and using INSERT ON DUPLICATE KEY UPDATE queries.
Trust me. 3 days of frustration to learn this. MySQL does NOT classify this as a bug, but the community has made it clear that it acts like one. Hopefully they will fix this in an upcomnig release.