如何删除 MySQL 中的重音符号？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4813620/
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
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-31 18:31:58 来源:igfitidea点击:
How to remove accents in MySQL?
提问by mpen
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.
我刚刚编译了一个包含 100 万个地名的数据库。我将在自动完成小部件中使用它来查找城市。很多这些地方都有重音......我希望能够在用户键入不带重音的名称时找到记录。
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?
为了做到这一点，我有一个第二列，其中包含名称的无重音副本。许多这些记录仍然是空白的，所以我想写一个查询来填充它们。这在直接的 MySQL 中可能吗？如果是这样，如何？
回答by Ignacio Vazquez-Abrams
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)
回答by igasparetto
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:
我遇到了同样的问题，所以我根据 PHP 脚本编写了一个查询列表，我必须删除重音并制作对 SEO 友好的 URL：
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,'D','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','Y');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'T','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','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,'e','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','y');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'y','y');
UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'t','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);
回答by Abdel
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,'D','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','Y');
SET str = REPLACE(str,'T','B');
SET str = REPLACE(str,'?','Ss');
SET str = REPLACE(str,'à','a');
SET str = REPLACE(str,'á','a');
SET str = REPLACE(str,'a','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,'e','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','y');
SET str = REPLACE(str,'y','y');
SET str = REPLACE(str,'t','b');
SET str = REPLACE(str,'?','y');
SET str = REPLACE(str,'?','f');
RETURN str;
END
//
DELIMITER ;
回答by user3245067
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 = '????àá??????èéê?ìí???òó????ùú?üY?Tàáa?????èéê?ìí???òó????ùú?üy?t?';
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 = '!@#$%¨&*()_+=§123￡￠?"`′{[^~}]<,>.:;?/°oa+*|\''';
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
|
回答by Erwan
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,'D','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','Y'),
alias = replace(alias,'?','s'),
alias = replace(alias,'D','Dj')
alias = replace(alias,'?','z'),
alias = replace(alias,'T','B'),
alias = replace(alias,'?','Ss'),
alias = replace(alias,'à','a'),
alias = replace(alias,'á','a'),
alias = replace(alias,'a','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,'e','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','y'),
alias = replace(alias,'y','y'),
alias = replace(alias,'t','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
- 'my_table' 是表的名称，
- 'name' 是原始字段
- “别名”是新字段
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?
如果您尝试使用 mysql 生成 slug 字段，您也可以检查此变体： 从名称列生成 slug 名称列的简单方法？
回答by jfx
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.
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 = '????àá??????èéê?ìí???òó????ùú?üY?Tàáa?????èéê?ìí???òó????ùú?üy?t?';
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_#$%¨&*()_+=§123￡￠?"`′{[^~}]<,>.:;?/°oa+*|\''';
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 ;
回答by Rick James
I wonder if MariaDB's REGEXP_REPLACE(col, 'e', 'e')with utf8_unicode_ci would do all the es at once.
我想知道REGEXP_REPLACE(col, 'e', 'e')带有 utf8_unicode_ci 的MariaDB是否会e一次完成所有的操作。
回答by Piskvor left the building
Check out the MySQL manual on CONVERT()and CAST(): http://dev.mysql.com/doc/refman/5.0/en/charset-convert.html
查看 MySQL 手册CONVERT()和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)
CONVERT() 提供了一种在不同字符集之间转换数据的方法。语法是：
CONVERT(expr USING transcoding_name)
