MySQL插入Emoji提示 HY000 1366 Incorrect string value 错误

==本文前提:mysql Ver 14.14 Distrib 5.7.14, for Linux (x86_64) using EditLine wrapper==

  • 问题描述:在获取微信粉丝信息入库时线上环境产生了错误,而在本地开发时是成功的,线上环境提示nickname字段设置了错误字符串值,如下图:
    MySQL错误提示
  1. 出现这个问题,我明白是有用户的nickname里面含有Emoji导致的.

  2. 经过查找资料,我知晓在Emoji字符有一个特殊的地方,在存储时,需要用到4个字节。MySQL中常用的utf8字符集的utf8_general_ci这个collate最大只支持3个字节。所以为了支持能够存储Emoji,需要修改为utf8mb4字符集;于是用到了这篇文章里提到的方法,不管是修改/etc/my.cnf文件配置,还是在MySQL Client里修改全局配置,错误仍然出现.

    在查询相关资料时,还知道了一个信息,my.cnf中的default_character_set设置只影响mysql命令连接服务器时的连接字符集,不会对使用libmysqlclient库的应用程序产生任何作用,就是说会影响客户端链接时命令执行时的字符集,不影响我使用php脚本里的使用!鸟哥相关文章

  3. 后来我意识到一个问题,会不会是MySQL的sql_mode造成的问题,之前在做数据库迁移也出现过各种不兼容的问题.
    于是,我查看了一下线上MySQL的sql_mode的设置,因为sql_mode是有默认值的,所以在/etc/my.cnf里并没有显性写出,所以只能使用命令select @@sql_mode;在MySQL的客户端里查看,线上环境如下:
    默认的sql_mode

  4. 本地环境的如下:
    本地的sql_mode

  5. 修改/etc/my.conf文件,在mysqld部分增加sql_mode的配置,再次运行程序后成功,修改配置后内容如下图:
    修改后内容

  6. 去MySQL官网查看了5.7版本的Server SQL Modes模块这部分的文档,有这么一句话:

    The default SQL mode in MySQL 5.7 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.

大意是,如果没有覆盖配置,那么你的5.7版本的MySQL的SQL mode默认配置就会是上面提到的那些,见3图的默认信息。在MySQL官网有对应每部分详细的说明,我怀疑是STRICT_TRANS_TABLES模式造成插入不成功,我没有测试,最后我只保留了NO_ENGINE_SUBSTITUTION.

NO_ENGINE_SUBSTITUTION:顾名思义,没有引擎时替换。详细来说就是,在创建或修改表时,当指定的数据表引擎不可用或是未编译时,会替换为MySQL默认设置的存储引擎.
> 1. 当此项未设置时,创建表和修改表在引擎不可用时它们的表现也不同。创建表时,如果引擎不可用,MySQL会选择默认的存储引擎,并产生一个警告,但表会创建成功;在修改表结构时,如果引擎不可用,也会产生警告,不同的是修改不会生效.
> 2. 当此项设置时,如果设置的存储引擎不可用,会产生一个错误,并且新建或是修改表都不会成功.

mysql客户端emma中文乱码问题的解决

emma默认用apt-get 安装的话,emma是不支持中文的,配置文件或直接修改emma程序源文件(python)。

apt-get安装emma

sudo apt-get install emma

ubuntu的apt-get 安装emma是在/usr/share/emma目录下面。

cd /usr/share/emma/emmalib

sudo vim init.py

找到

“db_encoding”: “latin1”

改为

“db_encoding”: “utf8”

保存退出。

如果你已经安装完毕并且运行过emma,程序就会创建 ~/.emma/emmarc文件,保存配置。所以可以更改这里的配置文件,或者像下面直接修改emma的python源文件。
vim ~/.emma/emmarc
找到
db_encoding=latin1
改为
db_encoding=utf8
重新运行emma,此时发现还是乱码,在执行所有的sql语句之前加入这条sql语句,
set names utf8
但每次新用户都要改配置文件,以及执行新sql前都加这个语句,岂不是很费力,直接修改emma的源文件,来实现,新创建的emmrc配置文件就是utf8,和当选择数据库时,自动的执行“set names utf8” 语句。

以后新创建的配置文件默认就会是utf8的解码了,我想在连接数据之后就执行 “set names utf8” 语句,所以

sudo vim /usr/share/emma/emmalib/mysql_host.py

但是我下边这一步没执行成功,写完之后,emma 就没发启动了,

跳到155行左右的_use_db(self, name, do_query=True)函数哪里,改成如下

def _use_db(self, name, do_query=True):

if self.current_db and name == self.current_db.name: return

if do_query:

self.query(“use %s” % name, False)

self.query(“set names utf8”,  False)

try:

self.current_db = self.databases[name]

except KeyError:

print “Warning: used an unknown database %r! please refresh host!/n%s” % (name, “”.join(traceback.format_stack()))

MySQL里group_concat函数的含义

MySQL里这个函数主要是在group时能看出它的作用,一般在我们使用group by时,同样的数据,只会显示一条,但某些时刻,我们却需要把同样的数据进行规类到一起,那么group_concat这个函数就派上用场了,例子如下:
CREATE TABLE test(
id int(11) unsigned not null auto_increment,
pid int(11) unsigned not null,
name varchar(20) not null,
primary key(id)
)ENGING=myisam default charset=utf8;
insert into `test`(`pid`, `name`) values (1, 'z1');
insert into `test`(`pid`, `name`) values (1, 'z2');
insert into `test`(`pid`, `name`) values (1, 'z3');
insert into `test`(`pid`, `name`) values (2, 'z4');
table name is test
id pid name
1 1 z1
2 1 z2
3 1 z3
4 2 z4
select name from test group by pid;
select group_concat(name) as name from test group by pid;

MySQL数据库表字段类型varchar存储中文和英文所占长度对比

CREATE TABLE `t1` (
  `str` varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
insert into `t1`(`str`) values ('一二三四五六七八九十');
insert into `t1`(`str`) values ('一二三四五六七八九十十一');
insert into `t1`(`str`) values ('abcdefghijklmnopqrst');
insert into `t1`(`str`) values ('1234567890123456');

结果是在varchar设置为10的长度时,中文和英文存储的长度是一样的。