首页 > 数据库 >MySQL如何使用字符集配置选项

MySQL如何使用字符集配置选项

时间：2024-04-22 11:35:19浏览次数：15  
标签：选项 binary set latin1 utf8mb4 字符集 mysql character MySQL

MySQL中与character_set有关的配置选项有8个，分别是：

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     | utf8mb3                             | 
| character_sets_dir       | /usr/share/percona-server/charsets/ | 
+--------------------------+-------------------------------------+

如果，不仔细阅读文档，很难区分这些配置项的用途；此外，有些配置项如果不经过深入的测试，也很难知道MySQL是如何使用这些配置项的。

下面将通过对它们进行分组和解释来简化这些配置项的目的。我还将使用示例说明MySQL如何使用和处理这些character_set选项，以便用户可以减少数据丢失和/或错误的发生。最后，我将解释如何使用“set names”命令将必要的配置组织成相同的。

·character_sets_dir：定义动态加载字符集的目录。
·character_set_filesystem：MySQL处理文件的时候，需要知道文件名，在打开文件之前，文件名会从character_set_client转换成character_set_filesystem的设置，默认是binary，表示不会发生转换。大多数时候，保持默认值，除非你明确知道要转换。

如果列中存储的是字符类型的数据(比如varchar、char、text等)，MySQL需要知道这些字符属于哪个字符集，从而正确地存储和解析。涉及的变量有：
·character_set_system：如果没有设置character_set_server的值，character_set_system定义了system字符集和collation
·character_set_server：如果没有设置character_set_database的值，character_set_server定义了server的字符集和collation
·character_set_database：在执行create database命令的时候，如果没有指定database的字符集和collation，character_set_database定义了database的字符集和collation；如果create table没有指定表的字符集和collation，则继承数据库的配置；如果create table没有指定列的字符集和collation，则继承表的配置

换句话说，如果在create table的时候，为列指定了字符集和collation，其它级别的配置都将被忽略。例如：

mysql> CREATE TABLE t1 ( c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,     
                         c2 VARCHAR(100) COLLATE utf8mb4_general_ci,     
                         c3 VARCHAR(100) )  DEFAULT CHARACTER SET=latin1;
mysql> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME, CHARACTER_SET_NAME from information_schema.columns where table_schema='test' and table_name='t1';
+--------------+------------+-------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | CHARACTER_SET_NAME |
+--------------+------------+-------------+--------------------+
| test         | t1         | c1          | NULL               |
| test         | t1         | c2          | utf8mb4            |
| test         | t1         | c3          | latin1             |
+--------------+------------+-------------+--------------------+

这里，没有没有为列c3指定字符集，因此默认是使用latin1，即create table时指定的字符集。

·character_set_client：MySQL Server将客户端发送的语句的字符集设置成character_set_client的值。
·character_set_connection：MySQL Server将客户端发送的语句的字符集从character_set_client转换成character_set_connection的值。
·character_set_results：返回结果给客户端使用的字符集。如果不想做任何转换，将character_set_results设置成null、或binary。

下面来通过例子进一步演示一下。

 

示例一

mysql> set names latin1;
mysql> select @@character_set_client,@@character_set_connection;##二者的设置相同，不会发生转换
+------------------------+----------------------------+
| @@character_set_client | @@character_set_connection |
+------------------------+----------------------------+
| latin1                 | latin1                     |
+------------------------+----------------------------+
mysql> select binary("王");
+------------------------------+
| binary("王")                 |
+------------------------------+
| 0xE78E8B                     |
+------------------------------+
mysql> select binary("李");
+------------------------------+
| binary("李")                 |
+------------------------------+
| 0xE69D8E                     |
+------------------------------+
mysql> set names utf8mb4;
mysql> select @@character_set_client,@@character_set_connection;
+------------------------+----------------------------+
| @@character_set_client | @@character_set_connection |
+------------------------+----------------------------+
| utf8mb4                | utf8mb4                    |
+------------------------+----------------------------+
mysql> select binary("王");
+------------------------------+
| binary("王")                 |
+------------------------------+
| 0xE78E8B                     |
+------------------------------+
mysql> select binary("李");
+------------------------------+
| binary("李")                 |
+------------------------------+
| 0xE69D8E                     |
+------------------------------+

不管字符集是latin1、utf8mb4， binary("王")和binary("李")的返回值不变。

示例二
(1)从小的字符集转成大的字符集

mysql> set character_set_client=latin1; set character_set_connection=utf8mb4;
mysql> select @@character_set_client,@@character_set_connection;
+------------------------+----------------------------+
| @@character_set_client | @@character_set_connection |
+------------------------+----------------------------+
| latin1                 | utf8mb4                    |
+------------------------+----------------------------+
mysql> select binary("王");
+------------------------------+
| binary("王")                 |
+------------------------------+
| 0xC3A7C5BDE280B9             |
+------------------------------+
mysql> select binary("李");
+------------------------------+
| binary("李")                 |
+------------------------------+
| 0xC3A6C29DC5BD               |
+------------------------------+

为何binary("王")和binary("李")的返回值和示例一不同？因为MySQL做了转换，从character_set_client(latin1)转换成了character_set_connection(utf8mb4)，即MySQL会使用character_set_client(latin1)解析字符串，然后将结果解析成character_set_connection(utf8mb4)。

可以来确认一下：

mysql> select binary(convert(convert('王' using latin1) using utf8mb4));
+-------------------------------------------------------------------------------+
| binary(convert(convert('王'  using latin1) using utf8mb4))                    |
+-------------------------------------------------------------------------------+
| 0xC3A7C5BDE280B9                                                              | 
+-------------------------------------------------------------------------------+
mysql> select binary(convert(convert('李' using latin1) using utf8mb4));
+-------------------------------------------------------------------------------+
| binary(convert(convert('李'  using latin1) using utf8mb4))                    |
+-------------------------------------------------------------------------------+
| 0xC3A6C29DC5BD                                                                | 
+-------------------------------------------------------------------------------+


(2)从大的字符集转成小的字符集

mysql> set character_set_client=utf8mb4; set character_set_connection=latin1;
mysql> select @@character_set_client,@@character_set_connection;
+------------------------+----------------------------+
| @@character_set_client | @@character_set_connection |
+------------------------+----------------------------+
| utf8mb4                | latin1                     |
+------------------------+----------------------------+
mysql> select binary("王");
+--------------------------+
| binary("?")              |
+--------------------------+
| 0x3F                     |
+--------------------------+
mysql> select binary("李");
+--------------------------+
| binary("?")              |
+--------------------------+
| 0x3F                     |
+--------------------------+

MySQL会做character_set_client(utf8mb4)到character_set_connection(latin1)的转换，因为是从大字符集转换成小字符集，所以会发生数据丢失。binary("王")和binary("李")的返回值相同，就是因为发生了数据丢失。

mysql> select binary(convert(convert('王' using utf8mb4) using latin1));
+-------------------------------------------------------------------------------+
| binary(convert(convert('王'  using utf8mb4) using latin1))                    |
+-------------------------------------------------------------------------------+
| 0x3F                                                                          |
+-------------------------------------------------------------------------------+
mysql> select binary(convert(convert('李' using utf8mb4) using latin1));
+-------------------------------------------------------------------------------+
| binary(convert(convert('李'  using utf8mb4) using latin1))                    |
+-------------------------------------------------------------------------------+
|0x3F                                                                           |
+-------------------------------------------------------------------------------+

 

示例三
列的字符集和character_set_connection不同，也会发生转换。
(1)从小的字符集转成大的字符集
C2列是utf8mb4字符集，与character_set_connection latin1不同，所以实际插入的数据是转换的结果，将字符串解析成latin1，然后将输出的结果解析成utf8mb4。
convert(convert('王' using latin1) using utf8mb4),binary("王")按照utf8mb4返回的结果是：0xC3A7C5BDE280B9
c3列是latin1字符集，不会涉及到转换，插入的数据就是最初的数据，即binary("李")按latin1就是0xE69D8E

mysql> set names latin1;
mysql> select @@character_set_client,@@character_set_connection;
+------------------------+----------------------------+
| @@character_set_client | @@character_set_connection |
+------------------------+----------------------------+
| latin1                 | latin1                     |
+------------------------+----------------------------+
mysql> select binary("王");
+------------------------------+
| binary("王")                 |
+------------------------------+
| 0xE78E8B                     |
+------------------------------+
mysql> select binary("李");
+------------------------------+
| binary("李")                 |
+------------------------------+
| 0xE69D8E                     |
+------------------------------+
mysql> insert into t1(c1,c2,c3) values(1,"王","李");
mysql> select * from t1 where c1=1;
+----+------+------+
| c1 | c2   | c3   |
+----+------+------+
|  1 | 王   | 李   |
+----+------+------+
mysql> select c1,binary(c2),binary(c3) from t1 where c1=1;
+----+------------------------+------------------------+
| c1 | binary(c2)             | binary(c3)             |
+----+------------------------+------------------------+
|  1 | 0xC3A7C5BDE280B9       | 0xE69D8E               |
+----+------------------------+------------------------+

C2列是utf8mb4字符集，不同于character_set_results latin1。因此，在读取数据的时候，需要数据转换。

mysql> select @@character_set_results;
+-------------------------+
| @@character_set_results |
+-------------------------+
| latin1                  |
+-------------------------+
mysql> select * from t1 where c1=1;
+----+------+------+
| c1 | c2   | c3   |
+----+------+------+
|  1 | 王   | 李   |
+----+------+------+

MySQL会将c2解析成utf8mb4，然后转换成latin1：

mysql> select convert(convert(0xC3A7C5BDE280B9 using utf8mb4) using latin1);
+---------------------------------------------------------------+
| convert(convert(0xC3A7C5BDE280B9 using utf8mb4) using latin1) |
+---------------------------------------------------------------+
| 王                                                            |
+---------------------------------------------------------------+

c3直接被解析成latin1：

mysql> select convert(0xE69D8E using latin1);
+--------------------------------+
| convert(0xE69D8E using latin1) |
+--------------------------------+
| 李                             |
+--------------------------------+

(2)从大的字符集转成小的字符集
character_set_connection 被设置成utf8mb4，c2列被定义为utf8mb4字符集，c3列是latin1字符集。
c2可以工作的很好，c3列会报错error 1366 (HY000): Incorrect string value
因为convert(convert('李' using utf8mb4) using latin1会发生数据丢失,从而在插入错误数据的时候会报错：

mysql> set names utf8mb4 ;
mysql> select @@character_set_client,@@character_set_connection;
+------------------------+----------------------------+
| @@character_set_client | @@character_set_connection |
+------------------------+----------------------------+
| utf8mb4                | utf8mb4                    |
+------------------------+----------------------------+
mysql> select binary("王");
+------------------------------+
| binary("王")                 |
+------------------------------+
| 0xE78E8B                     |
+------------------------------+
mysql> select binary("李");
+------------------------------+
| binary("李")                 |
+------------------------------+
| 0xE69D8E                     |
+------------------------------+
mysql> insert into t1(c1,c2,c3) values(2,"王","李");
ERROR 1366 (HY000): Incorrect string value: '\xE6\x9D\x8E' for column 'c3' at row 1
mysql> select binary(convert(convert('李' using utf8mb4) using latin1));
+------------------------------------------------------------------------------+
| binary(convert(convert('李' using utf8mb4) using latin1))                    |
+------------------------------------------------------------------------------+
| 0x3F                                                                         |
+------------------------------------------------------------------------------+

 

示例四
在innodb中，如果想直接将一个列从大的字符集转换成小的字符集，不涉及配置变量的话，也会发生相同的错误：

mysql> select c1, binary(c2), binary(c3) from t1;
+----+------------------------+------------------------+
| c1 | binary(c2)             | binary(c3)             |
+----+------------------------+------------------------+
|  1 | 0xC3A7C5BDE280B9       | 0xE69D8E               |
|  3 | 0xE78E8B               | 0x4131                 |
+----+------------------------+------------------------+
mysql> ALTER TABLE t1 MODIFY c2 varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL;
ERROR 1366 (HY000): Incorrect string value: '\xE7\x8E\x8B' for column 'c2' at row 2

 

示例五
在上面的例子中，多次使用了set names命令。这样可以方便的修改设置。

mysql> set names latin1;
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     | utf8mb3                             |
| character_sets_dir       | /usr/share/percona-server/charsets/ |
+--------------------------+-------------------------------------+
8 rows in set (0.00 sec)
mysql> set names 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     | utf8mb3                             |
| character_sets_dir       | /usr/share/percona-server/charsets/ |
+--------------------------+-------------------------------------+
8 rows in set (0.00 sec)

 

 

MySQL进行潜在的转换，以确保character_set_client、character_set_connection和列的目标字符集是对齐的。

我们需要小心地执行转换，因为当MySQL从一个允许更多值(更大)的字符集转换到一个允许更少值(更小)的字符集，如utf8mb4->latin1，它可能会导致数据丢失或"ERROR 1366 (HY000) Incorrect string value"可能发生。

我们可以通过命令"set names"使character_set_client, character_set_connection, character_set_results完全相同。

虽然可以根据需要定义列的字符集，在将列定义为不同于character_set_connection的字符集之前，你可能需要重新考虑一下。

 

标签：选项,binary,set,latin1,utf8mb4,字符集,mysql,character,MySQL
From： https://www.cnblogs.com/abclife/p/17015201.html

相关文章

  • [数据库] 排查MySQL锁表情况及解决思路 [转]
    0序1排查方法：查看当前锁表事务由于出现的是锁表的问题，所以第一步从数据库入手，查看导致锁表的SQL语句是什么；查看是否锁表SQL语句；SELECT*FROMINFORMATION_SCHEMA.INNODB_LOCKS;该命令的输出结果包含了当前事务等待的锁资源的相关信息，包括事务ID、锁的类型、锁的模式、被......
  • docker安装mysql
    下载最新的官方镜像dockerpullmysql:latest启动mysqldockerrun-d-p3306:3306\--privileged=true\-v/app/mysql/log:/var/log/mysql\-v/app/mysql/data:/var/lib/mysql\-v/app/mysql/conf:/etc/mysql/conf.d\......
  • mysql
    Mysql的基本架构是什么样的？MySQL大致可以分为server层和存储引擎层两部分Server层包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。存储引擎层负责数据存储和提取，支持InnoDB、MyISAM、Memory等多个存储引......
  • MYSQL explain的使用
     #1.table：表名#查询的每一行记录都对应着一个单表EXPLAINSELECT*FROMs1;#s1:驱动表s2:被驱动表EXPLAINSELECT*FROMs1INNERJOINs2;#2.id：在一个大的查询语句中每个SELECT关键字都对应一个唯一的idSELECT*FROMs1WHEREkey1='a';SELECT*FROMs1INNER......
  • [MYSQL] MYSQL ERROR CODE 错误编号合集
    1[MYSQL]MYSQLERRORCODE错误编号合集错误码错误解释备注说明错误：1236SQLSTATE:HY000(ER_MASTER_FATAL_ERROR_READING_BINLOG)消息：从二进制日志读取数据时，获得来自主服务器的致命错误%d:’%s’。错误：1237SQLSTATE:HY000(ER_SLAVE_IGNORED_TABLE)消息：......
  • MySQL-06.索引的数据结构
    1.为什么使用索引索引是存储引擎用于快速找到数据记录的一种数据结构，就好比一本书的目录部分，通过目录中找到对应文章的页码，便可快速定位到需要的文章。MySQL中的索引也是一样的道理，进行数据查找时，首先查看查询条件是否命中某条索引，符合则通过索引查找相关数据，如果不符合则需要全......
  • MySql入门操作 3.0
    子查询：在查询列中设定特定信息，筛选要的特殊信息： 标量子查询：在内部的select中设定具体常量，通过常量进行筛选对应的列；具体格式不太好描述，如果对于上一章所使用的指令可以熟练应用那么这里应该也没啥问题，就是一个嵌套类比罢了下面直接给示例：相当于在要设定的条件中再使用一......
  • MAC配置mysql：mac下启动/停止/重启mysql服务
    Mac安装msyql 第一种就是直接brew installmysql第二种就是下载安装咯：https://dev.mysql.com/downloads/mysql/配置mysql系统环境变量vim  .bash_profile增加 exportPATH=${PATH}:/usr/local/mysql/binsource.bash_profile  回车执行，运行环境变量。再输入m......
  • [转帖]MySQL知识体系的三驾马车
    https://plantegg.github.io/2019/05/26/MySQL%E7%9F%A5%E8%AF%86%E4%BD%93%E7%B3%BB%E7%9A%84%E4%B8%89%E9%A9%BE%E9%A9%AC%E8%BD%A6/ MySQL知识体系的三驾马车在我看来要掌握好MySQL的话要理解好这三个东西：索引（B+树）日志（WAL）事务(可见性)索引决定了查询的性能，也是用......
  • MySQL的安装与配置——详细教程
    转载自：Winton-H原文链接免安装版的MysqlMySQL关是一种关系数据库管理系统，所使用的SQL语言是用于访问数据库的最常用的标准化语言，其特点为体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，在Web应用方面MySQL是最好的RDBMS(RelationalDatabaseManagementSys......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99