问题描述
我正在尝试创建一些元数据来扩展 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 内部有几个只读表.他们实际上是视图，而不是基表，因此没有关联的文件与他们一起，你不能在他们身上设置触发器.另外，没有具有该名称的数据库目录.
虽然您可以选择 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 或添加触发器或外键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！