文档章节

MySQL编码引发的两个问题

阿dai学长
 阿dai学长
发布于 05/13 10:42
字数 2180
阅读 608
收藏 12

【推荐】2019 Java 开发者跳槽指南.pdf(吐血整理) >>>

概述

先讲一下写该文章的原因,首先,工作中又遇到一条很熟悉的MySQL报错信息 Cause: java.sql.SQLException: Incorrect string value:Cause: java.sql.SQLException: Incorrect string value… (emoji表情存储导致),原因是MySQL的字符集导致的;其次,因为一直听说数据库变更可能锁表,但是一直不知道到底哪些操作会导致锁表。所以今天对相关知识做一个系统的整理。

对于mysql的字符集编码已经不陌生了,不过,每次遇到相关问题都是依赖于百度、Google...

今天遇到的emoji表情的存储问题也是司空见惯了,原因多数是因为MySQL使用了utf8字符集(至于公司之前为什么会用utf8我也不清楚,就不过多吐槽了),utf8字符集本身并无可厚非,但是MySQL的这一败笔算是真正的技术打脸,详情可见 《永远不要在MySQL中使用UTF-8》

言归正传,今天整理两个问题:

  • 什么是MySQL编码?
  • 什么操作会导致MySQL锁表?

确定要大刀阔斧的干?

遇到上面关于数据库字符集的问题,想必大家会想到两种办法:一,修改编码字符集为utf8mb4;二,找开发对相应的字段进行处理,然后再存入数据库。当然,第一种方法要简单有效的多,这也是大多数情况会采用的方法,这次我们也是采用的第一种方法,于是有了接下来的问题。

修改字段字符集编码:

ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];

如:ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

再次插入数据,发现还是报错!why?Google一下,发现这篇文章 彻底解决MySQL字符集问题

沿着这个思路,查看公司线上和测试服(能正常写入数据)数据库编码相关配置: 20190508155731058848135.png

如图所示,线上数据库数据表的编码仍然是utf8!问题的原因浮出水面,那么为什么线上线下会不一致呢?刚来公司不久就不追溯了。那么,改线上数据表的编码类型吧。

此时,另一个问题迎面而来,更改数据表编码类型会不会导致数据库锁表呢?(话外音:线上数据库,如果锁表,影响还是比较严重的。。。)在此告诫各位看管,线上任何修改一定要三四而后行!

想要解决上面提到的问题,还得从数据库的原理入手,下面做一个系统的学习和整理。

MySQL编码体系——数据存储编码

MySQL的字符编码结构比较细,它大方向分为两个部分:数据存储编码和数据传输编码。本篇讨论数据存储编码部分,数据传输编码详见 MySQL的字符编码体系(二)——数据存储编码

编码层次

数据存储的字符编码配置是指定数据库中存储的数据默认采用什么字符编码。默认字符编码的设置分为四个层次:服务器级、数据库级、数据表级和列级。也就是说,可以为服务器设置一个默认字符编码,再为服务器中的每一个数据库设置不同的默认编码,再为同一个数据库中的每一个数据表设置不同的默认编码,再为同一个数据表中的每一个列设置不同的默认编码。

20190510155748291832633.png

那这四个层次的编码设置到底如何起作用呢?如果新建数据库时没有指定字符编码,就默认设置为服务器的编码;如果新建数据表时没有指定任何编码,就默认设置为数据库的编码;如果向数据表添加新列或新建数据表时没有特别指定某些列的编码,那么这些列就默认设置为数据表的编码。注意这里四个层次的编码都是作为“默认”的存在,用户创建数据库、表或增加列时直接指定的编码是最优先的。

另一方面,直接改变这四个层次的编码并不会改变它们各自所有下层对象的当前编码。比如修改只Server级,那么所有已经存在的数据库的默认编码不变,数据表、表列以及每一行现有数据记录的字符编码都不变,但是如果新建一个数据库且不指定其默认编码,那它的默认编码就会被设置为Server的默认编码;同样即使修改了所有四个层次的编码,但是数据表中每一条现有记录的字符字段仍然是按原来的编码存储的,但是如果向数据表中新插入一条记录,数据库将根据数据表当前各列的默认编码来存储该条记录的各个字符字段。

参考:MySQL的字符编码体系(一)——数据存储编码

设置方法

修改Server以下 各级编码的SQL语句如下:

# 库
ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name;

# 表
ALTER TABLE dbl_name [DEFAULT] CHARACTER SET [=] charset_name;

# 字段
ALTER TABLE dbl_name MODIFY [COLUMN] col_name {CHAR[(length)] | TEXT} CHARACTER SET charset_name;

注意上面第三条修改列字符编码,实际上是通过完全重新定义列属性的方式实现的,语法跟创建新数据表时指定列字段属性一样的。所以如果这里只是想修改列字符编码,那就必须完整地写上创建该列时使用的所有定义修饰。

修改Server默认编码可以通过运行时直接修改变量character_set_server实现,但这样是临时性的,客户端关闭重启后又会自动恢复。要想永久改变Server默认编码需要在my.ini或my.cnf配置文件的“[mysqld]”区域中设定该变量的值,然后重启服务器:

[mysqld]
character_set_server=charset_name

锁表问题

然后,突然想到另一个问题,上面操作会不会导致数据库锁表呢? 什么操作会导致mysql锁表?

mysql在修改表结构时的风险及解决办法

MySQL 5.7 online ddl的一些改进

mysql5.7 DDL 雷区

修改数据库编码的SQL

最后补充一下修改各个层级编码字符集的SQL:

  • 查看数据库各种编码类型:

    mysql> show variables like 'char%';
    
  • 修改字段编码:(不要直接修改,避免锁表,推荐工具:pt-online-schema-change)

    mysql> ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];
    
    如:ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
    
  • 修改数据表编码:

    mysql> ALTER TABLE table_name DEFAULT CHARACTER SET utf8mb4 [COLLATE ...] ;
    
    如:ALTER TABLE table_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
    
  • 修改数据库编码:

    mysql> set  character_set_server = xxx;
    mysql> set character_set_database = xxx;
    
    如:set character_set_database = utf8mb4;
    

    这样修改,重启数据库后会被还原,如果要永久生效,需要修改数据库配置:

    • vim my.cnf

      # 对本地的mysql客户端的配置
      [client]
      default-character-set = utf8mb4
      
      # 对其他远程连接的mysql客户端的配置
      [mysql]
      default-character-set = utf8mb4
      
      # 本地mysql服务的配置
      [mysqld]
      character-set-client-handshake = FALSE
      character-set-server = utf8mb4
      collation-server = utf8mb4_unicode_ci
      

      重启mysql生效。

MySQL几个character_set变量的含义

参数 含义
character_set_client 主要用来设置客户端使用的字符集
character_set_connection 主要用来设置连接数据库时的字符集,如果程序中没有指明连接数据库使用的字符集类型则按照这个字符集设置
character_set_database 主要用来设置默认创建数据库的编码格式,如果在创建数据库时没有设置编码格式,就按照这个格式设置
character_set_filesystem 文件系统的编码格式,把操作系统上的文件名转化成此字符集,即把 character_set_client转换character_set_filesystem, 默认binary是不做任何转换的。
character_set_results 数据库给客户端返回时使用的编码格式,如果没有指明,使用服务器默认的编码格式
character_set_server 服务器安装时指定的默认编码格式,这个变量建议由系统自己管理,不要人为定义
character_set_system 数据库系统使用的编码格式,这个值一直是utf8,不需要设置,它是为存储系统元数据的编码格式
character_sets_dir 这个变量是字符集安装的目录

© 著作权归作者所有

阿dai学长
粉丝 76
博文 263
码字总数 337363
作品 0
朝阳
运维
私信 提问
加载中

评论(4)

阿dai学长
阿dai学长 博主

引用来自“一个成为架构师的男人”的评论

学到了
多多指教
阿dai学长
阿dai学长 博主

引用来自“李阿斗”的评论

小哥哥,多多指教
李阿斗
李阿斗
PDO::ATTR_EMULATE_PREPARES属性设置为false引发的血案

前段时间给pdo设置了下emulate_prepare属性,引发了这次的血案。在这记录下事情的经过,没准大家能避免同样的错误。 先说以下环境。php 5.2.5,mysql 5.0.81,服务器使用的GBK编码。 起因 首...

wyxinlx
2015/01/19
5.7K
1
Python 操作 MySQL数据库

一、安装 MySQL 可以直接从MySQL官方网站下载最新版本。MySQL是跨平台的,选择对应的平台下载安装文件,安装即可。 如果是Windows用户,那么安装过程非常简单,直接根据向导一步一步操作即可...

skypeGNU1
2016/06/22
0
0
mac下用python连接mysql

本文主要描述在mac系统下如何从安装到实现python到mysql的连接,并解决乱码问题。 整体流程(mac 自带python不用安装): 1、安装mysql(这里就直接发大神的安装教程帖子了:http://www.jian...

yuppy_zyp
2017/11/15
0
0
MySQL 中把查询结果输出到文件中

在Mysql中把查询结果输出到文件中,我所经历的故事。 第一步,起始 Mysql 官方文档参考 http://tool.oschina.net/uploads/apidocs/mysql-5.5-en/sql-syntax.html#select-into 我的输入 SELE...

import_key
2016/01/19
882
0
django乱码问题

折腾了两天,终于解决了。返回json中文乱码 在网上那个找了很多方法,试了都比行,无意间看了一个方法。ok 1.就是mysql数据库本身编码的问题。有两个设置: 在mysql命令行环境输入:show va...

刘新全
2015/12/31
580
0

没有更多内容

加载失败,请刷新页面

加载更多

交换机switch 的shutdown 与 no shutdown

shutdown是关闭接口(端口),接口状态会变为DOWN,no shutdown是激活接口(端口),状态变为UP,一般在给vlan或者端口配置管理ip或者端口ip后使用。 有时候我们配置某个端口前会需要把端口关闭到...

刘日辉
37分钟前
5
0
AOP底层源码分析

思维导图 AOP AOP: 面向切面编程[底层就是动态代理] 指程序在运行期间动态的将某段代码切入到指定方法位置进行运行的编程方式。 AOP通知方式 前置通知: logStart(),在目标方法(div)运行之前运...

volc1612
50分钟前
5
0
OSChina 周六乱弹 —— 别听他们的,你不胖你只是毛茸茸的

Osc乱弹歌单(2019)请戳(这里) 【今日歌曲】 @且无需多言 :分享Rise Against的单曲《Audience Of One (Ghost Note Symphonies)》: 硬核朋克不插电版本,隐藏在喧嚣下的柔情! 《Audienc...

小小编辑
今天
34
2
apache httpClient实现代理发送Post请求

CredentialsProvider credsProvider = new BasicCredentialsProvider(); credsProvider.setCredentials( new AuthScope("host", port), new UsernamePasswordCredentials(username, password......

huangkejie
今天
6
0
SpringCloud

单体应用存在的问题 ● 随着业务的发展,开发变得越来越复杂。 ● 修改、新增某个功能,需要对整个系统进行测试,重新部署。 ● 一个模块出现问题,很可能导致整个系统崩溃。 ● 多个开发团队...

Star永恒
今天
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部