golang 插入mysql数据库Error 1366: Incorrect string value: '\\xB9<\\xC4L7\\x12...

如果说你的业务实[]byte类型的数据存入mysql，而数据库中是varchar之类的字符串形式，所以你将[]byte转为string后存入报的错，那就不用看下面这些东西了。
解决办法：mysql对应字段直接使用varbinary类型，go里面的字段类型直接使用[]byte，选择二进制类型注意不要直接使用binary,或者其他类型，因为binary在取数的时候会自动填充一部分，在类型选择的时候需要自己斟酌一下，详细解释请看我的另一篇文章https://www.jianshu.com/p/9871131fbfd5

一般情况下，我们会设置MySQL默认的字符编码为utf8，但是近些年来，emoji表情的火爆使用，给数据库带来了意外的错误，就是emoji的字符集已经超出了utf8的编码范畴😄

令人抓狂的字符编码问题

谈到字符编码问题，会让很多人感到头疼，这里不在深究各个字符编码的特点和理论，这里只说下Unicode和utf8字符编码的关系

Unicode是编码字符集，而UTF-8就是字符编码，即Unicode规则字库的一种实现形式。
随着互联网的发展，对同一字库集的要求越来越迫切，Unicode标准也就自然而然的出现。
它几乎涵盖了各个国家语言可能出现的符号和文字，并将为他们编号。
详见：Unicode on Wikipedia。
Unicode的编号从0000开始一直到10FFFF共分为16个Plane，每个Plane中有65536个字符。
而UTF-8则只实现了第一个Plane，可见UTF-8虽然是一个当今接受度最广的字符集编码，
但是它并没有涵盖整个Unicode的字库，这也造成了它在某些场景下对于特殊字符的处理困难

简单的说在计算机内存中，统一使用Unicode编码，当需要保存到硬盘或者需要传输的时候，就转换为UTF-8编码

用记事本编辑的时候，从文件读取的UTF-8字符被转换为Unicode字符到内存里，编辑完成后，保存的时候再把Unicode转换为UTF-8保存到文件

image

emoji是Unicode编码，在MySQL中使用utf8编码无法正常显示emoji的表情，为了解决这个问题，MySQL在5.5.3版本之后，引进了新的字符编码utf8mb4,本篇文章主要介绍如何将已经是utf8的database切换到utf8mb4字符编码

什么是utf8mb4

utf8mb4最明显的好处是解决了苹果挖的坑-推广了emoji表情。utf8mb4解决了MySQL数据库存储emoji表情的问题

utf8mb4是utf8的超集，理论上由utf8升级到utf8mb4字符编码没有任何兼容问题

升级utf8到utf8mb4

1. 备份

安全第一，备份所有需要升级字符编码的数据库

  • 可以将库dump出来
  • 如果是虚拟机，可以给整个主机做快照

2. 升级

utf8mb4是MySQL5.5.3版本之后支持的字符集，so，如果你需要使用这个字符集，前提条件是你的MySQL版本必须 >= 5.5.3

3. 修改

在MySQL中，可以为一个database设置字符编码，可以为一张表设置字符编码，甚至可以为某一个字段设置字符编码

  • 查看当前系统默认的字符集设置

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
+--------------------------+-----------------+
| Variable_name | Value |
+--------------------------+-----------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+--------------------------+-----------------+
10 rows in set (0.01 sec)

mysql>
</pre>

|

  • 查看database的字符编码
    mysql> show create database polarsnow;
    +-----------+--------------------------------------------------------------------+
    | Database | Create Database |
    +-----------+--------------------------------------------------------------------+
    | polarsnow | CREATE DATABASE polarsnow /*!40100 DEFAULT CHARACTER SET utf8 */ |
    +-----------+--------------------------------------------------------------------+
    1 row in set (0.00 sec)

mysql>
</pre>

|

  • 查看table的字符编码

mysql> show create table ps;
+-------+---------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------+
| ps | CREATE TABLE ps (
name varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
</pre>

|

  • 查看column的字符编码
    mysql> show full columns from ps;
    +-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
    | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
    +-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
    | name | varchar(100) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
    +-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
    1 row in set (0.00 sec)

修改database默认的字符集

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

mysql> ALTER DATABASE polarsnow CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
Query OK, 1 row affected (0.03 sec)

mysql> show create database polarsnow;
+-----------+--------------------------------------------------------------------------------------------------+
| Database | Create Database |
+-----------+--------------------------------------------------------------------------------------------------+
| polarsnow | CREATE DATABASE polarsnow /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ |
+-----------+--------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show tables;
+---------------------+
| Tables_in_polarsnow |
+---------------------+
| ps |
+---------------------+
1 row in set (0.00 sec)

mysql> show create table ps;
+-------+---------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------+
| ps | CREATE TABLE ps (
name varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show full columns from ps;
+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| name | varchar(100) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.00 sec)

mysql> create table test_tb2 (tb2 varchar(100) );
Query OK, 0 rows affected (0.21 sec)

mysql> show tables;
+---------------------+
| Tables_in_polarsnow |
+---------------------+
| ps |
| test_tb2 |
+---------------------+
2 rows in set (0.00 sec)

mysql> show create table test_tb2;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_tb2 | CREATE TABLE test_tb2 (
tb2 varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

可以看到，虽然修改了database的字符集为utf8mb4，但是实际只是修改了database新创建的表，默认使用utf8mb4，原来已经存在的表，字符集并没有跟着改变，需要手动为每张表设置字符集

修改table的字符集

  • 只修改表默认的字符集 ALTER TABLE table_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  • 修改表默认的字符集和所有字符列的字符集 ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

mysql> show create table ps;
+-------+---------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------+
| ps | CREATE TABLE ps (
name varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show full columns from ps;
+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| name | varchar(100) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.00 sec)

mysql> ALTER TABLE ps CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.38 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table ps;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| ps | CREATE TABLE ps (
name varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show full columns from ps;
+-------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| name | varchar(100) | utf8mb4_unicode_ci | YES | | NULL | | select,insert,update,references | |
+-------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.00 sec)

mysql>

修改column默认的字符集

ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

注：VARCHAR(191) 根据字段实例的类型填写

4. 检查字段的最大长度和索引列

  • 字段长度

由于从utf8升级到了utf8mb4，一个字符所占用的空间也由3个字节增长到4个字节，但是我们当初创建表时，设置的字段类型以及最大的长度没有改变。例如，你在utf8下设置某一字段的类型为TINYTEXT, 这中字段类型最大可以容纳255字节，三个字节一个字符的情况下可以容纳85个字符，四个字节一个字符的情况下只能容纳63个字符，如果原表中的这个字段的值有一个或多个超过了63个字符，那么转换成utf8mb4字符编码时将转换失败，你必须先将TINYTEXT更改为TEXT等更高容量的类型之后才能继续转换字符编码

  • 索引

在InnoDB引擎中，最大的索引长度为767字节，三个字节一个字符的情况下，索引列的字符长度最大可以达到255，四个字节一个字符的情况下，索引的字符长度最大只能到191。如果你已经存在的表中的索引列的类型为VARCHAR(255)那么转换utf8mb4时同样会转换失败。你需要先将VARCHAR(255)更改为VARCHAR(191)才能继续转换字符编码

5. 修改配置文件

SET NAMES utf8 COLLATE utf8_unicode_ci becomes SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci

vim /etc/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

service mysqld restart

检查修改
mysql> 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 |
+--------------------------+--------------------+
10 rows in set (0.00 sec)

注：character_set_system 一直都会是 utf8，不能被更改

6. 修复&优化所有数据表

mysqlcheck -u root -p --auto-repair --optimize --all-databases

总结

不要在MySQL上使用utf8字符编码，推荐使用utf8mb4，至于为什么，引用国外友人的一段话：

Never use utf8 in MySQL — always use utf8mb4 instead. Updating your databases and code might take some time, but it’s definitely worth the effort. Why would you arbitrarily limit the set of symbols that can be used in your database? Why would you lose data every time a user enters an astral symbol as part of a comment or message or whatever it is you store in your database? There’s no reason not to strive for full Unicode support everywhere. Do the right thing, and use utf8mb4. 🍻

参考文档

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 212,222评论 6赞 493
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,455评论 3赞 385
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 157,720评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,568评论 1赞 284
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,696评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,879评论 1赞 290
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,028评论 3赞 409
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,773评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,220评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,550评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,697评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,360评论 4赞 332
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,002评论 3赞 315
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,782评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,010评论 1赞 266
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,433评论 2赞 360
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,587评论 2赞 350

推荐阅读更多精彩内容