List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:May 11 1999 4:51pm
Subject:Problems with BINARY keyword
View as plain text  
>>>>> "Richard" == Richard Antecki
> <rantecki@stripped> writes:

Richard> I have a problem matching strings using the BINARY keyword.

Richard> I have fields in a table 'auth' defined as

Richard> 	username	CHAR(25) BINARY
Richard> 	passwd		CHAR(25) BINARY

Richard> So obviously all string comparisons are case sensitive.  However, I
Richard> noticed that trailing spaces in a string cause problems:

Richard> 	select 'user'='user    ';  will return 1

Richard> Leading spaces do not cause the same problem:

Richard> 	select 'user'='    user';  will return 0


Richard> If, for example, a valid username is 'user', then

Richard> 	select passwd from auth where username='user'

Richard> will work.  However, if some idiot decides to insert trailing spaces after
Richard> his username, the following will also match to 'user':

Richard> 	select passwd from auth where username='user   '

Richard> This doesn't bother me by itself, but this behaviour doesn't seem to be
Richard> consistant across INSERT, SELECT and UPDATE.

Richard> Assuming table 'blah' also contains a username field of type char(25)
Richard> BINARY, If I run:

Richard> 	insert into blah values ('user   ', ... )

Richard> then

Richard> 	select ... from blah where username='user   '

Richard> this does not return a match!

Richard> This is causing me problems, as then it will try to INSERT a new record
Richard> instead of UPDATE and I get a DUPLICATE KEY error!

Richard> This will work if I use strcmp, but it will be much slower.  Does anyone
Richard> know why it does this?

This problem is described in the MYSQL manual.

The problem is that all CHAR columns automaticly remove all end space
characters.

The simple fix (if you are using BINARY columns) is to use

select ... from blah where username=trim('user   ').

Regards,
Monty
Thread
Looking for ISP that provides MySQLChristopher R. Jones26 Apr
  • Re: Looking for ISP that provides MySQLBill Gerrard26 Apr
  • Re: Looking for ISP that provides MySQLBill Rhodes26 Apr
    • Selects with case sensitivityBarry26 Apr
    • Re: Looking for ISP that provides MySQLChris Jester27 Apr
    • Re: Looking for ISP that provides MySQLKyle Cordes27 Apr
      • Re: Looking for ISP that provides MySQLJohn Elliott27 Apr
  • Re: Selects with case sensitivitySasha Pachev27 Apr
    • Re: Selects with case sensitivityThimble Smith27 Apr
      • Problems with BINARY keywordRichard Antecki28 Apr
        • Re: Problems with BINARY keywordFred Read28 Apr
        • Re: Problems with BINARY keywordVladimir Berezniker29 Apr
        • Problems with BINARY keywordMichael Widenius11 May
    • Re: Selects with case sensitivityMartin Ramsch27 Apr
      • Re: Selects with - ThanksBarry27 Apr