为什么CONCAT()在MySQL中不默认为默认字符集? [英] Why CONCAT() does not default to default charset in MySQL?

问题描述

是什么原因，在纯UTF-8环境中使用CONCAT()MySQL仍将串联字符串(当表达式中的某些col是int或date时)视为其他字符集(可能是Latin-1)吗?

What is the reason, that using CONCAT() in pure UTF-8 environment MySQL still treats concatenated string (when some col in expression is for example int or date) as some other charset (probably Latin-1)?

从客户端(\s)看到的MySQL环境:

MySQL environment seen from client (\s):

Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8

测试数据集:

CREATE TABLE `utf8_test` ( 
    `id` int(10) unsigned NOT NULL auto_increment, 
    `title` varchar(50) collate utf8_estonian_ci default NULL,
    `year` smallint(4) unsigned NOT NULL default '0', 
    PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_estonian_ci;

INSERT INTO utf8_test VALUES (1, 'Õäöüšž', 2011);

此查询很好:

SELECT id, title FROM utf8_test;

这会关闭utf-8标志(已在MySQL和AFIU中使用):

This one turns utf-8 flag off (already in MySQL, AFIU):

SELECT CONCAT(id, title) FROM utf8_test;

在mysql-client中，一切看起来都很好，因为它设置为将字符显示为UTF-8，但是当通过perl DBI运行时，内部具有CONCAT()的查询的所有结果都没有设置utf-8标志.示例代码:

From mysql-client everything seems fine, because it is set to show chars as UTF-8, but when running through perl DBI, all results of queries having CONCAT() inside don't have utf-8 flag set. Example code:

#!/usr/bin/perl

use strict;
use utf8::all;
use Encode qw(is_utf8);

my $dbh = your_db_connect_routine('test');

my $str = $dbh->selectrow_array('SELECT CONCAT(id, title) FROM utf8_test');
print "CONCAT: False\n" unless ( is_utf8($str) );

my $str = $dbh->selectrow_array('SELECT title FROM utf8_test');
print "NO CONCAT: False\n" unless ( is_utf8($str) );

我知道至少有两种解决方法

There is at least two workarounds i know

• 使用CAST()SELECT CONCAT( CAST(id AS CHAR CHARACTER SET utf8), title) FROM utf8_test
进行查询
• 使用$str = Encode::_utf8_on($str)(被认为是不好的做法?)

• quering with CAST() SELECT CONCAT( CAST(id AS CHAR CHARACTER SET utf8), title) FROM utf8_test
• using $str = Encode::_utf8_on($str) (is considered as bad practice?)

但是我在问:为什么它在MySQL中如此?我应该将其视为错误或功能吗?

but i am asking: why it is in MySQL so? Should i consider it as bug or feature?

推荐答案

这是MySQL中一个众所周知的错误.它在MySQL 5.5中已修复

It's a well known bug in MySQL. It's fixed in MySQL 5.5

请参阅: http://bugs.mysql.com/bug.php?id=12030

问题源于将整数与varchar串联.

The issue stems from concatenating an integer with a varchar.

解决方法是先将id(整数)转换为char，然后进行连接， 即:

The work around is to cast the id (integer) first to a char, and then concatenate, ie:

SELECT CONCAT(cast(id as char), title) FROM utf8_test

这篇关于为什么CONCAT()在MySQL中不默认为默认字符集?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！