mysql substr无效_mysql – 当str包含’é’或’ë’而substr只有’e’时,INSTR(str,substr)不起作用...

在stackoverflow的另一篇文章中,我读到INSTR可用于按相关性排序结果.

我对col LIKE’％str％’和INSTR(col,’str’)`的理解是它们的行为都相同.如何处理排序规则似乎有所不同.

CREATE TABLE `users` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO users (name)

VALUES ('Joël'), ('René');

SELECT * FROM users WHERE name LIKE '%joel%'; -- 1 record returned

SELECT * FROM users WHERE name LIKE '%rene%'; -- 1 record returned

SELECT * FROM users WHERE INSTR(name, 'joel') > 0; -- 0 records returned

SELECT * FROM users WHERE INSTR(name, 'rene') > 0; -- 0 records returned

SELECT * FROM users WHERE INSTR(name, 'joël') > 0; -- 1 record returned

SELECT * FROM users WHERE INSTR(name, 'rené') > 0; -- 1 record returned

尽管INSTR进行了一些转换,但它在é中找到了ë.

SELECT INSTR('é', 'ë'), INSTR('é', 'e'), INSTR('e', 'ë');

-- returns 1, 0, 0

我错过了什么吗？