为什么我不能在MySQL中使用特定的排序规则？ [英] Why can't I use a specific collation in MySQL?

问题描述

我有一个表格，字符集 latin1 （通过显示变量像character_set_database; ）默认排序规则 latin1_swedish_ci （由 SHOW TABLE STATUS; 检查）。

我想使用在我的系统上编译的排序规则 latin1_general_cs 运行查询（通过显示排序规则LIKE ％[$ _cs; ）：

  select myTab WHERE col RLIKE' ]]'COLLATE'latin1_general_cs'LIMIT 10; 
  

出现错误：

 错误1253（42000）：COLLATION'latin1_general_cs'对CHARACTER SET'utf8'无效
  

b $ b

即使默认排序规则不起作用：

  select * from myTab WHERE col RLIKE'[[：upper ：]]'COLLATE'latin1_swedish_ci'LIMIT 10; 
  

错误：

 code> ERROR 1253（42000）：COLLATION'latin1_swedish_ci'对CHARACTER SET'utf8'无效
  

有什么问题？是因为我的终端以UTF-8发送数据？ （是UTF-8中的连接？）我如何使用区分大小写的排序规则？

解决方案

你可能会发现这很有趣。

utf8和latin1之间的差异< a>

我不能说我是这方面的专家，但据我所知，UTF8和Latin1对字符编码不同。所以尝试使用Latin1整理UTF8没有意义。

http://dev.mysql.com/doc/refman/5.0/en/charset-applications.html

然而：

  CREATE DATABASE mydb 
 DEFAULT CHARACTER SET utf8 
 DEFAULT COLLATE utf8_general_ci; 
 
  b 更新     h3> 
 
 
很抱歉，我刚重新读取您的问题，您说该表使用的是latin1。奇。您是否尝试过使用utf8整理？
 
I have a table with the character set latin1 (checked by show variables like "character_set_database";) and a default collation of latin1_swedish_ci (checked by SHOW TABLE STATUS;).

I'd like to run a query using the collation latin1_general_cs, which is compiled on my system (checked by Show collation LIKE "%_cs";):
select * from myTab WHERE col RLIKE '[[:upper:]]' COLLATE 'latin1_general_cs' LIMIT 10;
which gives an error:
ERROR 1253 (42000): COLLATION 'latin1_general_cs' is not valid for CHARACTER SET 'utf8'
Even the default collation does not work:
select * from myTab WHERE col RLIKE '[[:upper:]]' COLLATE 'latin1_swedish_ci' LIMIT 10;
error:
ERROR 1253 (42000): COLLATION 'latin1_swedish_ci' is not valid for CHARACTER SET 'utf8'
Whats wrong? is it because my terminal sends data in UTF-8? (Is the "Connection in UTF-8"?) How can I anyway use case sensitive collation? I need it, otherwise it seems impossible to check for uppercase letters.
解决方案
You may find this interesting. 

Differences between utf8 and latin1

I can't say I'm an expert in this area, but as far as I can see, UTF8 and Latin1 encode characters differently. So trying to collate UTF8 using Latin1 doesn't make sense.

http://dev.mysql.com/doc/refman/5.0/en/charset-applications.html

Whereas:
CREATE DATABASE mydb
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;
is consistent.

Update

Sorry, I just re-read your question and you say that the table is using latin1. Odd. Have you tried using the utf8 collations?

                        
这篇关于为什么我不能在MySQL中使用特定的排序规则？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！