List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:September 15 2007 2:33am
Subject:Re: What is up with = and trailing spaces?
View as plain text  
At 7:00 PM -0700 9/14/07, Daevid Vincent wrote:
>From: Matt Gordon
>Sent: Friday, September 14, 2007 6:37 PM
>To: Daevid Vincent
>Subject: Trailing space thing to post to mysql list
>I searched Google and didn't find anything obvious. 
>Here is minimal SQL to reproduce the problem.
># Running mysql 5.0.41
>   id int(10) unsigned NOT NULL auto_increment,
>   name varchar(255) default NULL,
>   PRIMARY KEY (id)
># Note the trailing spaces in the name.
>INSERT INTO test( id, name ) VALUES( 1, 'TEST      ' );
># Note there are no trailing spaces in the name but this query still returns
>the record.
>SELECT * FROM test where name = 'TEST';
># Note the leading spaces in the name.
>INSERT INTO test( id, name ) VALUES( 2, '    FOO' );
># Note there are no leading spaces in the name and this query fails.
>SELECT * FROM test where name = 'FOO';
>Also, related to this, we could use LIKE instead of = and that had almost
>the opposite results as we expected, not even using a % sign ?!!?

Trailing spaces in VARCHAR values are not significant.  See:

Which says:

"Note that all MySQL collations are of type PADSPACE. This means that 
all CHAR and VARCHAR values in MySQL are compared without regard to 
any trailing spaces."

Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
What is up with = and trailing spaces?Daevid Vincent15 Sep
  • Re: What is up with = and trailing spaces?Paul DuBois15 Sep