> We have a database that has one particular table, searchdata, that has
> the
> following columns
>
> BINARY key_low(5)
> BINARY key_high(5)
> VARCHAR searchss(255)
>
> The following query, when the SET QUERY UTF8 option is present,
> doesn't work
> because MySQL encodes the binary field.
> Any ideas how we get around this?
> We're running 4.1.5a gamma and the table encoding is set to utf8.
>
> SELECT searchsss FROM searchdata WHERE '$D6@/' BETWEEN key_low AND
> key_high;
I'm not sure I understand the problem. By "SET QUERY utf8" do you mean
"SET NAMES utf8"? I couldn't find "SET QUERY" in the manual. The
following seems to work correctly to me:
SET NAMES utf8;
CREATE TABLE t (key_low tinyblob, key_high tinyblob, searchss
VARCHAR(255));
INSERT INTO t VALUES ('aaa', 'ccc', 'str1'),('AAA', 'CCC', 'str2');
SELECT searchss FROM t WHERE 'bbb' BETWEEN key_low AND key_high;
+----------+
| searchss |
+----------+
| str1 |
+----------+
1 row in set (0.00 sec)
Isn't this what you want? A binary comparison? To get a
case-insensitive comparison use the following:
SELECT searchss FROM t WHERE 'bbb' COLLATE utf8_unicode_ci BETWEEN
key_low AND key_high;
+----------+
| searchss |
+----------+
| str1 |
| str2 |
+----------+
2 rows in set (0.00 sec)
Or is there more that I don't understand?
best regards,
Jeremy
>
> Kevin Cowley
> R&D
>
> Tel: 0118 902 9099 (direct line)
> Email: [EMAIL PROTECTED]
> Web: http://www.alchemetrics.co.uk
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
