4.3 Preserving Trailing Spaces in String Columns
4.3.1 Problem
MySQL strips trailing
spaces from strings, but you want to preserve them.
4.3.2 Solution
Use a different column type.
4.3.3 Discussion
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.
|
