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
>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:
"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