MySQL Global触发连接

Is it possible to create a trigger that will listen for any insert or update for one database connection/instance (all schemas)?

是否可以创建一个触发器来监听一个数据库连接/实例(所有模式)的任何插入或更新?

Here is my business case:

这是我的商业案例:

I have multiple schemas on one MySQL server.

我在一台MySQL服务器上有多个模式。

Each of these schemas has multiple tables.

这些模式中的每一个都有多个表。

All of the tables have a 'lastChanged' datetime column.

所有表都有一个'lastChanged'datetime列。

I need to update the 'lastChanged' column in a table whenever an update or insert occurs on that table.

每当在该表上发生更新或插入时,我需要更新表中的'lastChanged'列。

So to summarize, I am looking to see if it is possible to create a global rule that would make it so I do not need to create a trigger for every table, in every schema.

总而言之,我希望看看是否有可能创建一个全局规则来实现它,这样我就不需要在每个模式中为每个表创建一个触发器。

I haven't much experience with triggers, so if you think of something other than a trigger as the answer for catching these events and updating rows in corresponding tables that would be a fantastic answer as well!!

我对触发器的经验不多,所以如果你想到触发器以外的其他东西作为捕获这些事件和更新相应表中的行的答案,这也是一个很棒的答案!

1 个解决方案

#1

You can't do this with a single trigger.

单个触发器无法执行此操作。

However, if you change the lastChanged column from DATETIME to TIMESTAMP then you can use the DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP feature to set the value automatically on any insert or update.

但是,如果将lastChanged列从DATETIME更改为TIMESTAMP,则可以使用DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP功能在任何插入或更新时自动设置值。

One tradeoff is that the TIMESTAMP data type can only support values from 1970 to 2038, so you can't use that if you need to store values outside that range.

一个权衡是TIMESTAMP数据类型只能支持从1970到2038的值,因此如果需要存储超出该范围的值,则不能使用它。

Since you are looking to make this as simple as possible, and you said all of the tables already have a column named lastChanged, you can consider using information_schema to generate the alter table statements you need, and then execute them all at once.

由于您希望尽可能简单,并且您说所有表都已经有一个名为lastChanged的列,您可以考虑使用information_schema生成所需的alter table语句,然后立即执行它们。

Something like this:

像这样的东西:

select concat('alter table ',
   t.table_schema,
   '.',
   t.table_name,
   ' modify column ',
   c.column_name,
   ' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;') as ddl
into outfile '/tmp/lastChanged.sql'
from information_schema.columns c
  inner join information_schema.tables t 
    on t.table_schema = c.table_schema and t.table_name = c.table_name
where c.table_schema = database()
and c.data_type = 'datetime'
and c.column_name = 'lastChanged'
and t.table_type = 'base table';

\. /tmp/lastChanged.sql

#1