Categories
Software Development

Wanting real schema support in MySQL

While upgrading WordPress towards the latest version it also required some database modifications, but here is where I start hating MySQL again and more. I really hope that MySQL will get support for schemas 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 to create a 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 need to be shorter then that if I may believe 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.