在MySQL中检测utf8损坏的字符 [英] Detecting utf8 broken characters in MySQL

问题描述

我有一个数据库，其中有一堆破碎的utf8字符散布在几个表上. 字符列表不是很广泛的AFAIK(áéíúóÁÉÍÓÓÚÑñ)

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 (áéíúóÁÉÍÓÚÑñ)

修复给定表非常简单

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 "%Ã%";

由于排序规则(Ã= a)，我几乎获得了所有字段.到目前为止，所有损坏的字符均以Ã"开头.数据库是西班牙语，因此不使用此特殊字符

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

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

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

á = á
é = é
í- = í
ó = ó
ñ = ñ
á = Á

关于如何使此SELECT正常工作的任何想法? (二进制搜索或类似的内容)

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

推荐答案

如何处理另一种方法，即来回转换列以获取正确的字符集?您可以将其转换为二进制，然后转换为utf-8，然后转换为iso-8859-1或您使用的其他任何格式.有关详细信息，请参见手册.

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 manual for the details.

这篇关于在MySQL中检测utf8损坏的字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！