Batch Upserts with NULL values on Unique Index - MySQL

[bmckay959]

I am running into an issue, that I have(I think) determined to be a MySQL issue with how it handles Unique Indexes and NULL values. I'm curious if someone has ran into the same issue and was able to figure out a way to still hold uniqueness across multiple columns.

As you can see the below screenshot, the top red box is my database. The bottom red box is my Laravel migration file. The middle red box is my import with uniqueBy() function.

As you can see, my expectation would be that the database would not have the duplicate rows based on my unique index and uniqueBy() function. What I found is that uniqueness was not being forced when a value was NULL(state_code in this instance)

After digging further, I have found this in regards to MySQL Unique Indexes and NULL values. https://dba-presents.com/databases/mysql/274-mysql-unique-index-with-null-values

Based on the above article, we can see why MySQL doesn't use NULL values to enforce uniqueness, but it still does not solve my problem of needing it to be unique.

Solutions Available(That I could think of):

1. Use an empty string instead of NULL
2. Enforce uniqueness manually by doing a lookup before each insert
3. Create a new column that concatenates all the values together for a unique column

Solution 1:

• Pros: Simple to accomplish
• Cons: Feels wrong(Why? I like having null values in my DB for multiple reasons, but consistency is key. Trying to remember what columns use NULL for empty values and what columns us an empty string feels dirty.)

Solution 2:

• Pros: Simple to accomplish
• Cons: Would be slow for even small datasets(I'm dealing with around 150k records)

Solution 3:

• Pros: Simple to accomplish
• Cons: Feels wrong. Duplicate data just to enforce uniqueness.

Has anyone ran into this before? How did you solve?

[MarkBaker]

Re: option 3; if you're running MySQL8, look at "calculated columns", which needn't take up disk space (unless you want them to for indexing purposes), and don't duplicate data.
You could make a duplicated column based on a hash to reduce data volume if you did want to maintain it on disk for indexing