MySQL的INSTR和排序规则 [英] MySQL's INSTR and collations

问题描述

好，忘记下面所有复杂的内容.我的问题很简单:为什么以下结果的第二列设置为0，而所有其他列都设置为1.

EDIT 3: OK, forget all the complicated stuff below. My question is as easy as this: Why is the second column of the following result set 0 while all other columns are 1.

SELECT 'a' = 'á',
       INSTR('András','Andras'), 
       'András' LIKE 'Andras',
       INSTR('András','Andräs')

数据库和连接设置为utf8.

The database and connection is set to utf8.

结束编辑

我的MySQL的INSTR函数有问题.我有一个带有排序规则utf8_general_ci的表"values"和一个包含值AndrásSchiff"的VARCHAR列"value".现在，我执行以下查询:

I've got an issue with MySQL's INSTR function. I have a table 'values' with collation utf8_general_ci and a VARCHAR column 'value' containing the value 'András Schiff'. Now I perform the following queries:

> SET NAMES 'utf8' COLLATE 'utf8_general_ci'
> SELECT 'a' = 'á';
1
> SELECT * FROM values WHERE value LIKE '%Andras%'
'András'
> SELECT * FROM values WHERE INSTR(value,'Andras')
(Empty)
> SELECT * FROM values WHERE INSTR(value,'Andräs')
'András'

有人可以解释这种奇怪的行为吗?我认为LIKE'％...％'和INSTR是等效的，后者具有搜索字符串可以包含'％'的优点.

Can anyone explain this strange behavior? I thought LIKE '%...%' and INSTR were equivalent, the latter having the advantage that the search string may contain '%'.

谢谢

我的MySQL版本是使用readline 6.2 for debian-linux-gnu(x86_64)的Ver 14.14 Distrib 5.1.54

My MySQL version is Ver 14.14 Distrib 5.1.54, for debian-linux-gnu (x86_64) using readline 6.2

我注意到的另一件事:

> SELECT * FROM values WHERE INSTR(value,'Andras') COLLATE 'utf8_unicode_ci'

给出错误"COLLATION'utf8_unicode_ci'对于CHARACTER SET'binary'无效".但是我不明白为什么字符集应该是二进制的.

gives an error "COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'binary'". But I don't understand why the character set should be binary.

推荐答案

解决有关INSTR和COLLATE的Edit 2部分

Addressing the Edit 2 portion concerning INSTR and COLLATE

以下语法对我有用

SELECT * FROM values WHERE INSTR(value COLLATE utf8_unicode_ci,'Andras')

这篇关于MySQL的INSTR和排序规则的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！