utf8字符集下的比较规则( 二 )


下面我们来实践下三种比较规则的异同:
# 创建表 指定列为不同的比较规则mysql> create table utf8_test (-> col_general varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci,-> col_unicode varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci,-> col_bin varchar(20) CHARACTER SET utf8 COLLATE utf8_bin-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; # 插入数据 每行数据各个字段值一样 mysql> select * from utf8_test;+-------------+-------------+---------+| col_general | col_unicode | col_bin |+-------------+-------------+---------+| MySQL| MySQL| MySQL|| mysql| mysql| mysql|| A| A| A|| a| a| a|| aA| aA| aA|+-------------+-------------+---------+# 查询 验证utf8_bin比较规则区分大小写mysql> select * from utf8_test where col_general='mysql';+-------------+-------------+---------+| col_general | col_unicode | col_bin |+-------------+-------------+---------+| MySQL| MySQL| MySQL|| mysql| mysql| mysql|+-------------+-------------+---------+mysql> select * from utf8_test where col_unicode='MySQL';+-------------+-------------+---------+| col_general | col_unicode | col_bin |+-------------+-------------+---------+| MySQL| MySQL| MySQL|| mysql| mysql| mysql|+-------------+-------------+---------+mysql> select * from utf8_test where col_bin='mysql';+-------------+-------------+---------+| col_general | col_unicode | col_bin |+-------------+-------------+---------+| mysql| mysql| mysql|+-------------+-------------+---------+# 排序 发现不同排序规则对顺序有影响mysql> select * from utf8_test order by col_general;+-------------+-------------+---------+| col_general | col_unicode | col_bin |+-------------+-------------+---------+| A| A| A|| a| a| a|| aA| aA| aA|| MySQL| MySQL| MySQL|| mysql| mysql| mysql|+-------------+-------------+---------+mysql> select * from utf8_test order by col_bin;+-------------+-------------+---------+| col_general | col_unicode | col_bin |+-------------+-------------+---------+| A| A| A|| MySQL| MySQL| MySQL|| a| a| a|| aA| aA| aA|| mysql| mysql| mysql|+-------------+-------------+---------+3.关于比较规则的选择与建议对于MySQL 5.7版本,一般情况下建议将字符集改为utf8,比较规则选择默认的utf8_general_ci 。utf8_general_ci相对于utf8_unicode_ci来说校对速度快,但是如果你的应用有德语、法语或者俄语,建议使用utf8_unicode_ci 。如果某个表或列字段要求区分大小写,可以单独指定该表或字段使用utf8_bin比较规则
最后以思维导图的方式总结下本文的主要内容:

utf8字符集下的比较规则

文章插图
 




推荐阅读