MySQL automagically changes all char columns to varchar if there is one or more variable-length columns' present. The TEXT column in your first example is causing ALL char() columns to be converted to varchar(). In your second example, since there are NO variable-length columns, the char() columns are not changed by MySQL...
Have a look at the manual: http://www.mysql.com/doc/S/i/Silent_column_changes.html On Thu, 18 Oct 2001, jason wrote: > I am using MySQL Admin to create a new database called CustomerInvoice. I > enter 10 fields and some of them I set to CHAR(5) or CHAR(9). See Field > InstallerID and Claim below. MySQL Admin creates the following command and > sends it to MySQL using root login. > > CREATE TABLE `CustomerInvoice` (`InvoiceID` TINYINT(6) NOT NULL, `CustID` > TINYINT(6) NOT NULL, `InstallerID` CHAR(5) NOT NULL, `ServiceType` ENUM('New > Install','Install AO','Service Call','Dish Move','Upgrade/Other') NOT NULL, > `Date` DATE NOT NULL, `Time` ENUM('AM','Noon','PM','Evening','First > Available') NOT NULL, `PromoCode` > ENUM('NONE','RNINE','RDHP','RPVR','RDISH','RMOVE','R500') NOT NULL, `Claim` > CHAR(7) NOT NULL, `Status` > ENUM('Pending','Complete','LOS','Equip','Cust','Home','Pmt-NA','Pmt-NI','Oth > er') NOT NULL, `Notes` TEXT NOT NULL ) > > As you can see the command does call for CHAR and not CARCHAR for > InstallerID and Claim. But after the command runs MySQL Admin automatically > selects and displays the properties of the newly created table and the > following is shown: > > Field Type Null Default > InvoiceID tinyint(6) No 0 > CustID tinyint(6) No 0 > InstallerID varchar(5) No > ServiceType enum('New Install', 'Install AO', 'Service Call', 'Dish Move', > 'Upgrade/Other') No New Install > Date date No 0000-00-00 > Time enum('AM', 'Noon', 'PM', 'Evening', 'First Available') No AM > PromoCode enum('NONE', 'RNINE', 'RDHP', 'RPVR', 'RDISH', 'RMOVE', 'R500') > No NONE > Claim varchar(7) No > Status enum('Pending', 'Complete', 'LOS', 'Equip', 'Cust', 'Home', > 'Pmt-NA', 'Pmt-NI', 'Other') No Pending > Notes text No > > Why is it CHAR is converted to VARCHAR in this and other cases like it? > Sometimes I am able to make CHAR fields and other times I can't make a > single one at all. It seems to affect certain tables but not others. It > must have something to do with the other fields or types I am creating alone > with it. > > I can create a table with the following command and it works as expected > creating a two field table using CHAR() type: > > CREATE TABLE `CustomerInvoiceNew` (`InstallerID` CHAR(5) NOT NULL, `Claim` > CHAR(7) NOT NULL ) > Field Type Null Default > InstallerID char(5) No > Claim char(7) No --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
