I have a database table with a primary key called PremiseID.
Its MySQL column definition is CHAR(10).
The data that goes into the column is always 10 digits, which is either a 9-digit number followed by a space, like '113091000 ' or a 9-digit number followed by a letter, like '113091000A'.
I've tried writing one of these values into a table in a test MySQL database table t1. It has three columns
mainid integer
parentid integer
premiseid char(10)
If I insert a row that has the following values: 1,1,'113091000 ' and try to read row back, the '113991000 ' value is truncated, so it reads '113091000'; that is the space is removed. If I insert a number like '113091000A', that value is retained.
How can I get the CHAR(10) field retain the space character?
I have a programmatic way around this problem. It would be to take the len('113091000'), realize it's nine characters, and then realize a length of 9 infers there is a space suffix for that number.
To quote from the MySQL reference:
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.
So there's no way around it. If you're using MySQL 5.0.3 or greater, then using VARCHAR is probably the best way to go (the overhead is only 1 extra byte):
VARCHAR values are not padded when they are stored. Handling of trailing spaces is version-dependent. 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.
If you're using MySQL < 5.0.3, then I think you just have to check returned lengths, or use a character other than a space.
Probably the most portable solution would be to just use CHAR and check the returned length.
Q: How can I get the CHAR(10) field retain the space character?
Actually, that space is retained and stored. It's the retrieval of the value that's removing the spaces. (The removal of the trailing spaces on returned values is a documented "feature".)
One option (as a workaround) is to modify your SQL query to append trailing spaces to the returned value, e.g.
SELECT RPAD(premiseid,10,' ') AS premiseid FROM t1
That will return your value with as a character string with a length of 10 characters, padded with spaces if the value is shorter than 10 characters, or truncated to 10 characters if its longer.
A standard CHAR(10) column will always have trailing spaces to pad out the string to the required length of 10 characters. As such, any deliberately trailing spaces will be blended in and, typically, stripped by your database adapter.
If possible, convert to a VARCHAR(10) column if you want to preserve the trailing spaces. You can do this with the ALTER TABLE statement.
Though Gordon's answer may still be right by itself, there is on later versions than mentioned a solution.
In your code run SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
With this session setting you'll retrieve perfect columns on full length of the CHAR(10), while VARCHAR does not when no trailing spaces are entered beforehand. If you don't need the spaces, you can always rtrim().
