问题描述
我遇到以下问题(使用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查询返回不同的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！