My Top MySQL Gripes
Due to some (legitimate) criticism I received for complaining about MySQL in another forum without bothering to say why, here is my list of my top MySQL complaints:
- CHECK constraints are parsed and then ignored
- No custom data types!!! (This really hurts me)
- No partial index support
- No subqueries on views
- Default values are severely crippled
- Cascading updates and deletes will not fire triggers
- No multiple triggers of the same type
- No triggers on views
Yes, triggers should be avoided, but if you can't avoid them, don't cripple 'em.
Many of these issues force devs to push logic into the application layer where they're far more likely to be buggy.
Actually, if I could just have three of those fixed, I'd want custom data types (with operators, please), sane default values and check constraints.
Freelance Perl/Testing/Agile consultant and trainer. See http://www.allaroundtheworld.fr/ for our services. If you have a problem with Perl, we will solve it for you.
And don't forget to buy my book! http://www.amazon.com/Beginning-Perl-Curtis-Poe/dp/1118013840/
You think that's bad? In SQLLite, even the type of a column is silently parsed and then ignored!
"So, for example, if a column is of type INTEGER and you try to insert a string into that column, SQLite will attempt to convert the string into an integer. If it can, it inserts the integer instead. If not, it inserts the string."
Oh great, do something random, just what I want in a database engine.
Like MySQL, it is slowly improving: "As of version 3.6.19, SQLite supports foreign key constraints. Prior versions of SQLite parsed foreign key constraints, but did not enforce them."
Also like (older versions of) MySQL, the documentation tends to treat missing data integrity checks as features rather than bugs.
Out of curiosity, why should triggers be avoided?
The SQLite behavior works great for databases where you don't care how long a string is or how big a number or what format the date is in but you just want it in the database.
I did get bit one time by trying to insert "$!" into the database and it inserted as a string rather than the number I expected from the schema, but that was easily fixed even after-the-fact.
In fairness, SQLite doesn't bill itself as a full-featured database. It's a lightweight, no-daemon-required, just-enough-to-get-by database. The BDB of the SQL world.
MySQL over-promises and under-delivers; SQLite (despite providing fewer features) does the inverse.
When you use a database, you're generally listing the databases, listing the tables, seeing how the tables are constructed and fiddling with data. Triggers are usually invisible and people tend not to see them. Further, databases are generally not used directly, but with an application layer on top of them, making triggers even harder to see. Thus, you can get plenty of mysterious "action at a distance" problems and wonder why you updated a customer order and someone else is reporting a bug with negative inventory.
Toby: I think that's a great way of describing it. So long as you understand what it's actually for, it's a pretty nifty tool.
One I discovered this weekend: you can't say:
Because functions can't be used as defaults. The above will work using 'CURRENT_TIMESTAMP' instead of 'NOW()', but in other cases, you have to use a trigger if you want a function as a default value.
Given the ownership of MySql I wouldn't invest much hope in seeing it improve.
I think that having a general-purpose string type in the database is a wonderful idea. It is less good to pretend to parse definitions like 'int' or 'char(10)' but silently ignore them. If SQLLite required all columns to be declared with 'string' type, I'd be fine with that; it would also allow support for datatypes such as 'int' to be added in a later release.
The SQL standard defines data integrity checks which must happen when a column is declared with a certain type, or a foreign key constraint, or check constraint; programmers have a reasonable expectation that if they use these features they will work. If you don't support those parts of the SQL language, fine; just don't bogusly parse them and throw them away.
SQLite does assign some significance to the data type of a column – it picks different type coercion affinities depending on the data type. And it supports types in the same way Perl does: if you ask it for a string it will do its best to try and give you a string, and if you ask for an integer it will do its best to give you that. Works for me in Perl, I’m not sure why it’s suddenly horrible when it happens in SQL instead.
If you think MySQL is better than SQLite for supporting data types, I want to see your face next time you
INSERTa 200-character string into aCHAR(20)and MySQL smiles, nods and complies… by throwing away 90% of the data. Hooray! SQLite at least doesn’t mangle your data when it, uh, fails to reject it. If you need to constrain it you still can by doing so retrospectively. With MySQL you never get to find out.And really, SQLite is aimed at uses where you’d never pick MySQL. MySQL, in contrast, is aimed at uses where… you’d never pick MySQL.