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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
