So, I got this dummy table for a showcase demo project I just built, so I started uploading posts in a sequential order, is there a way to modify or alter the datetime column to simply change it to random dates?
所以，我为我刚刚构建的展示演示项目获得了这个虚拟表，所以我开始按顺序上传帖子，有没有办法修改或更改datetime列，只需将其更改为随机日期？
title | content | date
title1 | xxxxxxxxx | 2017-09-07 16:49:57
title2 | xxxxxxxxx | 2017-09-07 16:49:57
So... what I'd like to do is to simply run a query so the date column stays something like
所以...我想要做的只是运行一个查询，以便日期列保持类似
title | content | date
title1 | xxxxxxxxx | 2017-06-06 12:13:01 <- random generated date
title2 | xxxxxxxxx | 2017-19-07 21:37:57 <- random generated date
1 个解决方案
#1
1
You need to have a PRIMARY KEY (i.e. id) to do this with a self join update query.
您需要有一个PRIMARY KEY（即id）才能使用自联接更新查询执行此操作。
If not, you can use the title instead to match the rows, but it would result in all posts with the same title ending up with the same random date:
如果没有，您可以使用标题来匹配行，但这会导致所有具有相同标题的帖子以相同的随机日期结束：
/* convert date range to seconds to get an INT to express randomization range */
SET @min := UNIX_TIMESTAMP(STR_TO_DATE('Apr 15 2009 12:00AM', '%M %d %Y %h:%i%p'));
/* subtract the max ID from the range so we can add the ID later to the range
* without getting out of range values */
SET @max := UNIX_TIMESTAMP(STR_TO_DATE('Sep 08 2017 01:00AM', '%M %d %Y %h:%i%p')) - (SELECT MAX(id) FROM table);
/* join a second reference of the table as a source for the update */
UPDATE table AS target JOIN table AS source ON source.id = target.id
/* and add the source ID to the range to force the optimizer to calculate
* a random number for each row independently */
SET target.date = FROM_UNIXTIME( ROUND((RAND()*(@max - @min + source.id))+@min) )
/* dont forget to link source and destination with the primary key! */
WHERE source.id = target.id
#1
1
You need to have a PRIMARY KEY (i.e. id) to do this with a self join update query.
您需要有一个PRIMARY KEY（即id）才能使用自联接更新查询执行此操作。
If not, you can use the title instead to match the rows, but it would result in all posts with the same title ending up with the same random date:
如果没有，您可以使用标题来匹配行，但这会导致所有具有相同标题的帖子以相同的随机日期结束：
/* convert date range to seconds to get an INT to express randomization range */
SET @min := UNIX_TIMESTAMP(STR_TO_DATE('Apr 15 2009 12:00AM', '%M %d %Y %h:%i%p'));
/* subtract the max ID from the range so we can add the ID later to the range
* without getting out of range values */
SET @max := UNIX_TIMESTAMP(STR_TO_DATE('Sep 08 2017 01:00AM', '%M %d %Y %h:%i%p')) - (SELECT MAX(id) FROM table);
/* join a second reference of the table as a source for the update */
UPDATE table AS target JOIN table AS source ON source.id = target.id
/* and add the source ID to the range to force the optimizer to calculate
* a random number for each row independently */
SET target.date = FROM_UNIXTIME( ROUND((RAND()*(@max - @min + source.id))+@min) )
/* dont forget to link source and destination with the primary key! */
WHERE source.id = target.id