Shulou(Shulou.com)06/01 Report--

This article introduces you PostgreSQL MySQL line version management and SQL SERVER timestamp line version management comparison example analysis, the content is very detailed, interested friends can refer to, hope to be helpful to you.

When it happened, a long time ago, SQL SERVER had a field type called timestamp, compared to something that row version automates management that no other database has. This thing powerful place, although it may seem to be a time field, but in fact it is not, as long as you change any row of the SQL SERVER table, then you can rest assured that the value of that field will automatically change, so that you can first take out the timestamp value of this line in the program through this field, and then according to business logic, if you need some time to change this line or have you ever changed it? Compare with the current timestamp field value, that can properly tell you whether any field of this row of data has changed, some people say that MYSQL also has timestamp, that field is through time to update as long as this line changes to trigger timestamp change time on it, of course, datetime is also OK, the early version is not.

I can only respond with a hum, too young to simple. The sql server field of others is not time, there is no possibility of repetition of milliseconds, subtleties and so on given by you, because they are the only values, a string of binary systems that humans do not understand.

So MYSQL uses timestamp and then has to build a trigger, and then it's not necessarily accurate.

So that's it, NO NO NO.

As long as we know the general principle, PostgreSQL and MYSQL can be used to do our own row version management.

First of all, PG, PG is much more convenient than MYSQL, because of his natural genes, it is very simple for him to do this thing. PG's row versioning is based on system column, and one of the fields is ctid.

Here are the official documents

This means that the row version of the ctid field in the table indicates the physical location of the row. Note that although you can use ctid to locate the row version very quickly, it changes if the ctid of a row is updated or moved. Therefore, ctid is useless as a long-term line identifier. You should use OID, or a user-defined serial number, to identify logical lines.

So, what we want is that your ctid can be located quickly and will change if it is updated or moved. This is equivalent to the fact that postgresql naturally gives you a GPS on each row, and then whenever the update, or the data moves, for example, copy a table to another place, etc. Then this ctid will change.

You can look at the picture below.

Through the ctid included with this system, it is very clear whether the UPDATE operation has been done on this line, as long as it is done, and if the updated value is the same as the original value, the value will be updated.

Some of the principles of PG have been revealed from here.

So the row versioning of PG is so simple that it is easier to complete than SQL SERVER, and the performance is higher, which is native. As long as you get the ctid of the line you care about and then take the value once when you want to use it, it must be the same that the line has not been moved, which proves that the field you care about must not have changed.

Now it's MYSQL's turn. In fact, MYSQL itself does not have a physical location tag like POSTGRESQL, nor does it have a timestamp field like SQL SERVER. But people who use MYSQL are always clever, thoughtful, and have ways to do it.

In fact, sql server timestamp wants to add a counter to a table for each row, while Postgresql has a "GPS" positioning system for each row.

MYSQL, we can.

1 and the development team define a display value to judge the field change of a row, that is, to add a field to simulate the timestamp field of sql server, and to discuss with the development team what type to use and what value to insert.

2 in any DML statement, you need to attach an update to this field as long as there is a UPDATE

Let's take a look, we're going to add a field to the current table, and it doesn't matter what field type it is, it's just a simulation. Checksum is the added field binary

After adding the field, any change in the value of the additional field will change the value of the additional field. For specification, we can use a function, (the quality of the function should be high, otherwise it may affect performance)

We can take a look at the results and why we chose this weird thing, because no one will "make mistakes" to change our checksum field to avoid some "human error".

Through some of the above, we can also see that each kind of database actually has its own characteristics, mastering the principle, and can simulate the principle, it can make up for some of the functions that it does not provide, or born with special functions.

Maybe some people say, why not set default value for MYSQL so that I don't have to bring that field when I don't have to go to insert, or go back to using triggers or something.

1 using MYSQL is for higher concurrency, using triggers is not suitable for our original intention.

2 if some default values are set, and in the case of a non-single library, there will be a similar error prompt

This is the example analysis of the comparison between PostgreSQL MySQL line version management and SQL SERVER timestamp line version management. I hope the above content can be of some help and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.