I'll address this list - according to MySQL 4.0.2a-NT:
1. You can define a varchar/char field 'auto_increment'.
Like I would never do this in the first place, but I tried it both with varchar and char. MySQL produces this error:
#1063 - Incorrect column specifier for column '%name of your column%'
2. SELECT 'A' = 'a' gets you true.
This would be true because MySQL does a non-case-sensitive compare. If you wanted to know of 'A' = 'a' then use BINARY. Now, this is just the way it's done in MySQL and if you don't like that, then use a different product.
3. Int(10) is the same as int(1) eventhough the manual says differently.
I don't know where Micha is referencing in the manual. I tried a table with INT(1) and INT(10)...they seem to perform the same. Doc: "The display width does not constrain the range of values that can be stored in the column, nor the number of digits that will be displayed for values having a width exceeding that specified for the column."
4. Tablenames are treated case-sensitive on *nix systems, not on
windows.
I believe this not to be a MySQL specific problem. Look at using CFML pages on *nix vs Windows and you'll have the same problem. It's probably a file system thing.
5. Change a piece of a table definition and mysql creates a temporary
copy of the table, very nice if you have a 6GB table occupying a 10GB
tablespace... (yes, the change will fail)
Hmm...I would have to check this one out more. However, every DB has faults and other DBs may do this different. If you really have a 6GB table...should you be changing the structure at this point in the game? What about creating a new table and dumping the "new" data in.
6. Adding indices result in a similar temporary copy.
Yea, but how many times are you adding a new index? It's not like it does it on every SELECT statement.
7. What does zerofill do to a integer field? A database is meant to
store data, not to format it while storing.
Actually I find this to be a nice feature...if necessary. For instance you are using Auto_Increment INT field and you are using it as your order number. Maybe you want all of your order numbers to be a specific length.
8. When I define a char(32) (md5-strings anyone) field, I really don't
mean varchar(32) (MySQL automatically changes all char(X >4) to
varchar(X)).
I quote Jochem:
"If any column in a table has a variable length, the entire row
becomes variable-length as a result. Therefore, if a table
contains any variable-length columns (VARCHAR, TEXT, or BLOB),
all CHAR columns longer than three characters are changed to
VARCHAR columns.
http://dev.mysql.com/doc/mysql/en/Silent_column_changes.html"
That's all I have time with for now. A lot of these I must say you can't get anything better than MySQL for free (maybe PostgreSQL). I like MySQL and every program has it weriod things - I should mention CF here because you all know the work arounds that we end up having to use.
.Peter
MaePub
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
