Results 1 to 2 of 2
Jul 16, 2003, 18:44 #1
CHAR field definition becomes a VARCHAR!!!
I'm using phpMyAdmin 2.5.2 (on a WinXP PC, with MySQL 4.0.12) to create a new table in a localhost database.
All of a sudden (ie. today!) I can't create CHAR type fields more than 1 char in size.
For example the following create table command APPEARS to work ok:-
CREATE TABLE `working` (
`wg_working_code` CHAR( 4 ) NOT NULL ,
`wg_working_name` VARCHAR( 30 ) NOT NULL ,
PRIMARY KEY ( `wg_working_code` )
) TYPE = InnoDB
BUT..... afterwards the field 'wg_working_code' shows as VARCHAR(4) !?!
Any body else ever had this error?
More importantly ... anybody got the fix for it???
Jul 16, 2003, 19:27 #2
The reason for using char is to keep all the data lined up so it's faster to access... so if you had two CHAR() fields one of length 4 and one of 30 then to find the 5th record you would look at the 5*(4+30)th byte of the file and onwards for 34 bytes. Now what if one field is varchar? Varchars can be any length and only the length used is stored in the database. This can save TONS of space in a big database, but the side effect is that you can't use the simple math to find a record. You need to use an index of sorts to find where a record is. So you might as well save space and convert everything to a varchar where there is one variable length field in the table. This is exactly what mysql does and it's what you actually want it to do.
The moral? If you need every ounce of speed don't use VARCHAR or TEXT fields, but the speed difference is minor and the increased file size may actually counteract the increase in speed, except in certain specialized cases. VARCHAR and CHAR behave exactly the same so this won't cause any problems with your program.