Mysql将具有特定列和unix时间戳的所有行从一个表复制到另一个表

Question

I am currently copying all rows from Table1 to Table2 each day. Table2 is a 7 day archive of what Table1 contains each day. On average there are 150,000 rows to move over each day.

Table1

id | name | category | image | description | link

Table2

id | name | link | date

To do this I use PHP to select all rows in Table1:

SELECT name, category, link FROM Table1

Then I loop through each one and insert it into Table2:

INSERT INTO Table2 SET name = ''.$row["name"].'', link = ''.$row["link"].'', date = time()

time() is stored as an int(11) in the database. Can anybody advise if this is there is a better or more efficient way of doing this?

Answer 1

You can use INSERT INTO SELECT rather than looping:

INSERT INTO Table2(name,link,date)
SELECT name, link, UNIX_TIMESTAMP() FROM Table1