当前位置：文江博客话题详情

MySQL 中的 SELECT 整数范围。例如。 1,2,3,4,...,n;

发布于 2024-10-06 18:28:06 字数 334 浏览 7 评论 0原文

我需要在 MySQL 中选择整数范围。像这样的

SELECT RANGE(10,20) AS range;

10, 11, 12, 13, 14, ..., 20

为什么？强>
我想从尚未注册的范围中选择随机电话号码。这是想法。

SELECT RANGE(100000,999999) AS range FROM Phone WHERE PhoneNum <>;范围限制下限(100000 + RAND()*(899999);

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

三月梨花 2024-10-13 18:28:06

您的查询存在问题：

您不能在 WHERE 子句中使用 range。它是一个别名，只有在执行 WHERE 子句后才会定义。
即使您可以使用它，使用 <> 将一个数字与一组数字进行比较也是没有意义的。一般来说，您可以使用 IN(...)，但在特定情况下，您应该使用 BETWEEN 100000 和 999999 并避免需要 RANGE函数。
如果你只想要一个数字，那么限制应该是 1，而不是随机的。通常要选择随机项目，请使用ORDER BY RAND()。

尝试使用此查询：

SELECT phoneNum, 100000 as rangeStart, 999999 AS rangeEnd
FROM phone
WHERE phoneNum NOT BETWEEN 100000 AND 999999
ORDER BY RAND()
LIMIT 1

如果您想查找表中没有的数字，并且可用数字尚未接近耗尽（例如分配的数字少于 80%），一个好的方法是生成随机数并检查它们是否已分配，直到你会发现一个不是。

可能存在纯 MySQL 解决方案，但我认为它需要一些扭曲的连接、随机和模数。

Problems with your query:

You can't use range in the WHERE clause. It is an alias and will only be defined after the WHERE clause is performed.
Even if you could use it, it makes no sense to compare a number with a set of numbers using <>. In general you could use IN(...), but in you particular case you should use BETWEEN 100000 and 999999 and avoid the need for a RANGE function.
If you only want one number then the limit should be 1, not something random. Usually to select random items you use ORDER BY RAND().

Try using this query:

SELECT phoneNum, 100000 as rangeStart, 999999 AS rangeEnd
FROM phone
WHERE phoneNum NOT BETWEEN 100000 AND 999999
ORDER BY RAND()
LIMIT 1

If you want to find a number not in your table and the available numbers are not close to depletion (say less than 80% are assigned) a good approach would be to generate random numbers and check if they are assigned until you find one that isn't.

A pure MySQL solution may exists but I think it needs some twisted joins, random and modulus.

回复收藏 0 原文

天涯离梦残月幽梦 2024-10-13 18:28:06

另一种选择：

首先创建一个仅包含数字的表，其中包含从 1 到 MAX_NUM 的所有数字。

然后使用这个查询：

SELECT n.id as newNumber
FROM numbers AS n
LEFT JOIN phone AS p
    ON p.phoneNum = n.id
WHERE 
    p.phoneNum IS NULL AND
    n.id BETWEEN lowerLimit AND upperLIMIT    
ORDER BY RAND()
LIMIT 1

这样你也可以通过改变限制值来相对快速地获得多个数字。

An alternative:

First of all create a table with just numbers that has all the numbers from 1 to MAX_NUM.

Then use this query:

SELECT n.id as newNumber
FROM numbers AS n
LEFT JOIN phone AS p
    ON p.phoneNum = n.id
WHERE 
    p.phoneNum IS NULL AND
    n.id BETWEEN lowerLimit AND upperLIMIT    
ORDER BY RAND()
LIMIT 1

This way you can also get multiple number relatively fast by changing the limit value.

回复收藏 0 原文