在where子句中使用类似％..％的mysql查询返回不同的结果 [英] mysql query with like %..% in the where clause returning different results

问题描述

我遇到以下问题(使用mysql 5.0.70).

I have the following problem (using mysql 5.0.70).

在一个表中，我有一个varchar字段，其中包含某种数字，例如:

In one table I have a varchar field containing some kind of a number, like:

"0303A342", "21534463", "35663CE3"

等排序规则设置为utf8_general_ci.

当系统的用户尝试搜索包含该号码一部分的记录时，会出现问题. SQL查询看起来像

The problem shows up when a user of the system is trying to search for a record containing part of this number. SQL query looks like

...
WHERE 'number' LIKE '%0303A%'

现在，如果在LIKE部分中将'A'输入为Latin A，则结果将仅包含其中包含Latin A的记录-应当如此.并且当A为Cyrillic时，结果再次仅是包含Cyrillic A的那些行.还有许多其他字母，如E，C，B，T等.

Now if the 'A' in the LIKE part is entered as a Latin A, the result contains only records with Latin A's in them -- as it should. And when the A is Cyrillic, the results are again only those rows containing the Cyrillic A. There are many other letters like E, C, B, T and so on.

现在我的问题是，是否有一种方法可以修改我的sql查询，以便它返回与LIKE '%0303A%'部分匹配的所有行，但其中包含所有类型的A`?还是应该在插入/更新数据库之前转换用户输入?

Now my question is, if there is a way to modify my sql query so it returns all rows matching the LIKE '%0303A%' part but for all kind of A`s in there? Or I should convert the user input before inserting/updating the database?

推荐答案

您应该转换用户输入，在MySQL中没有功能LOOKS LIKE:)

You should convert the user input, there is no function LOOKS LIKE in MySQL :)

您可以将音译字符串与原始字符串存储在一起，并使用 php :: translit 为此:

You can store the transliterated string along with the original one and use php::translit to do this:

id data    trans_data
1  Москва  MOSKVA
2  София   SOFIA

SELECT  *
FROM    table
WHERE   trans_data LIKE CONCAT('%', ?, '%')

mysqli->bind_param('s', strtoupper(transliterate('Москва')));

这篇关于在where子句中使用类似％..％的mysql查询返回不同的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！