MySQL DATE_FORMATE函数内置字符集的坑
可以看到,随着通过 SET NAMES 修改 character_set_connection、collation_connection 值,DATE_FORMAT() 函数返回结果的字符集也
帮同事处理一个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

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

This article explores optimizing MySQL memory usage in Docker. It discusses monitoring techniques (Docker stats, Performance Schema, external tools) and configuration strategies. These include Docker memory limits, swapping, and cgroups, alongside

This article addresses MySQL's "unable to open shared library" error. The issue stems from MySQL's inability to locate necessary shared libraries (.so/.dll files). Solutions involve verifying library installation via the system's package m

The article discusses using MySQL's ALTER TABLE statement to modify tables, including adding/dropping columns, renaming tables/columns, and changing column data types.

This article compares installing MySQL on Linux directly versus using Podman containers, with/without phpMyAdmin. It details installation steps for each method, emphasizing Podman's advantages in isolation, portability, and reproducibility, but also

This article provides a comprehensive overview of SQLite, a self-contained, serverless relational database. It details SQLite's advantages (simplicity, portability, ease of use) and disadvantages (concurrency limitations, scalability challenges). C

This guide demonstrates installing and managing multiple MySQL versions on macOS using Homebrew. It emphasizes using Homebrew to isolate installations, preventing conflicts. The article details installation, starting/stopping services, and best pra

Article discusses configuring SSL/TLS encryption for MySQL, including certificate generation and verification. Main issue is using self-signed certificates' security implications.[Character count: 159]

Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]
