是否有MySQL功能(例如SQL Server的TIMESTAMP列)? [英] Is there a MySQL feature like SQL Server's TIMESTAMP column?

问题描述

我的初步研究说不".

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

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时间戳中看到的是行版本类似于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也不支持像Oracle或PostgreSQL或IBM DB2这样的SEQUENCE对象.例如，序列可以使您从触发器生成新值.

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中用来模拟序列对象的另一种技巧是一个表，该表包含一个自动递增的主键，仅此而已.您可以通过插入此表来生成新的唯一的单调递增的值，然后回滚插入.

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.

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