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

2022-01-24 00:00:00 sequence range mysql

我需要在 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 和 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 解决方案，但我认为它需要一些扭曲连接、随机连接和模数连接.

相关文章