----- Original Message -----
From: "Michael Stassen"
Sent: Sunday, January 11, 2004 5:10 PM
Subject: Re: Automatic conversion from `char` TO `varchar`
> Martijn Tonies wrote:
> > Hi,
> >>The manual <http://www.mysql.com/doc/en/Open_bugs.html> says
> >>>The following problems are known and will be fixed in due time:
> >>>All string columns, except BLOB and TEXT columns, automatically
> >>>all trailing spaces removed when retrieved. For CHAR types this is
> >>>and may be regarded as a feature according to SQL-92. The bug is
> >>>MySQL Server, VARCHAR columns are treated the same way.
> >>That seems the reverse of what you are saying.
> > Indeed. Nevertheless, I'm right at this one :-)
> I defer to your expertise on SQL standards. Regardless of the
> however, mysql does not pad CHARs with spaces. Thus, CHARs and
> are identical from the client's point of view, so silently changing
> CHARs to VARCHARs for tables with variable length rows does not affect
> the client, but does save space and time. As I understand it, a
> is a string in mysql. CHAR and VARCHAR are just two string storage
Not sure about any of this other stuff, but AFAIK the problem in MySQL
is that it strips trailing spaces from VARCHARs when storing, which
shouldn't happen. This will be fixed sometime. I thought CHARs were
handled correctly -- e.g. it's OK that they're not padded on retrieval.
> So long as that's true, mysql is doing you a favor when it
> makes this change.
No, it's NOT! If *I* specify CHAR, it should be CHAR, period. When
storing md5 values in a variable row length table, MySQL's "favor" of
changing CHAR(32) to VARCHAR(32) wastes 1 byte per row. :-(
It's also done a "favor" causing me to screw up my table design because
I wanted to use a CHAR column with a TEXT column for speed. That
"favor" causes slower row access as soon as that changed-to-VARCHAR
column splits the row by being updated to a longer length. :-( So... I
had to change the TEXT column to CHAR(255) and hope it's long enough.
Though in most cases, it's too long and makes the table bigger than it
would be variable-length with a never-updated TEXT column. Bigger,
because MySQL did me a "favor."
It sucks. :-(