MySQL中的编码相关总结

MySQL中的编码相关总结

字符集

字符集，Character Set,是一组字符的集合，其中的每个字符，都有唯一的编码。不同的字符集，包含的字符数量不同，对字符的编码方式也不同。
校对规则: Collation,用于比较同一个字符集中的字符，比如:utf8_general_ci 是 utf8 字符集的一种校对规则，其中 ‘ci’ 意味着 case-insensitive（不区分大小写）.

MySQL中字符集相关变量

在 MySQL 中，字符集和校对规则可以应用在多个级别，包括服务器级别、数据库级别、表级别、列级别。这意味着你可以为整个服务器设置默认的字符集和校对规则，也可以为特定的数据库、表或列设置不同的字符集和校对规则。

character_set_client是系统变量，表示客户端使用的字符集。这个变量，是给MySQL服务器使用，MySQL服务器根据这个变量来处理客户端发过来的数据。character_set_client只影响从客户端接收的数据。对于从服务器发送到客户端的数据的字符编码，依赖于另一个系统变量: character_set_results。

在大多数情况下，你应该确保 character_set_client、character_set_results 以及 character_set_connection 这三个变量有相同的值，以保持整个通信过程的一致性。

这里还有一个character_set_connection。character_set_connection 是 MySQL 中的一个系统变量，它设置了 MySQL 服务器在与客户端通信时所使用的字符集。更具体地说，它定义了 MySQL 在创建新的连接时，或者在处理查询语句时，将查询语句转换为什么样的字符集。

当 MySQL 服务器收到来自客户端的查询时，服务器会首先将查询从 character_set_client 转换为 character_set_connection。然后，服务器会使用 character_set_connection 来解析并执行查询。这样可以确保即使客户端和服务器使用不同的字符集，服务器也可以正确地解析查询。

例如，如果 character_set_client 是 latin1，而 character_set_connection 是 utf8，那么当服务器收到一个查询时，它会首先将查询从 Latin1 转换为 UTF-8，然后再解析并执行查询。

那么为什么有了character_set_client还需要character_set_connection？
character_set_client：这个参数决定了 MySQL 服务器如何解析从客户端发送过来的查询，换句话说，它告诉服务器客户端使用的是什么字符集。也就是说，character_set_client虽然是MySQL服务中的变量，但是这个变量是用来告诉MySQL，客户端用的是什么字符集，这个变量的实际应该的值，是取决于MySQL客户端的。
character_set_connection：这个参数定义了在 MySQL服务器内部处理查询时所使用的字符集。也就是说，MySQL工作时，内部使用的字符集。所以，在服务器处理查询时，它会将客户端发送过来的查询从 character_set_client 转换为 character_set_connection。在进行 SQL 查询的解析和执行时，MySQL 会使用 character_set_connection。

在大多数情况下，这两个参数应该设为相同的值，以避免不必要的字符集转换，这可能会导致性能下降和数据混乱。

和字符集相关的系统变量，还有character_set_database，character_set_server，character_set_system。

character_set_database：这个参数设置了当前数据库内使用的默认字符集。当你在这个数据库中创建新的表或者列时，如果没有显式指定字符集，那么就会使用这个参数的值作为默认字符集,也就是它的作用范围是当前数据库那得表和列。这里指的新建表时，为表的所有字段(或者指定字段)的数值设置的默认编码方式。
character_set_server：这个参数设置当前连接操作MySQL服务器的默认字符集。当你创建新的数据库时，如果没有显式指定字符集，那么就会使用这个参数的值作为默认字符集。此外，它也会影响到一些字符串函数的行为，例如 CONCAT() 或 SUBSTRING()。也就是说，它的作用范围是给数据库设置默认的字符集。这里指的新建数据库时，为所有表的所有字段的数值设置的默认编码方式。
character_set_system：这个参数设置了系统元数据的字符集，包括数据库名、表名、列名、列别名等等。这个参数的值通常是utf8，并且在大多数情况下你不需要更改它。

character_set_database和character_set_server设置的是表中数据的默认编码，而character_set_system设置的是库名、表名、列名、列别名等的字符编码。character_set_server的作用范围大于character_set_database，但是character_set_database的优先级大于character_set_server。

需要注意的是: 在登录到mysql服务器之后，执行的:SHOW VARIABLES LIKE 'character_set%';查看的是当前连接使用的相关字符串，默认情况下：

mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

如果要在命令行中设置连接时使用的字符集，可以使用--default-character-set选项,此时再查看相关的字符集:

mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

对比会发现character_set_client,character_set_database,character_set_results这3个变量，都变成了utf8。

注意，--default-character-set是可以设置utf8或者utf8mb4的，当设置为utf8mb4时：当前连接的信息:

mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

此时，character_set_client,character_set_database,character_set_results这3个变量，都变成了utf8mb4。

SpringBoot项目中设置字符集变量

在Springboot项目中，可以通过JDBC的URL参数来设置character_set_client。这可以通过在连接URL中添加 characterEncoding 参数来完成。

以下是设置 character_set_client 为 utf8 的示例：

spring.datasource.url=jdbc:mysql://localhost:3306/db_name?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC

在这个例子中，useUnicode=true 参数指示应用程序使用 Unicode 字符集，characterEncoding=utf8 参数则设置了 character_set_client、character_set_connection 和 character_set_results 这三个参数的值。

为什么有了characterEncoding，还需要useUnicode=true参数？首先，useUnicode这个参数，是给JDBC驱动程序用的，告诉驱动程序，应用程序会将字符串以 Unicode 形式处理和存储，所以当应用程序和驱动程序数据交换时，应该使用Unicode编码；其次，useUnicode表示使用Unicode编码，而characterEncoding表示具体使用哪种编码方式。Unicode编码是一种编码规范，而utf8是Unicode编码的一种实现。只有当useUnicode设置为true时，characterEncoding设置的值，才会被考虑使用。

utf8和utf8mb4

在MySQL中，utf8和utf8mb4是不同的字符集，那么在jdbc的url中设置characterEncoding时，应该如何设置呢？
实际上，在连接时MySQL时，不管是utf8还是utf8mb4,都应该设置为utf8.这是因为 MySQL 的 Java 连接器 (JDBC Connector/J) 不接受 utf8mb4 作为 characterEncoding 的值，它只识别utf8。

即便如此，utf8 在这里仍然可以用来表示真正的 UTF-8 编码，包括 4 字节的 Unicode 字符。这得益于 MySQL 服务器端与客户端之间的智能处理：即使 JDBC URL 中设定的 characterEncoding=utf8，如果服务器端的字符集设为 utf8mb4，那么客户端仍然可以处理 4 字节的 UTF-8 字符。