如何更改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.schemata(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屋！