在 MySQL 中检测 utf8 损坏的字符

Question

提问by The Disintegrator

I've got a database with a bunch of broken utf8 characters scattered across several tables. The list of characters isn't very extensive AFAIK (áéíúóáéíóú??)

我有一个数据库，其中有一堆散落在多个表中的损坏的 utf8 字符。字符列表不是很广泛 AFAIK (áéíúóáéíóú??)

Fixing a given table is very straightforward

修复给定的表非常简单

update orderItem set itemName=replace(itemName,'??','á');

But I can't get a way of detecting the broken characters. If I do something like

但是我无法找到检测损坏字符的方法。如果我做类似的事情

SELECT * FROM TABLE WHERE field LIKE "%?%";

I get nearly all the fields because of the collation (?=a). All broken characters so far start with an "?". The database is in spanish so this particular character isn't used

由于整理（？= a），我得到了几乎所有的字段。到目前为止，所有损坏的字符都以“？”开头。数据库是西班牙语的，所以不使用这个特定的字符

The list of broken chars I've got so far is

到目前为止我得到的损坏字符列表是

?? = á
?? = é
?-- = í
?3 = ó
?± = ?
?? = á

Any idea of how to make this SELECT to work as intended? (a binary search or something like that)

知道如何使这个 SELECT 按预期工作吗？（二进制搜索或类似的东西）

Answer 1

采纳答案by wds

How about a different approach, namely converting the column back and forth to get the correct character set? You can convert it to binary, then to utf-8 and then to iso-8859-1 or whatever else you're using. See the manualfor the details.

一种不同的方法，即来回转换列以获得正确的字符集怎么样？您可以将其转换为二进制，然后转换为 utf-8，然后转换为 iso-8859-1 或您正在使用的任何其他格式。有关详细信息，请参阅手册。

Answer 2

回答by Thales Ceolin

I fixed with

我用

UPDATE wp_zcs9ck_posts_copy SET post_title = 
    CONVERT(BINARY CONVERT(post_title USING latin1) USING utf8);

Complete solution: http://jonisalonen.com/2012/fixing-doubly-utf-8-encoded-text-in-mysql/

完整解决方案：http: //jonisalonen.com/2012/fixing-doubly-utf-8-encoded-text-in-mysql/

Answer 3

回答by Raúl Avila Solano

UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'??','á');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'?¤','?');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'??','é');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'í?','é');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'?3','ó');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'ío','ú');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'?o','ú');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'?±','?');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'í‘','?');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'?','í');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'a“','–');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'a?','\'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'a|','...');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'a“','-');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'a?','"');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'a','"');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'a?','\'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'a￠','-');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'a?','c');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'?','');

Answer 4

回答by acseven

Thanks for your answers!!

谢谢你的回答！！

I fixed my tables with this, and wanted to share the full list of changes. Note that it also includes fixing html decoded characters, besides latin ones, it was really a mess:

我用这个修复了我的表格，并想分享完整的更改列表。请注意，它还包括修复 html 解码的字符，除了拉丁字符之外，它真的是一团糟：

(If you need more conversions, look them up at https://www.utf8-chartable.de/unicode-utf8-table.pl)

（如果您需要更多转换，请在https://www.utf8-chartable.de/unicode-utf8-table.pl 中查找）

update `table` set `field` = replace(`field`, 'a', '"');
update `table` set `field` = replace(`field`, 'a“', '–');
update `table` set `field` = replace(`field`, 'a￠', '-');
update `table` set `field` = replace(`field`, 'a?', '"');

update `table` set `field` = replace(`field`, '??', '?');
update `table` set `field` = replace(`field`, '?￠', '￠');
update `table` set `field` = replace(`field`, '?￡', '￡');
update `table` set `field` = replace(`field`, '?¤', '¤');
update `table` set `field` = replace(`field`, '?￥', '￥');
update `table` set `field` = replace(`field`, '?|', '|');
update `table` set `field` = replace(`field`, '?§', '§');
update `table` set `field` = replace(`field`, '?¨', '¨');
update `table` set `field` = replace(`field`, '??', '?');
update `table` set `field` = replace(`field`, '?a', 'a');
update `table` set `field` = replace(`field`, '??', '?');
update `table` set `field` = replace(`field`, '??', '?');
# This one looks like it's missing a character, but it's there. 0xad
update `table` set `field` = replace(`field`, '?-', '-');
update `table` set `field` = replace(`field`, '??', '?');
update `table` set `field` = replace(`field`, '?ˉ', 'ˉ');
update `table` set `field` = replace(`field`, '?°', '°');
update `table` set `field` = replace(`field`, '?±', '±');
update `table` set `field` = replace(`field`, '?2', '2');
update `table` set `field` = replace(`field`, '?3', '3');
update `table` set `field` = replace(`field`, '?′', '′');
update `table` set `field` = replace(`field`, '?μ', 'μ');
update `table` set `field` = replace(`field`, '??', '?');
update `table` set `field` = replace(`field`, '?·', '·');
update `table` set `field` = replace(`field`, '??', '?');
update `table` set `field` = replace(`field`, '?1', '1');
update `table` set `field` = replace(`field`, '?o', 'o');
update `table` set `field` = replace(`field`, '??', '?');
update `table` set `field` = replace(`field`, '??', '?');
update `table` set `field` = replace(`field`, '??', '?');
update `table` set `field` = replace(`field`, '??', '?');
update `table` set `field` = replace(`field`, '??', '?');

update `table` set `field` = replace(`field`, '?', 'à');
# This one looks like it's missing a character, but it's there. 0x81
update `table` set `field` = replace(`field`, '?', 'á');
update `table` set `field` = replace(`field`, '??', '?');
update `table` set `field` = replace(`field`, '??', '?');
update `table` set `field` = replace(`field`, '??', '?');
update `table` set `field` = replace(`field`, '?…', '?');
update `table` set `field` = replace(`field`, '??', '?');
update `table` set `field` = replace(`field`, '??', '?');
update `table` set `field` = replace(`field`, '??', 'è');
update `table` set `field` = replace(`field`, '?‰', 'é');
update `table` set `field` = replace(`field`, '??', 'ê');
update `table` set `field` = replace(`field`, '??', '?');
update `table` set `field` = replace(`field`, '??', 'ì');
# This one looks like it's missing a character, but it's there. 0x8d
update `table` set `field` = replace(`field`, '?', 'í');
update `table` set `field` = replace(`field`, '??', '?');
# This one looks like it's missing a character, but it's there. 0x8f
update `table` set `field` = replace(`field`, '?', '?');
# This one looks like it's missing a character, but it's there. 0x90
update `table` set `field` = replace(`field`, '?', 'D');
update `table` set `field` = replace(`field`, '?‘', '?');
update `table` set `field` = replace(`field`, '?'', 'ò');
update `table` set `field` = replace(`field`, '?“', 'ó');
update `table` set `field` = replace(`field`, '?”', '?');
update `table` set `field` = replace(`field`, '??', '?');
update `table` set `field` = replace(`field`, '?–', '?');
update `table` set `field` = replace(`field`, '?—', '×');
update `table` set `field` = replace(`field`, '??', '?');
update `table` set `field` = replace(`field`, '??', 'ù');
update `table` set `field` = replace(`field`, '??', 'ú');
update `table` set `field` = replace(`field`, '??', '?');
update `table` set `field` = replace(`field`, '??', 'ü');
# This one looks like it's missing a character, but it's there. 0x9d
update `table` set `field` = replace(`field`, '?', 'Y');
update `table` set `field` = replace(`field`, '??', 'T');
update `table` set `field` = replace(`field`, '??', '?');
update `table` set `field` = replace(`field`, '??', 'à');
update `table` set `field` = replace(`field`, '??', 'á');
update `table` set `field` = replace(`field`, '?￠', 'a');
update `table` set `field` = replace(`field`, '?￡', '?');
update `table` set `field` = replace(`field`, '?¤', '?');
update `table` set `field` = replace(`field`, '?￥', '?');
update `table` set `field` = replace(`field`, '?|', '?');
update `table` set `field` = replace(`field`, '?§', '?');
update `table` set `field` = replace(`field`, '?¨', 'è');
update `table` set `field` = replace(`field`, '??', 'é');
update `table` set `field` = replace(`field`, '?a', 'ê');
update `table` set `field` = replace(`field`, '??', '?');
update `table` set `field` = replace(`field`, '??', 'ì');
# This one looks like it's missing a character, but it's there. 0xad
update `table` set `field` = replace(`field`, '--?', 'í');
update `table` set `field` = replace(`field`, '??', '?');
update `table` set `field` = replace(`field`, '?ˉ', '?');
update `table` set `field` = replace(`field`, '?°', 'e');
update `table` set `field` = replace(`field`, '?±', '?');
update `table` set `field` = replace(`field`, '?2', 'ò');
update `table` set `field` = replace(`field`, '?3', 'ó');
update `table` set `field` = replace(`field`, '?′', '?');
update `table` set `field` = replace(`field`, '?μ', '?');
update `table` set `field` = replace(`field`, '??', '?');
update `table` set `field` = replace(`field`, '?·', '÷');
update `table` set `field` = replace(`field`, '??', '?');
update `table` set `field` = replace(`field`, '?1', 'ù');
update `table` set `field` = replace(`field`, '?o', 'ú');
update `table` set `field` = replace(`field`, '??', '?');
update `table` set `field` = replace(`field`, '??', 'ü');
update `table` set `field` = replace(`field`, '??', 'y');
update `table` set `field` = replace(`field`, '??', 't');
update `table` set `field` = replace(`field`, '??', '?');

update `table` set `field` = replace(`field` ,'&ccedil;','?');
update `table` set `field` = replace(`field` ,'&atilde;','?');
update `table` set `field` = replace(`field` ,'&aacute;','á');
update `table` set `field` = replace(`field` ,'&acirc;','a');
update `table` set `field` = replace(`field` ,'&eacute;','é');
update `table` set `field` = replace(`field` ,'&iacute;','í');
update `table` set `field` = replace(`field` ,'&otilde;','?');
update `table` set `field` = replace(`field` ,'&uacute;','ú');
update `table` set `field` = replace(`field` ,'&ccedil;','?');
update `table` set `field` = replace(`field` ,'&Aacute;','á');
update `table` set `field` = replace(`field` ,'&Acirc;','?');
update `table` set `field` = replace(`field` ,'&Eacute;','é');
update `table` set `field` = replace(`field` ,'&Iacute;','í');
update `table` set `field` = replace(`field` ,'&Otilde;','?');
update `table` set `field` = replace(`field` ,'&Uacute;','ú');
update `table` set `field` = replace(`field` ,'&Ccedil;','?');
update `table` set `field` = replace(`field` ,'&Atilde;','?');
update `table` set `field` = replace(`field` ,'&Agrave;','à');
update `table` set `field` = replace(`field` ,'&Ecirc;','ê');
update `table` set `field` = replace(`field` ,'&Oacute;','ó');
update `table` set `field` = replace(`field` ,'&Ocirc;','?');
update `table` set `field` = replace(`field` ,'&Uuml;','ü');
update `table` set `field` = replace(`field` ,'&atilde;','?');
update `table` set `field` = replace(`field` ,'&agrave;','à');
update `table` set `field` = replace(`field` ,'&ecirc;','ê');
update `table` set `field` = replace(`field` ,'&oacute;','ó');
update `table` set `field` = replace(`field` ,'&ocirc;','?');
update `table` set `field` = replace(`field` ,'&uuml;','ü');
update `table` set `field` = replace(`field` ,'&amp;','&');
update `table` set `field` = replace(`field` ,'&gt;','>');
update `table` set `field` = replace(`field` ,'&lt;','<');
update `table` set `field` = replace(`field` ,'&circ;','?');
update `table` set `field` = replace(`field` ,'&tilde;','?');
update `table` set `field` = replace(`field` ,'&uml;','¨');
update `table` set `field` = replace(`field` ,'&cute;','′');
update `table` set `field` = replace(`field` ,'&cedil;','?');
update `table` set `field` = replace(`field` ,'&quot;','"');
update `table` set `field` = replace(`field` ,'&ldquo;','“');
update `table` set `field` = replace(`field` ,'&rdquo;','”');
update `table` set `field` = replace(`field` ,'&lsquo;','‘');
update `table` set `field` = replace(`field` ,'&rsquo;',''');
update `table` set `field` = replace(`field` ,'&lsaquo;','?');
update `table` set `field` = replace(`field` ,'&rsaquo;','?');
update `table` set `field` = replace(`field` ,'&laquo;','?');
update `table` set `field` = replace(`field` ,'&raquo;','?');
update `table` set `field` = replace(`field` ,'&ordm;','o');
update `table` set `field` = replace(`field` ,'&ordf;','a');
update `table` set `field` = replace(`field` ,'&ndash;','–');
update `table` set `field` = replace(`field` ,'&mdash;','—');
update `table` set `field` = replace(`field` ,'&macr;','ˉ');
update `table` set `field` = replace(`field` ,'&hellip;','…');
update `table` set `field` = replace(`field` ,'&brvbar;','|');
update `table` set `field` = replace(`field` ,'&bull;','?');
update `table` set `field` = replace(`field` ,'&para;','?');
update `table` set `field` = replace(`field` ,'&sect;','§');
update `table` set `field` = replace(`field` ,'&sup1;','1');
update `table` set `field` = replace(`field` ,'&sup2;','2');
update `table` set `field` = replace(`field` ,'&sup3;','3');
update `table` set `field` = replace(`field` ,'&frac12;','?');
update `table` set `field` = replace(`field` ,'&frac14;','?');
update `table` set `field` = replace(`field` ,'&frac34;','?');
update `table` set `field` = replace(`field` ,'&#8539;','?');
update `table` set `field` = replace(`field` ,'&#8540;','?');
update `table` set `field` = replace(`field` ,'&#8541;','?');
update `table` set `field` = replace(`field` ,'&#8542;','?');
update `table` set `field` = replace(`field` ,'&gt;','>');
update `table` set `field` = replace(`field` ,'&lt;','<');
update `table` set `field` = replace(`field` ,'&plusmn;','±');
update `table` set `field` = replace(`field` ,'&minus;','?');
update `table` set `field` = replace(`field` ,'&times;','×');
update `table` set `field` = replace(`field` ,'&divide;','÷');
update `table` set `field` = replace(`field` ,'&lowast;','?');
update `table` set `field` = replace(`field` ,'&frasl;','?');
update `table` set `field` = replace(`field` ,'&permil;','‰');
update `table` set `field` = replace(`field` ,'&int;','∫');
update `table` set `field` = replace(`field` ,'&sum;','∑');
update `table` set `field` = replace(`field` ,'&prod;','∏');
update `table` set `field` = replace(`field` ,'&radic;','√');
update `table` set `field` = replace(`field` ,'&infin;','∞');
update `table` set `field` = replace(`field` ,'&asymp;','≈');
update `table` set `field` = replace(`field` ,'&cong;','?');
update `table` set `field` = replace(`field` ,'&prop;','∝');
update `table` set `field` = replace(`field` ,'&equiv;','≡');
update `table` set `field` = replace(`field` ,'&ne;','≠');
update `table` set `field` = replace(`field` ,'&le;','≤');
update `table` set `field` = replace(`field` ,'&ge;','≥');
update `table` set `field` = replace(`field` ,'&there4;','∴');
update `table` set `field` = replace(`field` ,'&sdot;','?');
update `table` set `field` = replace(`field` ,'&middot;','·');
update `table` set `field` = replace(`field` ,'&part;','?');
update `table` set `field` = replace(`field` ,'&image;','?');
update `table` set `field` = replace(`field` ,'&real;','?');
update `table` set `field` = replace(`field` ,'&prime;','′');
update `table` set `field` = replace(`field` ,'&Prime;','″');
update `table` set `field` = replace(`field` ,'&deg;','°');
update `table` set `field` = replace(`field` ,'&ang;','∠');
update `table` set `field` = replace(`field` ,'&perp;','⊥');
update `table` set `field` = replace(`field` ,'&nabla;','?');
update `table` set `field` = replace(`field` ,'&oplus;','⊕');
update `table` set `field` = replace(`field` ,'&otimes;','?');
update `table` set `field` = replace(`field` ,'&alefsym;','?');
update `table` set `field` = replace(`field` ,'&oslash;','?');
update `table` set `field` = replace(`field` ,'&Oslash;','?');
update `table` set `field` = replace(`field` ,'&isin;','∈');
update `table` set `field` = replace(`field` ,'&notin;','?');
update `table` set `field` = replace(`field` ,'&cap;','∩');
update `table` set `field` = replace(`field` ,'&cup;','∪');
update `table` set `field` = replace(`field` ,'&sub;','?');
update `table` set `field` = replace(`field` ,'&sup;','?');
update `table` set `field` = replace(`field` ,'&sube;','?');
update `table` set `field` = replace(`field` ,'&supe;','?');
update `table` set `field` = replace(`field` ,'&exist;','?');
update `table` set `field` = replace(`field` ,'&forall;','?');
update `table` set `field` = replace(`field` ,'&empty;','?');
update `table` set `field` = replace(`field` ,'&not;','?');
update `table` set `field` = replace(`field` ,'&and;','∧');
update `table` set `field` = replace(`field` ,'&or;','∨');
update `table` set `field` = replace(`field` ,'&crarr;','?');

Answer 5

回答by David

No text replacement is a universal solutions because you can forget some character. A more suitable fix for double converted charactersis:

无文本替换是一种通用的解决方案，因为您可能会忘记某些字符。更适合双转换字符的修复方法是：

convert back to latin1
convert to binary
convert to utf8

转换回latin1
转换为二进制
转换为utf8

Like this:

像这样：

alter table descriptions modify name VARCHAR(2000) character set latin1;
alter table descriptions modify name blob;
alter table descriptions modify name VARCHAR(2000) character set utf8;

Answer 6

回答by Adam Lynch

The SELECTstatement you need is the following:

SELECT您需要的声明如下：

SELECT * FROM TABLE WHERE LENGTH(name) != CHAR_LENGTH(name);

This returns all rows which contain multi-byte characters.

这将返回包含多字节字符的所有行。

nameis assumed to be a field / the field where weird characters would be found. *

name假设是一个字段/会发现奇怪字符的字段。*

Answer 7

回答by Pablo S G Pacheco

This saved my life

这救了我的命

UPDATE ohp_posts SET post_content = CONVERT(CAST(CONVERT(post_content USING latin1) AS BINARY) USING utf8)

I've found it here http://stanis.net/2014/04/replacing-latin-1-with-utf-8-characters-in-mysql/

我在这里找到它http://stanis.net/2014/04/replacing-latin-1-with-utf-8-characters-in-mysql/

Answer 8

回答by Bob Davies

I had this same problem but didn't like the replace() solution because there's always the possibility of missing some characters. I was working against a column with mixed data (some had been utf8_encode()d and some not) with 4 million or so rows, about 250k records with mis-encoded data (with ?‰/etc characters), covering about 15 international languages, including mainly European languages but also Russian, Japanese and Chinese.

我有同样的问题，但不喜欢 replace() 解决方案，因为总是有可能丢失一些字符。我正在处理一个包含混合数据的列（有些是 utf8_encode()d，有些不是），大约有 400 万行，大约 25 万条记录有错误编码的数据（带有 ?‰/etc 字符），涵盖了大约 15 种国际语言，主要包括欧洲语言，但也包括俄语、日语和中文。

I started by duplicating the column, since I didn't want to lose any data:

我从复制列开始，因为我不想丢失任何数据：

ALTER TABLE images ADD COLUMN reptitle TEXT;

Copied all the data with multibyte characters (thanks Adam for the tip)

用多字节字符复制所有数据（感谢 Adam 的提示）

UPDATE images SET reptitle = title WHERE LENGTH(title) != CHAR_LENGTH(title)

Since reptitle was created with the table's default character set it was already utf8, but contained the corrupted data since images table used to be a latin source. Column reptitle now contains some data which is correctly encoded, and some corrupted (all values with multibyte characters, some had been correctly utf8_encode()d. So then with David's tip...

由于 reptitle 是使用表的默认字符集创建的，它已经是 utf8，但包含损坏的数据，因为图像表曾经是拉丁源。列 reptitle 现在包含一些正确编码的数据和一些已损坏的数据（所有值都带有多字节字符，有些已正确 utf8_encode()d。那么大卫的提示...

ALTER TABLE images MODIFY reptitle TEXT character set latin1;
ALTER TABLE images MODIFY reptitle BLOB;
ALTER TABLE images MODIFY reptitle TEXT character set utf8;

The middle step may not have been necessary since TEXT and BLOB (I think) are the same. This had the effect of correcting all incorrectly encoded data ('??tudiantes' became 'étudiantes', etc) but data which was previously correct was truncated at the first multibyte character ('Lapin de Paques' became 'Lapin de P'). I don't know why the truncation, but it's in a disposable column so I didn't care. The truncated data gives CHAR_LENGTH and LENGTH of the same values because there are no multi-byte characters remaining so easy query...

中间步骤可能没有必要，因为 TEXT 和 BLOB（我认为）是相同的。这会纠正所有错误编码的数据（'??tudiantes' 变成 'étudiantes' 等），但以前正确的数据在第一个多字节字符处被截断（'Lapin de Paques' 变成了 'Lapin de P'）。我不知道为什么要截断，但它在一次性列中，所以我不在乎。截断的数据给出了相同值的 CHAR_LENGTH 和 LENGTH，因为没有多字节字符剩余，所以很容易查询......

UPDATE images SET title = reptitle WHERE LENGTH(reptitle)!=CHAR_LENGTH(reptitle)

Then of course just drop the spare column

然后当然只是删除备用列

ALTER TABLE images DROP COLUMN reptitle

Also make sure (since I use PHP and this had tripped me up a couple of times so I thought I'd mention it here) all your script files are UTF8 (without BOM) and you are using:

还要确保（因为我使用 PHP 并且这让我绊倒了几次所以我想我会在这里提到它）你所有的脚本文件都是 UTF8（没有 BOM）并且你正在使用：

mysql_set_charset('utf8', $connection);

Et voilà... perfectly repaired data, all languages :)

等等……完美修复的数据，所有语言:)

Answer 9

回答by Alberto

this also solved my problem on some italian chars

这也解决了我在一些意大利字符上的问题

UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'??','á');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'?¤','?');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'??','é');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'í?','é');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'?3','ó');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'ío','ú');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'?o','ú');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'?±','?');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'í‘','?');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'?','í');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'a“','–');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'a?','\'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'a|','...');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'a“','-');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'a?','"');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'a','"');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'a?','\'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'a￠','-');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'a?','c');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'?','');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'í?','à');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'í¨','è');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'í?','è');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'a??','');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'eì','è');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'í2','ò');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'í1','ù');

Answer 10

回答by Martin Kouba

You might have rows with properly encoded UTF8 and with wrongly encoded characters. In this case "CONVERT(BINARY CONVERT(post_title USING latin1) USING utf8)" will trim some fields.

您可能有正确编码的 UTF8 行和错误编码的字符。在这种情况下，“CONVERT(BINARY CONVERT(post_title USING latin1) USING utf8)”将修剪一些字段。

I ended up doing it this way

我最终这样做了

update `table` set `name` = replace(`name` ,CONVERT(BINARY "?" USING latin1),'?');
update `table` set `name` = replace(`name` ,CONVERT(BINARY "?" USING latin1),'?');
update `table` set `name` = replace(`name` ,CONVERT(BINARY "ü" USING latin1),'ü');
update `table` set `name` = replace(`name` ,CONVERT(BINARY "?" USING latin1),'?');
update `table` set `name` = replace(`name` ,CONVERT(BINARY "?" USING latin1),'?');
update `table` set `name` = replace(`name` ,CONVERT(BINARY "ü" USING latin1),'ü');
update `table` set `name` = replace(`name` ,CONVERT(BINARY "?" USING latin1),'?');

在 MySQL 中检测 utf8 损坏的字符

提问by The Disintegrator

采纳答案by wds

回答by Thales Ceolin

回答by Raúl Avila Solano

回答by acseven

回答by David

回答by Adam Lynch

回答by Pablo S G Pacheco

回答by Bob Davies

回答by Alberto

回答by Martin Kouba

相关推荐

最近更新

标签

在 MySQL 中检测 utf8 损坏的字符

提问by The Disintegrator

采纳答案by wds

回答by Thales Ceolin

回答by Raúl Avila Solano

回答by acseven

回答by David

回答by Adam Lynch

回答by Pablo S G Pacheco

回答by Bob Davies

回答by Alberto

回答by Martin Kouba

相关推荐

MySQL 尽管以 root 用户身份登录，phpMyAdmin 表示没有创建数据库的权限

MySQL 如何管理 Rails database.yml

如何将 csv 文件导入 MySQL 工作台？

MySQL 如何选择一个空的结果集？

相关推荐

最近更新

标签