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.
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.