Hi!
>>>>> "John" == John Birrell <jb@stripped> writes:
John> On Mon, Jun 18, 2001 at 06:32:18PM +0300, Michael Widenius wrote:
>> The above is a design flaw (of which I am aware of) in how
>> we pass keys to the search functions.
>>
>> During MySQL usage shouldn't be notable, but we need to fix this
>> in 4.0 or 4.1 to be able to implement VARCHAR more efficiently.
>>
>> The idea would be to instead of filling up keys with space, we would
>> always use the format: (2 byte length) + string.
>>
>> (We already use this format with keys on blob)
>>
>> It's not that hard to change this; It's just a lot of code to check
>> when doing this.
>>
>> How did you notice this?
John> As a test for my application, I import data that I have sucked from
John> a system that runs on the software I dislike so much that I want
John> to write my own. 8-) It's a good test for me, because the data is
John> not contrived.
John> I have a 'user' table:
John> usrnum INTEGER AUTO_INCREMENT
John> name VARCHAR(32)
John> mailto TEXT
John> PRIMARY KEY ( usrnum )
John> UNIQUE INDEX usrname ( name )
John> When I import the data, most of the user rows are inserted, but
John> a few are rejected with ER_DUP_ENTRY. I looked at the data for the
John> rows that failed, using 'mysql' (actually 'mysqle' that I have
John> built embedded). The first one that failed was for the user name
John> 'r1man'. It was failing because there was already a user called
John> 'r1manic'.
You should not get a duplicate key for this case!
I tried to repeat this
create table t1 (id int not null auto_increment primary key, username varchar(32) not
null, unique (username));
insert into t1 values (0,"mysql");
insert into t1 values (0,"mysql ab");
insert into t1 values (0,"mysql a");
insert into t1 values (0,"r1manic");
insert into t1 values (0,"r1man");
The above worked without any problems.
What will not work is if you insert:
insert into t1 values (0,"r1man ");
Because MySQL doesn't honor end space in varchar, which in most cases
is not a problem.
Regards,
Monty
