如何从表中选取id列含随机非空有效值的第一条记录？

最近我碰到了个头疼的问题：需要从表中挑一条记录，要求它的id是随机且真实存在的非空值——可我的表id列的值不是连续的，直接生成随机数很可能选到根本不存在的id，完全没用。折腾了好一阵终于找到靠谱的解决方案，分享给有同样需求的小伙伴～

先看我的测试场景 #

我建了个测试表，故意删掉部分id让它变得不连续：

CREATE TABLE randomValue (id int);
-- 先插入1到10的id
INSERT INTO randomValue VALUES (generate_series(1, 10));
-- 删除所有偶数id，现在表中剩下的id是1、3、5、7、9
DELETE FROM randomValue WHERE id IN (2,4,6,8,10);

现在要从这个表中随机选一个存在的id对应的记录。

几种可行的解决方案 #

方法1：直接随机排序取第一条（简单易懂）

这是最直观的办法，把所有记录随机打乱顺序，然后取第一条就行：

SELECT * FROM randomValue ORDER BY RANDOM() LIMIT 1;

✅ 优点：写法简单，新手也能快速上手
❌ 缺点：如果表的数据量特别大，全表排序会拖慢性能，不适合超大数据表

方法2：先随机生成范围值，再匹配最近的存在id（性能友好）

如果你的表id有明确的范围，可以先拿到id的最大最小值，生成一个这个范围内的随机数，再找到第一个大于等于这个随机数的存在id——这样不用全表排序，速度快很多：

WITH id_range AS (
    SELECT MIN(id) AS min_id, MAX(id) AS max_id FROM randomValue
)
SELECT * FROM randomValue
WHERE id >= (SELECT FLOOR(RANDOM() * (max_id - min_id + 1)) + min_id FROM id_range)
ORDER BY id LIMIT 1;

万一生成的随机数比所有剩余id都大，上面的SQL会找不到记录，咱们可以加个兜底逻辑，找不到就从比随机数小的id里挑最大的那个：

WITH id_range AS (
    SELECT MIN(id) AS min_id, MAX(id) AS max_id FROM randomValue
), random_num AS (
    SELECT FLOOR(RANDOM() * (max_id - min_id + 1)) + min_id AS rand_id FROM id_range
)
SELECT * FROM randomValue
WHERE id >= (SELECT rand_id FROM random_num)
ORDER BY id LIMIT 1
UNION ALL
SELECT * FROM randomValue
WHERE id < (SELECT rand_id FROM random_num)
ORDER BY id DESC LIMIT 1
LIMIT 1;

这样不管随机数落在哪个位置，都能拿到有效的记录。

方法3：用TABLESAMPLE采样（超大数据表专属）

如果你的表数据量极大，前面两种方法还是慢，可以试试PostgreSQL的TABLESAMPLE先随机采样一部分数据，再从采样结果里选随机记录——速度会快很多：

SELECT * FROM randomValue TABLESAMPLE SYSTEM(10) -- 这里采样10%的数据，比例可以自己调
ORDER BY RANDOM() LIMIT 1;

✅ 优点：速度极快，适合百万级以上数据量的表
❌ 缺点：SYSTEM是基于数据块采样的，随机性不如前两种方法，如果对随机性要求极高，可能不太适合

内容的提问来源于stack exchange，提问作者Ms workaholic