[ORIGNAL POST TO COMP.DATABASES.MYSQL - A discussion on the proper use and
definitions of CHAR vs. VARCHAR data-types. ADDING CROSS-POST for discussion
purposes. I could be wrong in *my* thought-processes, but I don't think so...]

In attempting to solve a problem posted earlier (see "non-tab delimited output?)
I discovered that, in their infinite wisdom, the MySQL developers elected to
trim spaces from CHAR data when retrieved from the database. Is this a new
interpretation of the ANSI-standard for the CHAR datatype? Most of us use the
VARCHAR datatype to achieve this goal - well, at least *I* do.

I am baffled as to why you would waste space storing space-padded data, then
trimming it when retrieved from the database. This is apparently a documented
feature:

from: http://dev.mysql.com/doc/refman/5.1/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****."

Does that not defeat the purpose of the CHAR datatype?

Questions? Comments? Snide Remarks?
================================================== =============================
Dr.Zoidberb wrote:

Are you sure about that? You cannot assume that, can you?

If I insert the data WITH spaces - I certainly expect to retrieve it WITH
spaces. So, in your mind when does 'ABC' = 'ABC '? Do you not expect to
retrieve the data based on the datatype not the data? Isn't that the purpose of
using a datatype? Using that logic, that makes the use of datatypes moot. Just
store everything as CHAR (or binary) data and let the app handle "fixing" it.
Seems counter-productive/intuitive to me.

This also makes the CAST function of little or no value, mainly because now it
does not transform the data as expected, and a waste of the developers
programming time to implement it. This function allows the app developer to
easily change the look of the data based on datatype.

Would you not agree that now there is NO WAY to return fixed-length column data
to those applications and extracts without a lot of post-processing from the
application perspective? Again, makes the use of datatypes moot.

Again, you use datatypes not just to store the data, but to return that data in
a specific format. You expect to see the RESULTS based on the column datatype
even if the original data was of a different precision or character length.

While this example works, it is definately not intuitive and is flat wrong.
Again, in what world does 'ABC' = 'ABC '.

mysql> describe b;
+-------+----------+------+-----+---------+-------+
1 row in set (1.99 sec)

mysql> insert into b values ('DEF ');
mysql> insert into b values ('ABC');
mysql> select '~'||b||'~' from b;
+-------------+
2 rows in set (0.03 sec)

If I am expecting to see the data with the trialing spaces included, I will get
incorrect results - because in this example, I NEED to retrieve the data
INCLUDING the trailing spaces and I do not. This is just flat WRONG!!!!

Now, of course, MySQL has a REAL problem in that they cannot fix this without
adversely affecting all of those unsuspecting programmers that used this CHAR
datatype because they didn't know any better and yes, the products of "higher
education".

--
Michael Austin.
DBA Consultant