Don't worry about the SET syntax we have that sorted. The problem is MySQL is not treating the comparison in the where clause as a binary comparison. It appears to be encoding either the fields or the comparator value and therefore getting an incorrect result, it either returns the wrong row or no rows.
Now this could be because when the data is inserted MySQL is encoding the key fields which it shouldn't because they're binary. The insert is always SET CHARACTER SET <> insert (key_low,key_high,searchss) values(.....) and the SET CHARACTER SET <> always precedes the SELECT Statement in any query. As an aside using 'aaa' 'bbb' makes an incorrect statement about the comparison. The comparator value I used is typical but the character value for any of the 5 bytes in either the key fields or comparator can be 0-255 Kevin Cowley R&D Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk > -----Original Message----- > From: Jeremy March [mailto:[EMAIL PROTECTED] > Sent: 15 October 2004 06:24 > To: [EMAIL PROTECTED] > Subject: Re: Select Binary Field when UTF8 and Collate > > > > 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] ************************************************************************************** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000 Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ************************************************************************************** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
