Storing binary data (string with nulls/ASCII 0 in them) in CHAR/VARCHAR?

[David Garamond]

Can CHAR/VARCHAR store strings with nul (ASCII 0) in them? It seems it 
can't. [I've used CHAR(2), VARCHAR(2), CHAR(2) BINARY, VARCHAR(2) BINARY].

=======================================================================
create table t1 (
  id int not null primary key,
  code varchar(2) not null, unique(code));
insert into t1 values (1,'');
insert into t1 values (2,char(0));
insert into t1 values (3,char(32));
insert into t1 values (4,concat(char(0),char(0)));
insert into t1 values (5,concat(char(0),char(32)));
insert into t1 values (6,concat(char(32),char(0)));
insert into t1 values (7,concat(char(32),char(32)));
select
  id,
  length(code),
  if(length(code)=0,
     '',
     if(length(code)=1,
       ord(code),
       concat(ord(substring(code,1,1)),' ',ord(substring(code,2,1)))
     )
  ) as code
  from t1;
=======================================================================
Record 3, 5, and 7 is rejected due to duplicate value.

+----+--------------+------+
| id | length(code) | code |
+----+--------------+------+
|  1 |            0 |      |
|  2 |            1 | 0    |
|  4 |            2 | 0 0  |
|  6 |            2 | 32 0 |
+----+--------------+------+
4 rows in set (0.00 sec)
It seems the string in CHAR/VARCHAR is stored as null-terminated ala C. 
But why is record 3 rejected too?

If I want to store 128-bit MD5 hash as a primary key, what would be the 
most compact way of storing them?

--
dave


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]