Load text file into mysql database and add timestamp to each row

Question

I have a text file that has serial numbers on each line such as:

7656393835734594
8457457435983945
3489534689856950
4596859684560498

I want to add this text file into a mysql table using PHP. My table looks like this:

serials table
    id           bigint(20) auto_increment
    serial       varchar(16)    
    timestamp    timestamp

How can I add each line from the text file into the serial column and also add the current timestamp to each row?

Here is what I have already tried:

$file = "serials.txt";
$conn = mysqli_connect('localhost','root','root');
mysqli_select_db($conn, 'myDB');
mysqli_query($conn, "LOAD DATA INFILE '".$file."' INTO TABLE serials");

This code does not do anything let alone add the data and the timestamp.

Answer 1

I think that the simplest approach is to define a default value for the timestamp column:

create table serials (
    id bigint primary key auto_increment,
    serial varchar(16),
    ts timestamp default current_timestamp
)

Note that I renamed to timestamp column to some name that does not conflict with a language keyword.

You can then use the load data syntax. An important thing is that you need to provide a column list, since not all table columns are given for insert:

load data infile 'myfile.txt' into table serials(serial)

On the other hand, if you can't modify the table definition for some reason, an alternative is to use the set clause to provide the derived value:

load data infile 'myfile.txt' into table serials(serial)
set ts = current_timestamp