[Phpmyadmin-devel] BLOB always binary?

[Marc Delisle]

Hi Alexander and list,

Rabus a écrit:
> Hi Marc & list,
> 
> -----Original Message-----
> From: Marc Delisle
> 
>>Hi,
>>
>>In this change:
>>http://cvs.sourceforge.net/cgi-bin/viewcvs.cgi/phpmyadmin/phpM
>>yAdmin/tbl_properties_structure.php3.diff?r1=1.37&r2=1.38
>>
>>rabus treats all BLOBs as binary.
>>
>>According to
>>http://www.mysql.com/doc/en/BLOB.html
>>
>>we don't know if a BLOB has binary contents (even if the B stands for
>>Binary). Lots of users put text only in their blobs, and IMO
>>there is nothing wrong with this in MySQL. Do we really want
>>to force users on this?
> 
> 
> First of all, we have to define, what BINARY means in MySQL.
> 
> Of course, you could store binary data in a TEXT field as well as in a
> BLOB one, in a CHAR as well as in a CHAR BINARY.
> The only difference between CHAR and CHAR BINARY for instance is, that
> MySQL sorts CHAR BINARY in a case-sensitive way and CHAR in a
> case-insensitive way (based on the latin1 charset or - in MySQL 4.1 - on
> the charset that is assigned to the corresponding field).
> 
> And this exactly is also the difference between TEXT and BLOB.
> 
> Also, the doc page you mentioned says:
> 
> "In most respects, you can regard a TEXT column as a VARCHAR column that
> can be as big as you like. Similarly, you can regard a BLOB column as a
> VARCHAR BINARY column."

Yes, and here is what MySQL tells about BINARY:
http://www.mysql.com/doc/en/CHAR.html
"The BINARY attribute means that column values are sorted and compared in case-sensitive
  fashion according to the ASCII order of the machine where the MySQL server is running.
  BINARY doesn't affect how the column is stored or retrieved. "

So, from this, I understand that in MySQL, BINARY does not mean that the contents
is binary.  I agree that most users think so, but in the forums, we saw that
some users were using BLOBs to put text contents.

> 
> If you do a SHOW FIELDS query on a table with a BLOB field, MySQL will
> tell you that a BLOB field is treated as "binary".

I tried that with MySQL 4.0.13 and SHOW FIELDS does not tell me
that my BLOB is BINARY. Same thing on our demo site on MySQL 3.23.54.

Marc

> Furthermore, you can try to create a TEXT BINARY field and see what
> happens :-)

It's really the opposite that concerns me: a BLOB can have text contents.

> 
> It is not that I want to force users on anything. It's just about making
> them aware that BLOB is a binary field.
> 
> For MySQL 4.1 users, it's also an interface clarification. Otherwise,
> some unexpirienced users will wonder why there is not charset assigned
> to a BLOB field.
> 
> Regards,
> 
> Alexander M. Turek
> <alex at bugfixes.info>