MySQL date_formate function built-in character set pit

Today help colleagues to handle a SQL (Simplified) execution error:

mysql> select date_format (' 2013-11-19 ', ' Y-m-d ')  >  Timediff (' 2013-11-19 ',  ' 2013-11-20 ');                                          &NBSP 
 
 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. 
  

That is, the date_formate () function returns the result with a character set/check Set property, while the Timediff () function does not have a character set/checksum set attribute, let's verify that:

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 |
+--------------------------------------------+-----------------------------------------------+

As you can see, the character set that the Date_format () function returns results in the same way as you modify the character_set_connection, collation_connection values through SET NAMES. In this case, if you want to work properly, you will need to convert the results one time to the character set, for example:

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 |
+----------------------------------------------------------------------------------------------+

You can do it:)

Version p.s,mysql: 5.5.20-55-log Percona Server (GPL), release rel24.1, Revision 217

--------------------------------------Split Line--------------------------------------

Hall of Understanding ( http://zhishuedu.com Training is a professional quality training brand jointly launched by senior MySQL expert Ye Jinlong and Wu Bingxi, which mainly includes MySQL DBA combat optimization and Python Operation Development Course, which is the most conscientious and quality training course in the industry.

This article comes from " Lao ye teahouse "blog, be sure to keep this source http://imysql.blog.51cto.com/1540006/1879883

This article is from the "Lao Ye teahouse" blog, please be sure to keep this source http://imysql.blog.51cto.com/1540006/1880059

MySQL date_formate function built-in character set pit