MySQL存在值之间的第一个自由数 [英] MySQL first free number between exists values

问题描述

问题:在存在值之间获取服务器的第一个空闲端口.如果没有空位，则取最高的+1.

Problem: Get first free port for server between exists values. If there is not a free place then take the highest + 1.

额外:如果server_deleted = 1，那么我们可以使用空闲端口.当值之间存在间隙时，我们也应该使用空闲端口.我们应该在where子句中包含server_type.最小值是第一个端口，例如9000.

Extra: If server_deleted=1 then we can take the free port. Also we should take the free port when there is a gap between values. We should include server_type in where clause. The min value is first port e.g. 9000.

现在我的查询看起来是:

Now my query looks:

SELECT server_port + 1
  FROM pro_servers s
 WHERE s.server_port <> 0
   AND s.server_type = 'ts3'
   AND s.server_deleted = 0
   AND NOT EXISTS
        ( SELECT s1.server_port
            FROM pro_servers s1
           WHERE s1.server_port <> 0
             AND s1.server_type = 'ts3'
             AND s1.server_port = s.server_port + 1
             AND s1.server_deleted = 0
        )
 ORDER BY server_port LIMIT 1

我认为有更好的方法来做到这一点.该查询执行速度非常慢.

I think there is better way to do it. This query executes very slow.

例如，第一个=最小=最低= 9000，下一个是9002，9003.我们需要获取9001.如果我们添加9001，则得到9004.表中存在第一个值9000.

For example the first=minimum=lowest = 9000, next is 9002, 9003. We need to get the 9001. If we add 9001 then get 9004. The first value 9000 exists in the table.

样本数据

+-----------+-------------+----------------+-------------+
| server_id | server_port | server_deleted | server_type |
+-----------+-------------+----------------+-------------+
|       151 |        9500 |              1 | teamspeak3  |
|      8459 |        9500 |              0 | teamspeak3  |
|       183 |        9501 |              1 | teamspeak3  |
|       264 |        9502 |              1 | teamspeak3  |
|      4155 |        9502 |              1 | teamspeak3  |
|      2707 |        9503 |              1 | teamspeak3  |
|      4160 |        9503 |              1 | teamspeak3  |
|       154 |        9504 |              1 | teamspeak3  |
|      4163 |        9504 |              1 | teamspeak3  |
|       285 |        9506 |              1 | teamspeak3  |
|      4167 |        9506 |              1 | teamspeak3  |
|      8454 |        9506 |              0 | teamspeak3  |
|       241 |        9507 |              1 | teamspeak3  |
|      4169 |        9507 |              1 | teamspeak3  |
|       188 |        9509 |              1 | teamspeak3  |
|      4177 |        9509 |              1 | teamspeak3  |
+-----------+-------------+----------------+-------------+

查询结果:9501.当我们使用此端口时，接下来:9502、9503、9504、9505、9507、9508、9509、9510等.

QUERY RESULT: 9501. When we used this port then next: 9502, 9503, 9504, 9505, 9507, 9508, 9509, 9510 etc.

推荐答案

SELECT data.sPort
FROM 
  ((SELECT (s.server_port + 1) sPort
  FROM pro_servers s
  LEFT JOIN pro_servers sp1 ON sp1.server_port = s.server_port + 1
  WHERE (sp1.server_port IS NULL)
  ORDER BY sPort)

  UNION ALL

  (SELECT s.server_port sPost
  FROM pro_servers s
   GROUP BY s.server_port
   HAVING COUNT(s.server_port) = SUM(s.server_deleted)
  ORDER BY sPort)) AS data
ORDER BY data.sPort
LIMIT 1

SqlFiddle: http://sqlfiddle.com/#!2/12ab1/2

SqlFiddle: http://sqlfiddle.com/#!2/12ab1/2

工作原理

How it works

1. 使用server_port = server_port + 1连接条件将pro_servers与pro_servers左连接，并获取行，其中下一个端口中有null.这些行显示了每个间隙的第一个端口.端口ID可以用作server_port + 1.

1. Left Join pro_servers with pro_servers with server_port = server_port + 1 join condition and take rows, where there is null in the next port. These rows shows first ports from every gap. The port Id can be taken as server_port + 1.

获取所有已删除的端口.

Take all deleted ports.

联合1和2.按server_port的顺序排序，并取第一个.

Union 1. and 2., order by server_port and take the first one.

一个答案的假设是-始终采用编号最小的端口.如果不正确，请单独检查该端口(或在查询中添加另一个UNION ALL).

There is one assumption for an answer - port with the lowest number is always taken. If it's not true, check that port separately (or add another UNION ALL to the query).

这篇关于MySQL存在值之间的第一个自由数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！