暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / Mysql字符集的查看、修改、踩坑

Mysql字符集的查看、修改、踩坑

一安未来 2024-04-23
158

大家好，我是一安~

前言

字符(Character
)是指人类语言中最小的表义符号。例如'A'、'B'
等；

  • 给定一系列字符，对每个字符赋予一个数值，用数值来代表对应的字符，这一数值就是字符的编码(Encoding
    )。例如，我们给字符'A'
    赋予数值0
    ，则0
    就是字符'A'的编码；
  • 给定一系列字符并赋予对应的编码后，所有这些字符和编码对组成的集合就是字符集(Character Set
    )。例如，给定字符列表为{'A','B'}
    时，{'A'=>0, 'B'=>1}
    就是一个字符集；
  • 字符序(Collation
    )是指在同一字符集内字符之间的比较规则；
  • 确定字符序后，才能在一个字符集上定义什么是等价的字符，以及字符之间的大小关系；
  • 每个字符序唯一对应一种字符集，但一个字符集可以对应多种字符序，其中有一个是默认字符序；
  • MySQL
    中的字符序名称遵从命名惯例：以字符序对应的字符集名称开头，比如以_ci
    (表示大小写不敏感)、_cs
    (表示大小写敏感)或_bin
    (表示按编码值比较)结尾。例如：在字符序utf8_general_ci
    下，字符'a'
    和'A'
    是等价的；

字符集设置

查看MYSQL
所支持的字符集

show character set;
--------|-------------------------------|-------------------|------|
Charset |Description                    |Default collation  |Maxlen|
--------+-------------------------------+-------------------+------+
armscii8|ARMSCII-8 Armenian             |armscii8_general_ci|     1|
ascii   |US ASCII                       |ascii_general_ci   |     1|
big5    |Big5 Traditional Chinese       |big5_chinese_ci    |     2|
binary  |Binary pseudo charset          |binary             |     1|
cp1250  |Windows Central European       |cp1250_general_ci  |     1|
cp1251  |Windows Cyrillic               |cp1251_general_ci  |     1|
cp1256  |Windows Arabic                 |cp1256_general_ci  |     1|
cp1257  |Windows Baltic                 |cp1257_general_ci  |     1|
cp850   |DOS West European              |cp850_general_ci   |     1|
cp852   |DOS Central European           |cp852_general_ci   |     1|
cp866   |DOS Russian                    |cp866_general_ci   |     1|
cp932   |SJIS for Windows Japanese      |cp932_japanese_ci  |     2|
dec8    |DEC West European              |dec8_swedish_ci    |     1|
eucjpms |UJIS for Windows Japanese      |eucjpms_japanese_ci|     3|
euckr   |EUC-KR Korean                  |euckr_korean_ci    |     2|
gb18030 |China National Standard GB18030|gb18030_chinese_ci |     4|
gb2312  |GB2312 Simplified Chinese      |gb2312_chinese_ci  |     2|
gbk     |GBK Simplified Chinese         |gbk_chinese_ci     |     2|
geostd8 |GEOSTD8 Georgian               |geostd8_general_ci |     1|
greek   |ISO 8859-7 Greek               |greek_general_ci   |     1|
hebrew  |ISO 8859-8 Hebrew              |hebrew_general_ci  |     1|
hp8     |HP West European               |hp8_english_ci     |     1|
keybcs2 |DOS Kamenicky Czech-Slovak     |keybcs2_general_ci |     1|
koi8r   |KOI8-R Relcom Russian          |koi8r_general_ci   |     1|
koi8u   |KOI8-U Ukrainian               |koi8u_general_ci   |     1|
latin1  |cp1252 West European           |latin1_swedish_ci  |     1|
latin2  |ISO 8859-2 Central European    |latin2_general_ci  |     1|
latin5  |ISO 8859-9 Turkish             |latin5_turkish_ci  |     1|
latin7  |ISO 8859-13 Baltic             |latin7_general_ci  |     1|
macce   |Mac Central European           |macce_general_ci   |     1|
macroman|Mac West European              |macroman_general_ci|     1|
sjis    |Shift-JIS Japanese             |sjis_japanese_ci   |     2|
swe7    |7bit Swedish                   |swe7_swedish_ci    |     1|
tis620  |TIS620 Thai                    |tis620_thai_ci     |     1|
ucs2    |UCS-2 Unicode                  |ucs2_general_ci    |     2|
ujis    |EUC-JP Japanese                |ujis_japanese_ci   |     3|
utf16   |UTF-16 Unicode                 |utf16_general_ci   |     4|
utf16le |UTF-16LE Unicode               |utf16le_general_ci |     4|
utf32   |UTF-32 Unicode                 |utf32_general_ci   |     4|
utf8    |UTF-8 Unicode                  |utf8_general_ci    |     3|
utf8mb4 |UTF-8 Unicode                  |utf8mb4_general_ci |     4|

查看MySQL
数据服务器和数据库字符集

show variables where variable_name rlike 'character|collation';
------------------------|------------------------------------|
Variable_name           |Value                               |
------------------------+------------------------------------+
character_set_client    |utf8mb4                             |
character_set_connection|utf8mb4                             |
character_set_database  |utf8                                |
character_set_filesystem|binary                              |
character_set_results   |                                    |
character_set_server    |utf8                                |
character_set_system    |utf8                                |
character_sets_dir      |/home/mysql/database/share/charsets/|
collation_connection    |utf8mb4_general_ci                  |
collation_database      |utf8_general_ci                     |
collation_server        |utf8_unicode_ci                     |

  • character_set_server
    ：默认的内部操作字符集
  • character_set_client
    ：客户端来源数据使用的字符集
  • character_set_connection
    ：连接层字符集
  • character_set_results
    ：查询结果字符集
  • character_set_database
    ：当前选中数据库的默认字符集
  • character_set_system
    ：系统元数据(字段名等)字符集
  • 还有以collation_
    开头的同上面对应的变量，用来描述字符序。

修改全局字符集

临时生效：
set character_set_connection=utf8;
set character_set_database=utf8;
set character_set_results=utf8;
set character_set_server=utf8;
set collation_connection=utf8;
set collation_database=utf8;
set collation_server=utf8;

永久生效/etc/my.cnf：
[client] 
default-character-set=utf8 
[mysqld] 
character_set_server=utf8 
character_set_client=utf8 
collation-server=utf8_general_ci 

修改库的字符集

alter database 库名 default character set 字符集;

修改表的字符集

alter table 表名 convert to character set 字符集;

修改字段的字符集

alter table 表名 modify 字段名 字段属性 character set 字符集；

字符集转换过程

CREATE TABLE `versioninfo` (
  `name` varchar(64)  NOT NULL,
  `ip` varchar(16)  NOT NULL DEFAULT '127.0.0.1',
  `version` varchar(16) COLLATE utf8_bin DEFAULT 'V1.0.0',
  `descr` varchar(128)  DEFAULT NULL,
  `updatetime` datetime DEFAULT NULL,
  PRIMARY KEY (`name`,`ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

  • MySQL Server
    收到请求时将请求数据从character_set_client
    转换为character_set_connection
    ；
  • 进行内部操作前将请求数据从character_set_connection
    转换为内部操作字符集，其确定方法如下：
    • 使用每个数据字段的CHARACTER SET
      设定值；
    • 若上述值不存在，则使用对应数据表的DEFAULT CHARACTER SET
      设定值(MySQL
      扩展，非SQL
      标准)；
    • 若上述值不存在，则使用对应数据库的DEFAULT CHARACTER SET
      设定值；
    • 若上述值不存在，则使用character_set_server
      设定值。
  • 将操作结果从内部操作字符集转换为character_set_results
    。

产生的乱码问题

  1. 我们的字段没有设置字符集，因此使用表的数据集
  2. 我们的表没有指定字符集，默认使用数据库存的字符集
  3. 我们的数据库在创建的时候没有指定字符集，因此使用character_set_server
    设定值
  4. 我们没有特意去修改character_set_server
    的指定字符集，因此使用mysq
    l默认
  5. mysql
    默认的字符集是latin1
    ，因此我们使用了latin1
    字符集，而我们character_set_connection
    的字符集是UTF-8
    ，插入中文乱码也再所难免了。

常见问题解析

向默认字符集为utf8
的数据表插入utf8
编码的数据前没有设置连接字符集，查询时设置连接字符集为utf8

  • 插入时根据MySQL
    服务器的默认设置，character_set_client、character_set_connection、character_set_results
    均为latin1
    ；
  • 插入操作的数据将经过latin1=>latin1=>utf8
    的字符集转换过程，这一过程中每个插入的汉字都会从原始的3
    个字节变成6
    个字节保存；
  • 查询时的结果将经过utf8=>utf8
    的字符集转换过程，将保存的6
    个字节原封不动返回，产生乱码。

向默认字符集为latin1
的数据表插入utf8
编码的数据前设置了连接字符集为utf8

  • 插入时根据连接字符集设置，character_set_client、character_set_connection、character_set_results
    均为utf8
    ；
  • 插入数据将经过utf8=>utf8=>latin1
    的字符集转换，若原始数据中含有\u0000~\u00ff
    范围以外的Unicode
    字符，会因为无法在latin1
    字符集中表示而被转换为'?'(0×3F)
    符号，以后查询时不管连接字符集设置如何都无法恢复其内容了

使用字符集时的建议

  • 建立数据库/表和进行数据库操作时尽量显式指出使用的字符集，而不是依赖于MySQL
    的默认设置，否则MySQL
    升级时可能带来很大困扰；
  • 数据库和连接字符集都使用latin1
    时，虽然大部分情况下都可以解决乱码问题，但缺点是无法以字符为单位来进行SQL
    操作，一般情况下将数据库和连接字符集都置为utf8
    是较好的选择；
  • 使用mysql CAPI
    （C
    语言操作的API
    ）时，初始化数据库句柄后马上用mysql_options
    设定MYSQL_SET_CHARSET_NAME
    属性为utf8
    ，这样就不用显式地用SET NAMES
    语句指定连接字符集，且用mysql_ping
    重连断开的长连接时也会把连接字符集重置为utf8
    ；
  • 对于mysql PHP API
    ，一般页面级的PHP
    程序总运行时间较短，在连接到数据库以后显式用SET NAMES
    语句设置一次连接字符集即可；但当使用长连接时，请注意保持连接通畅并在断开重连后用SET NAMES
    语句显式重置连接字符集。

注意事项

  • my.cnf
    中的default_character_set
    设置只影响mysql
    命令连接服务器时的连接字符集，不会对使用lib mysql client
    库的应用程序产生任何作用！
  • 对字段进行的SQL
    函数操作通常都是以内部操作字符集进行的，不受连接字符集设置的影响。
  • SQL
    语句中的裸字符串会受到连接字符集或introducer
    设置的影响，对于比较之类的操作可能产生完全不同的结果，需要小心！

设置了表的默认字符集为utf8
并且通过UTF-8
编码发送查询，存入数据库的仍然是乱码。那connection
连接层上可能出了问题，解决方法是在发送查询前执行一下下面这句：SET NAMES 'utf8';
它相当于下面的三句指令：

SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;

有关mycat字符不一致

报错信息：

Caused by: com.mysql.cj.exceptions.CJException: UDAL - Unknown character set: 'utf8mb4;/* mysql-connector-j-8.0.31 (Revision: 0c86fc148d567b62266c2302bdad0f1e7a7e4eba) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout'

查看数据库字符集

SHOW VARIABLES LIKE 'character%';
------------------------+------------------------------------|
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      |/home/mysql/database/share/charsets/|

在mycat
环境的管理9066
端口，查看后端数据库的连接字符集

mysql -umycat -p123456 -P9066 -h172.xxx.xxx.xxx

mysql>  show @@backend;
+------------+------+---------+-------------+------+--------+--------+---------+------+--------+----------+------------+----------------+---------+---------+------------+-------------+
| processor  | id   | mysqlId | host        | port | l_port | net_in | net_out | life | closed | borrowed | SEND_QUEUE | schema         | charset | txlevel | autocommit | tx_readonly |
+------------+------+---------+-------------+------+--------+--------+---------+------+--------+----------+------------+----------------+---------+---------+------------+-------------+
| Processor0 |   32 |    5778 | 172.xx.xx.1 | 3306 |  43930 |    241 |     113 | 1685 | false  | false    |          0 | yian           | utf8:33 | 3       | true       | false       |
| Processor0 |    4 |  223124 | 172.xx.xx.2 | 3306 |  57980 |   3129 |     792 | 1686 | false  | false    |          0 | yian           | utf8:33 | 3       | true       | false       |
| Processor0 |   36 | 5519194 | 172.xx.xx.3 | 3306 |  55814 |   2521 |     644 | 1685 | false  | false    |          0 | yian           | utf8:33 | 3       | true       | false       |
+------------+------+---------+-------------+------+--------+--------+---------+------+--------+----------+------------+----------------+---------+---------+------------+-------------+

查看mycat
字符集配置

more ../conf/index_to_charset.properties 
1=big5
2=latin2
3=dec8
4=cp850
5=latin1
6=hp8
7=koi8r
8=latin1
9=latin2
10=swe7
11=ascii
12=ujis
13=sjis
14=cp1251
15=latin1
16=hebrew
18=tis620
19=euckr
20=latin7
21=latin2
22=koi8u
23=cp1251
24=gb2312
25=greek
26=cp1250
27=latin2
28=gbk
29=cp1257
30=latin5
31=latin1
32=armscii8
33=utf8
34=cp1250
35=ucs2
36=cp866
37=keybcs2
38=macce
39=macroman
40=cp852
41=latin7
42=latin7
43=macce
44=cp1250
45=utf8mb4
46=utf8mb4
47=latin1
48=latin1
49=latin1
50=cp1251

server.xml
下的system
标签下添加

<property name="charset">utf8mb4</property>

index_to_charset.properties
配置

33=utf8mb4

配置Jpa

JPA
有自动事务，所有的更新语句、查询语句都有事务，这涉及到JPA的缓存机制，因此JPA
的自动事务必须关闭

@SpringBootApplication
@EnableJpaRepositories(enableDefaultTransactions = false)
public class MydemoApplication {
 
    public static void main(String[] args) {
        SpringApplication.run(MydemoApplication.class, args);
    } 
}

JPA
的事务配置：

  • 手动加事务，注解@Transactional
  • 通过配置类，统一添加事务
@Aspect
@Configuration
public class TransactionalConfigForBoot {
 
    private static final String AOP_POINTCUT_EXPRESSION = "execution(* org.yian.*.service.*.*(..))";
    
    @Autowired
    private PlatformTransactionManager transactionManager;
    
    @Bean
    public TransactionInterceptor txAdvice() {
        DefaultTransactionAttribute txAttr_REQUIRED = new DefaultTransactionAttribute();
        txAttr_REQUIRED.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
 
        DefaultTransactionAttribute txAttr_REQUIRED_READONLY = new DefaultTransactionAttribute();
        txAttr_REQUIRED_READONLY.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
        txAttr_REQUIRED_READONLY.setReadOnly(true);
 
        NameMatchTransactionAttributeSource source = new NameMatchTransactionAttributeSource();
 
        source.addTransactionalMethod("save*", txAttr_REQUIRED);
        source.addTransactionalMethod("add*", txAttr_REQUIRED);
        source.addTransactionalMethod("create*", txAttr_REQUIRED);
        source.addTransactionalMethod("update*", txAttr_REQUIRED);
        source.addTransactionalMethod("delete*", txAttr_REQUIRED);
        source.addTransactionalMethod("exec*", txAttr_REQUIRED);
        source.addTransactionalMethod("set*", txAttr_REQUIRED);
        source.addTransactionalMethod("get*", txAttr_REQUIRED_READONLY);
        source.addTransactionalMethod("query*", txAttr_REQUIRED_READONLY);
        source.addTransactionalMethod("find*", txAttr_REQUIRED_READONLY);
        source.addTransactionalMethod("list*", txAttr_REQUIRED_READONLY);
        source.addTransactionalMethod("count*", txAttr_REQUIRED_READONLY);
        source.addTransactionalMethod("is*", txAttr_REQUIRED_READONLY);
 
        return new TransactionInterceptor(transactionManager, source);
    }
 
    @Bean
    public Advisor txAdviceAdvisor() {
        AspectJExpressionPointcut pointcut = new AspectJExpressionPointcut();
        pointcut.setExpression(AOP_POINTCUT_EXPRESSION);
        return new DefaultPointcutAdvisor(pointcut, txAdvice());
    }
}


如果这篇文章对你有所帮助，或者有所启发的话，帮忙 分享、收藏、点赞、在看，你的支持就是我坚持下去的最大动力！

深入解析SpringBoot默认JSON解析器及自定义字段序列化策略

SpringBoot：策略模式实现文件上传方式的灵活切换

在Spring Boot启动时实现自动化的优化和准备工作

mysqlmysql update语句utf8字符编码mysql创建数据库
文章转载自一安未来，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论