首页 数据库 Mysql

# IP 节点管理 SQL

  1. 查询节点池中的所有节点名称以及更新截止时间等相关信息
SELECT b.valid_id,a.code,b.server_url,b.provide,a.ip,a.port,a.country,a.province,a.city,a.county,a.pcc,a.isp,a.finish_time,a.invalid_time FROM http_ip_pool AS a left JOIN http_product_dynamic_node AS b ON a.CODE = b.CODE ORDER BY invalid_time ASC

2.mysql 查询当前时间与五分钟后的时间差

current_time: 数据库当前时间
expr : 时间数 unit : 时间单位 <SECOND,MINUTE,HOUR,DAY : 秒,分,时,天>
SUBDATE(date,INTERVAL expr unit) : date 向前偏移 expr unit 时间
ADDDATE(date,INTERVAL expr unit) : date 向后偏移 expr unit 时间
SELECT TIMESTAMPDIFF(SECOND,current_time,ADDDATE(current_time,INTERVAL 5 MINUTE)) FROM http_ip_pool

3. 查询到期时间和更新时间的时间差 (查询当前对应可用时长的 IP)

SELECT TIMESTAMPDIFF(SECOND,finish_time,invalid_time),current_time FROM http_ip_pool

4. 查询当前数据库中 1-5 分钟的可用 IP

  • 查询结束时间与当前时间差在 60 ~ 300 秒之间的节点
SELECT * FROM http_ip_pool WHERE TIMESTAMPDIFF(SECOND,current_time,invalid_time) > 60  AND  TIMESTAMPDIFF(SECOND,current_time,invalid_time) <300;
## 包含节点名称等信息的联表查询
SELECT b.valid_id,a.code,b.server_url,a.ip,a.port,a.country,a.province,a.city,a.county,a.pcc,a.isp,a.finish_time,a.invalid_time FROM http_ip_pool AS a left JOIN http_product_dynamic_node AS b ON a.CODE = b.CODE WHERE TIMESTAMPDIFF(SECOND,current_time,a.invalid_time) > 60  AND  TIMESTAMPDIFF(SECOND,current_time,a.invalid_time) <300;
  • 查询结束时间在 当前时间后的 1 ~ 5 分钟之间的节点
SELECT * FROM http_ip_pool WHERE invalid_time BETWEEN addDATE(current_time,INTERVAL 1 MINUTE)  AND addDATE(current_time,INTERVAL 5 MINUTE);
## 包含节点名称等信息的联表查询
SELECT b.valid_id,a.code,b.server_url,a.ip,a.port,a.country,a.province,a.city,a.county,a.pcc,a.isp,a.finish_time,a.invalid_time FROM http_ip_pool AS a left JOIN http_product_dynamic_node AS b ON a.CODE = b.CODE  WHERE a.invalid_time BETWEEN addDATE(current_time,INTERVAL 1 MINUTE)  AND addDATE(current_time,INTERVAL 5 MINUTE);