转载

小心你的 sql_mode

如果你对MySQL的sql mode不熟悉，请不要随意修改或者设置sql_mode。

sql_mode是MySQL为了适配不同的数据库语法提供的一个配置。比如，你可以设置sql_mode=oracle，这样，你在操作MySQL数据库时就需要像Oracle一样去做了。其实Oracle并不是一个真正的mode，而是：PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER这些sql_mode的组合。通过这些mode的组合，MySQL在外在操作表现上就可以模拟不同的数据库的操作。

这里拿NO_AUTO_CREATE_USER为例，作为MySQL DBA，我们习惯了每次创建用户时就用GRANT语句，而不像oracle一样需要先创建这个用户，然后再授权。但是如果你设置了NO_AUTO_CREATE_USER的mode，那么你就应该自己创建用户，而不应该期望GRANT语句自动帮你创建。（5.7.7以后，NO_AUTO_CREATE_USER被设置成默认了）

如果你对sql_mode不熟悉，随意设置了一个值的话，那么你会发现MySQL给你的结果也比较“随意”

就拿我们一个客户遇到的问题为例。

他们在备库观察到复制中断了，SQL 线程报错：

Last_Error: Error 'Unknown column '' in 'field list’’

没有名字的column?

而这个出错的SQL如下：

UPDATE auuser SET usertype = 0x31,  usercode = 0x6D616F6D616F, password = "",  latestaccesstime = NULL  WHERE userid = 4

这个也是一个很正常的SQL啊

主备库表结构也一致。

这个update的SQL在备库再次执行，也会出现这个问题

最后，我们定位到，就是sql_mode导的鬼，主库的sql_mode包含有ANSI_QUOTES，而备库的不包含这个。

ANSI_QUOTES是什么列，我们看看reference的描述：

ANSI_QUOTES  Treat “"” as an identifier quote character (like the “`” quote character) and not as a string quote character. You can still use “`” to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotation marks to quote literal strings, because it is interpreted as an identifier.

也就是说，MySQL在ANSI_QUOTES会把双引号"当成关键字 ，MySQL DBA对反引号 应该很熟悉，不熟悉的同学，我show create table给你看看：

show create table INNODB_LOCK_WAITS/G  *************************** 1. row ***************************         Table: INNODB_LOCK_WAITS  Create Table: CREATE TEMPORARY TABLE `INNODB_LOCK_WAITS` (    `requesting_trx_id` varchar(18) NOT NULL DEFAULT '',    `requested_lock_id` varchar(81) NOT NULL DEFAULT '',    `blocking_trx_id` varchar(18) NOT NULL DEFAULT '',    `blocking_lock_id` varchar(81) NOT NULL DEFAULT ''  ) ENGINE=MEMORY DEFAULT CHARSET=utf8  1 row in set (0.00 sec)

这样我们也就明白了，MySQL报错说“ 'Unknown column '' in 'field list’’”的真正含义了吧。

也就是说，在ANSI_QUOTES的sql_mode下，双引号"被认为是关键字，在update语句中password = ""，双引号括起来MySQL在语法解析时认为这里应该出现一个字段名，但是表中并没有这样一个字段，所以报错出来了。

解决问题的方法有很多，这里就不详细列举了。

另外，在解决这个问题的时候，我们希望模拟真实环境测试，去下载5.6.18时，发现：

小心你的 sql_mode

社区版只有5.6.17和5.6.19，5.6.18哪里去了列？

“There is no MySQL Community Server 5.6.18. That version number was used for an out-of-schedule release of the Enterprise Edition to address the OpenSSL ‘Heartbleed’ issue. This issue did not affect the Community Edition because it uses yaSSL, not OpenSSL, so a new release of the Community Server was not needed, and 5.6.17 is followed by 5.6.19.”

由于企业版使用了OpenSSL，它有“Heartbleed”的问题，而社区版使用的是yaSSL，它没有这个问题，所以5.6.18只是在企业版的升级，社区版没有变化，所以社区版5.6.17就直接升级到5.6.19了。

正文到此结束