I have the following problem (using mysql 5.0.70).
我有以下问题(使用mysql 5.0.70)。
In one table I have a varchar field containing some kind of a number, like:
在一个表中,我有一个包含某种数字的varchar字段,例如:
"0303A342", "21534463", "35663CE3"
etc. Collation is set to utf8_general_ci.
Collation设置为utf8_general_ci。
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
当系统的用户试图搜索包含该号码的一部分的记录时,问题就出现了。 SQL查询看起来像
...
WHERE 'number' LIKE '%0303A%'
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.
现在,如果LIKE部分中的'A'作为拉丁语A输入,则结果仅包含其中包含拉丁语A的记录 - 应该如此。当A是西里尔文时,结果只是那些包含西里尔文A的行。还有许多其他字母,如E,C,B,T等。
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?
现在我的问题是,如果有一种方法可以修改我的SQL查询,那么它会返回与LIKE'%0303A%'部分匹配的所有行,但是对于那里的所有类型的A?或者我应该在插入/更新数据库之前转换用户输入?
3 个解决方案
#1
You should convert the user input, there is no function LOOKS LIKE in MySQL :)
你应该转换用户输入,在MySQL中没有LOOKS LIKE的功能:)
You can store the transliterated string along with the original one and use php::translit to do this:
您可以将音译字符串与原始字符串一起存储,并使用php :: translit执行此操作:
id data trans_data 1 Москва MOSKVA 2 София SOFIA
SELECT *
FROM table
WHERE trans_data LIKE CONCAT('%', ?, '%')
mysqli->bind_param('s', strtoupper(transliterate('Москва')));
#2
The fact that some letters look alike in several alphabets does not make them identical. In fact their looking alike might even depend on the font used to display them.
一些字母在几个字母表中看起来相似的事实并不能使它们相同。事实上,他们看起来相似甚至可能取决于用于显示它们的字体。
Your example data looks like hexadecimal values. Without knowing more about the context of your problem, might it be possible to have the user enter the data in decimal format (just using digits)? Another possibility might be a custom control to enter the search criteria. Does it have to be free text or would maybe a listbox with several options suffice?
您的示例数据看起来像十六进制值。如果不了解您的问题的上下文,是否可以让用户以十进制格式输入数据(只使用数字)?另一种可能性是用于输入搜索条件的自定义控件。它必须是*文本还是可能有几个选项的列表框就足够了?
If not, you might find functions similar to what Quassnoi describes (transliterate()) in the language or library you are using. With plain MySQL you are probably out of luck. And even if you find some function that does what you want, make sure it supports all the languages/alphabets your users are going to use - I for one would not know how many alphabets there are that have 1:1 mappings of their characters to the latin ones.
如果没有,您可能会发现类似于Quassnoi所描述的函数(transliterate())在您正在使用的语言或库中。使用普通的MySQL你可能会运气不好。即使你找到了一些你想要的功能,也要确保它支持你的用户将要使用的所有语言/字母 - 我不知道有多少字母表有1到1个字符的映射到拉丁人。
#3
I can't use the php translit - the data I'm dealing with (the 'numbers' as I call them) are some kind of an identifiers, so it doesn't make sense to search for "IA" if somebody enters "Я". Because I know my users will use only Latin and Cyrillic characters, what I'm going to do is make a list of the common letters in the two alphabets and convert user input before reaching the db. Kind of sucks but seems the most appropriate solution.
我不能使用php translit - 我正在处理的数据(我称之为'数字')是某种标识符,所以如果有人输入“搜索”IA是没有意义的Я”。因为我知道我的用户只会使用拉丁字母和西里尔字符,所以我要做的是列出两个字母表中的常用字母,并在到达数据库之前转换用户输入。有点糟透,但似乎是最合适的解决方案。
#1
You should convert the user input, there is no function LOOKS LIKE in MySQL :)
你应该转换用户输入,在MySQL中没有LOOKS LIKE的功能:)
You can store the transliterated string along with the original one and use php::translit to do this:
您可以将音译字符串与原始字符串一起存储,并使用php :: translit执行此操作:
id data trans_data 1 Москва MOSKVA 2 София SOFIA
SELECT *
FROM table
WHERE trans_data LIKE CONCAT('%', ?, '%')
mysqli->bind_param('s', strtoupper(transliterate('Москва')));
#2
The fact that some letters look alike in several alphabets does not make them identical. In fact their looking alike might even depend on the font used to display them.
一些字母在几个字母表中看起来相似的事实并不能使它们相同。事实上,他们看起来相似甚至可能取决于用于显示它们的字体。
Your example data looks like hexadecimal values. Without knowing more about the context of your problem, might it be possible to have the user enter the data in decimal format (just using digits)? Another possibility might be a custom control to enter the search criteria. Does it have to be free text or would maybe a listbox with several options suffice?
您的示例数据看起来像十六进制值。如果不了解您的问题的上下文,是否可以让用户以十进制格式输入数据(只使用数字)?另一种可能性是用于输入搜索条件的自定义控件。它必须是*文本还是可能有几个选项的列表框就足够了?
If not, you might find functions similar to what Quassnoi describes (transliterate()) in the language or library you are using. With plain MySQL you are probably out of luck. And even if you find some function that does what you want, make sure it supports all the languages/alphabets your users are going to use - I for one would not know how many alphabets there are that have 1:1 mappings of their characters to the latin ones.
如果没有,您可能会发现类似于Quassnoi所描述的函数(transliterate())在您正在使用的语言或库中。使用普通的MySQL你可能会运气不好。即使你找到了一些你想要的功能,也要确保它支持你的用户将要使用的所有语言/字母 - 我不知道有多少字母表有1到1个字符的映射到拉丁人。
#3
I can't use the php translit - the data I'm dealing with (the 'numbers' as I call them) are some kind of an identifiers, so it doesn't make sense to search for "IA" if somebody enters "Я". Because I know my users will use only Latin and Cyrillic characters, what I'm going to do is make a list of the common letters in the two alphabets and convert user input before reaching the db. Kind of sucks but seems the most appropriate solution.
我不能使用php translit - 我正在处理的数据(我称之为'数字')是某种标识符,所以如果有人输入“搜索”IA是没有意义的Я”。因为我知道我的用户只会使用拉丁字母和西里尔字符,所以我要做的是列出两个字母表中的常用字母,并在到达数据库之前转换用户输入。有点糟透,但似乎是最合适的解决方案。