October 2002
Intermediate to advanced
1024 pages
27h 26m
English
MySQL strips trailing spaces from strings, but you want to preserve them.
Use a different column type.
If you store a string that contains trailing spaces into the
database, you may find that they’re gone when you
retrieve the value. This is the normal MySQL behavior for
CHAR and
VARCHAR columns; the server returns values from
both types of columns without trailing spaces. If you want to
preserve trailing spaces, use one of the TEXT or
BLOB column types. (The TEXT
types are not case sensitive, the BLOB types are.)
The following example illustrates the difference in behavior for
VARCHAR and TEXT columns:
mysql>CREATE TABLE t (c VARCHAR(255));mysql>INSERT INTO t (c) VALUES('abc ');mysql>SELECT c, LENGTH(c) FROM t;+------+-----------+ | c | LENGTH(c) | +------+-----------+ | abc | 3 | +------+-----------+ mysql>DROP TABLE t;mysql>CREATE TABLE t (c TEXT);mysql>INSERT INTO t (c) VALUES('abc ');mysql>SELECT c, LENGTH(c) FROM t;+------------+-----------+ | c | LENGTH(c) | +------------+-----------+ | abc | 10 | +------------+-----------+
There are plans to introduce a VARCHAR type that
retains trailing spaces in a future version of MySQL.