Hi Michael, > >>>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.
Ah, you meant "processing time". I was thinking "working time". > > 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. Then again: different from the SQL standard. So there seems to be more to it then "just" the declaration itself? > > 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". You can if you have to support multiple database backends. And yes, there are people doing just that :-) ... > 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. Then, in the "crashme" thing on the MySQL website, CHAR should be "supported, but not as expected". Documented or not. Currently it says "supported". > > 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. No, you're confusing the logical datatype "varchar" and "char" with how they are stored. It just might be implemented differently in MySQL, but they are not storage methods. MySQL can do anything it likes when storing CHAR or VARCHAR data, as long as it returns them in a consistent and documented (usually the SQL standard :-) way. > > 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*. Apparently so. Nevertheless, a developer shouldn't have to worry about that and this really is a "gotcha". > 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*. I think I got on the wrong foot by the "fixed length storage" as opposed to "fixed length string" (or datatype, or whatever). You talked about storage, which is physical, I was talking about the datatype itself. > >>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. ok perhaps "flaky" is not the right word. "Different" for sure is the right one :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
