Timestamp field only on insert in MariaDB, combined with 'LOAD DATA LOCAL INFILE' data load

Question

I want a timestamp field in MySQL table, to be set only on inserts, not on updates. The table created like that:

CREATE TABLE `test_insert_timestamp` (
  `key` integer NOT NULL,
  `value` integer NOT NULL,
  `insert_timestamp` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`key`)
);

The data is loaded with this sentence (need to be used LOAD DATA LOCAL INFILE):

LOAD DATA LOCAL INFILE 
   "inserts_test_timestamp1.txt"
REPLACE
INTO TABLE 
    `test_insert_timestamp`
FIELDS TERMINATED BY ';'

Note: I need to use REPLACE option, not matter why. The content of inserts_test_timestamp**1**.txt been:

1;2
3;4

I have another file inserts_test_timestamp**2**.txt been:

3;4
5;6

What I wont is:

if I load file inserts_test_timestamp**1**.txt then the field insert_timestamp is set (that is ok with the code)
if I load inserts_test_timestamp**2**.txt, record (3;4) don't change field insert_timestamp already set, but record (5;6) set new insert_timestamp.

But no way. Both records are timestamped with same value, instead of left (3;4) with the old timestamp.

I'm working on MariaDB 5.5.52 database over CentOS 7.3 release. Think that MariaDB version is important, but I can't change that.

The question can be summarized like that: are there any way to use LOAD DATA LOCAL INFILE... REPLACE... in a table with a timestamp field that only records insert time (no update time)? — framontb, Mar 22 '18 at 16:04
There is no `UPDATE`, there is a `DELETE` and an `INSERT`. `REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.`, see [REPLACE](https://mariadb.com/kb/en/library/replace/). — wchiquito, Mar 24 '18 at 15:15

score 1 · Answer 1 · answered Mar 24 '18 at 17:03

You can divide the process in two steps:

MariaDB [_]> DROP TABLE IF EXISTS
    ->   `temp_test_insert_timestamp`,
    ->   `test_insert_timestamp`;
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]> CREATE TABLE IF NOT EXISTS `test_insert_timestamp` (
    ->   `key` integer NOT NULL,
    ->   `value` integer NOT NULL,
    ->   `insert_timestamp` timestamp DEFAULT CURRENT_TIMESTAMP,
    ->   PRIMARY KEY (`key`)
    -> );
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]> CREATE TABLE IF NOT EXISTS `temp_test_insert_timestamp` (
    ->   `key` integer NOT NULL,
    ->   `value` integer NOT NULL,
    ->   `insert_timestamp` timestamp DEFAULT CURRENT_TIMESTAMP,
    ->   PRIMARY KEY (`key`)
    -> );
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]> LOAD DATA LOCAL INFILE '/path/to/file/inserts_test_timestamp1.txt'
    -> INTO TABLE `test_insert_timestamp`
    -> FIELDS TERMINATED BY ';'
    -> (`key`, `value`);
Query OK, 2 rows affected (0.00 sec)                 
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [_]> SELECT
    ->   `key`,
    ->   `value`,
    ->   `insert_timestamp`
    -> FROM
    ->   `test_insert_timestamp`;
+-----+-------+---------------------+
| key | value | insert_timestamp    |
+-----+-------+---------------------+
|   1 |     2 | 2018-03-20 00:49:38 |
|   3 |     4 | 2018-03-20 00:49:38 |
+-----+-------+---------------------+
2 rows in set (0.00 sec)

MariaDB [_]> DO SLEEP(5);
Query OK, 0 rows affected (5.00 sec)

MariaDB [_]> LOAD DATA LOCAL INFILE '/path/to/file/inserts_test_timestamp2.txt'
    -> INTO TABLE `temp_test_insert_timestamp`
    -> FIELDS TERMINATED BY ';'
    -> (`key`, `value`);
Query OK, 2 rows affected (0.00 sec)                 
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [_]> SELECT
    ->   `key`,
    ->   `value`,
    ->   `insert_timestamp`
    -> FROM
    ->   `temp_test_insert_timestamp`;
+-----+-------+---------------------+
| key | value | insert_timestamp    |
+-----+-------+---------------------+
|   3 |     4 | 2018-03-20 00:49:43 |
|   5 |     6 | 2018-03-20 00:49:43 |
+-----+-------+---------------------+
2 rows in set (0.00 sec)

MariaDB [_]> REPLACE INTO `test_insert_timestamp`
    -> SELECT
    ->   `ttit`.`key`,
    ->   `ttit`.`value`,
    ->   `tit`.`insert_timestamp`
    -> FROM
    ->   `temp_test_insert_timestamp` `ttit`
    ->   LEFT JOIN `test_insert_timestamp` `tit`
    ->   ON `ttit`.`key` = `tit`.`key`;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [_]> SELECT
    ->   `key`,
    ->   `value`,
    ->   `insert_timestamp`
    -> FROM
    ->   `test_insert_timestamp`;
+-----+-------+---------------------+
| key | value | insert_timestamp    |
+-----+-------+---------------------+
|   1 |     2 | 2018-03-20 00:49:38 |
|   3 |     4 | 2018-03-20 00:49:38 |
|   5 |     6 | 2018-03-20 00:49:43 |
+-----+-------+---------------------+
3 rows in set (0.00 sec)

MariaDB [_]> TRUNCATE TABLE `temp_test_insert_timestamp`;
Query OK, 0 rows affected (0.00 sec)

score 0 · Accepted Answer · answered Mar 26 '18 at 08:21

I implement the solution in this post: MySQL LOAD DATA INFILE with ON DUPLICATE KEY UPDATE

This solution not only allows me to get the insert_timestamp, but also a field with update_timestamp:

# --- Create temporary table ---
CREATE TEMPORARY TABLE temporary_table LIKE test_insert_timestamp;

# --- Delete index to speed up
DROP INDEX `PRIMARY` ON temporary_table;
DROP INDEX `STAMP_INDEX` ON temporary_table;

# --- Load data in temporary table
LOAD DATA LOCAL INFILE "./inserts_test_timestamp1.txt"
INTO TABLE temporary_table
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
IGNORE 1 LINES
SET
        insert_timestamp = CURRENT_TIMESTAMP(),
        update_timestamp = NULL
;

# --- Insert data in temporary table ---
INSERT INTO test_insert_timestamp
SELECT * FROM temporary_table
ON DUPLICATE KEY UPDATE
   update_timestamp = CURRENT_TIMESTAMP();

# --- Drop temporary
DROP TEMPORARY TABLE temporary_table;

Thanks for help !

Timestamp field only on insert in MariaDB, combined with 'LOAD DATA LOCAL INFILE' data load

2 Answers2