邯郸网站设计有哪些,友链交易网,o2o网站开发公司,实时热搜榜榜单前言 在创建数据库时#xff0c;我们经常会需要填写数据库的所用字符集、排序规则#xff0c;字符集和排序规则是两个非常重要的概念#xff0c;它们决定了数据库如何存储和比较字符串数据。在 MySQL 中#xff0c;常用的存储字符集有 utf8、utf8mb4#xff0c;而排序字符…
前言 在创建数据库时我们经常会需要填写数据库的所用字符集、排序规则字符集和排序规则是两个非常重要的概念它们决定了数据库如何存储和比较字符串数据。在 MySQL 中常用的存储字符集有 utf8、utf8mb4而排序字符集有 utf8mb4_unicode_ci、utf8mb4_general_ci、utf8mb4_bin、utf8mb4_0900_ai_ci如下图所示。今天我们来探讨一下MySQL中几种常用的字符集和排序规则之间的区别和适用场景。 一、存储字符集 utf8mb4 和 utf8 是 MySQL 中两种常用的字符集它们都可以用来存储 Unicode 字符但是有一些区别和联系。接下来我们将从以下几个方面对比 utf8mb4 和 utf8。
1.1 字符集简介 utf8 是 Mysql 中最早支持的 Unicode 字符集是一种可变长度的字符编码方式可以表示世界上几乎所有的字符。它使用 1 到 3 个字节的 UTF-8 字符具体取决于字符的编码范围。 要在 Mysql 中保存 4 字节长度的 UTF-8 字符需要使用 utf8mb4 字符集但只有 5.5.3 版本以后的才支持。个人觉得为了获取更好的兼容性应该总是使用 utf8mb4 而非 utf8。
1.2 区别详解
编码范围 UTF-8 可以表示大多数常见的字符但对于一些罕见的字符和 emoji 表情等可能无法正确表示。这些字符需要 4 个字节来编码所以 utf8 会在遇到这些字符时报错或者出现乱码。utf8mb4 则可以表示几乎所有的 Unicode 字符能够存储 Emoji 表情、部分罕用汉字、新增的 Unicode 字符等。 存储需求 由于 utf8mb4 可以表示更多的字符所以它通常需要比 UTF-8 更多的存储空间。具体来说UTF8MB4 可能需要使用 1 到 4 个字节来表示一个字符而 UTF-8 通常只需要使用 1 到 3 个字节。对于 CHAR 类型数据utf8mb4 会多消耗一些空间根据 Mysql 官方建议使用 VARCHAR 替代 CHAR。例如一个 CHAR(10) 类型的字段如果使用 utf8 字符集那么它需要保留 10 * 3 30 个字节的空间。如果使用 utf8mb4 字符集那么它需要保留 10 * 4 40 个字节的空间。对于 VARCHAR 类型的字段如果使用 utf8 字符集那么它需要额外使用一个字节来记录字符串的长度。如果使用 utf8mb4 字符集那么它需要额外使用两个字节来记录字符串的长度。 兼容性 UTF-8 是一种广泛使用的字符集几乎所有的操作系统和编程语言都支持它。因此如果需要与其他系统进行数据交换UTF-8 可能是一个更好的选择。如果需要支持更广泛的字符范围或者需要更高的安全性和兼容性那么应该使用 utf8mb4 字符集。utf8mb4 虽然可以表示更多的字符但并不是所有的系统都支持它。在使用 utf8mb4 时需要确保应用程序和数据库服务器都支持这个字符集。
1.3 选择建议
如果应用程序只需要处理常见的字符那么 UTF-8 可能是一个不错的选择它具有广泛的兼容性和较低的存储需求。如果应用程序需要处理一些罕见的字符或 emoji 表情等那么 utf8mb4 可能是更好的选择它可以确保数据能够正确地存储和显示所有的字符。在选择字符集时还需要考虑数据库的性能和存储需求。如果数据库中存储了大量的文本数据那么选择一个合适的字符集可以提高数据库的性能和存储效率 总之UTF-8 和 utf8mb4 都是 MySQL 中常用的字符集选项都可以用来存储 Unicode 字符它们之间的区别主要在于编码范围、存储需求和兼容性。在选择字符集时需要根据应用程序的具体业务需求和实际情况来进行选择以确保数据能够正确地存储和显示。虽然utf8mb4 比 utf8 需要更多的存储空间并且在某些情况下可能会导致性能略有下降。不过在现代硬件和应用场景下utf8mb4 的性能影响通常可以忽略不计。
二、排序规则
2.1 排序规则概述 排序规则是指在比较和排序字符串时所遵循的规则用于定义字符的排序和比较方式而排序规则会影响字符串的比较、排序等操作。不同的字符集可以有不同的排序规则甚至同一个字符集也可以有多种排序规则。在 MySQL中比较常用的字符集是utf8和utf8mb4这两个字符集是类似的查看下 utf8mb4 字符集下支持的所有比较规则
SHOW COLLATION LIKE utf8\_%;
SHOW COLLATION LIKE utf8mb4\_%;Collation(排序方式)Charset(字符集)IdDefault(默认值)CompiledSortlen(排序)utf8mb4_general_ciutf8mb445YesYes1utf8mb4_binutf8mb446Yes1utf8mb4_unicode_ciutf8mb4224Yes8utf8mb4_icelandic_ciutf8mb4225Yes8utf8mb4_latvian_ciutf8mb4226Yes8utf8mb4_romanian_ciutf8mb4227Yes8utf8mb4_slovenian_ciutf8mb4228Yes8utf8mb4_polish_ciutf8mb4229Yes8utf8mb4_estonian_ciutf8mb4230Yes8utf8mb4_spanish_ciutf8mb4231Yes8utf8mb4_swedish_ciutf8mb4232Yes8utf8mb4_turkish_ciutf8mb4233Yes8utf8mb4_czech_ciutf8mb4234Yes8utf8mb4_danish_ciutf8mb4235Yes8utf8mb4_lithuanian_ciutf8mb4236Yes8utf8mb4_slovak_ciutf8mb4237Yes8utf8mb4_spanish2_ciutf8mb4238Yes8utf8mb4_roman_ciutf8mb4239Yes8utf8mb4_persian_ciutf8mb4240Yes8utf8mb4_esperanto_ciutf8mb4241Yes8utf8mb4_hungarian_ciutf8mb4242Yes8utf8mb4_sinhala_ciutf8mb4243Yes8utf8mb4_german2_ciutf8mb4244Yes8utf8mb4_croatian_ciutf8mb4245Yes8utf8mb4_unicode_520_ciutf8mb4246Yes8utf8mb4_vietnamese_ciutf8mb4247Yes8 这些比较规则的命名还挺有规律的具体规律如下 比较规则名称以与其关联的字符集的名称开头如上述查询结果的比较规则名称都是以utf8mb4开头的。 后边紧跟着该比较规则主要作用于哪种语言比如 utf8_polish_ci 表示以波兰语的规则比较utf8_spanish_ci 是以西班牙语的规则比较utf8_general_ci 是一种通用的比较规则。 名称后缀意味着该比较规则是否区分语言中的重音、大小写啥的具体可以用的值如下 后缀英文释义描述_aiaccent insensitive不区分重音。也就是说排序时 e、è、é、ê 和 ë 之间没有区别。_asaccent sensitive区分重音。_cicase insensitive不区分大小写即 “大小写不敏感”。_cscase sensitive区分大小写即大小写敏感。_binbinary以二进制方式比较区分大小写和重音符号。 比如 utf8mb4_general_ci 这个比较规则是以 ci 结尾的说明不区分大小写。每种字符集都有一种默认的比较规则SHOW COLLATION 返回结果中的Default 列的值为 YES 的就是该字符集的默认比较规则比如 utf8mb4 字符集默认规则就是utf8mb4_general_ci又比如utf8字符集默认规则就是 utf8_general_ci。排序规则的不同会影响字符串的比较和排序的结果进而影响索引的效率和查询的性能。一般来说_bin 排序规则的性能最高因为它只需要按照二进制方式比较字符串不需要考虑字符的大小写和重音符号等因素。_general_ci 排序规则的性能次之因为它只需要按照一般方式比较字符串不需要考虑字符的语言或地区等因素。_unicode_ci 和 _ci 排序规则的性能最低因为它们需要按照 Unicode 标准或特定语言或地区的方式比较字符串需要考虑字符的大小写和重音符号等因素。
2.2 比较规则 比较规则可以作用于四个级别分别是服务器级别、数据库级别、表级别、列级别。服务器级别的比较规则由collation_server参数控制如果创建数据库、表、列时没有显式的指定比较规则则会继承上一级的比较规则。下面给出创建及修改库、表、列的比较规则的示例语句
-- 创建数据库指定比较规则
CREATE DATABASE 数据库名[[DEFAULT] CHARACTER SET 字符集名称][[DEFAULT] COLLATE 比较规则名称];-- 修改数据库的比较规则
ALTER DATABASE 数据库名[[DEFAULT] CHARACTER SET 字符集名称][[DEFAULT] COLLATE 比较规则名称];-- 创建表时指定比较规则
CREATE TABLE 表名 (列的信息)[[DEFAULT] CHARACTER SET 字符集名称][COLLATE 比较规则名称]]-- 修改表的比较规则
ALTER TABLE 表名[[DEFAULT] CHARACTER SET 字符集名称][COLLATE 比较规则名称]# 创建时指定列的比较规则
CREATE TABLE 表名(列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],其他列...
);-- 修改列的比较规则
ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];2.3 排序字符集 utf8 字符集默认规则是 utf8_general_ci常用到的有utf8_general_ci、utf8_unicode_ci、utf8_bin等。而 utf8mb4 字符集默认规则是utf8mb4_general_ci常用到的有utf8mb4_general_ci、utf8mb4_unicode_ci、utf8mb4_bin等其他比较规则基本很少会用下面简单了解下这几种规则的异同。
utf8_bin、utf8mb4_bin 将字符串每个字符用二进制数据编译存储区分大小写也区分重音符号。_bin 的比较方法其实就是直接将所有字符看作二进制串然后从最高位往最低位比对所以很显然它是区分大小写的。
utf8_general_ci、utf8mb4_general_ci utf8_general_ci 是 utf8 字符集的一种排序规则不区分大小写也不区分重音符号。utf8mb4_general_ci 是utf8字符集的一种排序规则不支持扩展它仅能够在字符之间进行逐个比较不区分大小写但会区分重音符号。utf8_general_ci 校对规则进行的比较速度很快但是与使用 utf8mb4_unicode_ci 的校对规则相比比较正确性较差。 注意utf8mb4_general_ci 与 utf8_general_ci 基本兼容只是前者支持更多字符。 utf8_unicode_ci、utf8mb4_unicode_ci 是基于标准的Unicode来排序和比较能够在各种语言之间精确排序Unicode排序规则为了能够处理特殊字符的情况实现了略微复杂的排序算法。
utf8mb4_0900_ai_ci MySQL 8.0 默认的是 utf8mb4_0900_ai_ci属于 utf8mb4_unicode_ci 中的一种。 以前utf8mb4_general_ci 是默认排序规则。由于 utf8mb4_0900_ai_ci 排序规则现在是默认排序规则因此默认情况下新表格可以存储基本多语言平面之外的字符。现在可以默认存储表情符号。如果需要重音灵敏度和区分大小写则可以使用 utf8mb4_0900_as_cs 代替。
三、附录
3.1 utf8mb4_unicode_ci 和 utf8mb4_general_ci 的区别 准确性 排序字符集说明utf8mb4_unicode_ci是基于标准的 Unicode 来排序和比较能够在各种语言之间精确排序。utf8mb4_general_ci没有实现 Unicode 排序规则在遇到某些特殊语言或者字符集排序结果可能不一致。 注意在绝大多数情况下这些特殊字符的顺序并不需要那么精确。 性能 排序字符集说明utf8mb4_general_ci在比较和排序的时候更快。utf8mb4_unicode_ci在特殊情况下Unicode 排序规则为了能够处理特殊字符的情况实现了略微复杂的排序算法。 注意在绝大多数情况下不会发生此类复杂比较。
3.2 实践建议
在开发时个人建议:
尽可能使用 utf8mb4 字符集以确保能够支持完整的 Unicode 字符集。根据具体需求选择合适的排序规则大多数情况下可以使用 utf8mb4_general_ci。在创建表和列时就指定字符集和排序规则避免以后出现编码问题。如果需要存储大小写或重音符号敏感的数据使用 utf8mb4_bin 排序规则。对于加密数据或需要按二进制方式比较的场景也应该使用 utf8mb4_bin。
四、总结 虽然推荐用 utf8mb4_unicode_ci但是用 utf8mb4_general_ci 也没啥问题。因为 utf8mb4_unicode_ci 比较准确utf8mb4_general_ci 速度比较快。通常情况下新建数据库和创建 varchar 字符集类型字段时一般选用 utf8mb4_general_ci 就可以。 通过正确设置和使用字符集及排序规则我们可以有效地在 MySQL 中存储和处理 Unicode 字符数据确保数据的完整性和一致性。编码问题一直是web开发中的一个棘手问题希望这篇文章能够帮助大家更好地理解和应对编码相关的挑战。
