python mysql操作报UTF8MB3警告，最好让你用UTF8MB4的警告（python utf-8报错）

25-02-15 455

最近很多小伙伴都在问pythonmysql操作报UTF8MB3警告，最好让你用UTF8MB4的警告和pythonutf-8报错这两个问题，那么本篇文章就来给大家详细解答一下，同时本文还将给你拓展Jav

最近很多小伙伴都在问python mysql操作报UTF8MB3警告，最好让你用UTF8MB4的警告和python utf-8报错这两个问题，那么本篇文章就来给大家详细解答一下，同时本文还将给你拓展Java Unicode编码及 Mysql utf8 utf8mb3 utf8mb4 的区别与utf8mb4的过滤、mysql 5.5 数据库 utf8改utf8mb4、MySQL 8.0：字符集从 utf8 转换成 utf8mb4的迁移方法【转】、MySQL Charset--UTF8和UTF8MB4对比测试等相关知识，下面开始了哦！

本文目录一览：

python mysql操作报UTF8MB3警告，最好让你用UTF8MB4的警告（python utf-8报错）
Java Unicode编码及 Mysql utf8 utf8mb3 utf8mb4 的区别与utf8mb4的过滤
mysql 5.5 数据库 utf8改utf8mb4
MySQL 8.0：字符集从 utf8 转换成 utf8mb4的迁移方法【转】
MySQL Charset--UTF8和UTF8MB4对比测试

python mysql操作报UTF8MB3警告，最好让你用UTF8MB4的警告（python utf-8报错）

python mysql操作报UTF8MB3警告，最好让你用UTF8MB4的警告如下：

Warning: (3719, "''utf8'' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.")

怎么解决？最简单办法是：将原来的CHARSET=utf8修改为CHARSET=utf8mb4即可！

create table xiangjiaosp_OuMe(`id` int(15) NOT NULL AUTO_INCREMENT, `title_name` varchar(255) DEFAULT NULL,`pict_url` varchar(255) DEFAULT NULL,`xunlei_url` varchar(255) DEFAULT NULL, `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT ''创建时间'', `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ''更新时间'', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

修改为

create table xiangjiaosp_OuMe(`id` int(15) NOT NULL AUTO_INCREMENT, `title_name` varchar(255) DEFAULT NULL,`pict_url` varchar(255) DEFAULT NULL,`xunlei_url` varchar(255) DEFAULT NULL, `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT ''创建时间'', `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ''更新时间'', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

Java Unicode编码及 Mysql utf8 utf8mb3 utf8mb4 的区别与utf8mb4的过滤

内容简介

本文主要介绍了UTF8的一些基本概念，简要介绍了mysql中 utf8 utf8mb3 utf8mb4 的区别；然后为介绍Java对Unicode编码的支持，引入了一些编码的基本概念，包括code point， code unit等，并介绍了Java提供的常用的支持Unicode编码的方法；最后给出了过滤UTF8mb4的方案

UTF-8简介

UTF-8（8-bit Unicode Transformation Format）是一种针对Unicode的可变长度字符编码，也是一种前缀码。它可以用来表示Unicode标准中的任何字符，且其编码中的第一个字节仍与ASCII兼容，这使得原来处理ASCII字符的软件无须或只须做少部分修改，即可继续使用。因此，它逐渐成为电子邮件、网页及其他存储或发送文字的应用中，优先采用的编码。

UTF-8使用一至四个字节为每个字符编码（2003年11月UTF-8被RFC 3629重新规范，只能使用原来Unicode定义的区域，U+0000到U+10FFFF，也就是说最多四个字节）：

128个US-ASCII字符只需一个字节编码（Unicode范围由U+0000至U+007F）。
带有附加符号的拉丁文、希腊文、西里尔字母、亚美尼亚语、希伯来文、阿拉伯文、叙利亚文及它拿字母则需要两个字节编码（Unicode范围由U+0080至U+07FF）。
其他基本多文种平面（BMP, Basic Multilingual Plane）中的字符（这包含了大部分常用字，例如CJVK常用字字符集 —— Chinese, Japanese, Vietnam, Korean）使用三个字节编码（Unicode范围由U+0800至U+FFFF）。
其他使用极少的Unicode 辅助平面（Supplementary Multilingual Plane）的字符使用四字节编码（Unicode范围由U+10000至U+10FFFF，主要包括不常用的CJK字符, 数学符号, emoji表情等）。

utf-8编码方式

unicode code point table
unicode

参考与扩展：
维基百科 UTF-8 https://en.wikipedia.org/wiki/UTF-8, 中文版 https://zh.wikipedia.org/wiki/UTF-8*
维基百科 Plane_(Unicode) https://en.wikipedia.org/wiki/Plane_%28Unicode%29*
维基百科 CJK characters https://en.wikipedia.org/wiki/CJK_characters*
维基百科 Emoji https://en.wikipedia.org/wiki/Emoji*

UTF-8与Unicode的关系

utf8编码是unicode编码的一种实现，可以简单的理解为unicode编码定义一串数字来一一对应我们用到的字符，utf8定义了如何将unicode定义的这串数字保存到内存中。另外需要强调的是utf8是一种变长的编码规范。
unicode 的范围 U+0000 - U+10FFFF。

参考与扩展
*维基百科 Unicode https://en.wikipedia.org/wiki/Unicode*

Mysql中的 UTF-8、UTF8mb3， UTF8mb4

utf8mb4, MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode字符。
mysql中的utf8，就是最大3字节的unicode字符，也就是mysql中的utf8mb3.

参考
mysql-charset-unicode-utf8mb3 https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb3.html and https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8.html*
mysql-charset-unicode-utf8mb4 https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html*

表示范围：

说明	mysql utf8 / utf8mb3	mysql utf8mb4
max bit	3	4
范围	基本多文种平面 + US-ASCII	辅助平面(Supplementary) + 基本多文种平面 + US-ASCII
unicode范围	U+0000 - U+FFFF	U+0000 - U+10FFFFF
常见字符	英文字母，CJK大部分常用字等	CJK非常用字，数学符号，emoji表情等

那么问题来了，如果用了utf8mb3编码的mysql数据库，在插入一些4字节长的字符时就会报错（形如："java.sql.SQLException: Incorrect string value: ''\xF0\x9F\x94\x91\xE6\x9D...'' for column ''core_data'' at row 1" 的错误），后文会介绍如何在Java中过滤掉这些字符。

要在Java中过滤Mysql的utf8mb4，必须弄清Java是如何支持Unicode编码，接下来徐徐展开......

编码简介

下面先介绍几个概念：character（字符）, character set（字符集）, coded character set（字符编码集）, code point（代码点）, code space（代码空间），character encoding scheme（字符编码方案），code unit（编码单元），和3种Unicode常用的编码方式。

character——字符，''a'', ''€'', ''中'' 等, 都是一个字符
character set——字符集，字符的集合
coded character set——字符编码集，为每一个字符指定一个唯一的数字用来表示这个字符，这些数字组成的集合就是字符编**码集合，Unicode就是一个字符编码集
code point——代码点，是一个数字，用来表示字符集中的一个字符，也就是字符编码集中的一个数，例如 Unicode 编码中, ''A''的code point就是65（在Unicode中通常写作 U+0041）
code space——代码空间，就是一个编码集中，code point的范围，例如 Unicode 编码的 code space 就是 0x0000 - 0x10FFFF
character encoding scheme——字符编码方案，它定义了将字符用一个或多个固定长度的代码单元的方案，如前文提到的"utf-8编码方式"就是一个字符编码方案，其它的还有UTF16，UTF32，GBK等等
code unit——编码单元，就是编码方案中固定长度的最小编码单元，如UTF8的编码单元是1bit，UTF16是2bit，UTF32是4bit，

Unicode常用的三种编码方式 UTF-8, UTF-16, UTF-32，下面以辅助平面中的字符'''' 为例做一个简要的介绍，它的code point为128273（0x1F511）:

utf8，编码单元为8bit，使用1-4个编码单元来表示Unicode中的字符，辅助平面中的字符在utf8中需要用4字节表示，对照前面的utf-8编码方案中4字节的编码格式, 从高到低依次为：11110xxx 10xxxxxx 10xxxxxx 10xxxxxx，所以其编码是编码是 ''11110000 10011111 10010100 10010001''，注意并不是 0x1F511的二进制表示，不要混淆
utf16，编码单元是16bit，用1-2个编码单元来表示Unicode中的字符，U+0000-U+FFFF（BMP）用一个编码单元表示，0x10000-0x10FFFF（SMP）用两个编码单元（high-surrogates和low-surrogates）表示，high-surrogates范围U+D800-U+DBFF，low-surrogates范围U+DC00-U+DFFF，编码方式见下文图片，编码结果为''11011000 00111101 11011101 00010001''。在Unicode编码中U+D800-U+DFFF是专门为UTF16保留的区间，没有分配其它字符，所以不用担心一个code point有两个含义的问题。
utf32，编码半圆是32bit，可以只用一个编码单元来表示全部的Unicode字符，其编码就是 code point的值，也就是 ''00000000 00000001 11110101 00010001''。

UTF-8编码方式

UTF-16编码方式

打印编码的code：

    @Test
    public void printCharacterCode() {
        String s = "\uD83D\uDD11"; //字符''''
        log.info("UTF8: {}", bytesToBits(s.getBytes(Charset.forName("utf-8"))));
        log.info("UTF16: {}", bytesToBits(s.getBytes(Charset.forName("utf-16"))));
        log.info("UTF32: {}", bytesToBits(s.getBytes(Charset.forName("utf-32"))));
    }

    public static String byteToBit(byte b) {
        return ""
                + (byte) ((b >> 7) & 0x1) + (byte) ((b >> 6) & 0x1)
                + (byte) ((b >> 5) & 0x1) + (byte) ((b >> 4) & 0x1)
                + (byte) ((b >> 3) & 0x1) + (byte) ((b >> 2) & 0x1)
                + (byte) ((b >> 1) & 0x1) + (byte) ((b >> 0) & 0x1);
    }

    public static String bytesToBits(byte[] bytes) {
        String s = "";
        for (byte b : bytes) {
            s += byteToBit(b) + " ";
        }
        return s;
    }

使用上面的代码打印结果如下：

UTF8: 11110000 10011111 10010100 10010001 
UTF16: 11111110 11111111 11011000 00111101 11011101 00010001 
UTF32: 00000000 00000001 11110101 00010001

可以看到utf-16的结果并非我们期待的''11011000 00111101 11011101 00010001'', 前面多了一个编码单元 ''FEFF'', 这个是这个是Unicode编码中的 BOM（byte order mark）位，用来表示byte（注意不是bit）的顺序，BOM是可选的，如果用那么它必须出现在字符串的开始（在其它编码中BOM不会出现在字符串开始，所以可以用来识别字符串是否Unicode编码）。

为什么要用BOM位？为了标识编码单元的字节序，例如：“奎”的Unicode编码是594E，“乙”的Unicode编码是4E59，如果我们收到UTF-16字节流“594E”，那么这是“奎”还是“乙”？如果字符串的字节码是 ''FEFF 4E59''，那么则表示大端在左（big-endian），这个字是“乙”。

Unicode定义的6种BOM位

BOM位是可以缺省的，缺省时默认大端在左。

UTFs的属性归纳

参考与扩展
Supplementary Characters in the Java Platform http://www.oracle.com/us/technologies/java/supplementary-142654.html*
Unicode surrogate programming with the Java language https://www.ibm.com/developerworks/library/j-unicode/*
微机百科 UTF16 https://zh.wikipedia.org/wiki/UTF-16*
维基百科 code-point https://en.wikipedia.org/wiki/Code_point*
D000-DFFF编码表 http://jicheng.tw/hanzi/unicode.html?s=D000&e=DFFF*
utf bom http://unicode.org/faq/utf_bom.html*

Java与Unicode

最初Unicode的编码数量并没有超过65,535 (0xFFFF)，早期Java版本中使用16bit的char表示当时全部的Unicode字符。后来Unicode字符集扩展到了1,114,111 (0x10FFFF)(在Unicode标准2.0用引入了辅助编码平面SMP，在3.1首次为SMP的部分编码分配了字符)， JAVA中的char已经不足以表示Unicode的全部编码（需要32bit），JSR-204的专家讨论了很多方法想要解决这个问题，其中包括：

设计一种新的字符类型char32来替换原有的char
用int来表示code point，同时保留，并为String和StringBuffer等增加兼容char和int表示的api
...
最后处于内存占用和兼容性等方面的考虑，采用了如下方法：
在底层api中用int来表示code point，比如在Character类中
所有的字符串都char表示，并采用utf16的格式来表示，并提倡在高层api中使用这种方式
提供便于在int（code point）和char之间转换的方法，用于必要时候两者的转换

前文提到了UTF16用两个编码单元来表示超过U+FFFF的1,048,576 (1024*1024)个字符，Java中与之对应的概念就是"代理对（surrogate pair）"。

下面介绍Java中几个常用的code point(int)和char的转换方法

Character.toCodePoint(char high, char low)，return int，将两个UTF16的char（两个UTF16代码单元）转换为code point
Character.toChars(int codePoint), return char[]，将code point转换为一个或两个UTF16代码单元
isSupplementaryCodePoint(int codePoint)，判断一个code point是否SMP（Unicode中超过U+FFFF）的字符
Character.isSurrogate(char ch), 判断一个char是否为UTF16超过U+FFFF的两代码单元的字符的一个代码单元
Character.isHighSurrogate(char ch)，判断是否UTF16中两单元字符的高位单元
Character.isLowSurrogate(char ch)，判断是否UTF16中两单元字符的低位单元
Stirng提供的length(), 这是一个比较常用的方法，但是它的实际含义是UTF16代码单元的个数，也就是说如果字符串中包含了两代码单元的字符，那么length的值比实际的字符个数要多
String提供的codePointCount(), 这个是返回的代码点的个数，对于不包含两代码单元的字符时，其值等于length的值，包含时，其值为字符的个数，小于length的值
StringBuilder和StringBuffer主要提供的都是string和char的append方法，但是也提供了一个可以通过codePoint添加字符的方法 appendCodePoint(int codePoint)

下面是一个简单的例子：

    @Test
    public void testConverterOfCodePointAndChar() {
        String s = "a中\uD83D\uDD11a中";
        for (int i = 0; i < s.codePointCount(0, s.length()); i++) {
            int codePoint = s.codePointAt(i);
            log.info("code point at {}: {},\t isSupplementaryCodePoint:{}", i, codePoint, Character.isSupplementaryCodePoint(codePoint));
        }

        for (int i = 0; i < s.length(); i++) {
            char c = s.charAt(i);
            log.info("char at {}: {},\t isSurrogate:{},\t isHighSurrogate:{},\t isLowSurrogate:{}, ", i, c, Character.isSurrogate(c), Character.isHighSurrogate(c), Character.isLowSurrogate(c));
        }
    }

输出结果为：

code point at 0: 97,     isSupplementaryCodePoint:false
code point at 1: 20013,  isSupplementaryCodePoint:false
code point at 2: 128273,     isSupplementaryCodePoint:true
code point at 3: 56593,  isSupplementaryCodePoint:false
code point at 4: 97,     isSupplementaryCodePoint:false
char at 0: a,    isSurrogate:false,  isHighSurrogate:false,  isLowSurrogate:false
char at 1: 中,    isSurrogate:false,  isHighSurrogate:false,  isLowSurrogate:false
char at 2: ?,    isSurrogate:true,   isHighSurrogate:true,   isLowSurrogate:false
char at 3: ?,    isSurrogate:true,   isHighSurrogate:false,  isLowSurrogate:true
char at 4: a,    isSurrogate:false,  isHighSurrogate:false,  isLowSurrogate:false
char at 5: 中,    isSurrogate:false,  isHighSurrogate:false,  isLowSurrogate:false

上面的例子中我们看到一个奇怪的现象，codePointCount获取的字符的个数是对的，但是通过codePointAt去获取时，遇到SMP字符不会自动计算为两个代码单元，从源码（见附录）中可以看到

codePointCount中是通过判断是通过length的值减去2代码单元的个数得到

codePointAt 是通过判断当前代码单元是否UTF16高位单元，当是高位单元时会自动获取低位单元的值，得到完整的code point，但是获取到低位单元时不会做处理
所以要正确的遍历一个有2代码单元的字符时，需要自己做处理：

@Test
public void testIterateCodePoint() {
    String s = "a中\uD83D\uDD11a中";
    for (int i = 0; i < s.length(); i++) {
        int codePoint = s.codePointAt(i);
        log.info("code point at {}: {},\t isSupplementaryCodePoint:{}", i, codePoint, Character.isSupplementaryCodePoint(codePoint));
        if (Character.isSupplementaryCodePoint(codePoint)) i++;
    }
}

输出结果为：

code point at 0: 97,     isSupplementaryCodePoint:false
code point at 1: 20013,  isSupplementaryCodePoint:false
code point at 2: 128273,     isSupplementaryCodePoint:true
code point at 4: 97,     isSupplementaryCodePoint:false
code point at 5: 20013,  isSupplementaryCodePoint:false

Java过滤4字长UTF-8编码字符

在理解了前面的概念后，我想再过滤掉4字长的UTF-8字符已经不难了吧。
4字长的UTF-8字符就是Unicode SMP（辅助平面）中的字符, 也就是Unicode编码大于U+FFFF的字符, 所以我们只需要获取字符串中各个字符的code point，当code point 大于FFFF时（或者直接使用Character.isSupplementaryCodePoint来判断），过滤掉即可，示例代码如下：

    @Test
    public void filterUtf8mb4Test() {
        String s = "a中\uD83D\uDD11a中";
        log.info(filterUtf8mb4(s));
    }

    public static String filterUtf8mb4(String str) {
        final int LAST_BMP = 0xFFFF;
        StringBuilder sb = new StringBuilder(str.length());
        for (int i = 0; i < str.length(); i++) {
            int codePoint = str.codePointAt(i);
            if (codePoint < LAST_BMP) {
                sb.appendCodePoint(codePoint);
            } else {
                i++;
            }
        }
        return sb.toString();
    }

输出结果为：

a中a中

附录

String的 codePointCount 和 codePointAt 源码：

    public int codePointCount(int beginIndex, int endIndex) {
        if (beginIndex < 0 || endIndex > value.length || beginIndex > endIndex) {
            throw new IndexOutOfBoundsException();
        }
        return Character.codePointCountImpl(value, beginIndex, endIndex - beginIndex);
    }
    
    public int codePointAt(int index) {
        if ((index < 0) || (index >= value.length)) {
            throw new StringIndexOutOfBoundsException(index);
        }
        return Character.codePointAtImpl(value, index, value.length);
    }

它们调用的Character的 codePointCountImpl 和 codePointAtImpl 的源码：

    static int codePointCountImpl(char[] a, int offset, int count) {
        int endIndex = offset + count;
        int n = count;
        for (int i = offset; i < endIndex; ) {
            if (isHighSurrogate(a[i++]) && i < endIndex &&
                isLowSurrogate(a[i])) {
                n--;
                i++;
            }
        }
        return n;
    }
    
    static int codePointAtImpl(char[] a, int index, int limit) {
        char c1 = a[index];
        if (isHighSurrogate(c1) && ++index < limit) {
            char c2 = a[index];
            if (isLowSurrogate(c2)) {
                return toCodePoint(c1, c2);
            }
        }
        return c1;
    }

mysql 5.5 数据库 utf8改utf8mb4

由于需要用到utf8mb4，之前是utf8现在给改成utf8mb4

查看当前环境

SHOW VARIABLES WHERE Variable_name LIKE ''character\_set\_%'' OR Variable_name LIKE ''collation%'';
+--------------------------+--------------------+
| 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               |
| collation_connection     | latin1_swedish_ci  |
| collation_database       | utf8mb4_general_ci |
| collation_server         | utf8mb4_general_ci |
+--------------------------+--------------------+

打开MySQL的配置文件，将字符集的配置修改成如下：

[client]  
default-character-set = utf8mb4

[mysql]  
default-character-set = utf8mb4

[mysqld]  
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect=''SET NAMES utf8mb4''

修改后

root@localhost:(none)>SHOW VARIABLES WHERE Variable_name LIKE ''character\_set\_%'' OR Variable_name LIKE ''collation%'';
+--------------------------+--------------------+
| 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               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+

root@localhost:(none)>\s;
--------------
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id:        61
Current database:    
Current user:        root@localhost
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''''
Using delimiter:    ;
Server version:        5.5.36-log Source distribution
Protocol version:    10
Connection:        Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:        /tmp/mysql.sock
Uptime:            3 min 22 sec

修改后发现连接报错

mysql: Character set ''utf8mb4'' is not a compiled character set and is not specified in the ''/usr/share/mysql/charsets/Index.xml'' file

修改/usr/share/mysql/charsets/Index.xml

修改前

<charset name="utf8">
  <family>Unicode</family>
  <description>UTF-8 Unicode</description>
  <alias>utf-8</alias>
  <collation name="utf8_general_ci"     id="33">
   <flag>primary</flag>
   <flag>compiled</flag>
  </collation>
  <collation name="utf8_bin"            id="83">
    <flag>binary</flag>
    <flag>compiled</flag>
  </collation>
</charset>

修改后

<charset name="utf8mb4">
  <family>Unicode</family>
  <description>UTF-8 Unicode</description>
  <alias>utf-8</alias>
  <collation name="utf8_general_ci"     id="33">
   <flag>primary</flag>
   <flag>compiled</flag>
  </collation>
  <collation name="utf8_bin"            id="83">
    <flag>binary</flag>
    <flag>compiled</flag>
  </collation>
</charset>

MySQL 8.0：字符集从 utf8 转换成 utf8mb4的迁移方法【转】

整理 MySQL 8.0 文档时发现一个变更：

默认字符集由 latin1 变为 utf8mb4。想起以前整理过字符集转换文档，升级到 MySQL 8.0 后大概率会有字符集转换的需求，在此正好分享一下。

当时的需求背景是：

部分系统使用的字符集是 utf8，但 utf8 最多只能存 3 字节长度的字符，不能存放 4 字节的生僻字或者表情符号，因此打算迁移到 utf8mb4。

迁移方案一

1. 准备新的数据库实例，修改以下参数：

    [mysqld]

    ## Character Settings

    init_connect=''SET NAMES utf8mb4''

    #连接建立时执行设置的语句，对super权限用户无效

    character-set-server = utf8mb4

    collation-server = utf8mb4_general_ci

    #设置服务端校验规则，如果字符串需要区分大小写，设置为utf8mb4_bin

    skip-character-set-client-handshake

    #忽略应用连接自己设置的字符编码，保持与全局设置一致

    ## Innodb Settings

    innodb_file_format = Barracuda

    innodb_file_format_max = Barracuda

    innodb_file_per_table = 1

    innodb_large_prefix = ON

    #允许索引的最大字节数为3072（不开启则最大为767字节，对于类似varchar(255)字段的索引会有问题，因为255*4大于767）

2. 停止应用，观察，确认不再有数据写入

可通过 show master status 观察 GTID 或者 binlog position，没有变化则没有写入。

3. 导出数据

先导出表结构：

mysqldump -u -p --no-data --default-character-set=utf8mb4 --single-transaction --set-gtid-purged=OFF --databases testdb > /backup/testdb.sql

后导出数据：

mysqldump -u -p --no-create-info --master-data=2 --flush-logs --routines --events --triggers --default-character-set=utf8mb4 --single-transaction --set-gtid-purged=OFF --database testdb > /backup/testdata.sql

4. 修改建表语句

修改导出的表结构文件，将表、列定义中的 utf8 改为 utf8mb4

5. 导入数据

先导入表结构：

mysql -u -p testdb < /backup/testdb.sql

后导入数据：

mysql -u -p testdb < /backup/testdata.sql

6. 建用户

查出旧环境的数据库用户，在新数据库中创建

7. 修改新数据库端口，启动应用进行测试

关闭旧数据库，修改新数据库端口重启，启动应用

迁移方案二

1. 修改表的字符编码会锁表，建议先停止应用

2. 停止 mysql，备份数据目录（也可以其他方式进行全备）

3. 修改配置文件，重启数据库

    [mysqld]

    ## Character Settings

    init_connect=''SET NAMES utf8mb4''

    #连接建立时执行设置的语句，对super权限用户无效

    character-set-server = utf8mb4

    collation-server = utf8mb4_general_ci

    #设置服务端校验规则，如果字符串需要区分大小写，设置为utf8mb4_bin

    skip-character-set-client-handshake

    #忽略应用连接自己设置的字符编码，保持与全局设置一致

    ## Innodb Settings

    innodb_file_format = Barracuda

    innodb_file_format_max = Barracuda

    innodb_file_per_table = 1

    innodb_large_prefix = ON

    #允许索引的最大字节数为3072（不开启则最大为767字节，对于类似varchar(255) 字段的索引会有问题，因为255*4大于767）

4. 查看所有表结构，包括字段、修改库和表结构，如果字段有定义字符编码，也需要修改字段属性，sql 语句如下：修改表的字符集：

alter table t convert to character set utf8mb4;

影响：拷贝全表，速度慢，会加锁，阻塞写操作

修改字段的字符集（utf8mb4 每字符占 4 字节，注意字段类型的最大字节数与字符长度关系）：

alter table t modify a char CHARACTER SET utf8mb4;

影响：拷贝全表，速度慢，会加锁，阻塞写操作

修改 database 的字符集：

alter database sbtest CHARACTER SET utf8mb4;

影响：只需修改元数据，速度很快

5. 修改 JDBC url characterEncoding=utf-8

转自

知数堂-技术分享 | MySQL 8.0：字符集从 utf8 转换成 utf8mb4 https://mp.weixin.qq.com/s/p-aooa7WRDSPK6wCaqVy6w

MySQL Charset--UTF8和UTF8MB4对比测试

UTF8和UTF8MB4

在早期MySQL版本中，使用只支持最长三字节的UTF8字符集便可以存放所有Unicode字符。随着Unicode的完善，Unicode字符集收录的字符数量越来越多，最新版本的UTF8需要使用1到4个字节来存放Unicode字符，而MySQL为保持版本兼容，依旧使用最多3字节的UTF8字符集，并在MySQL 5.5.3版本引入UTF8MB4字符集来支持4字节的Unicode字符。

汉字 '''' 和 '' '' 是异体字，读音均为xi，但两个字的unicode不同：

 对应的UNICODE是 \ud850\udeee; 
 对应的UTF8是 &#xD850;&#xDEEE;
 对应的HEX编码是  %f0%a4%8b%ae

熙 对应的UNICODE是 \u7199 
熙 对应的UTF8是 &#x7199;
熙 对应的HEX编码是 %e7%86%99

在UTF8字符集模式下测试

创建测试表：

CREATE TABLE `tb5001` (
  `ID` INT(11) NOT NULL AUTO_INCREMENT,
  `C1` VARBINARY(100) DEFAULT NULL,
  `C2` VARCHAR(100) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=INNODB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4

在UTF8字符集下测试

SET NAMES utf8;

INSERT INTO TB5001(C1,C2)
SELECT '''','''';

INSERT INTO TB5001(C1,C2)
SELECT ''熙'',''熙'';

SELECT * FROM TB5001;

执行第一条INSERT有警告，警告信息为：

Warning Code : 1300
Invalid utf8 character string: ''F0A48B''

Warning Code : 1366
Incorrect string value: ''\xF0\xA4\x8B\xAE'' for column ''C2'' at row 1

查询结果为：

在UTF8字符集下，VARCHAR类型"无法支持“四字节的""，但VARBINARY不受字符集影响。

在UTF8MB4字符集模式下测试

测试脚本

SET NAMES utf8mb4;

INSERT INTO TB5001(C1,C2)
SELECT '''','''';

INSERT INTO TB5001(C1,C2)
SELECT ''熙'',''熙'';

SELECT * FROM TB5001;

测试中无任何警告，查询结果：

在UTF8MB4字符集下，VARCHAR类型"完美支持“四字节的""，但VARBINARY不受字符集影响。

乱码问题

表TB5001字符集已定义为UTF8MB4，表上C1列的字符集也是UTF8MB4，为啥还出现乱码呢？

测试脚本：

SET NAMES utf8;
SELECT * FROM TB5001;

SET NAMES utf8mb4;
SELECT * FROM TB5001;

测试对比图：

虽然表上C1列的字符集是UTF8MB4，能存放4字节的字符，但：

1、对于ID=33的记录，由于在插入时使用UTF8字符集，在插入到C1列前''''字已经发生乱码，存储到C1列中数据也是乱码，因此无论读取时使用UTF8还是UTF8MB4都是乱码。

2、对于ID-35的记录，由于在插入时使用UTF8MB4字符集，插入C1列前和存储到C1中都正常，在读取时使用UTF8MB4能正常读取，但在读取使用UTF8是乱码。

SET NAMES x相当于执行下面三条语句：

SET character_set_client = x;
SET character_set_results = x;
SET character_set_connection = x;

要保证数据库正常存储4字节的表情符合生僻字，除将数据库相关表和列设置为UTF8MB4外，还需要确保操作数据库时使用UTF8MB4，需重点关注以下几个方面：

1、数据库启动配置参数

2、应用与数据库连接配置

3、DBA日常运维操作

如DBA操作过程中，使用mysql客户端连接到数据库执行操作，而mysql客户端可能使用默认UTF8字符集(default-character-set)，导出乱码问题。

在xshell工具下粘贴下面代码：

SELECT '''','''';
SELECT ''熙'',''熙'';

将代码粘贴到vim工具中自动变为：

SELECT ''<d850><deee>'',''<d850><deee>'';
SELECT ''熙'',''熙'';

将代码粘贴到mysql命令总变为：

因此建议DBA在日常运维中关注生僻字和表情符，避免异常。

参考：http://seanlook.com/2016/10/23/mysql-utf8mb4/

今天关于python mysql操作报UTF8MB3警告，最好让你用UTF8MB4的警告和python utf-8报错的分享就到这里，希望大家有所收获，若想了解更多关于Java Unicode编码及 Mysql utf8 utf8mb3 utf8mb4 的区别与utf8mb4的过滤、mysql 5.5 数据库 utf8改utf8mb4、MySQL 8.0：字符集从 utf8 转换成 utf8mb4的迁移方法【转】、MySQL Charset--UTF8和UTF8MB4对比测试等相关知识，可以在本站进行查询。

本文标签：