php - 选择包含非 ASCII 字符的行

Question

问题是:我已经从mochimedia导入了大约20000个游戏描述到我的数据库中，但是有很多外国游戏，我不想列出。

我想出了这个查询来查找包含非 ASCII 字符的列

SELECT * FROM TABLE WHERE NOT HEX(COLUMN) REGEXP '^([0-7][0-9A-F])*$';

请注意，我在 stackoverflow 上找到了这个解决方案，因为我不是 mysql 查询方面的专家。

然而，虽然这个查询捕获了一些外国描述，但它似乎有时也会失败并找到非常好的描述，所以我正在寻找的是微调这个查询以跳过“好的”描述。

以下是一些“正常”的返回行，这意味着不应返回它们:

Game Boy Jam game that uses game boy restrictions. It’s a western platform game, where you play as a sheriff of the town. Your mission is to capture all the bad bandits in the land and bring them to justice.

还有一个

It's hard to be a kitten if you have such a clumsy owner! Yesterday she lost a lot of things in the park and now it's up to you to find them!

Memories of that day can be helpful – you should remember where have you seen that thing last and search there.Map also can be usefull for your task. And finally you can climb up a tree and ask a big cat for a hint – you will see all the events of that day again.

But sometimes it's not enough to just find a lost thing. Some residents of the park may already be using it for themselves – be it mice or ants. In that case you may have to bring them something in exchange for a lost thing – only then you will get it back.

还有最后一个例子

Hungry honey bee is a unique fun game. It includes the fun of a platform game, puzzle game, adventure game, role playing game. In this fantasy game, one needs to make honey bee to collect all the flowers in order to win a match. As level progresses new challenges will be introduced with gradually toughness. Overall it’s a complete blend of fun which makes one stick with the game for hours. GOI: Rating 4.5 our of 5

请记住，我不是 mysql 专家，所以我只能猜测是什么问题，我的猜测是某些字符，如

' in It's or the characters – 和 :

可能会导致这种情况。

也许有人愿意分享一个优化查询来解决这个问题？我花了一些时间来解决这个问题，但考虑到我仍然是 php 的新手，绝对不是 REGEXP 和 mysql 查询的专家，如果能在这里得到一些帮助，这样我就可以提高我的知识，那就太好了。请不要假设只要你把它扔给我，我就会理解你说的任何东西，所以详细的帮助会很棒。

感谢您花时间阅读本文。

Answer 1

如果您只是想查找包含非 ASCII 字符的列，您可以使用以下查询:

SELECT * 
FROM table 
WHERE column != CONVERT(column USING ASCII);