mysql - 随机更新行

有时我表中有超过一百万行。

每批将有100,000个随机行。

随机化行的最佳方法是什么？

我执行以下操作：

UPDATE  temp1 SET  `batch` =  '';
UPDATE  temp1 SET  `batch` =  'B1' WHERE batch = '' order by rand(number) limit 100000;
UPDATE  temp1 SET  `batch` =  'B2' WHERE batch = '' order by rand(number) limit 100000;
UPDATE  temp1 SET  `batch` =  'B3' WHERE batch = '' order by rand(number) limit 100000;
UPDATE  temp1 SET  `batch` =  'B4' WHERE batch = '' order by rand(number) limit 100000;
UPDATE  temp1 SET  `batch` =  'B5' WHERE batch = '' order by rand(number) limit 100000;

最佳答案

如果您不需要每批精确地有100000条记录，则可以使用rand（）建立一个随机的批处理字符串，并且从统计上讲，每批应该有〜100000条记录

UPDATE temp1
JOIN   (
    SELECT number, CONCAT('B',CONVERT( FLOOR( RAND() * (nrows/10) ) + 1, CHAR(19) )) AS randBatch
    FROM   temp1
    CROSS JOIN ( SELECT COUNT(*) AS nrows FROM temp1 ) as x
   ) AS y
ON     y.number = temp1.number
SET    batch = randBatch

SQL提琴：http://sqlfiddle.com/#!9/eb161/1

关于mysql - 随机更新行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/22378757/