What is the difference between utf8_unicode_ci and utf8_general_ci in Mysql?

The content of this article is about the difference between utf8_unicode_ci and utf8_general_ci in Mysql? It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

What is the difference between utf8_general_ci and utf8_unicode_ci in Mysql? In programming languages, unicode is usually used to process Chinese characters to prevent garbled characters. So in MySQL, why does everyone use utf8_general_ci instead of utf8_unicode_ci?

After using it for so long, I found that I didn’t even know the difference between utf_bin and utf_general_ci. .
ci is case insensitive, that is, "case insensitive", a and A will be treated as the same in character judgment;
bin is binary, a and A will be treated differently.
For example, if you Run:
SELECT * FROM table WHERE txt = 'a'
Then you will not find the line with txt = 'A' in utf8_bin, but utf8_general_ci can.
utf8_general_ci is not case-sensitive. You will use this when registering your username and email address.
utf8_general_cs is case-sensitive. If this is used for username and email, there will be adverse consequences.
utf8_bin: String Each string is compiled and stored with binary data. It is case-sensitive and can store binary content

1. Official document description
The following is an excerpt from the Mysql 5.1 Chinese manual about utf8_unicode_ci and utf8_general_ci:

Currently, the utf8_unicode_ci collation rule only partially supports the Unicode collation rule algorithm. Some characters are still not supported. Also, combined tokens are not fully supported. This mainly affects some minority languages in Vietnam and Russia, such as: Udmurt, Tatar, Bashkir and Mari.

The most important feature of utf8_unicode_ci is to support expansion, that is, when a letter is regarded as equal to other letter combinations. For example, 'ß' is equivalent to 'ss' in German and some other languages.

utf8_general_ci is a legacy collation rule and does not support extensions. It is only capable of character-by-character comparisons. This means that comparisons made by the utf8_general_ci collation are fast, but less accurate than those using the utf8_unicode_ci collation).

For example, using the two collation rules utf8_general_ci and utf8_unicode_ci the following comparisons are equal:
Ä = A
Ö = O
Ü = U

Between the two collation rules The difference is that for utf8_general_ci the following equation holds:
ß = s

However, for utf8_unicode_ci the following equation holds:
ß = ss

For one language only When sorting using utf8_unicode_ci does not work well, the utf8 character set collation rules related to the specific language are implemented. For example, for German and French, utf8_unicode_ci works just fine, so there is no need to create special utf8 collation rules for these two languages.

utf8_general_ci also works with German and French, except that 'ß' equals 's' instead of 'ss'. If your application can accept this, you should use utf8_general_ci because it is fast. Otherwise, use utf8_unicode_ci since it is more accurate.

If you want to use gb2312 encoding, it is recommended that you use latin1 as the default character set of the data table, so that you can directly insert data in the command line tool in Chinese and display it directly. Do not use gb2312 Or gbk and other character sets. If you are worried about query sorting and other issues, you can use binary attribute constraints, for example:

create table my_table ( name varchar(20) binary not null default '')type=myisam default charset latin1;

2. Brief summary
utf8_unicode_ci and utf8_general_ci for Chinese and English There is no real difference.
utf8_general_ci The proofreading speed is fast, but the accuracy is slightly worse.
utf8_unicode_ci has high accuracy, but the proofing speed is slightly slower.

If your application is in German, French or Russian, please be sure to use utf8_unicode_ci. Generally, it is enough to use utf8_general_ci, and no problem has been found so far. . .

3. Detailed summary

1. For a language, only when the utf8_unicode_ci sorting is not done well, the utf8 character set correction related to the specific language will be performed. rule. For example, for German and French, utf8_unicode_ci works just fine, so there is no need to create special utf8 collation rules for these two languages.
2. utf8_general_ci is also applicable to German and French, except that '?' is equal to 's', not 'ss'. If your application can accept this, you should use utf8_general_ci because it is fast. Otherwise, use utf8_unicode_ci since it is more accurate.

Use one sentence to summarize the above paragraph: utf8_unicode_ci is more accurate, and utf8_general_ci is faster. Under normal circumstances, the accuracy of utf8_general_ci is enough for our use. After I read many program source codes, I found that most of them also use utf8_general_ci, so when creating a new database, generally choose utf8_general_ci.

4. How to use UTF8 in MySQL5.0
Add the following parameters in my.cnf

[mysqld] init_connect='SET NAMES utf8′ default-character-set=utf8 default-collation = utf8_general_ci

Execute query mysql> show variables; Related as follows:

character_set_client | utf8 character_set_connection | utf8 character_set_database | utf8 character_set_results | utf8 character_set_server | utf8 character_set_system | utf8

collation_connection | utf8_general_ci collation_database | utf8_general_ci collation_server | utf8_general_ci

Personal opinion, for the use of databases, utf8 - general is accurate enough, and compared with utf8 - unicode, it has an advantage in speed, so you can use it with confidence

附1：旧数据升级办法

以原来的字符集为latin1为例，升级成为utf8的字符集。原来的表: old_table (default charset=latin1)，新表：new_table(default charset=utf8)。

第一步：导出旧数据

mysqldump --default-character-set=latin1 -hlocalhost -uroot -B my_db --tables old_table > old.sql

第二步：转换编码(类似unix/linux环境下)

iconv -t utf-8 -f gb2312 -c old.sql > new.sql

或者可以去掉 -f 参数，让iconv自动判断原来的字符集

iconv -t utf-8 -c old.sql > new.sql

在这里，假定原来的数据默认是gb2312编码。

第三步：导入

修改old.sql，在插入/更新语句开始之前，增加一条sql语句： "SET NAMES utf8;"，保存。

mysql -hlocalhost -uroot my_db < new.sql

大功告成！！

附2：支持查看utf8字符集的MySQL客户端有
1.) MySQL-Front，据说这个项目已经被MySQL AB勒令停止了，不知为何，如果国内还有不少破解版可以下载（不代表我推荐使用破解版 :-P）。
2.) Navicat，另一款非常不错的MySQL客户端，汉化版刚出来，还邀请我试用过，总的来说还是不错的，不过也需要付费。
3.) PhpMyAdmin，开源的php项目，非常好。
4.) Linux下的终端工具（Linux terminal），把终端的字符集设置为utf8，连接到MySQL之后，执行 SET NAMES UTF8; 也能读写utf8数据了。

本篇文章到这里就已经全部结束了，更多其他精彩内容可以关注PHP中文网的MySQL视频教程栏目！

The above is the detailed content of What is the difference between utf8_unicode_ci and utf8_general_ci in Mysql?. For more information, please follow other related articles on the PHP Chinese website!