Attaching simple metadata to a MySQL database

Question

Is there a way to attach a piece of metadata to a MySQL database? I'm trying to write code to automatically update the database schema whenever a code upgrade requires it. This requires the storage of a single integer value -- the schema version. I could of course create a whole table for it, but that seems like overkill for just a simple number.

Answer 1

To answer the question as titled, that is for metadata for the entire database and not individual tables, there are a couple of choices, depending on the privileges that you have.

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 metadata as default values. There’s no need to store any data in the table.

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

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

Answer 2

You can use table comments to store the version:

ALTER TABLE table1 COMMENT = '1.4';

You'll have to regex to get the comment from this:

SHOW CREATE TABLE table1;
/COMMENT='(.*)'/