mysql过滤出非连续数字(最小缺失值问题)

场景一:统计测试环境的容器开启的IP地址

CREATE TABLE `ip` (
  `ip` int(11) NOT NULL,
  PRIMARY KEY (`ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

插入下面的过滤出的连续SQL==>

[root@docker01 arun]# docker ps |grep 'java32--'|awk '{print $NF}'|awk -F'--' '{print $2}'

INSERT INTO ip VALUES
(175),
(174),
(172),
(171),
(170),
(169),
(168),
(167),
(166),
(165),
(164),
(163),
(162),
(161),
(160),
(159),
(158),
(157),
(156),
(155),
(154),
(153),
(152),
(151),
(150),
(149),
(148),
(147),
(146),
(145),
(144),
(143),
(142),
(141),
(140),
(139),
(138),
(137),
(136),
(135),
(134),
(133),
(132),
(131),
(130),
(129),
(128),
(127),
(126),
(125),
(124),
(123),
(122),
(121),
(120),
(119),
(118),
(117),
(116),
(115),
(114),
(113),
(112),
(111),
(110),
(109),
(108),
(107),
(106),
(105),
(104),
(103),
(102),
(101),
(100),
(99),
(98),
(97),
(96),
(95),
(94),
(93),
(92),
(91),
(90),
(89),
(88),
(87),
(86),
(85),
(84),
(83),
(82),
(81),
(80),
(79),
(78),
(77),
(76),
(75),
(74),
(73),
(72),
(71),
(70),
(69),
(68),
(67),
(66),
(65),
(64),
(63),
(62),
(61),
(60),
(59),
(58),
(57),
(56),
(55),
(54),
(53),
(52),
(51),
(50),
(49),
(48),
(47),
(46),
(45),
(44),
(43),
(42),
(41),
(40),
(39),
(38),
(37),
(36),
(35),
(34),
(33),
(32),
(31),
(30),
(29),
(28),
(27),
(26),
(25),
(24),
(23),
(22),
(21),
(20),
(19),
(18),
(17),
(16),
(15),
(14),
(13),
(12),
(11),
(10),
(9),
(8),
(7),
(6),
(5),
(4),
(3),
(2),
(1);

View Code

插入后查询是174,可知确实1个ip=>

SELECT COUNT(*) FROM `ip`;

二、解决。

SELECT MIN(ip)+1 AS missing FROM ip AS ip1 WHERE NOT EXISTS(SELECT * FROM ip AS ip2 WHERE ip1.ip+1=ip2.ip);

查询确实没有10.10.32.173这个ip地址

[root@docker01 arun]# docker ps |grep 'java32--'|awk '{print $NF}'|awk -F'--' '{print $2}'
[root@docker01 arun]# docker ps|grep 173
822899bf620f        template-java8       "/usr/sbin/sshd -D"   10 days ago         Up 12 hours                             java-33-173
a173d367aa75        template-java8       "/usr/sbin/sshd -D"   10 days ago         Up 12 hours                             java-33-14

三、进一步验证。

进一步验证缺失比较多的时候,这个SQL思路并不好用。可见仅仅适用于缺失1个的值的场景。

 

参考:

http://idber.github.io/2016/03/23-%E6%9C%80%E5%B0%8F%E7%BC%BA%E5%A4%B1%E5%80%BC%E9%97%AE%E9%A2%98.html