Hi, ----- Original Message ----- From: "Michael Stassen" Sent: Sunday, January 11, 2004 5:10 PM Subject: Re: Automatic conversion from `char` TO `varchar`
> > Martijn Tonies wrote: > > > Hi, > > > >>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. As I understand it, a string > is a string in mysql. CHAR and VARCHAR are just two string storage > methods. Not sure about any of this other stuff, but AFAIK the problem in MySQL is that it strips trailing spaces from VARCHARs when storing, which shouldn't happen. This will be fixed sometime. I thought CHARs were handled correctly -- e.g. it's OK that they're not padded on retrieval. > So long as that's true, mysql is doing you a favor when it > makes this change. No, it's NOT! If *I* specify CHAR, it should be CHAR, period. When storing md5 values in a variable row length table, MySQL's "favor" of changing CHAR(32) to VARCHAR(32) wastes 1 byte per row. :-( It's also done a "favor" causing me to screw up my table design because I wanted to use a CHAR column with a TEXT column for speed. That "favor" causes slower row access as soon as that changed-to-VARCHAR column splits the row by being updated to a longer length. :-( So... I had to change the TEXT column to CHAR(255) and hope it's long enough. Though in most cases, it's too long and makes the table bigger than it would be variable-length with a never-updated TEXT column. Bigger, because MySQL did me a "favor." It sucks. :-( -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
