如何在MySQL中去除重音符号？

Question

我刚刚编译了一个包含一百万个地名的数据库，将用于自动完成小部件以查找城市。很多这些地方都有重音... 我希望能够在用户输入没有重音的名称时找到记录。

为了实现这一点，我有第二列是不带重音的名称拷贝。其中许多记录仍然是空白的，因此我想编写一个查询来填充它们。这在MySQL中是否可行？如果是，该怎么做呢？

- mpen

---

查看此帖子：https://dev59.com/M3E95IYBdhLWcg3wdtqj，您无需创建另一列，只需更改查询即可。 - Haim Evgi

为什么不使用正则表达式，例如 (a|á)？ - Eric Fortis

@Eric Fortis 在这种情况下，正则表达式是不必要的，而且很重。 - yoda

@Eric，因为那会变得非常庞大。PHP的iconv()函数可以帮助解决这个问题。不过需要注意的是，在TCPDF源代码中，有很多特殊情况需要处理，这些情况并不是PHP所使用的库本身就具备的（为了确保非常安全/兼容Unicode）。 - zanlok

@mark 抱歉是我的错误，希望这个链接能够帮到你。 - Haim Evgi

Answer 1

如果您为该列设置适当的排序规则，则该字段中的值将自然地与其非变音等效项进行比较。

mysql> SET NAMES 'utf8' COLLATE 'utf8_unicode_ci';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT 'é' = 'e';
+------------+
| 'é' = 'e' |
+------------+
|          1 |
+------------+
1 row in set (0.05 sec)

- Ignacio Vazquez-Abrams

---

问题..这对非常非英语字符的处理效果如何？ - zanlok

@zanlok：翻译效果不太好。它可以处理一些，比如将“ß”等同于“ss”，但是更复杂的情况就会出现问题。 - Ignacio Vazquez-Abrams

"ß" = "ss" COLLATE utf8_unicode_ci -- 是匹配的。在该排序中，组合变音符号也有效。 - Rick James

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_520_ci - Lee Goddard

Answer 2

我曾经遇到过同样的问题，于是我编写了一份基于PHP脚本的查询列表，以便去除重音并生成符合SEO标准的URL：

也许你想添加其他特殊字符，例如$或£符号...

UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Š','S');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'š','s');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ð','Dj');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ž','Z');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ž','z');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'À','A');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Á','A');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Â','A');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ã','A');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ä','A');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Å','A');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Æ','A');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ç','C');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'È','E');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'É','E');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ê','E');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ë','E');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ì','I');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Í','I');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Î','I');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ï','I');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ñ','N');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ò','O');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ó','O');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ô','O');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Õ','O');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ö','O');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ø','O');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ù','U');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ú','U');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Û','U');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ü','U');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ý','Y');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Þ','B');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ß','Ss');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'à','a');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'á','a');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'â','a');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ã','a');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ä','a');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'å','a');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'æ','a');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ç','c');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'è','e');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'é','e');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ê','e');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ë','e');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ì','i');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'í','i');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'î','i');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ï','i');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ð','o');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ñ','n');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ò','o');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ó','o');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ô','o');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'õ','o');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ö','o');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ø','o');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ù','u');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ú','u');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'û','u');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ý','y');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ý','y');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'þ','b');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ÿ','y');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ƒ','f');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'.',' ');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,' ','-');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'--','-');

UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ě','e');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ž','z');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'š','s');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'č','c');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ř','r');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ď','d');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ť','t');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ň','n');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ů','u');

UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ě','E');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ž','Z');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Š','S');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Č','C');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ř','R');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ď','D');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ť','T');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ň','N');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ů','U');

UPDATE TABLE_NAME SET COLUMN = LOWER(COLUMN);

- igasparetto

---

恭喜，存储过程很棒。 - tony gil

A) 这实际上回答了标题问题 B) 当您拥有一个大型项目列表且没有预先生成的slug/pretty-url时，这非常有用，并且您希望直接在DB中生成它们（因为值的数量很大）。顺便说一句：我会添加 ě, ž, š, č , ř , ď, ť, ň, ů - jave.web

完美运行！敬礼。 - Ramiro Arizpe Giacomelli

它确实会去除重音符号，但是有些语言对某些值使用不同的表示方法（例如在德语中，“ä”是“ae”，“ö”是“oe”等）。 - Gerrit-K

Answer 3

我分享这个，也许可以帮助到你....：

DELIMITER //
CREATE OR REPLACE FUNCTION `remove_accents`(`str` TEXT)
    RETURNS text
    LANGUAGE SQL
    DETERMINISTIC
    NO SQL
    SQL SECURITY INVOKER
    COMMENT ''

BEGIN

    SET str = REPLACE(str,'Š','S');
    SET str = REPLACE(str,'š','s');
    SET str = REPLACE(str,'Ð','Dj');
    SET str = REPLACE(str,'Ž','Z');
    SET str = REPLACE(str,'ž','z');
    SET str = REPLACE(str,'À','A');
    SET str = REPLACE(str,'Á','A');
    SET str = REPLACE(str,'Â','A');
    SET str = REPLACE(str,'Ã','A');
    SET str = REPLACE(str,'Ä','A');
    SET str = REPLACE(str,'Å','A');
    SET str = REPLACE(str,'Æ','A');
    SET str = REPLACE(str,'Ç','C');
    SET str = REPLACE(str,'È','E');
    SET str = REPLACE(str,'É','E');
    SET str = REPLACE(str,'Ê','E');
    SET str = REPLACE(str,'Ë','E');
    SET str = REPLACE(str,'Ì','I');
    SET str = REPLACE(str,'Í','I');
    SET str = REPLACE(str,'Î','I');
    SET str = REPLACE(str,'Ï','I');
    SET str = REPLACE(str,'Ñ','N');
    SET str = REPLACE(str,'Ò','O');
    SET str = REPLACE(str,'Ó','O');
    SET str = REPLACE(str,'Ô','O');
    SET str = REPLACE(str,'Õ','O');
    SET str = REPLACE(str,'Ö','O');
    SET str = REPLACE(str,'Ø','O');
    SET str = REPLACE(str,'Ù','U');
    SET str = REPLACE(str,'Ú','U');
    SET str = REPLACE(str,'Û','U');
    SET str = REPLACE(str,'Ü','U');
    SET str = REPLACE(str,'Ý','Y');
    SET str = REPLACE(str,'Þ','B');
    SET str = REPLACE(str,'ß','Ss');
    SET str = REPLACE(str,'à','a');
    SET str = REPLACE(str,'á','a');
    SET str = REPLACE(str,'â','a');
    SET str = REPLACE(str,'ã','a');
    SET str = REPLACE(str,'ä','a');
    SET str = REPLACE(str,'å','a');
    SET str = REPLACE(str,'æ','a');
    SET str = REPLACE(str,'ç','c');
    SET str = REPLACE(str,'è','e');
    SET str = REPLACE(str,'é','e');
    SET str = REPLACE(str,'ê','e');
    SET str = REPLACE(str,'ë','e');
    SET str = REPLACE(str,'ì','i');
    SET str = REPLACE(str,'í','i');
    SET str = REPLACE(str,'î','i');
    SET str = REPLACE(str,'ï','i');
    SET str = REPLACE(str,'ð','o');
    SET str = REPLACE(str,'ñ','n');
    SET str = REPLACE(str,'ò','o');
    SET str = REPLACE(str,'ó','o');
    SET str = REPLACE(str,'ô','o');
    SET str = REPLACE(str,'õ','o');
    SET str = REPLACE(str,'ö','o');
    SET str = REPLACE(str,'ø','o');
    SET str = REPLACE(str,'ù','u');
    SET str = REPLACE(str,'ú','u');
    SET str = REPLACE(str,'û','u');
    SET str = REPLACE(str,'ý','y');
    SET str = REPLACE(str,'ý','y');
    SET str = REPLACE(str,'þ','b');
    SET str = REPLACE(str,'ÿ','y');
    SET str = REPLACE(str,'ƒ','f');


    RETURN str;
END
//
DELIMITER ;

- Abdel

Answer 4

drop function if exists fn_remove_accents;
delimiter |
create function fn_remove_accents( textvalue varchar(20000) )
returns varchar(20000)
begin

set @textvalue = textvalue;

-- ACCENTS
set @withaccents = 'ŠšŽžÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÑÒÓÔÕÖØÙÚÛÜÝŸÞàáâãäåæçèéêëìíîïñòóôõöøùúûüýÿþƒ';
set @withoutaccents = 'SsZzAAAAAAACEEEEIIIINOOOOOOUUUUYYBaaaaaaaceeeeiiiinoooooouuuuyybf';
set @count = length(@withaccents);

while @count > 0 do
    set @textvalue = replace(@textvalue, substring(@withaccents, @count, 1), substring(@withoutaccents, @count, 1));
    set @count = @count - 1;
end while;

-- SPECIAL CHARS
set @special = '!@#$%¨&*()_+=§¹²³£¢¬"`´{[^~}]<,>.:;?/°ºª+*|\\''';
set @count = length(@special);
while @count > 0 do
    set @textvalue = replace(@textvalue, substring(@special, @count, 1), '');
    set @count = @count - 1;
end while;

return @textvalue;

end
|

- user3245067

---

解决方案很好，但是使用了会话变量而不是本地变量，并且缺少DETERMINISTIC关键字。请参见下面的更新解决方案... - jfx

Answer 5

以下是使用单个查询的简便解决方案：

UPDATE `my_table` SET alias = lower(name),
alias = replace(alias,'Š','S'),
alias = replace(alias,'š','s'),
alias = replace(alias,'Ð','Dj'),
alias = replace(alias,'Ž','Z'),
alias = replace(alias,'ž','z'),
alias = replace(alias,'À','A'),
alias = replace(alias,'Á','A'),
alias = replace(alias,'Â','A'),
alias = replace(alias,'Ã','A'),
alias = replace(alias,'Ä','A'),
alias = replace(alias,'Å','A'),
alias = replace(alias,'Æ','A'),
alias = replace(alias,'Ç','C'),
alias = replace(alias,'È','E'),
alias = replace(alias,'É','E'),
alias = replace(alias,'Ê','E'),
alias = replace(alias,'Ë','E'),
alias = replace(alias,'Ì','I'),
alias = replace(alias,'Í','I'),
alias = replace(alias,'Î','I'),
alias = replace(alias,'Ï','I'),
alias = replace(alias,'Ñ','N'),
alias = replace(alias,'Ò','O'),
alias = replace(alias,'Ó','O'),
alias = replace(alias,'Ô','O'),
alias = replace(alias,'Õ','O'),
alias = replace(alias,'Ö','O'),
alias = replace(alias,'Ø','O'),
alias = replace(alias,'Ù','U'),
alias = replace(alias,'Ú','U'),
alias = replace(alias,'Û','U'),
alias = replace(alias,'Ü','U'),
alias = replace(alias,'Ý','Y'),  
alias = replace(alias,'š','s'),
alias = replace(alias,'Ð','Dj')
alias = replace(alias,'ž','z'),
alias = replace(alias,'Þ','B'),
alias = replace(alias,'ß','Ss'),
alias = replace(alias,'à','a'),
alias = replace(alias,'á','a'),
alias = replace(alias,'â','a'),
alias = replace(alias,'ã','a'),
alias = replace(alias,'ä','a'),
alias = replace(alias,'å','a'),
alias = replace(alias,'æ','a'),
alias = replace(alias,'ç','c'),
alias = replace(alias,'è','e'),
alias = replace(alias,'é','e'),
alias = replace(alias,'ê','e'),
alias = replace(alias,'ë','e'),
alias = replace(alias,'ì','i'),
alias = replace(alias,'í','i'),
alias = replace(alias,'î','i'),
alias = replace(alias,'ï','i'),
alias = replace(alias,'ð','o'),
alias = replace(alias,'ñ','n'),
alias = replace(alias,'ò','o'),
alias = replace(alias,'ó','o'),
alias = replace(alias,'ô','o'),
alias = replace(alias,'õ','o'),
alias = replace(alias,'ö','o'),
alias = replace(alias,'ø','o'),
alias = replace(alias,'ù','u'),
alias = replace(alias,'ú','u'),
alias = replace(alias,'û','u'),
alias = replace(alias,'ý','y'),
alias = replace(alias,'ý','y'),
alias = replace(alias,'þ','b'),
alias = replace(alias,'ÿ','y'),
alias = replace(alias,'ƒ','f'),
alias = replace(alias, 'œ', 'oe'),
alias = trim(alias);

在这个例子中：

- 'my_table' 是表名， - 'name' 是原始字段， - 'alias' 是新的字段。

希望这可以帮到你！

如果你想在mysql中生成一个slug字段，也可以参考这个问题的解决方案： Easy way of generating a slug name column from the name column?

- Erwan

---

太好了！只是快速评论：在“alias = replace(alias,'Ð','Dj')”这行后面，你漏掉了一个逗号。而且你还可以添加这两行额外的代码，以捕获更多的变化： alias = replace(alias, 'Ł', 'L'), alias = replace(alias, 'ą', 'a'), - Asparagirl

Answer 6

user3245067提供的解决方案很好，但它使用了会话变量。这是一个使用本地变量和在mysql 5.5中带有DETERMINISTIC关键字的解决方案。在mysql-5.5中测试了德语umlaut。

drop function if exists fn_remove_accents;
delimiter $$
create function fn_remove_accents( param_textvalue varchar(20000) )
returns varchar(20000) DETERMINISTIC
begin

DECLARE var_textvalue VARCHAR(20000);
DECLARE var_withaccents VARCHAR(50);
DECLARE var_withoutaccents VARCHAR(50);
DECLARE var_count INT;
DECLARE var_special INT;

set var_textvalue = param_textvalue;

-- ACCENTS
set var_withaccents = 'ŠšŽžÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÑÒÓÔÕÖØÙÚÛÜÝŸÞàáâãäåæçèéêëìíîïñòóôõöøùúûüýÿþƒ';
set var_withoutaccents = 'SsZzAAAAAAACEEEEIIIINOOOOOOUUUUYYBaaaaaaaceeeeiiiinoooooouuuuyybf';
set var_count = length(var_withaccents);

while var_count > 0 do
    set var_textvalue = replace(var_textvalue, substring(var_withaccents, var_count, 1), substring(var_withoutaccents, var_count, 1));
    set var_count = var_count - 1;
end while;

-- SPECIAL CHARS
set var_special = '!var_#$%¨&*()_+=§¹²³£¢¬"`´{[^~}]<,>.:;?/°ºª+*|\\''';
set var_count = length(var_special);
while var_count > 0 do
    set var_textvalue = replace(var_textvalue, substring(var_special, var_count, 1), '');
    set var_count = var_count - 1;
end while;

return var_textvalue;

end$$

delimiter ;

- jfx

---

你好！你确定你的 var_special 变量吗？为什么要使用这种模式：var_？ - Laurent.B

我使用模式“var_”来轻松区分变量和列名 - 如果在函数中使用选择，则特别有用，但必须承认，在这里并不是那么有价值 :)。 - jfx

我支持Laurent.B的评论。可能存在误解，我们对var_special的内容感到困惑，而不是它的名称。此外，我认为它应该是一个字符串而不是INT。 - Vojta Havránek

Answer 7

这是我找到的最快、最有效的方法：

UPDATE movies SET `name` = REPLACE(`name`,'Š','S');
UPDATE movies SET `name` = REPLACE(`name`,'š','s');
UPDATE movies SET `name` = REPLACE(`name`,'Ð','Dj');
UPDATE movies SET `name` = REPLACE(`name`,'Ž','Z');
UPDATE movies SET `name` = REPLACE(`name`,'ž','z');
UPDATE movies SET `name` = REPLACE(`name`,'À','A');
UPDATE movies SET `name` = REPLACE(`name`,'Á','A');
UPDATE movies SET `name` = REPLACE(`name`,'Â','A');
UPDATE movies SET `name` = REPLACE(`name`,'Ã','A');
UPDATE movies SET `name` = REPLACE(`name`,'Ä','A');
UPDATE movies SET `name` = REPLACE(`name`,'Å','A');
UPDATE movies SET `name` = REPLACE(`name`,'Æ','A');
UPDATE movies SET `name` = REPLACE(`name`,'Ç','C');
UPDATE movies SET `name` = REPLACE(`name`,'È','E');
UPDATE movies SET `name` = REPLACE(`name`,'É','E');
UPDATE movies SET `name` = REPLACE(`name`,'Ê','E');
UPDATE movies SET `name` = REPLACE(`name`,'Ë','E');
UPDATE movies SET `name` = REPLACE(`name`,'Ì','I');
UPDATE movies SET `name` = REPLACE(`name`,'Í','I');
UPDATE movies SET `name` = REPLACE(`name`,'Î','I');
UPDATE movies SET `name` = REPLACE(`name`,'Ï','I');
UPDATE movies SET `name` = REPLACE(`name`,'Ñ','N');
UPDATE movies SET `name` = REPLACE(`name`,'Ò','O');
UPDATE movies SET `name` = REPLACE(`name`,'Ó','O');
UPDATE movies SET `name` = REPLACE(`name`,'Ô','O');
UPDATE movies SET `name` = REPLACE(`name`,'Õ','O');
UPDATE movies SET `name` = REPLACE(`name`,'Ö','O');
UPDATE movies SET `name` = REPLACE(`name`,'Ø','O');
UPDATE movies SET `name` = REPLACE(`name`,'Ù','U');
UPDATE movies SET `name` = REPLACE(`name`,'Ú','U');
UPDATE movies SET `name` = REPLACE(`name`,'Û','U');
UPDATE movies SET `name` = REPLACE(`name`,'Ü','U');
UPDATE movies SET `name` = REPLACE(`name`,'Ý','Y');
UPDATE movies SET `name` = REPLACE(`name`,'Þ','B');
UPDATE movies SET `name` = REPLACE(`name`,'ß','Ss');
UPDATE movies SET `name` = REPLACE(`name`,'à','a');
UPDATE movies SET `name` = REPLACE(`name`,'á','a');
UPDATE movies SET `name` = REPLACE(`name`,'â','a');
UPDATE movies SET `name` = REPLACE(`name`,'ã','a');
UPDATE movies SET `name` = REPLACE(`name`,'ä','a');
UPDATE movies SET `name` = REPLACE(`name`,'å','a');
UPDATE movies SET `name` = REPLACE(`name`,'æ','a');
UPDATE movies SET `name` = REPLACE(`name`,'ç','c');
UPDATE movies SET `name` = REPLACE(`name`,'è','e');
UPDATE movies SET `name` = REPLACE(`name`,'é','e');
UPDATE movies SET `name` = REPLACE(`name`,'ê','e');
UPDATE movies SET `name` = REPLACE(`name`,'ë','e');
UPDATE movies SET `name` = REPLACE(`name`,'ì','i');
UPDATE movies SET `name` = REPLACE(`name`,'í','i');
UPDATE movies SET `name` = REPLACE(`name`,'î','i');
UPDATE movies SET `name` = REPLACE(`name`,'ï','i');
UPDATE movies SET `name` = REPLACE(`name`,'ð','o');
UPDATE movies SET `name` = REPLACE(`name`,'ñ','n');
UPDATE movies SET `name` = REPLACE(`name`,'ò','o');
UPDATE movies SET `name` = REPLACE(`name`,'ó','o');
UPDATE movies SET `name` = REPLACE(`name`,'ô','o');
UPDATE movies SET `name` = REPLACE(`name`,'õ','o');
UPDATE movies SET `name` = REPLACE(`name`,'ö','o');
UPDATE movies SET `name` = REPLACE(`name`,'ø','o');
UPDATE movies SET `name` = REPLACE(`name`,'ù','u');
UPDATE movies SET `name` = REPLACE(`name`,'ú','u');
UPDATE movies SET `name` = REPLACE(`name`,'û','u');
UPDATE movies SET `name` = REPLACE(`name`,'ý','y');
UPDATE movies SET `name` = REPLACE(`name`,'ý','y');
UPDATE movies SET `name` = REPLACE(`name`,'þ','b');
UPDATE movies SET `name` = REPLACE(`name`,'ÿ','y');
UPDATE movies SET `name` = REPLACE(`name`,'ƒ','f');

- George Chalhoub

Answer 8

我想知道使用 utf8_unicode_ci 的 MariaDB 的 REGEXP_REPLACE(col, 'e', 'e') 是否可以一次替换所有的 e。

- Rick James

---

REGEX 函数通常将带重音的字符视为与普通字符不同。因此，这种方法行不通。 - Linga

是的，MySQL 8.0应该可以工作。但在MariaDB中还不行。 - Linga

Answer 9

请查看MySQL手册上关于CONVERT()和CAST()的内容：http://dev.mysql.com/doc/refman/5.0/en/charset-convert.html

CONVERT()提供了一种在不同字符集之间转换数据的方法。语法如下：

CONVERT(expr USING transcoding_name)

- Piskvor left the building

---

CONVERT和CAST不会转换字符。 - Marco Marsala