List:General Discussion« Previous MessageNext Message »
From:Vladimir Berezniker Date:April 28 1999 9:02pm
Subject:Re: Problems with BINARY keyword
View as plain text  
    Did you try VARCHAR rather than CHAR, because in documentation it states
that CHAR is always padded with spaces on the right. I guess that why it
returns 1

Richard Antecki wrote:

> I have a problem matching strings using the BINARY keyword.
> I have fields in a table 'auth' defined as
>         username        CHAR(25) BINARY
>         passwd          CHAR(25) BINARY
> So obviously all string comparisons are case sensitive.  However, I
> noticed that trailing spaces in a string cause problems:
>         select 'user'='user    ';  will return 1
> Leading spaces do not cause the same problem:
>         select 'user'='    user';  will return 0
> If, for example, a valid username is 'user', then
>         select passwd from auth where username='user'
> will work.  However, if some idiot decides to insert trailing spaces after
> his username, the following will also match to 'user':
>         select passwd from auth where username='user   '
> This doesn't bother me by itself, but this behaviour doesn't seem to be
> consistant across INSERT, SELECT and UPDATE.
> Assuming table 'blah' also contains a username field of type char(25)
> BINARY, If I run:
>         insert into blah values ('user   ', ... )
> then
>         select ... from blah where username='user   '
> this does not return a match!
> This is causing me problems, as then it will try to INSERT a new record
> instead of UPDATE and I get a DUPLICATE KEY error!
> This will work if I use strcmp, but it will be much slower.  Does anyone
> know why it does this?
> Richard
> ---------------------------------------------------------------------
> Please check "" before
> posting. To request this thread, e-mail mysql-thread2548@stripped
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail mysql-unsubscribe@stripped instead.

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