List:General Discussion« Previous MessageNext Message »
From:David Garamond Date:November 29 2003 1:05am
Subject:Storing binary data (string with nulls/ASCII 0 in them) in CHAR/VARCHAR?
View as plain text  
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


Thread
Storing binary data (string with nulls/ASCII 0 in them) in CHAR/VARCHAR?David Garamond29 Nov
  • Re: Storing binary data (string with nulls/ASCII 0 in them) in CHAR/VARCHAR?David Garamond29 Nov
    • Re: Storing binary data (string with nulls/ASCII 0 in them) in CHAR/VARCHAR?Dan Nelson29 Nov