批量转换和更新datetime列值到UNIX时间戳? [英] Bulk convert and update datetime column values to UNIX timestamp?

问题描述

背景

我有一个大约有1600万条记录的MySQL数据库.目前有2列created_at和updated_at以datetime格式显示.

I have a MySQL db with around 16 million records. There are 2 columns created_at and updated_at which are presently in datetime format.

问题

我想通过转换所有值并使用新值更新记录来将其更改为UNIX Timestamp.

I'd like to change this to UNIX Timestamp by converting all the values and updating the records with the new values.

我知道如何使用PHP循环执行此操作，但是有没有办法通过在MySQL中执行单个查询来执行此更新?

I know how to do this with PHP in loops, but is there a way to perform this update by executing a single query in MySQL?

推荐答案

因为这将是一次更改；您可以按照以下方式进行操作:

As it'll be a one time change; you can proceed this way:

1. 使用INT数据类型创建新列，并将其命名为created和updated.

1. Create new columns with INT datatypes and name them, say, created and updated.

ALTER TABLE `nameOfTable`
    ADD COLUMN `created` INT UNSIGNED NOT NULL DEFAULT '0' AFTER `created_at`,
    ADD COLUMN `updated` INT UNSIGNED NOT NULL DEFAULT '0' AFTER `updated_at`;

更新表:

Update table:

UPDATE `nameOfTable`
SET `created` = UNIX_TIMESTAMP( `created_at` ),
    `updated` = UNIX_TIMESTAMP( `updated_at` );

删除旧列，并将较新的列重命名为created_at和updated_at.

Remove the older columns and rename newer ones back to created_at and updated_at.

---

替代方式:

---

Alternative way:

1. 将DATETIME列设置为VARCHAR字段.

2. 使用查询进行更新:

1. Set the DATETIME columns to VARCHAR field.

2. Update using the query:

UPDATE `nameOfTable`
SET `created_at` = UNIX_TIMESTAMP( `created_at` ),
    `updated_at` = UNIX_TIMESTAMP( `updated_at` );

将列更改为INT.

Change the columns to INT.

这篇关于批量转换和更新datetime列值到UNIX时间戳?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！