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:

1. if I load file inserts_test_timestamp**1**.txt then the field insert_timestamp is set (that is ok with the code)

2. 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.

Answer 1

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 !