CODEWITHSUNDEEP
mysqlcharwhitespace
T. Brian Jones
T. Brian Jones

Reputation: 13522

How can I store a space character in a MySQL CHAR field?

I have a MySQL Table with a CHAR(1) field. I store single characters in it. Sometimes I need to store a space in this field, but MySQL doesn't let me. I'm using MySQL v.5.6.22.

This runs but an empty field is stored:

INSERT INTO
companies(name_char_0)
values(' ');

This returns an empty value:

SELECT HEX(name_char_0)
FROM companies;

I don't experience this problem with VARCHAR(1) fields, but I have a series of these CHAR fields as lookups and speed is an issue. I believe the VARCHAR(1) field will always be slower to search on than a CHAR(1) field.

Upvotes: 6

Views: 7147

Answers (1)

Digital Chris
Digital Chris

Reputation: 6202

In CHAR fields, spaces are used to pad the field to its length, then the spaces are stripped:

From https://dev.mysql.com/doc/refman/5.0/en/char.html:

The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.

You should not use type CHAR if you want to hold only a space.

If you use VARCHAR(1) in MySQL > 5.0.2 you should not experience this issue:

As of MySQL 5.0.3, trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL. Before MySQL 5.0.3, trailing spaces are removed from values when they are stored into a VARCHAR column; this means that the spaces also are absent from retrieved values.

Upvotes: 6

Related Questions