While upgrading WordPress towards the latest version it also required some database modifications, but there is where I start hating MySQL again and more. I really hope that MySQL will get support for schema’s as PostgreSQL or Oracle has, but it appears that MySQL has painted itself in the corner. And before some will say that MySQL has, the create schema function is only an alias for create database.
There lies a problem as I don’t want to create a new database for every new instance of an application. Others suggest to use a prefix for the tables, but this means I need to know the prefix in advance before creating tables and/or altering tables. This is nice when you have two or three instances, but there it stops. I want a single set of commands and just switch from schema to schema and apply the patches without any additional scripting changing prefixes.
So you have the choice of creating a “database” for every instance, which will just create another directory with database files. Or you need to use prefix, but then you’re limited to a max of 64 characters for the complete table name. If you would do replication it may even needs to be shorter then that if I may belief MySQL fora. I can only hope that some applications will get decent PostgreSQL support or that Oracle will give MySQL real schema support, but I doubt if they would do that.