Today, I'm going to help my colleagues with a SQL (Simplified) execution error:
Copy Code code as follows:
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 ' & gt; '
At first glance it is very baffling, check the manual, found that there are such a paragraph:
Copy Code code as follows:
The language used for day and month names and abbreviations are controlled by the value of the Lc_time_names system variabl E (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_conn Ection So, it can return month and weekday names containing Non-ascii.
That is, the date_formate () function returns the result with the character set/checksum set attribute, while the Timediff () function does not have a character set/checksum set attribute, so let's verify:
Copy Code code as follows:
mysql> set names UTF8;
Mysql> Select CharSet (date_format (' 2013-11-19 ', ' y-m-d ')), CharSet (' 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 (' 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 for the Date_format () function returns the result is different, as the character_set_connection, collation_connection values are modified through the set NAMES. In this case, if you want to work properly, you need to make a character set conversion of the result, for example:
Copy Code code as follows:
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 |
+----------------------------------------------------------------------------------------------+
It's okay.
P.s,mysql version: 5.5.20-55-log percona Server (GPL), release rel24.1, Revision 217
