mysql 以自增id等于某值为条件查出两条数据

Question

表：users,自增id
sql：
select id from users where id = （select floor（rand（） * （（select max（id） from users）- （select min（id）from users））+ select min（id） from users））

求大神解答，是sql有问题嘛

Answer 1

SELECT
    id
FROM
    users
WHERE
    id = FLOOR(
        rand() * (
            (SELECT max(id) FROM users) - (SELECT min(id) FROM users)
        ) + (SELECT min(id) FROM users)
    );

这是你写的SQL,我用手边的表查得时候不仅有两个结果的,还有空的,还有三个结果的.我也有点费解了.这种谜一样的问题是怎么出现的??

我觉得这个问题很神奇,虽然我也没找到原因,但是先把这个现象发出来给不方便看的人看一下,我再找找资料...

update:

我查找了一些其他人对这个问题的讨论,加上自己做了一些测试,大概得出了问题的原因(原谅我说的不那么肯定).
创建了一个单列表.一共1000条数据.

下面我们来复现问题

可以看到问题就出现在了这里,id=floor(rand()*1000)在我们的直观印象中,应该等于一个值.然而实际上不是这样,
在这里,floor(rand()*1000)是在变化的
也就是说,这1000行数据,执行这条语句的时候,是这样的操作:

• 判断第一行的id 1是否等于floor(rand()*1000),
• 判断第二行的id 2是否等于floor(rand()*1000),
• 判断第三行的id 3是否等于floor(rand()*1000),

我们只看这三次操作就够了,后边的floor(rand()*1000)是在不断变化的,第一次操作也许等于2,第2次操作可能等于1,第三次操作可能等于3.写成代码的话就是这样.

function rand() {
    // 随机生成一个数N
    return N
}
for (let i = 1; i <= 1000; i++) {
    if (i == rand()) {
        //选中这一条记录
        //可以看到,每次匹配的时候,都会执行一次rand()
    }

}
//而我们想要的其实是

let selected = rand()

for (let i = 1; i <= 1000; i++) {
    if (i == selected) {
        //选中这一条记录
        //每次都和已选出且不会变动的随机值进行比较
    }

}

在mariadb的文档中也提到,In a WHERE clause, RAND() is evaluated each time the WHERE is executed.

这就造成了id看似是在和一个值比较,实际上是1000个id分别和1000个随机数进行比较,两个数刚好相等的概率是1*1/1000,而刚好比较一千次,所以每次查到的结果期望值为1(个),但是这并不是稳定的,所以才会出现有时候查不到,有时候查到两条三条这中情况.

解决问题

知道了问题发生的原因,解决的思路自然就出现了,让rand()只计算一次.

第一个方法,使用join

SELECT
    `测试表`.`id`
FROM
    `测试表`
INNER JOIN (
    SELECT
        floor(rand() *(MAX(id) - MIN(id))) + min(id) AS id
    FROM
        `测试表`
) AS tmp
WHERE
    `测试表`.`id` = `tmp`.`id`;

第二个方法,使用会话变量.

SET @rnd = RAND();

SELECT
    id
FROM
    `测试表`
WHERE
    id = (
        SELECT
            floor(@rnd * (MAX(id) - MIN(id))) + min(id)
        FROM
            `测试表`
    );

应该还有别的方法,我就不写了,半吊子水平.

ref:

https://stackoverflow.com/que...
https://stackoverflow.com/que...