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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
