下面我们来实践下三种比较规则的异同:
# 创建表 指定列为不同的比较规则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比较规则
最后以思维导图的方式总结下本文的主要内容:
文章插图
推荐阅读
- 华为手机下滑怎么不显示通知栏
- 喝茶谈生意,不用去茶楼,有这3款茶,在办公室也能把客户拿下
- 林下参泡水喝的功效,玫瑰花茶泡水喝的功效
- 4 个 Linux 命令行下的 Markdown 工具
- CentOS下MySQL8.0的超详细的安装及配置文档
- ping 域名+端口 Windows和Linux下检测网络是否可用
- 信用卡用几个月可以提额
- 保养时机油没用完?这样保存下次还能用!
- 下雨天,水淹车,上了保险有的也不赔!别大意!
- 线上手机和线下手机有什么区别