Ben McKay

I was working on a project recently that had to import multiple CSV files into a MySQL database. I was using Laravel and the Laravel-Excel package to try and accomplish this. I built the project out and realized that I was having some duplicate values show up even though I built a unique index based upon multiple columns.

Long story short: MySQL does not enforce uniqueness with NULL values.

Here is a great article going into more details. https://dba-presents.com/databases/mysql/274-mysql-unique-index-with-null-values

I currently have a discussion started on GitHub for this very issue, specifically centered around the Laravel-Excel package, but will wait to hear how others are handling. Here are a few solutions I have thought of and my gut feeling on them.

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.