如何更改 INFORMATION_SCHEMA 或添加触发器或外键? [英] How to alter INFORMATION_SCHEMA or add triggers or foreign keys to it?

问题描述

我正在尝试创建一些元数据来扩展 mysql 功能，但我无法在数据库 INFORMATION_SCHEMA 中创建表.我认为我可以创建另一个数据库并在其中保存我的元数据，但我需要一些外键从我的表到 INFORMATION_SCHEMA 数据库中的一些表.不过，我在尝试创建它们时会出错.然后我想我可以创建一个触发器来获得更改通知，但是由于触发器与表相关联，我无法更改该数据库，因此我也无法创建触发器.

I'm trying to create some meta-data to extend mysql functionality but I can't create tables in the database INFORMATION_SCHEMA. I thought that I could just create another database and have my metadata in there but I need some foreign keys from my tables to some tables in the INFORMATION_SCHEMA DB. Nevertheless, I get errors when trying to create them. Then I thought I could create a trigger to get notified of changes but since triggers are associated to a table and I can't alter that database, I can't create triggers either.

具体来说，我有一些表引用了 information_schema.schema(schema_name) 和 information_schema.schemata(columns) 和其他一些表.我想要那些外键，这样我就可以使用 ON UPDATE CASCADE ON DELETE CASCADE 否则我的表中会有一些行没有引用，我不能允许这样做.

Specifically I have some tables that references to information_schema.schemata(schema_name) and to information_schema.schemata(columns) and some others. I want to have those foreign key so I can use ON UPDATE CASCADE ON DELETE CASCADE or otherwise I'll have some rows in my tables referencing to nothing and I can't allow that.

我正在使用使用 MySql 5.3 的 mariaDB 5.5.30.

I'm using mariaDB 5.5.30 which uses MySql 5.3.

推荐答案

INFORMATION_SCHEMA 表实际上是视图，其内容由 MySQL 服务器自动维护.

INFORMATION_SCHEMA tables are actually views whose contents is automatically maintained by the MySQL server.

手册提供了更多信息:

在 INFORMATION_SCHEMA 内部有几个只读表.他们实际上是视图，而不是基表，因此没有关联的文件与他们一起，你不能在他们身上设置触发器.另外，没有具有该名称的数据库目录.

Inside INFORMATION_SCHEMA there are several read-only tables. They are actually views, not base tables, so there are no files associated with them, and you cannot set triggers on them. Also, there is no database directory with that name.

虽然您可以选择 INFORMATION_SCHEMA 作为默认数据库使用 USE 语句，只能读取表的内容，不能读取对它们执行 INSERT、UPDATE 或 DELETE 操作.

Although you can select INFORMATION_SCHEMA as the default database with a USE statement, you can only read the contents of tables, not perform INSERT, UPDATE, or DELETE operations on them.

这篇关于如何更改 INFORMATION_SCHEMA 或添加触发器或外键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！