Here’s a demo of that really important MySQL gotcha about side effects when modifying a column, and a case against “SQL is pretty much the same everywhere” :)
Say I want to expand a VARCHAR(30) to a VARCHAR(40), on a column that has a NOT NULL constraint, a default value, and perhaps other attributes.
Demo: MySQL edition: https://dbfiddle.uk/vmpGShfp
Demo: PostgreSQL edition: https://dbfiddle.uk/OB8hRoJv
As you can see form the above demo, in MySQL it's not enough to simply run a command like alter table t modify a varchar(40); You also need to respecify the NOT NULL constraint, the default value if any, and other stuff (including non-default character set and collation!)
Now imagine I’m using a generic SQL script, or a tool like Liquibase that’s supposed to be “database agnostic”…say you initially have Postgres and this change doesn’t mangle anything. then you switch to MySQL and re-run the same scripts: mangle city!
Yeah you’ll get different results, and Liquibase certainly doesn’t hash this out for you automatically. (I've checked!)
The question is: which other databases also have this behavior?
No comments:
Post a Comment