是否有像 SQL Server 的 TIMESTAMP 列这样的 MySQL 功能? [英] Is there a MySQL feature like SQL Server's TIMESTAMP column?

问题描述

我的初步研究说不".

我在 SQL Server 中有一个数据库应用程序，它使用 SQL Server 时间戳列来跟踪更改....也就是说，我可以浏览该表并知道如果我看到一个时间戳 > 而不是某个参考时间戳，那么行已添加或更新.

I have a database app in SQL Server that uses the SQL Server timestamp column for tracking changes.... that is, I can go through the table and know that if I see a timestamp > than some reference timestamp, that the row has been added or updated.

我正在 MySQL 中寻找类似的类型.它似乎不存在.那么，我是否必须使用触发器(好)或应用程序逻辑(坏)来复制此功能?

I'm looking for a type like that in MySQL. It doesn't seem to exist. So, will I have to replicate this functionality using either triggers (good) or application logic (bad)?

请注意，仅使用 MySQL 的 TIMESTAMP 并不是一个很好的解决方案，因为它并不总是唯一的，并且不会随时间单调增加.

Note that just using MySQL's TIMESTAMP isn't really a great solution, as it's not always unique, and not monotonically increasing over time.

编辑 --- 这是我多年来一直使用这些的场景......

EDIT --- here's the scenario I've been using these for, for years...

我在 OLTP 系统中有一个表，它是库存的当前状态.我有一个前端 GUI，它显示库存的不同视图.我想偶尔去问数据库哪些行是新的/更改的".使用 SQL Server 的 TIMESTAMP/ROWVERSION，我可以轻松做到这一点.如果我使用实际日期类型，我总是会遇到问题，我要么多次更新(不是大灾难)，要么偶尔错过更新(灾难).

I have a table in a OLTP system which is the current state of the inventory. I have a front end GUI that's displaying different views of the inventory. I'd like to occasionally go and ask the DB "which rows are new/changed". With SQL Server's TIMESTAMP/ROWVERSION, I could do that easily. If I use an actual date type, I would always run into problems where I either got an update more than once (not a huge disaster), or occasionally missed an update (a disaster).

推荐答案

我不确定我是否理解为什么这不适用于您的方案.TIMESTAMP 是否保证在 MS SQL Server 中是唯一的?

I'm not sure I understand why this doesn't work for your scenario. Is TIMESTAMP guaranteed to be unique in MS SQL Server?

啊哈——我在 SQL Server 中看到时间戳是 rowversion 有点像 MySQL 的 AUTO_INCREMENT，除了它在 UPDATE 和 INSERT 上生成一个新的单调递增的值.

Aha -- I see in SQL Server timestamp is a synonym for rowversion which is sort of like MySQL's AUTO_INCREMENT except it generates a new monotonically increasing value on UPDATE as well as on INSERT.

不，MySQL 没有这样的东西.MySQL 也不支持 SEQUENCE 对象，如 Oracle 或 PostgreSQL 或 IBM DB2.例如，序列将允许您从触发器生成新值.

No, MySQL doesn't have anything like this. Nor does MySQL support a SEQUENCE object like Oracle or PostgreSQL or IBM DB2. A sequence would allow you to generate a new value from a trigger, for instance.

我在您的问题中阅读了您的更新信息，因此我了解您要解决的问题.

I read your updated info in your question, so I understand the problem you are trying to solve.

我所看到的替代解决方案是在您正在处理的表中添加另一个属性，将其命名为 is_processed 或其他名称.插入新行时输入 NULL.当您开始处理它时，将该列中的值更改为 1.然后您将始终知道哪些行尚未处理——它们将是 is_processed IS NULL 的行.

What I have seen as an alternative solution is to add another attribute in the table you're processing, call it is_processed or something. Enter a NULL when you insert a new row. When you get around to processing it, change the value in that column to 1. Then you'll always know which rows you have yet to process -- they'll be the rows where is_processed IS NULL.

回复您的评论:好的，我看到了问题所在.每个用户都需要自己的视图来了解哪些行是新的/更改的.

Re your comment: Okay, I see the problem. Each user needs their own view of which rows are new/changed.

我在 MySQL 中看到的另一个用于模拟序列对象的 hack 是一个包含自动递增主键的表，没有其他任何内容.您可以通过插入到此表中来生成新的唯一单调递增值，然后回滚插入.

Another hack I've seen used in MySQL to simulate a sequence object is a table that contains an auto-increment primary key and nothing else. You can generate new unique monotonically increasing values by inserting into this table, and then roll back the insert.

CREATE TABLE sequence (id SERIAL) ENGINE=InnoDB; -- Must be InnoDB

START TRANSACTION;
INSERT INTO sequence () VALUES (); -- Yes this is a legal statement.
ROLLBACK;

SELECT LAST_INSERT_ID();

您无法在 MySQL 触发器内启动和回滚事务，因此您必须在应用程序代码中生成新值.

You can't start and rollback transactions within a MySQL trigger, so you'll have to generate the new values in your application code.

或者你可以切换到 PostgreSQL，并获得对序列的真正支持.

Or else you could switch to PostgreSQL, and get real support for sequences.

这篇关于是否有像 SQL Server 的 TIMESTAMP 列这样的 MySQL 功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！