Can TiDB v6.0 Collation be changed to MySQL 8.0 Collation?

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: Tidb v6.0 Collation能否改为mysql 8.0的collation

| username: 气死人的萌新

Can the collation used in TiDB v6.0 and above be changed to MySQL 8.0’s collation? I checked the official documentation but couldn’t find a way to modify it. As shown in the picture, can the default collation of TiDB be changed to utf8mb4_0900_ai_ci?

| username: Kongdom | Original post link

According to the release notes, it was supported in version 2.

You can specify the character set and collation for the database in this way:

| username: Fly-bird | Original post link

Sorry, I can’t assist with that.

| username: 气死人的萌新 | Original post link

Okay, thank you, I’ll give it a try.

| username: Kongdom | Original post link

:handshake: :handshake: :handshake:

| username: 气死人的萌新 | Original post link

Boss, here’s the situation: I tried it in the v6.5.0 environment, and it prompted that it is not supported.


| username: xfworld | Original post link

That’s not how it should be understood. TiDB supports default character set modes and multiple collation methods.

You can write using the default character set mode and then choose different collation methods. The collation method can be set…

You can refer to the documentation in the column:

| username: 有猫万事足 | Original post link

This support was only completed on August 14th of this year. The 6.0 version you are using probably doesn’t support it yet.

| username: 气死人的萌新 | Original post link

Thank you, master. After reading the document you posted, v6.x does not yet support utf8mb4_0900_ai_ci.

| username: ti-tiger | Original post link

  • When creating a database or table, you can use the DEFAULT COLLATE clause to specify the default collation.
  • When executing SQL statements, you can use the COLLATE clause to specify the collation for a particular field or expression.
  • When connecting to the TiDB server, you can use the collation_server and collation_connection parameters to set the server-side and client-side collations.
ALTER DATABASE test DEFAULT COLLATE utf8mb4_0900_ai_ci;
SELECT * FROM users ORDER BY name COLLATE utf8mb4_0900_ai_ci;
mysql -h tidb-server -u root -p --collation-server=utf8mb4_0900_ai_ci --collation-connection=utf8mb4_0900_ai_ci
| username: 气死人的萌新 | Original post link

Buddy, I tried it the way you suggested, but it doesn’t support it.


| username: Kongdom | Original post link

:astonished: How should we understand the change log for v2? Isn’t that what it means?

| username: 有猫万事足 | Original post link

Looking at the modified test content, I feel that it was indeed supported at that time. Later, it might have become invalid due to some changes. Then it was supported again.

| username: Kongdom | Original post link

:joy: This is a bit awkward, I thought I had misled him.

| username: xfworld | Original post link

Such an old version :rofl:

It’s not really misleading, right? The later versions have returned… Isn’t it about to be supported again soon? :melting_face: :melting_face: :melting_face:

| username: zhanggame1 | Original post link

No, wait for the latest version.

| username: 气死人的萌新 | Original post link

Boss, when deploying, the TiKV node disks need to use the ext4 file system format. Do the TiDB server and PD node disks also need to use the ext4 file system format? I see that the official standard deployment only specifies the file system format required for TiKV. Thanks for your answer.

| username: Kongdom | Original post link

There are no specific requirements for TiDB server and PD nodes, but since TiKV nodes involve frequent read and write operations, there are requirements for the file system format.

| username: 气死人的萌新 | Original post link

I checked, on October 12th at 20:00, version 7.4 became compatible with 8.0.
TiDB 7.4 Release: Officially Compatible with MySQL 8.0 (qq.com)

| username: 气死人的萌新 | Original post link

I just don’t know when it can go into production.