When I was a student, I created the database just give the name and leave the other option as default. I was assumed that the MySQL compare is case insensitive. Then, I learned that MySQL actually gives us the option to control how comparison works. This behavior is handled by a collation. By default, MySQL is case-insensitive because of the collation that was setup, by default it setup as utf8mb4_0900_ai_ci ( in MySQL 8 ). If we want comparisons to be case-sensitive, we can change the collation when creating a database, table, or even just a column.

Actually the collation consists of 4 parts. Let’s break it down:

1. utf8mb4 → This is the character set. It’s basically the “alphabet” your database understands. utf8mb4 is the recommended one because it supports pretty much all characters (including emojis 🎉).
2. 0900 → This refers to the Unicode version. In this case, it’s Unicode 9.0.0. Different Unicode versions can slightly change how characters are treated or compared.
3. ai → This means accent-insensitive. So for example, a and á would be considered the same when comparing text.
4. ci → This means case-insensitive. So Hello, HELLO, and hello are all treated as equal.

Now, what if you want case sensitivity? It is easy, we can just change ci with cs (case-sensitive) and/or ai with as (accent-sensitive), so it can be utf8mb4_0900_as_cs

So next time when you are designing a database, don’t just hit enter on the defaults (like I used to). Think about whether you want case sensitivity or accent sensitivity, because collations can really affect how your queries behave.

It might sound small, but it can be frustrating when debugging why “Password” and “password” look the same to your database.

Happy coding and learning 😅