7 Jun
2003
7 Jun
'03
4:57 p.m.
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(a)CollegeSherbrooke.qc.ca> geschrieben:
Why not? If they just wanted to, they could also put text in a CHAR BINARY
field.
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 |
| | | |
+---------------+----------------------------------+-------------------+----
--+-----+---------+----------------+
Well, a CHAR BINARY can also have text contents, so I see no contradiction.
Regards,
Alexander M. Turek
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.
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.