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)?
是什么原因,在純UTF-8環境中使用CONCAT()MySQL仍然將連接字符串(當表達式中的某些col為例如int或date)視為其他字符集(可能是Latin-1)?
MySQL environment seen from client (\s):
從客戶端看到的MySQL環境(\ s):
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
Test dataset:
測試數據集:
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);
This query is good:
這個查詢很好:
SELECT id, title FROM utf8_test;
This one turns utf-8 flag off (already in MySQL, AFIU):
這個關閉utf-8標志(已經在MySQL,AFIU):
SELECT CONCAT(id, title) FROM utf8_test;
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:
從mysql-client看,一切似乎都很好,因為它被設置為將字符顯示為UTF-8,但是當通過perl DBI運行時,所有內部具有CONCAT()的查詢結果都沒有設置utf-8標志。示例代碼:
#!/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
我知道至少有兩種解決方法
quering with CAST() SELECT CONCAT( CAST(id AS CHAR CHARACTER SET utf8), title) FROM utf8_test
使用CAST()SELECT CONCAT(CAST(id AS CHAR CHARACTER SET utf8),標題)從utf8_test查詢
using $str = Encode::_utf8_on($str) (is considered as bad practice?)
使用$ str = Encode :: _ utf8_on($ str)(被認為是不好的做法?)
but i am asking: why it is in MySQL so? Should i consider it as bug or feature?
但我在問:為什么它在MySQL中呢?我應該將其視為錯誤或功能嗎?
2 个解决方案
#1
18
It's a well known bug in MySQL. It's fixed in MySQL 5.5
這是MySQL中一個眾所周知的錯誤。它已在MySQL 5.5中修復
請參閱:http://bugs.mysql.com/bug.php?id = 12030
The issue stems from concatenating an integer with a varchar.
問題源於將整數與varchar連接起來。
The work around is to cast the id (integer) first to a char, and then concatenate, ie:
解決方法是首先將id(整數)轉換為char,然后連接,即:
SELECT CONCAT(cast(id as char), title) FROM utf8_test
#2
0
It probably is DBD::mysql issue/peculiarity. Try enabling utf8 in database handle as described in POD for DBD::mysql (mysql_enable_utf8 part).
它可能是DBD :: mysql問題/特殊性。嘗試在數據庫句柄中啟用utf8,如POD for DBD :: mysql(mysql_enable_utf8 part)中所述。
This old (Perl 5.8 times) article can also help.
這篇舊的(Perl 5.8次)文章也可以提供幫助。