MySQL MySQL中不区分大小写的REPLACE？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5656056/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Case-insensitive REPLACE in MySQL?
提问by dkarp
MySQL runs pretty much all string comparisons under the default collation... except the REPLACEcommand. I have a case-insensitive collation and need to run a case-insensitive REPLACE. Is there any way to force REPLACEto use the current collation rather than always doing case-sensitive comparisons? I'm willing to upgrade my MySQL (currently running 5.1) to get added functionality...
MySQL 在默认排序规则下几乎运行所有字符串比较......除了REPLACE命令。我有一个不区分大小写的排序规则，需要运行一个不区分大小写的REPLACE. 有没有办法强制REPLACE使用当前的排序规则而不是总是进行区分大小写的比较？我愿意升级我的 MySQL（目前运行 5.1）以获得附加功能......
mysql> charset utf8 collation utf8_unicode_ci;
Charset changed
mysql> select 'abc' like '%B%';
+------------------+
| 'abc' like '%B%' |
+------------------+
| 1 |
+------------------+
mysql> select replace('aAbBcC', 'a', 'f');
+-----------------------------+
| replace('aAbBcC', 'a', 'f') |
+-----------------------------+
| fAbBcC | <--- *NOT* 'ffbBcC'
+-----------------------------+
采纳答案by fvox
If replace(lower())doesn't work, you'll need to create another function.
如果replace(lower())不起作用，您将需要创建另一个函数。
回答by santiago arizti
My 2 cents.
我的 2 美分。
Since many people have upgraded from MySQL to MariaDB those people will have available a new function called REGEXP_REPLACE. Use it as you would a normal replace, but the pattern is a regular expression.
由于许多人已经从 MySQL 升级到 MariaDB，这些人将可以使用一个名为REGEXP_REPLACE. 像正常替换一样使用它，但模式是一个正则表达式。
This is a working example:
这是一个工作示例：
UPDATE `myTable`
SET `myField` = REGEXP_REPLACE(`myField`, '(?i)my insensitive string', 'new string')
WHERE `myField` REGEXP '(?i)my insensitive string'
The option (?i)makes all the subsequent matches case insensitive (if put at the beginning of the pattern like I have then it all is insensitive).
该选项(?i)使所有后续匹配不区分大小写（如果像我一样放在模式的开头，那么它都是不敏感的）。
See here for more information: https://mariadb.com/kb/en/mariadb/pcre/
有关更多信息，请参见此处：https: //mariadb.com/kb/en/mariadb/pcre/
Edit: as of MySQL 8.0 you can now use the regexp_replacefunction too, see documentation: https://dev.mysql.com/doc/refman/8.0/en/regexp.html
编辑：从 MySQL 8.0 开始，您现在也可以使用该regexp_replace功能，请参阅文档：https: //dev.mysql.com/doc/refman/8.0/en/regexp.html
回答by Aivar
Alternative function for one spoken by fvox.
fvox 所说的替代功能。
DELIMITER |
CREATE FUNCTION case_insensitive_replace ( REPLACE_WHERE text, REPLACE_THIS text, REPLACE_WITH text )
RETURNS text
DETERMINISTIC
BEGIN
DECLARE last_occurency int DEFAULT '1';
IF LCASE(REPLACE_THIS) = LCASE(REPLACE_WITH) OR LENGTH(REPLACE_THIS) < 1 THEN
RETURN REPLACE_WHERE;
END IF;
WHILE Locate( LCASE(REPLACE_THIS), LCASE(REPLACE_WHERE), last_occurency ) > 0 DO
BEGIN
SET last_occurency = Locate(LCASE(REPLACE_THIS), LCASE(REPLACE_WHERE));
SET REPLACE_WHERE = Insert( REPLACE_WHERE, last_occurency, LENGTH(REPLACE_THIS), REPLACE_WITH);
SET last_occurency = last_occurency + LENGTH(REPLACE_WITH);
END;
END WHILE;
RETURN REPLACE_WHERE;
END;
|
DELIMITER ;
Small test:
小测试：
SET @str = BINARY 'New York';
SELECT case_insensitive_replace(@str, 'y', 'K');
Answers: New Kork
答案： New Kork
回答by Wandering Zombie
I went with http://pento.net/2009/02/15/case-insensitive-replace-for-mysql/(in fvox's answer) which performs the case insensitive search with case sensitive replacement and without changing the case of what should be unaffected characters elsewhere in the searched string.
我使用了http://pento.net/2009/02/15/case-insensitive-replace-for-mysql/（在 fvox 的回答中），它使用区分大小写的替换来执行不区分大小写的搜索，并且不改变应该的大小写是搜索字符串中其他地方不受影响的字符。
N.B. the comment further down that same page stating that CHAR(255) should be changed to VARCHAR(255) - this seemed to be required for me as well.
注意同一页面下方的评论指出 CHAR(255) 应该更改为 VARCHAR(255) - 这似乎也是我所需要的。
回答by notalbert
This modification of Luist's answer allows one to replace the needle with a differently cased version of the needle (two lines change).
Luist 的答案的这种修改允许人们用不同套管版本的针替换针（两行更改）。
DELIMITER |
CREATE FUNCTION case_insensitive_replace ( REPLACE_WHERE text, REPLACE_THIS text, REPLACE_WITH text )
RETURNS text
DETERMINISTIC
BEGIN
DECLARE last_occurency int DEFAULT '1';
IF LENGTH(REPLACE_THIS) < 1 THEN
RETURN REPLACE_WHERE;
END IF;
WHILE Locate( LCASE(REPLACE_THIS), LCASE(REPLACE_WHERE), last_occurency ) > 0 DO
BEGIN
SET last_occurency = Locate(LCASE(REPLACE_THIS), LCASE(REPLACE_WHERE), last_occurency);
SET REPLACE_WHERE = Insert( REPLACE_WHERE, last_occurency, LENGTH(REPLACE_THIS), REPLACE_WITH);
SET last_occurency = last_occurency + LENGTH(REPLACE_WITH);
END;
END WHILE;
RETURN REPLACE_WHERE;
END;
|
DELIMITER ;
回答by fisharebest
In the previous answers, and the pento.net link, the arguments to LOCATE()are lower-cased.
在前面的答案和 pento.net 链接中， 的参数LOCATE()是小写的。
This is a waste of resources, as LOCATE is case-insensitive by default:
这是一种资源浪费，因为 LOCATE 默认不区分大小写：
mysql> select locate('el', 'HELLo');
+-----------------------+
| locate('el', 'HELLo') |
+-----------------------+
| 2 |
+-----------------------+
You can replace
你可以更换
WHILE Locate( LCASE(REPLACE_THIS), LCASE(REPLACE_WHERE), last_occurency ) > 0 DO
WHILE Locate( LCASE(REPLACE_THIS), LCASE(REPLACE_WHERE), last_occurency ) > 0 DO
with
和
WHILE Locate(REPLACE_THIS, REPLACE_WHERE, last_occurency ) > 0 DO
WHILE Locate(REPLACE_THIS, REPLACE_WHERE, last_occurency ) > 0 DO
etc.
等等。
回答by iwritecode
In case of 'special' characters there is unexpected behaviour:
如果出现“特殊”字符，则会出现意外行为：
SELECT case_insensitive_replace('A', '?', 'a')
Gives
给
a
Which is unexpected... since we only want to replace the ? not A
这是出乎意料的......因为我们只想替换 ? 不是一个
What is even more weird:
更奇怪的是：
SELECT LOCATE('?', 'A');
gives
给
0
Which is the correct result... seems to have to do with encoding of the parameters of the stored procedure...
哪个是正确的结果……似乎与存储过程参数的编码有关……
