List:General Discussion« Previous MessageNext Message »
From:Matt W Date:January 12 2004 11:27am
Subject:Re: Automatic conversion from `char` TO `varchar`
View as plain text  
Hi,

----- 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
have
> >>>all trailing spaces removed when retrieved. For CHAR types this is
okay,
> >>>and may be regarded as a feature according to SQL-92. The bug is
that in
> >>>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
standard,
> however, mysql does not pad CHARs with spaces.  Thus, CHARs and
VARCHARs
> 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
string
> is a string in mysql.  CHAR and VARCHAR are just two string storage
> methods.

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. :-(

Thread
Automatic conversion from `char` TO `varchar`Hassan Shaikh10 Jan
  • Re: Automatic conversion from `char` TO `varchar`Luciano Barcaro10 Jan
  • Re: Automatic conversion from `char` TO `varchar`Fred van Engen10 Jan
  • Re: Automatic conversion from `char` TO `varchar`Martijn Tonies10 Jan
    • Re: Automatic conversion from `char` TO `varchar`Michael Stassen10 Jan
  • Re: Automatic conversion from `char` TO `varchar`Martijn Tonies10 Jan
    • Re: Automatic conversion from `char` TO `varchar`Michael Stassen12 Jan
  • Re: Automatic conversion from `char` TO `varchar`Martijn Tonies12 Jan
    • Re: Automatic conversion from `char` TO `varchar`Michael Stassen12 Jan
  • Re: Automatic conversion from `char` TO `varchar`Matt W12 Jan
  • Re: Automatic conversion from `char` TO `varchar`Martijn Tonies12 Jan
  • Re: Automatic conversion from `char` TO `varchar`Bernard Kenik12 Jan