mysql - Set timestamp on insert when loading CSV -

this question has answer here:

• how can add date auto update when import data csv file? 2 answers

i have timestamp field defined automatically updated current_timestamp value.
works fine when fire query, when import csv (which i'm forced since 1 of fields longtext) , update not work.

i have tried to:

1. give timestamp column now() function in csv
2. manually enter timestamp 2013-08-08 in csv

both approaches not work

from gather, after updating question, you're updating rows using csv, , expect on update clause set value of timestamp field updated.
sadly, when loading csv database you're not updating, inserting data, , overwriting existing records. @ least, when using local infile, if infile isn't local, query produce error, if it's local file, these errors (duplicates) produce warnings , operation continue.

if isn't case you, perhaps consider following 1 of examples on doc pages:

load data infile 'your.csv'    table tbl       (field_name1, field_name2, field_name3)    set updated = now() fields terminated ','        optionally enclosed '"' lines terminated ('\n'); 

just in case can't/won't/forget add additional information, loading csv int mysql table quite easy:

load data    local infile '/path/to/file/filename1.csv' table db.tbl     fields terminated ','     optionally enclosed '"'     lines terminated '\n' (`field_name1`,`field_name2`,`field_name3`) 

if create table along lines of:

create table tbl(    id int auto_increment primary key, -- since previous question mentioned auto-increment    field_name1 varchar(255) not null primary key, -- normal fields    field_name2 integer(11) not null primary key,    field_name3 varchar(255) not null default '',     -- when not specified, field receive current_timestamp value:    inserted timestamp not null default current_timestamp,    -- if row updated, field hold timestamp of update-time    updated timestamp not null default 0                               on update current_timestamp )engine = innodb  character set utf8 collate utf8_general_ci; 

this query untested, please careful it, it's give general idea of need insert timestamp in there.
example table work so:

> insert tbl (field_name1, field_name2) values ('foobar', 123); > select tbl field_name1 = 'foobar' , field_name2 = 123; 

this show:

+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+ |          id         |     field_name1     |     field_name2     |     field_name3     |       inserted      |       updated       | +---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+ |           1         |        foobar       |         123         |                     | 2013-08-07 00:00:00 | 0000-00-00 00:00:00 | +---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+ 

as can see, because didn't explicitly insert value last 3 fields, mysql used default values. field_name3, empty string used, inserted, default current_timestamp, updated default value 0 which, because field-type timestamp represented value 0000-00-00 00:00:00. if run following query next:

update tbl    set field_name3 = 'an update' field_name1 = 'foobar'   , field_name2 = 123   , id          = 1; 

the row this:

+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+ |          id         |     field_name1     |     field_name2     |     field_name3     |       inserted      |       updated       | +---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+ |           1         |        foobar       |         123         |      update      | 2013-08-07 00:00:00 | 2013-08-07 00:00:20 | +---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+ 

that's all. basics can found here, on mysqltutorial.org, best keep the official manual ready. it's not bad once used it.
perhaps this question might worth quick peek, too.