当前位置： 文江博客话题详情

如何删除MySQL中的重音符号？

发布于 2024-10-14 15:55:44 字数 178 浏览 3 评论 0原文

我刚刚编制了一个包含 100 万个地名的数据库。我将在自动完成小部件中使用它来查找城市。很多地方都有重音...我希望能够在用户键入不带重音的名称时找到记录。

为了做到这一点，我有一个第二列，其中包含未重音的名称副本。其中许多记录仍然是空白的，所以我想编写一个查询来填充它们。这在直接 MySQL 中可能吗？如果是这样，怎么办？

原文

I've just compiled a database of 1 million place names. I'm going to use it in an auto-complete widget to look up cities. A lot of these places have accents... I want to be able to find records when a user types the name without an accent.

In order to do this, I've got a 2nd column with an unaccented copy of the name. Many of these records are still blank, so I want to write a query to fill them in. Is this possible in straight MySQL? If so, how?

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（10）

孤独难免 2024-10-21 15:55:44

如果您为列设置了适当的排序规则，那么该字段中的值将自然地与其不带重音符号的等效值进行比较。

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)

If you set an appropriate collation for the column then the value within the field will compare equal to its unaccented equivalent naturally.

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)
回复 原文
昇り龍 2024-10-21 15:55:44

我遇到了同样的问题，所以我根据 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);

I had the same problem, so I wrote a list of querys based on a PHP script I have to remove accents and make SEO friendly URLs:

Maybe you would like to add other special characters, such as the $ or £ symbols...

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);
回复 原文
向地狱狂奔 2024-10-21 15:55:44

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

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 ;

I share this maybe can help....:

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 ;
回复 原文
末が日狂欢 2024-10-21 15:55:44
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
|

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
|
回复 原文
乖乖公主 2024-10-21 15:55:44

这是一个使用单个查询的简单解决方案：

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 字段，您也可以检查此变体：
生成从名称列中提取名称列？

Here is an easy solution with a single query :

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);

In this example :

  • 'my_table' is the name of the table,
  • 'name' is the original field
  • 'alias' is the new field

Hope it helps !

You can also check this variant if you try to generate a slug field with mysql :
Easy way of generating a slug name column from the name column?

回复 原文
明明#如月 2024-10-21 15:55:44

user3245067的解决方案很棒，但它使用会话变量。
这是使用局部变量以及 mysql 5.5 的 DETERMINISTIC 关键字的解决方案。在 mysql-5.5 中使用德语变音符号进行了测试。

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 ;

The solution of user3245067 is great, but it uses session variables.
This is the solution with local variables and also with DETERMINISTIC keyword for mysql 5.5. Tested with german umlaute in mysql-5.5.

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 ;
回复 原文
指尖上的星空 2024-10-21 15:55:44

我想知道 MariaDB 的 REGEXP_REPLACE(col, 'e', 'e') 与 utf8_unicode_ci 是否会同时执行所有 e 操作。

I wonder if MariaDB's REGEXP_REPLACE(col, 'e', 'e') with utf8_unicode_ci would do all the es at once.

回复 原文
寒江雪… 2024-10-21 15:55:44

这是一个基于 WordPress 的 remove_accents() 的重音去除函数。

唯一的区别是：

  • 将“ß”更改为“ss”。
  • 删除更复杂的替换逻辑。仅移植了 $chars 数组。
DELIMITER //
CREATE FUNCTION `remove_accents`(`str` TEXT)
    RETURNS TEXT
    DETERMINISTIC
BEGIN
    SET str = REPLACE(str, 'ª', 'a');
    SET str = REPLACE(str, 'º', 'o');
    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, 'Æ', 'AE');
    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, 'Ð', 'D');
    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, 'Ù', '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, 'Þ', 'TH');
    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, 'æ', 'ae');
    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, 'ð', 'd');
    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, 'þ', 'th');
    SET str = REPLACE(str, 'ÿ', 'y');
    SET str = REPLACE(str, 'Ø', 'O');
    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, 'ć', 'c');
    SET str = REPLACE(str, 'Ĉ', 'C');
    SET str = REPLACE(str, 'ĉ', 'c');
    SET str = REPLACE(str, 'Ċ', 'C');
    SET str = REPLACE(str, 'ċ', 'c');
    SET str = REPLACE(str, 'Č', 'C');
    SET str = REPLACE(str, 'č', 'c');
    SET str = REPLACE(str, 'Ď', 'D');
    SET str = REPLACE(str, 'ď', 'd');
    SET str = REPLACE(str, 'Đ', 'D');
    SET str = REPLACE(str, 'đ', 'd');
    SET str = REPLACE(str, 'Ē', 'E');
    SET str = REPLACE(str, 'ē', 'e');
    SET str = REPLACE(str, 'Ĕ', 'E');
    SET str = REPLACE(str, 'ĕ', 'e');
    SET str = REPLACE(str, 'Ė', 'E');
    SET str = REPLACE(str, 'ė', 'e');
    SET str = REPLACE(str, 'Ę', 'E');
    SET str = REPLACE(str, 'ę', 'e');
    SET str = REPLACE(str, 'Ě', 'E');
    SET str = REPLACE(str, 'ě', 'e');
    SET str = REPLACE(str, 'Ĝ', 'G');
    SET str = REPLACE(str, 'ĝ', 'g');
    SET str = REPLACE(str, 'Ğ', 'G');
    SET str = REPLACE(str, 'ğ', 'g');
    SET str = REPLACE(str, 'Ġ', 'G');
    SET str = REPLACE(str, 'ġ', 'g');
    SET str = REPLACE(str, 'Ģ', 'G');
    SET str = REPLACE(str, 'ģ', 'g');
    SET str = REPLACE(str, 'Ĥ', 'H');
    SET str = REPLACE(str, 'ĥ', 'h');
    SET str = REPLACE(str, 'Ħ', 'H');
    SET str = REPLACE(str, 'ħ', 'h');
    SET str = REPLACE(str, 'Ĩ', 'I');
    SET str = REPLACE(str, 'ĩ', 'i');
    SET str = REPLACE(str, 'Ī', 'I');
    SET str = REPLACE(str, 'ī', 'i');
    SET str = REPLACE(str, 'Ĭ', 'I');
    SET str = REPLACE(str, 'ĭ', 'i');
    SET str = REPLACE(str, 'Į', 'I');
    SET str = REPLACE(str, 'į', 'i');
    SET str = REPLACE(str, 'İ', 'I');
    SET str = REPLACE(str, 'ı', 'i');
    SET str = REPLACE(str, 'IJ', 'IJ');
    SET str = REPLACE(str, 'ij', 'ij');
    SET str = REPLACE(str, 'Ĵ', 'J');
    SET str = REPLACE(str, 'ĵ', 'j');
    SET str = REPLACE(str, 'Ķ', 'K');
    SET str = REPLACE(str, 'ķ', 'k');
    SET str = REPLACE(str, 'ĸ', 'k');
    SET str = REPLACE(str, 'Ĺ', 'L');
    SET str = REPLACE(str, 'ĺ', 'l');
    SET str = REPLACE(str, 'Ļ', 'L');
    SET str = REPLACE(str, 'ļ', 'l');
    SET str = REPLACE(str, 'Ľ', 'L');
    SET str = REPLACE(str, 'ľ', 'l');
    SET str = REPLACE(str, 'Ŀ', 'L');
    SET str = REPLACE(str, 'ŀ', 'l');
    SET str = REPLACE(str, 'Ł', 'L');
    SET str = REPLACE(str, 'ł', 'l');
    SET str = REPLACE(str, 'Ń', 'N');
    SET str = REPLACE(str, 'ń', 'n');
    SET str = REPLACE(str, 'Ņ', 'N');
    SET str = REPLACE(str, 'ņ', 'n');
    SET str = REPLACE(str, 'Ň', 'N');
    SET str = REPLACE(str, 'ň', 'n');
    SET str = REPLACE(str, 'ʼn', 'n');
    SET str = REPLACE(str, 'Ŋ', 'N');
    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, 'Œ', 'OE');
    SET str = REPLACE(str, 'œ', 'oe');
    SET str = REPLACE(str, 'Ŕ', 'R');
    SET str = REPLACE(str, 'ŕ', 'r');
    SET str = REPLACE(str, 'Ŗ', 'R');
    SET str = REPLACE(str, 'ŗ', 'r');
    SET str = REPLACE(str, 'Ř', 'R');
    SET str = REPLACE(str, 'ř', 'r');
    SET str = REPLACE(str, 'Ś', 'S');
    SET str = REPLACE(str, 'ś', 's');
    SET str = REPLACE(str, 'Ŝ', 'S');
    SET str = REPLACE(str, 'ŝ', 's');
    SET str = REPLACE(str, 'Ş', 'S');
    SET str = REPLACE(str, 'ş', 's');
    SET str = REPLACE(str, 'Š', 'S');
    SET str = REPLACE(str, 'š', 's');
    SET str = REPLACE(str, 'Ţ', 'T');
    SET str = REPLACE(str, 'ţ', 't');
    SET str = REPLACE(str, 'Ť', 'T');
    SET str = REPLACE(str, 'ť', 't');
    SET str = REPLACE(str, 'Ŧ', 'T');
    SET str = REPLACE(str, 'ŧ', 't');
    SET str = REPLACE(str, 'Ũ', 'U');
    SET str = REPLACE(str, 'ũ', 'u');
    SET str = REPLACE(str, 'Ū', 'U');
    SET str = REPLACE(str, 'ū', 'u');
    SET str = REPLACE(str, 'Ŭ', 'U');
    SET str = REPLACE(str, 'ŭ', 'u');
    SET str = REPLACE(str, 'Ů', 'U');
    SET str = REPLACE(str, 'ů', 'u');
    SET str = REPLACE(str, 'Ű', 'U');
    SET str = REPLACE(str, 'ű', 'u');
    SET str = REPLACE(str, 'Ų', 'U');
    SET str = REPLACE(str, 'ų', 'u');
    SET str = REPLACE(str, 'Ŵ', 'W');
    SET str = REPLACE(str, 'ŵ', 'w');
    SET str = REPLACE(str, 'Ŷ', 'Y');
    SET str = REPLACE(str, 'ŷ', 'y');
    SET str = REPLACE(str, 'Ÿ', 'Y');
    SET str = REPLACE(str, 'Ź', 'Z');
    SET str = REPLACE(str, 'ź', 'z');
    SET str = REPLACE(str, 'Ż', 'Z');
    SET str = REPLACE(str, 'ż', 'z');
    SET str = REPLACE(str, 'Ž', 'Z');
    SET str = REPLACE(str, 'ž', 'z');
    SET str = REPLACE(str, 'ſ', 's');
    SET str = REPLACE(str, 'Ə', 'E');
    SET str = REPLACE(str, 'ǝ', 'e');
    SET str = REPLACE(str, 'Ș', 'S');
    SET str = REPLACE(str, 'ș', 's');
    SET str = REPLACE(str, 'Ț', 'T');
    SET str = REPLACE(str, 'ț', 't');
    SET str = REPLACE(str, '€', 'E');
    SET str = REPLACE(str, '£', '');
    SET str = REPLACE(str, 'Ơ', 'O');
    SET str = REPLACE(str, 'ơ', 'o');
    SET str = REPLACE(str, 'Ư', 'U');
    SET str = REPLACE(str, 'ư', 'u');
    SET str = REPLACE(str, 'Ầ', 'A');
    SET str = REPLACE(str, 'ầ', 'a');
    SET str = REPLACE(str, 'Ằ', 'A');
    SET str = REPLACE(str, 'ằ', 'a');
    SET str = REPLACE(str, 'Ề', 'E');
    SET str = REPLACE(str, 'ề', 'e');
    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, 'Ỳ', 'Y');
    SET str = REPLACE(str, 'ỳ', 'y');
    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, 'Ẻ', '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, 'Ỏ', '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, 'ỷ', 'y');
    SET str = REPLACE(str, 'Ẫ', 'A');
    SET str = REPLACE(str, 'ẫ', 'a');
    SET str = REPLACE(str, 'Ẵ', 'A');
    SET str = REPLACE(str, 'ẵ', 'a');
    SET str = REPLACE(str, 'Ẽ', 'E');
    SET str = REPLACE(str, 'ẽ', 'e');
    SET str = REPLACE(str, 'Ễ', 'E');
    SET str = REPLACE(str, 'ễ', 'e');
    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, 'Ỹ', 'Y');
    SET str = REPLACE(str, 'ỹ', 'y');
    SET str = REPLACE(str, 'Ấ', 'A');
    SET str = REPLACE(str, 'ấ', 'a');
    SET str = REPLACE(str, 'Ắ', 'A');
    SET str = REPLACE(str, 'ắ', 'a');
    SET str = REPLACE(str, 'Ế', 'E');
    SET str = REPLACE(str, 'ế', 'e');
    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, 'Ạ', '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, 'Ẹ', '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, 'Ọ', '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, 'ỵ', 'y');
    SET str = REPLACE(str, 'ɑ', 'a');
    SET str = REPLACE(str, 'Ǖ', 'U');
    SET str = REPLACE(str, 'ǖ', 'u');
    SET str = REPLACE(str, 'Ǘ', 'U');
    SET str = REPLACE(str, 'ǘ', 'u');
    SET str = REPLACE(str, 'Ǎ', 'A');
    SET str = REPLACE(str, 'ǎ', 'a');
    SET str = REPLACE(str, 'Ǐ', 'I');
    SET str = REPLACE(str, 'ǐ', 'i');
    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, 'Ǜ', 'U');
    SET str = REPLACE(str, 'ǜ', 'u');

    RETURN str;
END
//
DELIMITER ;

Here is an accent removal function based on WordPress's remove_accents().

The only differences are:

  • Changing "ß" to "ss".
  • Removing the more complicated replacement logic. Only the $chars array was ported over.
DELIMITER //
CREATE FUNCTION `remove_accents`(`str` TEXT)
    RETURNS TEXT
    DETERMINISTIC
BEGIN
    SET str = REPLACE(str, 'ª', 'a');
    SET str = REPLACE(str, 'º', 'o');
    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, 'Æ', 'AE');
    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, 'Ð', 'D');
    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, 'Ù', '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, 'Þ', 'TH');
    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, 'æ', 'ae');
    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, 'ð', 'd');
    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, 'þ', 'th');
    SET str = REPLACE(str, 'ÿ', 'y');
    SET str = REPLACE(str, 'Ø', 'O');
    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, 'ć', 'c');
    SET str = REPLACE(str, 'Ĉ', 'C');
    SET str = REPLACE(str, 'ĉ', 'c');
    SET str = REPLACE(str, 'Ċ', 'C');
    SET str = REPLACE(str, 'ċ', 'c');
    SET str = REPLACE(str, 'Č', 'C');
    SET str = REPLACE(str, 'č', 'c');
    SET str = REPLACE(str, 'Ď', 'D');
    SET str = REPLACE(str, 'ď', 'd');
    SET str = REPLACE(str, 'Đ', 'D');
    SET str = REPLACE(str, 'đ', 'd');
    SET str = REPLACE(str, 'Ē', 'E');
    SET str = REPLACE(str, 'ē', 'e');
    SET str = REPLACE(str, 'Ĕ', 'E');
    SET str = REPLACE(str, 'ĕ', 'e');
    SET str = REPLACE(str, 'Ė', 'E');
    SET str = REPLACE(str, 'ė', 'e');
    SET str = REPLACE(str, 'Ę', 'E');
    SET str = REPLACE(str, 'ę', 'e');
    SET str = REPLACE(str, 'Ě', 'E');
    SET str = REPLACE(str, 'ě', 'e');
    SET str = REPLACE(str, 'Ĝ', 'G');
    SET str = REPLACE(str, 'ĝ', 'g');
    SET str = REPLACE(str, 'Ğ', 'G');
    SET str = REPLACE(str, 'ğ', 'g');
    SET str = REPLACE(str, 'Ġ', 'G');
    SET str = REPLACE(str, 'ġ', 'g');
    SET str = REPLACE(str, 'Ģ', 'G');
    SET str = REPLACE(str, 'ģ', 'g');
    SET str = REPLACE(str, 'Ĥ', 'H');
    SET str = REPLACE(str, 'ĥ', 'h');
    SET str = REPLACE(str, 'Ħ', 'H');
    SET str = REPLACE(str, 'ħ', 'h');
    SET str = REPLACE(str, 'Ĩ', 'I');
    SET str = REPLACE(str, 'ĩ', 'i');
    SET str = REPLACE(str, 'Ī', 'I');
    SET str = REPLACE(str, 'ī', 'i');
    SET str = REPLACE(str, 'Ĭ', 'I');
    SET str = REPLACE(str, 'ĭ', 'i');
    SET str = REPLACE(str, 'Į', 'I');
    SET str = REPLACE(str, 'į', 'i');
    SET str = REPLACE(str, 'İ', 'I');
    SET str = REPLACE(str, 'ı', 'i');
    SET str = REPLACE(str, 'IJ', 'IJ');
    SET str = REPLACE(str, 'ij', 'ij');
    SET str = REPLACE(str, 'Ĵ', 'J');
    SET str = REPLACE(str, 'ĵ', 'j');
    SET str = REPLACE(str, 'Ķ', 'K');
    SET str = REPLACE(str, 'ķ', 'k');
    SET str = REPLACE(str, 'ĸ', 'k');
    SET str = REPLACE(str, 'Ĺ', 'L');
    SET str = REPLACE(str, 'ĺ', 'l');
    SET str = REPLACE(str, 'Ļ', 'L');
    SET str = REPLACE(str, 'ļ', 'l');
    SET str = REPLACE(str, 'Ľ', 'L');
    SET str = REPLACE(str, 'ľ', 'l');
    SET str = REPLACE(str, 'Ŀ', 'L');
    SET str = REPLACE(str, 'ŀ', 'l');
    SET str = REPLACE(str, 'Ł', 'L');
    SET str = REPLACE(str, 'ł', 'l');
    SET str = REPLACE(str, 'Ń', 'N');
    SET str = REPLACE(str, 'ń', 'n');
    SET str = REPLACE(str, 'Ņ', 'N');
    SET str = REPLACE(str, 'ņ', 'n');
    SET str = REPLACE(str, 'Ň', 'N');
    SET str = REPLACE(str, 'ň', 'n');
    SET str = REPLACE(str, 'ʼn', 'n');
    SET str = REPLACE(str, 'Ŋ', 'N');
    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, 'Œ', 'OE');
    SET str = REPLACE(str, 'œ', 'oe');
    SET str = REPLACE(str, 'Ŕ', 'R');
    SET str = REPLACE(str, 'ŕ', 'r');
    SET str = REPLACE(str, 'Ŗ', 'R');
    SET str = REPLACE(str, 'ŗ', 'r');
    SET str = REPLACE(str, 'Ř', 'R');
    SET str = REPLACE(str, 'ř', 'r');
    SET str = REPLACE(str, 'Ś', 'S');
    SET str = REPLACE(str, 'ś', 's');
    SET str = REPLACE(str, 'Ŝ', 'S');
    SET str = REPLACE(str, 'ŝ', 's');
    SET str = REPLACE(str, 'Ş', 'S');
    SET str = REPLACE(str, 'ş', 's');
    SET str = REPLACE(str, 'Š', 'S');
    SET str = REPLACE(str, 'š', 's');
    SET str = REPLACE(str, 'Ţ', 'T');
    SET str = REPLACE(str, 'ţ', 't');
    SET str = REPLACE(str, 'Ť', 'T');
    SET str = REPLACE(str, 'ť', 't');
    SET str = REPLACE(str, 'Ŧ', 'T');
    SET str = REPLACE(str, 'ŧ', 't');
    SET str = REPLACE(str, 'Ũ', 'U');
    SET str = REPLACE(str, 'ũ', 'u');
    SET str = REPLACE(str, 'Ū', 'U');
    SET str = REPLACE(str, 'ū', 'u');
    SET str = REPLACE(str, 'Ŭ', 'U');
    SET str = REPLACE(str, 'ŭ', 'u');
    SET str = REPLACE(str, 'Ů', 'U');
    SET str = REPLACE(str, 'ů', 'u');
    SET str = REPLACE(str, 'Ű', 'U');
    SET str = REPLACE(str, 'ű', 'u');
    SET str = REPLACE(str, 'Ų', 'U');
    SET str = REPLACE(str, 'ų', 'u');
    SET str = REPLACE(str, 'Ŵ', 'W');
    SET str = REPLACE(str, 'ŵ', 'w');
    SET str = REPLACE(str, 'Ŷ', 'Y');
    SET str = REPLACE(str, 'ŷ', 'y');
    SET str = REPLACE(str, 'Ÿ', 'Y');
    SET str = REPLACE(str, 'Ź', 'Z');
    SET str = REPLACE(str, 'ź', 'z');
    SET str = REPLACE(str, 'Ż', 'Z');
    SET str = REPLACE(str, 'ż', 'z');
    SET str = REPLACE(str, 'Ž', 'Z');
    SET str = REPLACE(str, 'ž', 'z');
    SET str = REPLACE(str, 'ſ', 's');
    SET str = REPLACE(str, 'Ə', 'E');
    SET str = REPLACE(str, 'ǝ', 'e');
    SET str = REPLACE(str, 'Ș', 'S');
    SET str = REPLACE(str, 'ș', 's');
    SET str = REPLACE(str, 'Ț', 'T');
    SET str = REPLACE(str, 'ț', 't');
    SET str = REPLACE(str, '€', 'E');
    SET str = REPLACE(str, '£', '');
    SET str = REPLACE(str, 'Ơ', 'O');
    SET str = REPLACE(str, 'ơ', 'o');
    SET str = REPLACE(str, 'Ư', 'U');
    SET str = REPLACE(str, 'ư', 'u');
    SET str = REPLACE(str, 'Ầ', 'A');
    SET str = REPLACE(str, 'ầ', 'a');
    SET str = REPLACE(str, 'Ằ', 'A');
    SET str = REPLACE(str, 'ằ', 'a');
    SET str = REPLACE(str, 'Ề', 'E');
    SET str = REPLACE(str, 'ề', 'e');
    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, 'Ỳ', 'Y');
    SET str = REPLACE(str, 'ỳ', 'y');
    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, 'Ẻ', '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, 'Ỏ', '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, 'ỷ', 'y');
    SET str = REPLACE(str, 'Ẫ', 'A');
    SET str = REPLACE(str, 'ẫ', 'a');
    SET str = REPLACE(str, 'Ẵ', 'A');
    SET str = REPLACE(str, 'ẵ', 'a');
    SET str = REPLACE(str, 'Ẽ', 'E');
    SET str = REPLACE(str, 'ẽ', 'e');
    SET str = REPLACE(str, 'Ễ', 'E');
    SET str = REPLACE(str, 'ễ', 'e');
    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, 'Ỹ', 'Y');
    SET str = REPLACE(str, 'ỹ', 'y');
    SET str = REPLACE(str, 'Ấ', 'A');
    SET str = REPLACE(str, 'ấ', 'a');
    SET str = REPLACE(str, 'Ắ', 'A');
    SET str = REPLACE(str, 'ắ', 'a');
    SET str = REPLACE(str, 'Ế', 'E');
    SET str = REPLACE(str, 'ế', 'e');
    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, 'Ạ', '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, 'Ẹ', '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, 'Ọ', '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, 'ỵ', 'y');
    SET str = REPLACE(str, 'ɑ', 'a');
    SET str = REPLACE(str, 'Ǖ', 'U');
    SET str = REPLACE(str, 'ǖ', 'u');
    SET str = REPLACE(str, 'Ǘ', 'U');
    SET str = REPLACE(str, 'ǘ', 'u');
    SET str = REPLACE(str, 'Ǎ', 'A');
    SET str = REPLACE(str, 'ǎ', 'a');
    SET str = REPLACE(str, 'Ǐ', 'I');
    SET str = REPLACE(str, 'ǐ', 'i');
    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, 'Ǜ', 'U');
    SET str = REPLACE(str, 'ǜ', 'u');

    RETURN str;
END
//
DELIMITER ;
回复 原文
浅浅淡淡 2024-10-21 15:55:44

这是我发现的最快、最有效的方法：

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');

This is the fastest and most efficient way I found:

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');
回复 原文
喵星人汪星人 2024-10-21 15:55:44

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

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

CONVERT(expr USING transcoding_name)

Check out the MySQL manual on CONVERT() and CAST(): http://dev.mysql.com/doc/refman/5.0/en/charset-convert.html

CONVERT() provides a way to convert data between different character sets. The syntax is:

CONVERT(expr USING transcoding_name)

回复 原文
~没有更多了~

关于作者

荒路情人

暂无简介

0 文章
0 评论
22 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

烙印

文章 0 评论 0

singlesman

文章 0 评论 0

给自己一个微笑

文章 0 评论 0

独孤求败

文章 0 评论 0

晨钟暮鼓

文章 0 评论 0

我是自愿种绣球花的

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文