MySQL exists 查询添加异常，SELECT 最大值+1

Question

我有以下脚本。它一直在工作。昨天它开始响应 0 个结果。我找到了原因。脚本不选择最高端口，当空闲端口之间没有时加 1。如果异常已存在，如何添加或修复它。

它应该选择第一个空闲服务器端口(来自已删除的服务器)，或者如果不存在则选择最新的端口 + 1。

工作原理:MySQL first free number between exists values

SELECT data.sPort
    FROM 
      ((SELECT (s.server_port + 1) sPort
      FROM servers s
      LEFT JOIN servers sp1 ON sp1.server_port = s.server_port + 1
      WHERE (sp1.server_port IS NULL) AND sp1.server_port<>0 AND sp1.server_type='" . $server['server_type'] . "' AND sp1.server_port<>9987
      ORDER BY sPort)

      UNION ALL

      (SELECT s.server_port sPost
      FROM servers s
    WHERE s.server_port<>0  AND s.server_port<>9987 AND s.server_type='" . $server['server_type'] . "'
       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

Answer 1

在您的第一个派生表中，您似乎想要为特定服务器类型选择下一个端口。如果是这样，请尝试更改它

LEFT JOIN servers sp1 ON sp1.server_port = s.server_port + 1
WHERE (sp1.server_port IS NULL) AND sp1.server_port<>0 AND sp1.server_type='" . $server['server_type'] . "' AND sp1.server_port<>9987

到

LEFT JOIN servers sp1 ON sp1.server_port = s.server_port + 1 
  AND s.server_type = sp1.server_type
WHERE sp1.server_port IS NULL
  AND s.server_port<>0 
  AND s.server_type='" . $server['server_type'] . "' 
  AND s.server_port<>9987