How to solve the problem of Mysql using on update current_timestamp

Mysql uses on update current_timestamp. Notes

In recent projects, the time field (datetime, timestamp) has used the ON UPDATE CURRENT_TIMESTAMP attribute to automatically update the column timestamp. . The following records some points that need attention.

Let’s talk about the conclusion first

If you set the time to update with the current timestamp (ON UPDATE CURRENT_TIMESTAMP), then when you update the field, only the field value changes, your time field will be It will change to the time during UPDATE operation.

That is, when there is no field update, although the update statement is executed successfully, the time field will not be updated.

For example, we create a new table:

CREATE TABLE `t_temp` (
  `id` int(11) NOT NULL DEFAULT '0',
  `username` varchar(255) DEFAULT NULL,
  `upd_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '默认取当前时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Then insert a row of data, and then perform an update.

UPDATE t_temp SET username = 'mm' WHERE id = 1;

You will find that the time column will not be updated.

Similarly, some frameworks are used in the program, such as tk-mybatis. When using the update method (such as int updateByPrimaryKeySelective(T record); method), if the field is not actually updated, although the syntax is executed, the time field It will not be updated, which is easy to overlook in the system.

CURRENT_TIMESTAMP() is related to ON UPDATE and index

Basic probability

CURRENT_TIMESTAMP() can set the TIMESTAMP type in MYSQL to the current time.

ON UPDATE personally thinks this is an artifact. It is generally used when a certain field is updateTIme and the last modified time is used. Just use this ON UPDATE. In this way, developers do not have to handle it themselves, MySQL can handle it automatically.

Finally, let’s talk about the index. This one comes from Baidu. I forgot who it is from: the index is a special file (the index on the InnoDB data table is a component of the table space) , they contain reference pointers to all records in the data table.

It can be said that a database index is like the table of contents of a book, which can increase the speed of database queries. Indexes are divided into clustered indexes and non-clustered indexes. Clustered indexes are ordered according to the physical location of data storage, but non-clustered indexes are different; clustered indexes can increase the speed of multi-row retrieval, while Non-clustered indexes are fast for retrieval of single rows.

If you create too many indexes, it will have an impact on the speed of update and insert operations, because each index file needs to be updated. For a table that frequently needs to be updated and inserted, there is no need to create a separate index for a rarely used where clause. For smaller tables, the sorting overhead will not be very large, and there is no need to create additional indexes.

Code and Example

The table creation code is as follows:

CREATE TABLE timeDemo(
 
	id VARCHAR(64) NOT NULL,
	timeTest TIMESTAMP NOT NULL,
	PRIMARY KEY (id)
);

When adding a new data:

He will automatically create a time, such as an order table, and if he wants to record the time when an order is placed, he can use this method to process it.

Including updateTime, the last update time:

CREATE TABLE timeDemo2(
 
	id VARCHAR(64) NOT NULL,
	createTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
	updateTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(),
	PRIMARY KEY (id)
 
);

//The above version of mysql5.7 or above will not have problems, but the version below 5.7 will have problems

// For versions below 5.7, it is recommended to use a program to insert.

Here we use 5.5 to demonstrate the effect of ON UPDATE

CREATE TABLE timeDemo3(
 
	id VARCHAR(64) NOT NULL,
	updateTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(),
	PRIMARY KEY (id)
);

When some data in this table is modified, the value of updateTime will be automatically updated.

#Every time this record is updated, updateTime will be updated automatically. In this case, the programmer does not need to handle it himself.

It can be found here that unfortunately many Internet companies like to use MySQL5.7 or above, while traditional industries still use MySQL5.5 version.

Let’s talk about index key and index

CREATE TABLE timeDemo4(
 
	id VARCHAR(64) NOT NULL,
	id2 VARCHAR(64) NOT NULL,
	updateTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(),
	PRIMARY KEY (id),
	KEY(id2)
)ENGINE=INNODB DEFAULT CHARSET=utf8

The only purpose of ordinary index is to improve the speed of data access. This kind of index is defined by the keyword KEY or INDEX. Therefore, indexes should only be created for those data columns that appear most frequently in query conditions (WHERE column = ...) or sorting conditions (ORDER BY column).

In order to create an index, you should choose the most regular and compact data column, such as an integer type data column, whenever possible.

The above is the detailed content of How to solve the problem of Mysql using on update current_timestamp. For more information, please follow other related articles on the PHP Chinese website!