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