At 13:34 -0400 on 04/18/2004, Stormblade wrote about varchar < 4 = char < 4? Why?:
This has been puzzling me. At first I thought it was something that Navicat
was doing but I also tried in EMS MySQL and it does the same.
If I set the type of a field to varchar and set the length to anything less
than 4 it will get converted to a char type of the same length. Now I know
that char is faster. I read it can be up to 50% faster but I am curious why
I am prevented from having a varchar of length less than 4?
Is it prohibitively expensive to do this in MySQL? Is it such a bad idea
that they simply don't allow you to do it? Inquiring minds want to know.
Taking a wild guess here so I might be wrong. Varchar needs a length to be appended to the start of the character data so MySQL can tell how long it is. This length field is probably 2 bytes long. Thus if the max length of the string is 1-3 bytes long, the field will be from 3-5 bytes so you might as well bite-the-bullet and just declare the field as char(4). If the length field is 4 bytes, then ALL Varchars under max=3 will be 5-8 bytes for max=1-3 so again char(4) is shorter.
No need to guess, this is a documented behavior of MySQL:
http://dev.mysql.com/doc/mysql/en/Silent_column_changes.html
VARCHAR columns with a length less than four are changed to
CHAR.Further explanation is also given in the documentation:
http://dev.mysql.com/doc/mysql/en/CHAR.html
The following table illustrates the differences between the two types
of columns
by showing the result of storing various string values into CHAR(4)
and VARCHAR(4) columns:
| Value | CHAR(4) |
Storage Required | VARCHAR(4) |
Storage Required |
'' |
' ' |
4 bytes | '' |
1 byte |
'ab' |
'ab ' |
4 bytes | 'ab' |
3 bytes |
'abcd' |
'abcd' |
4 bytes | 'abcd' |
5 bytes |
'abcdefgh' |
'abcd' |
4 bytes | 'abcd' |
5 bytes |
The values retrieved from the CHAR(4) and VARCHAR(4)
columns
will be the same in each case, because trailing spaces are removed from
CHAR columns upon retrieval.
HTH,
Robert J Taylor
[EMAIL PROTECTED]
P.S. Robert Rosenberg -- happen to go to high school in San Antonio,
TX, by chance? :)
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
