批量转换和更新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:
-
使用
INT
数据类型创建新列,并将其命名为created
和updated
.
Create new columns with
INT
datatypes and name them, say,created
andupdated
.
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
.
created_at
and updated_at
.
替代方式:
Alternative way:
- 将
DATETIME
列设置为VARCHAR
字段. -
使用查询进行更新:
- Set the
DATETIME
columns toVARCHAR
field. Update using the query:
UPDATE `nameOfTable`
SET `created_at` = UNIX_TIMESTAMP( `created_at` ),
`updated_at` = UNIX_TIMESTAMP( `updated_at` );
INT
.
INT
.这篇关于批量转换和更新datetime列值到UNIX时间戳?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!