[Phpmyadmin-devel] BLOB always binary?

[Rabus]

Hi Marc & list,

I'm sorry for not having answered, but I have been reconfiguring my machine 
completely for the last couple of days...

Am Mon, 02 Jun 2003 09:08:41 -0400 hat Marc Delisle 
<DelislMa at CollegeSherbrooke.qc.ca> geschrieben:

>> 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.
>

Why not? If they just wanted to, they could also put text in a CHAR BINARY 
field.

>>
>> 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.
>

Sorry, my fault. The indicator I was talking about was added in 4.1.0. I 
thought I had already seen it in an earlier version, but I was wrong.
Here's what SHOW FIELDS says about my character set test table:

mysql> SHOW FIELDS FROM `test`;
+---------------+----------------------------------+-------------------+---- 
--+-----+---------+----------------+
| Field         | Type                             | Collation         | 
Null | Key | Default | Extra          |
+---------------+----------------------------------+-------------------+---- 
--+-----+---------+----------------+
| id            | tinyint(3) unsigned              | binary            |    
  | PRI | NULL    | auto_increment |
| string_latin1 | varchar(20)                      | latin1_swedish_ci |    
  |     |         |                |
| string_latin2 | varchar(20) character set latin2 | latin2_general_ci |    
  |     |         |                |
| string_latin7 | varchar(20) character set latin7 | latin7_general_ci |    
  |     |         |                |
| string_binary | varchar(20) binary               | binary            |    
  |     |         |                |
| text_utf8     | text character set utf8          | utf8              |    
  |     |         |                |
| text_binary   | blob                             | binary            |    
  |     |         |                |
+---------------+----------------------------------+-------------------+---- 
--+-----+---------+----------------+

>
>> 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.
>

Well, a CHAR BINARY can also have text contents, so I see no contradiction.

Regards,

Alexander M. Turek