support variable of `default_collation_for_utf8`

[PetterZhukov]

Feature Request

My application originally used a MySQL database. When creating tables with the utf8 charset, the expected collation was utf8_general_ci.

Since the default collation for utf8 in TiDB is utf8_bin, I need to modify either the business logic or the CREATE TABLE statements.

However, because a large number of applications are involved, modifying the business logic or the CREATE TABLE statements is difficult to execute.

Therefore, I hope to add a system variable to set the default collation for the utf8 charset.

TiDB already supports default_collation_for_utf8mb4, so the same logic can be implemented.

[ti-chi-bot]

Welcome @PetterZhukov! It looks like this is your first issue to pingcap/tidb 🎉

[D3Hunter]

maybe create the database with your default collation, then the tables created inside it should follow the default from the database

[YangKeao]

TiDB supports 'default_collation_for_utf8mb4' to keep compatible with MySQL. I personally don't think it's a good practice. MySQL used this variable to avoid replication/upgrade issues between different MySQL versions, and it also doesn't have default_collation_for_utf8.

As @D3Hunter said, setting collation for databases is more suggested.

[PetterZhukov]

TiDB supports 'default_collation_for_utf8mb4' to keep compatible with MySQL. I personally don't think it's a good practice. MySQL used this variable to avoid replication/upgrade issues between different MySQL versions, and it also doesn't have default_collation_for_utf8.TiDB 支持“default_collation_for_utf8mb4”以保持与 MySQL 的兼容性。我个人认为这不是一个好的做法。MySQL 使用此变量来避免不同 MySQL 版本之间的复制/升级问题，并且它也没有 default_collation_for_utf8。

As @D3Hunter said, setting collation for databases is more suggested.如前所述，更建议为数据库设置排序规则。

We have some SQL for MySQL that may encounter some issues when migrating to TiDB: the tables created define default characters but no defining collation, so they will use the default collation about TiDB's default config.
In this case, it is hoped that config can be used to control and avoid modifying SQL.

[D3Hunter]

maybe create the database with your default collation, then the tables created inside it should follow the default from the database

@PetterZhukov see above solution.

it's not a good idea to create such variable for each charset

[PetterZhukov]

maybe create the database with your default collation, then the tables created inside it should follow the default from the database

@PetterZhukov see above solution.

it's not a good idea to create such variable for each charset

I know that modifying SQL is a better idea.
From another perspective, supporting the modification of default collation can better seamlessly adapt to the cost of migrating from pure MySQL databases