MySQL中DATE_FORMATE函数使用时的注意点

更新时间：2015年05月09日 10:23:05 投稿：goldensun

这篇文章主要介绍了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 |
+----------------------------------------------------------------------------------------------+

就可以了 :)

您可能感兴趣的文章:

MySQL

MySQL模糊查询用法大全(正则、通配符、内置函数)
这篇文章主要介绍了MySQL模糊查询用法大全(正则、通配符、内置函数),文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧
2020-12-12
mysql怎么设置最大连接数详解
这篇文章主要给大家介绍了关于mysql怎么设置最大连接数的相关资料,设置mysql最大连接数的方法,首先打开mysql的控制台,然后输入语句set GLOBAL max_connections=1000;即可直接设置最大连接数,需要的朋友可以参考下
2022-02-02
删除MySQL重复数据的方法
这篇文章主要介绍了删除MySQL重复数据的方法,通过建立中间表实现针对冗余数据的删除功能,非常具有实用价值,需要的朋友可以参考下
2014-12-12
详解MySQL中InnoDB的存储文件
本篇是一篇关于MySQL专题知识点的内容，详细讲述了InnoDB的存储文件的相关内容，感兴趣的朋友学习下。
2018-02-02
mysql如何设置表中字段为当前时间
这篇文章主要介绍了mysql如何设置表中字段为当前时间问题，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教
2023-07-07
MySQL中关于datetime、date、time、str之间的转化与比较
这篇文章主要介绍了MySQL中关于datetime、date、time、str之间的转化与比较，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教
2022-10-10
mysql 连接出现Public Key Retrieval is n
在MySQL连接中出现“Public Key Retrieval is not allowed”错误,通常是因为在使用安全套接字层（SSL）连接时遇到了问题,本文就来介绍一下解决方法,感兴趣的可以了解一下
2024-03-03
Mysql触发器字段双向更新方式
这篇文章主要介绍了Mysql触发器字段双向更新方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教
2023-08-08
MySQL数据库必知必会之安全管理
MySQL数据库通常包含关键的数据,为确保这些数据的安全和完整,需要利用访问控制和用户管理的功能,下面这篇文章主要给大家介绍了关于MySQL数据库必知必会之安全管理的相关资料,需要的朋友可以参考下
2022-05-05
深入理解SQL的四种连接-左外连接、右外连接、内连接、全连接
本篇文章是对SQL中的四种连接-左外连接、右外连接、内连接、全连接进行了详细的分析介绍，需要的朋友参考下
2013-07-07