MySQL DATE_FORMATE函数内置字符集的坑

帮同事处理一个SQL（简化过后的）执行报错：mysql> select date_format（"2013-11-19","Y-m-d"） > timediff（"2013-11-19", "2013-11-20"）; ERROR 1267 （HY000）: Illegal mix of collations （utf8_general_ci,COERCIBLE） and （latin1_swedish_ci,NUMERIC） for operation ">"乍一看挺莫名其妙的，查了下手册，发现有这么一段：The language used for day and month names and abbreviations is controlled by the value of the lc_time_names system variable （Section 9.7, “MySQL Server Locale Support”）.The DATE_FORMAT（） returns a string with a character set and collation given by character_set_connection and collation_connection so that it can return month and weekday names containing non-ASCII characters.也就是说，DATE_FORMATE（）函数返回的结果是带有字符集/校验集属性的，而 TIMEDIFF（）函数则没有字符集/校验集属性，我们来验证一下：mysql> set names utf8;mysql> select charset（date_format（"2013-11-19","Y-m-d"））, charset（timediff（"2013-11-19", "2013-11-20"））;+--------------------------------------------+-----------------------------------------------+| charset（date_format（"2013-11-19","Y-m-d"）） | charset（timediff（"2013-11-19", "2013-11-20"）） |+--------------------------------------------+-----------------------------------------------+| utf8 | binary|+--------------------------------------------+-----------------------------------------------+mysql> set names gb2312;mysql> select charset（date_format（"2013-11-19","Y-m-d"））, charset（timediff（"2013-11-19", "2013-11-20"））;+--------------------------------------------+-----------------------------------------------+| charset（date_format（"2013-11-19","Y-m-d"）） | charset（timediff（"2013-11-19", "2013-11-20"）） |+--------------------------------------------+-----------------------------------------------+| gb2312 | binary|+--------------------------------------------+-----------------------------------------------+可以看到，随着通过 SET NAMES 修改 character_set_connection、collation_connection 值，DATE_FORMAT（）函数返回结果的字符集也跟着不一样。在这种情况下，想要正常工作，就需要将结果进行一次字符集转换，例如：mysql> select date_format（"2013-11-19","Y-m-d"） > convert（timediff（"2013-11-19", "2013-11-20"） using utf8）;+----------------------------------------------------------------------------------------------+| date_format（"2013-11-19","Y-m-d"） > convert（timediff（"2013-11-19", "2013-11-20"） using utf8） |+----------------------------------------------------------------------------------------------+|1 |+----------------------------------------------------------------------------------------------+就可以了 P.S，MySQL的版本：5.5.20-55-log Percona Server （GPL）, Release rel24.1, Revision 217<！-- .entry-content -->MySQL Proxy快速实现读写分离以及负载均衡Oracle 10g r2上switch over主备库的过程演示相关资讯 MySQL字符集

MySQL 5.6.25 修改字符集（今 09:55）
Ubuntu下设置MySQL字符集为utf8 （04月02日）
MySQL字符集知识总结（04/22/2015 06:19:08）

MySQL字符集详解（07月17日）
MySQL之字符集与校对集（06/08/2015 19:32:24）
MySQL字符集GBK转换到UTF8 （04/22/2015 06:15:42）

本文评论查看全部评论（0）