在 MySQL 中选择整数范围。例如。1,2,3,4,...,n;
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4386425/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SELECT range of integers in MySQL. Eg. 1,2,3,4,...,n;
提问by cervenak
I need to select range of integer in MySQL. Something like this
我需要在 MySQL 中选择整数范围。像这样的东西
SELECT RANGE(10,20) AS range;
SELECT RANGE(10,20) AS range;
returns
返回
10, 11, 12, 13, 14, ..., 20
10, 11, 12, 13, 14, ..., 20
Why?
I would like to select random phone number from range which is not yet registered. This is idea.
为什么？
我想从尚未注册的范围中选择随机电话号码。这是想法。
SELECT RANGE(100000,999999) AS range FROM phone WHERE phoneNum <> range LIMIT FLOOR(100000 + RAND()*(899999);
SELECT RANGE(100000,999999) AS range FROM phone WHERE phoneNum <> range LIMIT FLOOR(100000 + RAND()*(899999);
回答by Alin Purcaru
Problems with your query:
您的查询问题：
- You can't use
rangein 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 useIN(...), but in you particular case you should useBETWEEN 100000 and 999999and avoid the need for aRANGEfunction. - 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().
- 您不能
range在 WHERE 子句中使用。它是一个别名，只有在执行 WHERE 子句后才会定义。 - 即使您可以使用它，将一个数字与一组数字使用
<>. 一般来说，您可以使用IN(...)，但在您的特定情况下，您应该使用BETWEEN 100000 and 999999并避免需要RANGE函数。 - 如果你只想要一个数字，那么限制应该是 1，而不是随机的。通常选择您使用的随机项目
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.
如果你想找到一个不在你的表中的数字并且可用的数字没有接近耗尽（比如少于 80% 被分配）一个好方法是生成随机数并检查它们是否被分配，直到你找到一个'不。
A pure MySQL solution may exists but I think it needs some twisted joins, random and modulus.
可能存在纯 MySQL 解决方案，但我认为它需要一些扭曲的连接、随机和模数。
回答by Alin Purcaru
An alternative:
替代：
First of all create a table with just numbers that has all the numbers from 1 to MAX_NUM.
首先创建一个只有数字的表，其中包含从 1 到 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.
通过这种方式，您还可以通过更改限制值来相对快速地获得多个数字。
