dolthub / dolthub/dolt

have default collation be `utf8mb4_0900_ai_ci`

Open
#7,856 3 comments 0 reactions 1 assignee Claimed by @zachmu View on GitHub
correctness good repro wontfix
Dominant language
Go
Stars
24.4k
Forks
873
Avg merge
1d 5h
Merged PRs (30d)
108

Description

By default, MySQL's collation is `utf8mb4_0900_ai_ci`
https://dev.mysql.com/doc/refman/8.0/en/charset.html#:~:text=The%20default%20MySQL%20server%20character%20set%20and%20collation%20are%20utf8mb4%20and%20utf8mb4_0900_ai_ci

Dolt has it set as `utf8mb4_0900_bin`.

As a result, we have to set the collation through `@@collation_connection` (`@@persist.collation_connection` if you want it to stick for `dolt sql` and `dolt sql -q`) to get the same behavior out of the box.
```sql
tmp/main*> select @@collation_connection;
+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_0900_bin |
+------------------------+
1 row in set (0.00 sec)

tmp/main*> select 'abc' like 'ABC';
+------------------+
| 'abc' like 'ABC' |
+------------------+
| false |
+------------------+
1 row in set (0.00 sec)

tmp/main*> set @@collation_connection = utf8mb4_0900_ai_ci;
tmp/main*> select 'abc' like 'ABC';
+------------------+
| 'abc' like 'ABC' |
+------------------+
| true |
+------------------+
1 row in set (0.00 sec)
```

Additionally, MySQL's `LIKE` operator is always case-insensitive, regardless of `@@collation_connection`
https://stackoverflow.com/questions/14007450/how-do-you-force-mysql-like-to-be-case-sensitive

related: https://github.com/dolthub/dolt/issues/7851

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.