MySQL bulk insert timestamp

[Dani]

If a bulk MySQL insert takes 5 minutes to complete, and one of the columns in the table is a TIMESTAMP column with a default value of CURRENT TIMESTAMP, will the timestamps of the rows that were inserted be reflective of the entire 5 minutes, or will they all default to the same timestamp from beginning or the end of the query? The bulk insert exists within a transaction.

[Reverend Jim]

I found it was safe to not rely on default behaviour. Pre-2008 (before retirement) the scripts I wrote/maintained had to bulk insert 8000+ records at a time. In order to maintain consistency I would massage the data and add a date/time field to the records. That way I could guarantee the accuracy of the date/time without having to rely on SQL. Also, any delay in the process would not affect the timestamps.

[Dani]

For my purposes, I actually need the timestamps for each row to reflect the time that specific row was inserted, spanning over the five minutes it would take to do the bulk insert. It’s actually an INSERT ... SELECT of about 200,000 rows. Obviously doing it as 200,000 individual queries is not efficient.

[pty]

MySQL binds current_timestamp()/now() at the beginning of the statement so I believe all of your records would have the same created_at value.

If you wanted them to differ I think you'd need to use a cursor and separate insert statements.