List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:January 12 2004 6:23pm
Subject:Re: Automatic conversion from `char` TO `varchar`
View as plain text  
Martijn Tonies wrote:
> 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.

It saves time by saving space.  Once your rows are variable length, you 
want them to be as short as possible.  And they are not semantically 
different in mysql, so far as I can tell.

> For example, a VARCHAR "LIKE" is different:

How?

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

Yes:

mysql> DESC t1;
+--------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
+--------+----------+------+-----+---------+----------------+
| id     | int(11)  |      | PRI | NULL    | auto_increment |
| string | char(12) | YES  |     | NULL    |                |
+--------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM t1 WHERE string LIKE 'A %';
+----+--------+
| id | string |
+----+--------+
|  3 | A bear |
+----+--------+
1 row in set (0.00 sec)


> 
> While
> 
> WHERE myvarchar like 'A %'
> 
> doesn't. So there IS a difference. If my "code" would be 5 chars long,

No:

mysql> DESC t2;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     |      | PRI | NULL    | auto_increment |
| string | varchar(12) | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM t2 WHERE string LIKE 'A %';
+----+--------+
| id | string |
+----+--------+
|  3 | A bear |
+----+--------+
1 row in set (0.00 sec)

As I said, they are the same in mysql.

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

No.  There is no such thing as "padded with spaces" in mysql.  mysql 
does not pad either CHARs or VARCHARS, so changing from CHAR to VARCHAR 
does not affect the client, and cannot get you "into trouble".

I think you are confusing two issues here.  You state how the standard 
behaves with respect to CHARs, then assert that mysql will cause trouble 
when it switches from CHAR to VARCHAR because of the standard.  But 
mysql doesn't follow the standard, as you also point out.  If mysql 
padded CHARs according to the standard, then changing from CHAR to 
VARCHAR would produce a change in behavior which could get you into 
trouble.  But mysql does not pad CHARs, so the change in column type 
produces no change in behavior.  It should be transparent.

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

So long as "properly" is defined as "meeting the standard".  There are 
plenty of advantages to doing it the way mysql does.  The main 
disadvantage is the one you point out: non-compliance with a standard.

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

In the standard, not in mysql.

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

You misunderstood me.  I said that they're storage methods *in mysql*. 
One is fixed-length storage, the other is variable.  I didn't claim 
they're stored padded.  I'd bet they're not.  Beyond that, I see no 
difference between the two *in mysql*.

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

Yes, I can see how this would be surprising to someone used to the 
standard, but as you say, it is documented.  And I'll accept "stange", 
from that point of view, but not "flaky", as it consistently behaves as 
documented.  Perhaps we could agree that it is not an implementation of 
"the standard CHAR datatype", but rather an implementation of the mysql 
CHAR datatype -- that is, VARCHAR with fixed-length storage.

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

Michael

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