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
