have default collation be `utf8mb4_0900_ai_ci`
- 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.