简单的方式来存储关于MySQL数据库的元数据 [英] Easy way to store metadata about MySQL-Database

问题描述

今天早些时候，我要求一个简单的方法以存储您在SQLite中使用的SQL表布局的版本号，并得到建议使用 PRAGMA user_version 。由于在MySQL中没有这样的东西，如 Pragma ，我想知道你将如何在MySQL中这样做（除了创建一个名为META的表与列DB-Scheme-Version）。

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").

只是为了重复我在链接问题中说的话：我不是要找出安装了哪个版本的MySQL，而是保存一个版本nuber，告诉我我使用的MySQL-Scheme的版本，而不用通过脚本检查每个表。

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的 SET GLOBAL 可能会工作，但我更喜欢一个解决方案，时间服务器重新启动，不需要 SUPER 权限和/或访问要使用的配置文件。简而言之：它应该使用一个标准的MySQL数据库，当你租用一个小的虚拟主机包，而不是你得到的，如果你租一个完整的服务器，因为你有更多的访问这些。

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.

推荐答案

有几个选择，取决于你拥有的权限。

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

最直接的路由是创建一个存储函数，它需要 CREATE ROUTINE 特权。例如

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)

这篇关于简单的方式来存储关于MySQL数据库的元数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！