Re: Question to the MySQL guru's
Date: Wed, 22 Nov 2006 17:54:43 GMT
Message-ID: <D909h.24689$yl4.11819_at_newssvr12.news.prodigy.com>
[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:
> I don't feel that it defeats the purpose. You inserted that record
> without the trailing spaces.
Are you sure about that? You cannot assume that, can you?
> You could argue you expect to get it back
> the same way.
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.
>Also it's not necesarrily wasting space, MyISAM performs
> better with equal length records. But just because you want the
> increased performance doesn't mean you want those trailing spaces.
>
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;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| b | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
1 row in set (1.99 sec)
mysql> insert into b values ('DEF ');
mysql> insert into b values ('ABC');
mysql> select '~'||b||'~' from b;
+-------------+
| '~'||b||'~' |
+-------------+
| ~DEF~ | | ~ABC~ |
+-------------+
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 ConsultantReceived on Wed Nov 22 2006 - 18:54:43 CET