SELECT在MySQL中的整数范围.例如. 1,2,3,4，...，n; [英] SELECT range of integers in MySQL. Eg. 1,2,3,4,...,n;

问题描述

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

I need to select range of integer in MySQL. Something like this

SELECT RANGE(10,20) AS range;

返回

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);

推荐答案

查询问题:

1. 您不能在WHERE子句中使用range.它是一个别名，仅在执行WHERE子句之后定义.
2. 即使您可以使用它，也不能使用<>将数字与一组数字进行比较.通常，您可以使用IN(...)，但是在特定情况下，您应该使用BETWEEN 100000 and 999999，并避免使用RANGE函数.
3. 如果您只想要一个数字，则限制应为1，而不是随机数.通常要选择随机项目，请使用ORDER BY RAND().

1. You can't use range in the WHERE clause. It is an alias and will only be defined after the WHERE clause is performed.
2. 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.
3. 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().

尝试使用此查询:

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

---

如果您要查找表格中未存在的数字，并且可用数字接近耗尽(例如分配的数字少于80％)，则一种好的方法是生成随机数字并检查是否分配了数字，直到找到为止一个不是.

---

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.

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

这篇关于SELECT在MySQL中的整数范围.例如. 1,2,3,4，...，n;的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！