Linux Ask!

Linux Ask! is a Q & A web site specific for Linux related questions. Questions are collected, answered and audited by experienced Linux users.

Jul 152010
 

Trailing spaces of char in MySQL

Answer:

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

E.g.

1. Create a table

mysql> CREATE TABLE t1 ( 
    c CHAR(10), 
    v VARCHAR(10) 
) ENGINE = INNODB;

2. Insert some data

mysql> INSERT INTO t1 VALUES ('foo', 'bar');

3. Select the data back

mysql> SELECT COUNT(*) FROM t1 WHERE c = 'foo';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM t1 WHERE c = 'foo   ';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)


As you can see, the trailing spaces makes no difference.

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

  One Response to “Trailing spaces of char in MySQL”

  1. [...] Why you cannot find the result in the second query? Because the column is BINARY, the trailing space(s) is not ignored like this. [...]

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>