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

The article is reproduced from the official Wechat account of "developer Round Table", which is about the introduction, advancement and trampling of developers.

Many developers of OLTP applications know that some important operations need to record the operation history, back up the data before the operation to the history table, and then perform the corresponding modification operation. In this way, you can get the operation log and the record value before the operation at a certain point.

There are two levels to record the operation history, one is the application layer, that is, the preservation of history records through the application logic, and the other is the database layer, that is, database archiving, audit and other DBA management functions. Only the former is discussed here.

For example, there is a user table t_user that contains id,name,sex,age fields, because the modification of the t _ user table is very important. To record the operation history, the general approach is to establish a history table named t_user_his with the same structure as t_user, which contains id,name,sex,age fields, while adding the corresponding his_id,oper_time,oper_remark and other fields of record operation instructions.

OK, the table has been designed. We need to record the previous information of the user when we modify it. The general practice is to insert an insert into the history table, and then modify the data of the original table, and put these two operations into the same transaction. The definition code is as follows:

SetAutoCommit (false)

Insert into t_user_his (id,name,sex,age,his_id,oper_time,oper_remark) select id,name,sex,age,' history table id',timestamp,' modified name 'from t_user where id=18970

Update t_user set name='xxx' where id=18970

Commit ()

Deployment online everything works well, the leader is very satisfied, suddenly one day need to add a phone field in the t_user to record the user's phone number, the only constant in the software product is the following word

We have no choice but to modify it. We need to follow the following steps:

First, modify the t_user table to add a phone field.

Second, modify the t_user_his table to add a phone field.

Third, modify the SQL that records the operation history involved. If there are multiple points, you also need to modify it as well:

SetAutoCommit (false)

Insert into t_user_his (id,name,sex,age,phone,his_id,oper_time,oper_remark) select id,name,sex,age,phone,' history table id',timestamp,' modified name 'from t_user where id=18970

Update t_user set name='xxx' where id=18970

Commit ()

Finally, the compiler, test, commit, deployment online, a process can take many days to come down.

Here comes the problem. Is this the only way to deal with it? How to avoid the repetitive modification caused by field modification? Is there a better solution? The answer is yes, it can be done with a simple modification.

We can design the fields of the tweak username _ his table in this way, leading the fields related to historical operations, and keeping the subsequent fields exactly the same as t_user. For example, the t_user_his field can be arranged like this: his_id,oper_time,oper_remark,id,name,sex,age.

The application is modified as follows:

SetAutoCommit (false)

Insert into t_user_his select, 'history table id',timestamp,' modified name', a.* from t_user a where id=18970

Update t_user set name='xxx' where id=18970

Commit ()

In that case, what should I do to add a new phone field?

First, modify the t_user table to add a phone field.

Second, modify the t_user_his table to add a phone field in the same order as the t_user field.

Well, fields can be added, modified, or deleted freely in two steps, without modifying any application code, nor complex deployment, testing, and online processes, but only in the database.

In the above method, to modify a field, it is necessary to record the whole data to the history table, which will take up more space, or write the field to death. How to choose or analyze the business requirements of the system, not blindly copy.

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.