List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:January 12 2004 7:22am
Subject:Re: Automatic conversion from `char` TO `varchar`
View as plain text  
Hi Michael,

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

I'm unsure why it saves time. However, they are semantically different.
For example, a VARCHAR "LIKE" is different:
If I would be, for example, storing certain "codes" in a CHAR, and
would do a search like this:

WHERE mychar LIKE 'A  %'

This can return rows.

While

WHERE myvarchar like 'A %'

doesn't. So there IS a difference. If my "code" would be 5 chars long,
always and be, eg, one character and 4 numericals, or less, but padded
with spaces, CHAR would be a good choice. Having this automatically
changed to VARCHAR can get me into trouble.

Even more so when I'm converting from another database engine,
or when I have to support multiple engines. Fact is, CHAR isn't
implemeted properly in MySQL.

>As I understand it, a string
> is a string in mysql.  CHAR and VARCHAR are just two string storage
> methods.

Actually, they're not storage methods. They're logical
things, CHAR is padded, VARCHAR isn't. How they are stored
is something completely different. There's nothing that tells MySQL
(or any database engine, for that matter) to store a CHAR fully
padded. For example, Firebird almost stores CHARs and VARCHARs
the same, but on retrieval pads a CHAR.

> So long as that's true, mysql is doing you a favor when it
> makes this change.
>
> > Chars should be padded.
>
> You mean according to the standard, I assume.  I am unconvinced changing
> mysql to pad CHARS at this point, at the cost of speed and compatibility
> with existing code, is a good idea.

Well, it certainly is a "gotcha" for new people. Luckily, it's documented,
that's something. But it's still a flaky (or at least: strange)
implementation
of the standard CHAR datatype.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com

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