SELECT и WHERE
Buy this Reference Manual in softcover from Barnes & Noble!
BINARYBINARY преобразует следующую за ним строку в строку с двоичными данными. Это простой способ обеспечить сравнение в столбце с учетом регистра, даже если данный столбец не определен как BINARY или BLOB:
mysql> SELECT "a" = "A";
-> 1
mysql> SELECT BINARY "a" = "A";
-> 0
BINARY string является сокращением для CAST(string AS BINARY). See section 6.3.5 Функции приведения типов. Оператор BINARY был введен в версии MySQL 3.23.0. Следует учитывать, что при приведении индексированного столбца к типу BINARY MySQL в некоторых случаях не сможет эффективно использовать индексы. Для сравнения двоичных данных типа BLOB без учета регистра данные с типом BLOB перед выполнением сравнения всегда можно конвертировать в верхний регистр:
SELECT 'A' LIKE UPPER(blob_col) FROM table_name;В скором времени мы планируем ввести преобразование между различными кодировками, чтобы сделать сравнение строк еще более гибким.
| Posted by [name withheld] on Friday May 17 2002, @6:24am | [Delete] [Edit] |
Experimenting a bit with "column LIKE constant" on an indexed column
shows that it's been optimized as much as one would expect. Thus, having
a constant that starts with a non-wildcard gives you a join type of 'range'.
It's unclear what happens when there's a leading '_' followed by non-wildcards -
will mysql use the sub-ranges for each possible first character, or just search
all rows. The latter is what I'd guess, although I might be wrong.
| Posted by Caoimhin O Donnaile on Friday May 17 2002, @6:24am | [Delete] [Edit] |
The new BINARY cast is very useful when you need for different purposes the benefits of both case-independence and case- (and diacritic-) dependence. There seem to be two limitations, though.
There is no symmetric "NATIONAL" cast, so if you are going to sometimes need to do non-BINARY ("NATIONAL") searches, you have to define the field as non-BINARY in the first place and use the BINARY cast when you need it.
Searches using the BINARY cast on a non-BINARY field can also be slow in a big table - presumably because the indexes are non-binary. I can't at the moment see any way of getting the benefit of fast indexed searches in both BINARY and non-BINARY form without duplicating the field.
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
I needed to put the BINARY keyword after the WHERE
keyword for it to have the desired effect with a
REGEXP operation. Easy to infer, but not exactly
obvious from the example given here.
Add your own comment.