代码之家 › 专栏 › 技术社区 › Rudie

如何在不保存的情况下将列转换为ASCII,以检查是否与外部ASCII字符串匹配?

latin1 casting ascii mysql

Rudie · 技术社区 · 15 年前

我有一个成员搜索功能,您可以在其中给出部分名称,返回的应该是所有成员,其中至少有一个用户名、名字或姓氏与输入匹配。这里的问题是有些名字有“奇怪”的字符,比如 Ã© 在里面 RenÃ©e 用户不想输入奇怪的字符,只想输入普通的ASCII替换字符 e .

在PHP中,我使用iconv将输入字符串转换为ASCII(以防有人键入奇怪的字符)。不过,在数据库中,我还应该将奇怪的字符转换为ASCII(显然),以便字符串匹配。

我尝试了以下方法:

SELECT
  CONVERT(_latin1'RenÃ©e' USING ascii) t1, 
  CAST(_latin1'RenÃ©e' AS CHAR CHARACTER SET ASCII) t2;

(试了两次)都不行。都有 Ren?e 作为输出。问号应该是 e . 如果它输出的话没关系 Ren?ee 因为我可以在转换后删除所有问号。

正如您可以想象的那样,我要查询的列是用Latin1编码的。

谢谢。

4 回复 | 直到 10 年前

Vince Bowdren 15 年前

collation

mysql> SET NAMES 'utf8' COLLATE 'utf8_general_ci';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT 'a' = 'A', 'a' = 'Ã', 'a' = 'Ã¡';
+-----------+-----------+-----------+
| 'a' = 'A' | 'a' = 'Ã' | 'a' = 'Ã¡' |
+-----------+-----------+-----------+
|         1 |         1 |         1 |
+-----------+-----------+-----------+
1 row in set (0.06 sec)

Community Mohan Dere 9 年前

SELECT * FROM `test` WHERE `name` COLLATE utf8_general_ci LIKE '%renee%';

test

+-----+--------+
| id  | name   |
+-----+--------+
|  1  | RenÃ©e  |
|  2  | RenÃªe  |
|  3  | Renee  |
+-----+--------+

PHP Transliteration

username_slug firstname_slug lastname_slug

+------+----------+---------------+----------+---------------+ ...
| id   | username | username_slug | lastname | lastname_slug | ...
+------+----------+---------------+----------+---------------+ ...
|    1 | RenÃ©e    |    renee      | La NiÃ±a  | la-nina       | ...
|    2 | RenÃªe    |    renee      | ...      | ...           | ...
|    3 | Renee    |    renee      | ...      | ...           | ...
+------+----------+---------------+----------+---------------+ ...

..._slug

Danosaure 15 年前

CREATE TABLE `members` (
  `id` int(11) DEFAULT NULL,
  `lastname` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL
);
insert into members values (1, 'test6Ã«');
select id from members where lastname like 'test6e%';

+------+
| id   |
+------+
|    1 |
+------+

set names latin1;
CREATE TABLE `members2` (
  `id` int(11) DEFAULT NULL,
  `lastname` varchar(20) CHARACTER SET latin1 DEFAULT NULL
);
insert into members2 values (1, 'RenÃ©e');
select id from members2 where lastname like '%Renee%';

set names latin1;
select id from members where lastname like 'test6Ã«%';

set names utf8;
insert into members values (5, 'RenÃ©e'), (6, 'RenÃªe'), (7, 'RenÃ¨e');
select members.id, members.lastname, members2.id, members2.lastname
from members inner join members2 using (lastname);

members members2

+------+----------+------+----------+
| id   | lastname | id   | lastname |
+------+----------+------+----------+
|    5 | RenÃ©e    |    1 | RenÃ©e    |
|    6 | RenÃªe    |    1 | RenÃ©e    |
|    7 | RenÃ¨e    |    1 | RenÃ©e    |
+------+----------+------+----------+

Community Mohan Dere 9 年前

CAST() normalizing accented characters in MySQL queries