Two or three things about timestamp _ MySQL

Source: Internet
Author: User
The reason why I want to write timestamp casually is that there is a misunderstanding of timestamp, so. ihavetoremindmyselfbywritingthisinformalessay. microsoft document link: msdn. microsoft. comzh-cnlibraryms182776.aspx release... the reason why I want to write timestamp casually is that there is a misunderstanding of timestamp, so. I have to remind myself by writing this informal essay.

Microsoft document link: https://msdn.microsoft.com/zh-cn/library/ms182776.aspx

Meaning (rowversion and timestamp are the same as those in C # (String and string are conceptual synonyms, but they are written differently). However, Microsoft recommends rowversion, there are still some operations on the database that have not been studied in depth for the moment. you can query the following hyperlink to see ):

Specifies the data type of the unique binary number automatically generated in the public database. Rowversion is usually used to add version stamps to table rows. The storage size is 8 bytes. The rowversion data type is only an incremental number and does not retain the date or time. To record the date or time, use the datetime2 data type.

Note

Each database has a counter. when you insert or update a table that contains the rowversion column in the database, the counter value increases. This counter is the row version of the database. This can track the relative time in the database, rather than the actual time associated with the clock. A table can have only one rowversion column. Each time you modify or insert a row that contains the rowversion column, the incremental database row version value is inserted in the rowversion column. This attribute makes rowversion columns not suitable for use as keys, especially those that cannot be used as primary keys. Any update to a row changes the row version value to change the key value. If the column belongs to the primary key, the old key value is invalid, and the foreign key that references the old value is no longer valid. If the table is referenced in a dynamic cursor, all updates change the row position of the cursor. If this column belongs to the index key, all updates to the data row will also lead to index updates.

The data type of timestamp is a synonym of the rowversion data type and acts as a synonym of the data type. In DDL statements, use rowversion instead of timestamp. For more information, see data type synonyms (Transact-SQL ).

The Transact-SQL timestamp data type is different from the timestamp data type defined in the ISO standard.

From the meaning above, I realized that there is no relationship between the timestamp and the date and time. It is only a binary value. Hi... timestamp everyone knows it is used for concurrency control, but how can we implement it .?

Nothing left: Let's take a look at how Microsoft introduces timestamp concurrency control: supplement the query timestamp of the current database: SELECT @ DBTS

CREATE TABLE MyTest2 (myKey int PRIMARY KEY    ,myValue int, TS timestamp);GO INSERT INTO MyTest2 (myKey, myValue) VALUES (1, 0);GO INSERT INTO MyTest2 (myKey, myValue) VALUES (2, 0);GODECLARE @t TABLE (myKey int);UPDATE MyTest2SET myValue = 2    OUTPUT inserted.myKey INTO @t(myKey) WHERE myKey = 1     AND TS = myValue;IF (SELECT COUNT(*) FROM @t) = 0    BEGIN        RAISERROR ('error changing row with myKey = %d'            ,16 -- Severity.            ,1 -- State             ,1) -- myKey that was changed     END;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.