Easy way to store metadata about MySQL-Database

Question

Earlier today, I asked for an easy way to store a version number for the SQL table layout you are using in SQLite, and got the suggestion to use PRAGMA user_version. As there is no such thing as a Pragma in MySQL, I was wondering on how you would go about this in MySQL (Except for creating a table named "META" with a column "DB-Scheme-Version").

Just to repeat what I said in the linked question: I'm not looking for a way to find out which version of MySQL is installed, but to save a version nuber that tells me what version of my MySQL-Scheme I am using, without checking every table via script.

I also saw this question, but it only allows me to version single tables. Is there something similar or, preferably, easier, for whole Databases (Since it would be no fun to query every single table seperately)? Thanks in advance.

MySQL's SET GLOBAL would probably work, but I prefer a solution that does not reset itself every time the server reboots and does not require SUPER Privilege and / or access to the configuration file to use. To put it short: It should work with a standard MySQL-Database that you get when you rent a small webhosting package, not the ones you get if you rent a full server, as you tend to have more access to those.

Answer 1

There are a couple of choices, depending on the privileges that you have. The higher privileges you have, the more “elegant” the solution.

The most direct route is to create a stored function, which requires the CREATE ROUTINE privilege. e.g.

mysql> CREATE FUNCTION `mydb`.DB_VERSION() RETURNS VARCHAR(15)
       RETURN '1.2.7.2861';
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT `mydb`.DB_VERSION();
+--------------+
| DB_VERSION() |
+--------------+
| 1.2.7.2861   |
+--------------+
1 row in set (0.06 sec)

If your privileges limit you to only creating tables, you can create a simple table and put the version in a default value:

mysql> CREATE TABLE `mydb`.`db_version` (
    `version` varchar(15) not null default '1.2.7.2861');
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW COLUMNS FROM `mydb`.`db_version`;
+---------+-------------+------+-----+------------+-------+
| Field   | Type        | Null | Key | Default    | Extra |
+---------+-------------+------+-----+------------+-------+
| version | varchar(15) | NO   |     | 1.2.7.2861 |       |
+---------+-------------+------+-----+------------+-------+
1 row in set (0.00 sec)