MySQL中DATE_FORMATE函数使用时的注意点
发布网友
发布时间：2022-04-21 06:28
共1个回答
热心网友
时间：2022-04-08 03:12
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
|
+----------------------------------------------------------------------------------------------+
就可以了
:)
