ON UPDATE CURRENT_TIMESTAMP column attribute

Feature introduction

ON UPDATE CURRENT_TIMESTAMP is a column attribute in MySQL-compatible mode that automatically sets the column value to the current timestamp when a row is updated (UPDATE). It is primarily used to record the last modification time of a data row.

In OceanBase Database, both TIMESTAMP and DATETIME columns support this attribute, and the behavior is consistent with MySQL.

Feature description

When a row in a table is updated, the system automatically sets the column with the ON UPDATE CURRENT_TIMESTAMP attribute to the current timestamp, without requiring the application to explicitly assign a value. This attribute is typically used together with DEFAULT CURRENT_TIMESTAMP: DEFAULT CURRENT_TIMESTAMP records the creation time during INSERT, and ON UPDATE CURRENT_TIMESTAMP automatically maintains the last modification time during updates.

Applicable scenarios

• Audit logs: Record the last modification time of data and track data change history
• Data synchronization: Automatically maintain the update time of records for incremental synchronization
• Business table design: Scenarios where the last modification time of a row needs to be recorded, reducing the development workload of applications

Syntax

Specify in the column definition of the CREATE TABLE statement. The syntax is as follows:

column_name TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Specifying timestamp precision is also supported. The precision value must be consistent with the precision in the column definition:

column_name TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)

The usage for DATETIME columns is the same:

column_name DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Note

• This attribute applies only to TIMESTAMP and DATETIME columns. Other types of columns are not supported.
• The precision after ON UPDATE CURRENT_TIMESTAMP must be consistent with the precision in the column definition. Otherwise, an error is reported. For example, when the column is defined as TIMESTAMP(6), you need to use ON UPDATE CURRENT_TIMESTAMP(6).
• In addition to CREATE TABLE, this attribute is also supported in ALTER TABLE operations such as ADD COLUMN, MODIFY COLUMN, and CHANGE COLUMN.

Examples

Create a table that includes creation time and modification time columns:

obclient> CREATE TABLE t1 (
    id INT PRIMARY KEY,
    c1 VARCHAR(20),
    gmt_create TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    gmt_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
Query OK, 0 rows affected

obclient> INSERT INTO t1(id, c1) VALUES (1, 'a');
Query OK, 1 row affected

obclient> SELECT id, c1, gmt_create, gmt_modified FROM t1;
+----+------+---------------------+---------------------+
| id | c1   | gmt_create          | gmt_modified        |
+----+------+---------------------+---------------------+
|  1 | a    | 2026-08-20 15:38:28 | 2026-08-20 15:38:28 |
+----+------+---------------------+---------------------+
1 row in set

obclient> UPDATE t1 SET c1 = 'b' WHERE id = 1;
Query OK, 1 row affected

obclient> SELECT id, c1, gmt_modified FROM t1;
+----+------+---------------------+
| id | c1   | gmt_modified        |
+----+------+---------------------+
|  1 | b    | 2026-08-20 15:38:30 |
+----+------+---------------------+
1 row in set

From the result, you can see that after executing UPDATE, the value of the gmt_modified column is automatically updated to the current timestamp, while the gmt_create column remains unchanged.

Example with precision:

obclient> CREATE TABLE t2 (
    c1 TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);
Query OK, 0 rows affected

Considerations

• MySQL-compatible mode exclusive: This attribute is a feature of MySQL-compatible mode. Oracle-compatible mode does not support it.
• Consistent precision: The precision of ON UPDATE CURRENT_TIMESTAMP must be consistent with the precision in the column definition. Otherwise, an error is reported.
• Use with DEFAULT CURRENT_TIMESTAMP: It is recommended to specify DEFAULT CURRENT_TIMESTAMP at the same time to ensure an initial value during insertion.
• Implicit default attribute: When the system variable explicit_defaults_for_timestamp is OFF (the default value), if the first TIMESTAMP column in the table does not explicitly specify the NULL attribute and default value, the system automatically adds DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes to the column. For more information about this variable, see explicit_defaults_for_timestamp.
• Triggered only when data changes: The ON UPDATE CURRENT_TIMESTAMP column is updated only when the UPDATE actually changes the values of other columns in the row. If the row data does not change after the update (for example, UPDATE t1 SET c1 = c1), this column is not refreshed.
• Parallel DML restriction: Tables containing ON UPDATE CURRENT_TIMESTAMP columns do not support parallel DML. For more information, see Classification of parallel execution.