From: Vladimir Berezniker
Date: April 28 1999 9:02pm
Subject: Re: Problems with BINARY keyword
List-Archive: http://lists.mysql.com/mysql/2561
Message-Id: <37277756.A2461F06@email.com>
MIME-Version: 1.0
Content-Type: multipart/mixed;
 boundary="------------1994FF27FABA8B2769E8C927"

--------------1994FF27FABA8B2769E8C927
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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.

--------------1994FF27FABA8B2769E8C927--