Code Collections

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.

Chris Fontes Programming mysql unique index primary update null insert query multiple identical duplicate