Trailing spaces in MySQL

Trailing spaces in MySQL

PREVIOUS POST
NEXT POST

In the past life was easy in MySQL. Both CHAR and VARCHAR types meant the same, only being difference in the sense of fixed or dynamic row length used. Trailing spaces were removed in both cases.

With MySQL 5.0 however things changed so now VARCHAR keeps trailing spaces while CHAR columns do not any more. Well in reality CHAR columns are padded to full length with spaces but it is invisible as those trailing spaces are removed upon retrieval. This is something you need to watch both upgrading to MySQL 5.0 as well as designing your applications – you should keep into account if you mind trailing spaces stored choosing VARCHAR vs CHAR in addition to fixed length vs dynamic level rows and space spent for column size counter.

There is more fun stuff with trailing spaces. When comparison is done trailing spaces are always removed, even if VARCHAR column is used which is pretty counterintuitive. So “a “=”a”=”a ” for all textual column types – CHAR, VARCHAR, TEXT. BLOB is exception it will preserve trailing spaces and use them in comparison.

PREVIOUS POST
NEXT POST

Share this post

Comments (7)

  • Martijn Tonies Reply

    Are you sure that CHARs has spaces that are invisible? Cause that would be wrong according to the SQL Standard. VARCHARs should be trimmed.

    November 27, 2006 at 10:42 am
  • peter Reply

    Sure,

    Check the manual here:
    http://dev.mysql.com/doc/refman/5.0/en/char.html

    I’ve tested it myself and storing “a ” to CHAR(10) and reading it back gives “a” and gives LENGTH(col)=1
    VARCHAR would read “a ” and LENGTH(col)=2 while both of them will retrieve column with value “a” (no space in the end) if we use WHERE col=”a ” (with space)

    November 27, 2006 at 11:13 am
  • Jay Pipes Reply

    Unless, of course, you compare using BINARY:

    mysql> SELECT “a ” = “a”;
    +————+
    | “a ” = “a” |
    +————+
    | 1 |
    +————+
    1 row in set (0.00 sec)

    mysql> SELECT BINARY “a ” = BINARY “a”;
    +————————–+
    | BINARY “a ” = BINARY “a” |
    +————————–+
    | 0 |
    +————————–+
    1 row in set (0.00 sec)

    I think, however, a best practice would be to have the application strip trailing spaces before insertion. I can’t think of a good reason *not* to do so…

    Jay

    November 30, 2006 at 11:40 am
  • peter Reply

    Right Jay,

    You can use Binary or as I mentioned BLOB does not have this behavior. The problem with using BINARY for comparison it becomes case sensitive of course and you may get in trouble with using your indexes.

    Stripping spaces is good practice however it is yet another step you need to think about.

    November 30, 2006 at 11:43 am
  • Alex Reply

    Wow! “a” = “a “… I did not know about this. Thanks for this information!

    January 12, 2010 at 7:01 am
  • Mike Reply

    I also did not know this, thanks

    January 26, 2010 at 6:19 am
  • richlv Reply

    “I think, however, a best practice would be to have the application strip trailing spaces before insertion. I can’t think of a good reason *not* to do so…”

    IPMI sensors can have trailing space in some implementations. storing the sensor in the db means the space is actually meaningful (sensor names are normal, non-binary strings)

    February 27, 2013 at 2:26 pm

Leave a Reply