On 2003-09-10 08:33:03 +0100, Steve Hay wrote:
> Chuck Fox wrote:
> >I am a Sybase DBA, but in situations like this, I have declared the 
> >column on the table to be varbinary or binary and stored the data 
> >directly without conversion.  Don't know if MySql supports this datatype. 
> 
> One can declare a column "VARCHAR(n) BINARY" for a similar thing to 
> Sybase's "VARBINARY(n)", but unfortunately it makes no difference.  If I 
> store the data directly without conversion into such a column then, as 
> far as I can make out, each character's bytes get stored exactly as if I 
> had converted to bytes beforehand.  And when I retrieve the data (again 
> without conversion) I just get octet sequences into my Perl scalars - 
> not flagged, UTF-8 character strings as I would like.
> 
> This is all exactly the same as what I get with a "VARCHAR(n)" column.

Yes, but the difference is that with a binary column the database knows
that the data is not character data in a charset it knows. For example
the MySQL manual states:

  Values in CHAR and VARCHAR columns are sorted and compared in
  case-insensitive fashion, unless the BINARY attribute was specified
  when the table was created.

This doesn't work with UTF-8 data of course, because the individual
bytes of a multi-byte-character are not whole characters and hence don't
have a uppercase or lowercase equivalent. So two strings which should
compare equal generally won't, and sometimes strings which should not
compare equal will. If you just tell the database "this is binary data,
not character data" it won't try to do case conversions on it and it
(hopefully) will stop you doing them in SQL code (You can and must do
them in perl).

        hp

Disclaimer: I haven't actually used MySQL for some time, this is general
advice, not targeted at the specific way MySQL compares strings.

-- 
   _  | Peter J. Holzer      | Unser Universum w�re betr�blich
|_|_) | Sysadmin WSR / LUGA  | unbedeutend, h�tte es nicht jeder
| |   | [EMAIL PROTECTED]        | Generation neue Probleme bereit.
__/   | http://www.hjp.at/   |  -- Seneca, naturales quaestiones

Attachment: pgp00000.pgp
Description: PGP signature

Reply via email to