List:General Discussion« Previous MessageNext Message »
From:Richard Antecki Date:April 28 1999 4:28pm
Subject:Problems with BINARY keyword
View as plain text  
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


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