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  
Hi,
    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

Vlad
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 "http://www.mysql.com/Manual_chapter/manual_toc.html" 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.

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