RAND()在MYSQL的WHERE子句中 [英] RAND() In WHERE clause in MYSQL

问题描述

我发现此博客文章显示了一种从表中获取随机行的方法:

I've found this Blog post that shows a way to grab a random rows from a table : http://www.rndblog.com/how-to-select-random-rows-in-mysql/

我在尝试限制的繁重的删除查询中使用了它，并且它的运行速度非常快.

I used it in a heavy delete query that I was trying to LIMIT and it worked, while also being quite fast.

DELETE table1 FROM table1 
    JOIN table2 ON table2.id = table1.salesperson_id 
    JOIN table3 ON table3.id = table2.user_id
    JOIN table4 ON table3.office_id = table4.id
WHERE table1.type = "Snapshot" 
    AND table4.id = 113 OR table4.parent_id =113
    AND RAND()<=0.001;

我不知道这是如何工作的.我尝试了广泛的谷歌搜索，但是在以这种方式使用的WHERE子句中，我什么都没有找到关于RAND()的信息. 这些文档也都没有给出任何信息.

I don't understand how does this works. I tried googling extensively, but I found nothing regarding a RAND() in the WHERE clause that's used in this way. Neither did the docs give anything about this.

先谢谢您.

P.S.我正在使用MYSQL 5.5

P.S. I'm Using MYSQL 5.5

推荐答案

对于您的联接生成的每一行，都会评估WHERE子句中的表达式.

The expression in your WHERE clause is evaluated for every row generated by your joins.

每次表达式调用RAND()时，该函数将返回一个介于0和1之间的不同随机浮点值.如果此随机值等于或小于0.001，则该术语为true.因此，随机数大约为1000行中的1行.这些将是它将删除的行.而1000行中的其他999条将跳过删除.

Every time the expression calls RAND(), that function returns a different random floating-point value between 0 and 1. If this random value is equal to or less than 0.001, then that term is true. So it will be true approximately 1 out of 1000 rows, randomly. Those will be the rows it will delete. Whereas the other 999 out of 1000 rows it will skip deleting.

顺便说一句，我想您想让WHERE子句带有一些括号来强制运算符优先级:

By the way, I would guess you want the WHERE clause to have some parentheses to enforce operator precedence:

WHERE table1.type = "Snapshot" 
    AND (table4.id = 113 OR table4.parent_id = 113)
    AND RAND()<=0.001;

否则，AND绑定比OR绑定更牢固，因此如果没有这些括号，该表达式将像您以这种方式编写的一样工作:

Otherwise AND binds more strongly than OR, so without these parentheses, the expression will work as if you had written it this way:

WHERE (table1.type = "Snapshot" 
    AND table4.id = 113) OR (table4.parent_id =113
    AND RAND()<=0.001);

这意味着 all 类型为'Snapshot'和id = 113的行将被删除，而parent_id = 113的1/1000行将被删除，包括一些类型不是'快照".

That means all the rows with type='Snapshot' AND id=113 will be deleted, whereas 1/1000 rows with parent_id=113 will be deleted, including some rows with type other than 'Snapshot'.

那可能不是您想要的.您应该查看布尔代数和运算符优先级.参见 https://dev.mysql.com/doc/refman/5.7/en/operator-precedence.html

That's probably not what you want. You should review boolean algebra and operator precedence. See https://dev.mysql.com/doc/refman/5.7/en/operator-precedence.html

这篇关于RAND()在MYSQL的WHERE子句中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！