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
>#
>
>CREATE TABLE test (
>   id int(10) unsigned NOT NULL auto_increment,
>   name varchar(255) default NULL,
>   PRIMARY KEY (id)
>) CHARSET=utf8 ENGINE=MyISAM;
>
>
># 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:

http://dev.mysql.com/doc/refman/5.0/en/char.html

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
MySQL AB, www.mysql.com
Thread
What is up with = and trailing spaces?Daevid Vincent15 Sep
  • Re: What is up with = and trailing spaces?Paul DuBois15 Sep