{"id":929,"date":"2022-11-04T16:08:46","date_gmt":"2022-11-04T09:08:46","guid":{"rendered":"http:\/\/www.oopsbox.com\/?p=929"},"modified":"2022-11-04T16:12:10","modified_gmt":"2022-11-04T09:12:10","slug":"mysql-get-missing-ids","status":"publish","type":"post","link":"https:\/\/www.oopsbox.com\/?p=929","title":{"rendered":"MySQL get missing IDs"},"content":{"rendered":"<p>\u0e40\u0e04\u0e2a\u0e41\u0e23\u0e01\u0e2b\u0e25\u0e48\u0e30\u0e01\u0e31\u0e19 \u0e01\u0e23\u0e13\u0e35\u0e01\u0e33\u0e2b\u0e19\u0e14 Key \u0e40\u0e1b\u0e47\u0e19 Auto increment \u0e43\u0e19 MySQL \u0e41\u0e25\u0e49\u0e27\u0e40\u0e25\u0e02\u0e25\u0e33\u0e14\u0e31\u0e1a\/Key\/IDs \u0e1a\u0e32\u0e07\u0e15\u0e31\u0e27\u0e2b\u0e32\u0e22\u0e44\u0e1b (\u0e2b\u0e25\u0e32\u0e22\u0e2a\u0e32\u0e40\u0e2b\u0e15\u0e38) \u0e25\u0e2d\u0e07\u0e21\u0e32\u0e17\u0e33 Dummy Table \u0e17\u0e14\u0e2a\u0e2d\u0e1a\u0e14\u0e39\u0e01\u0e31\u0e19<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">CREATE TABLE numbers(\r\n  startNumber INTEGER UNSIGNED,\r\n  PRIMARY KEY (`startNumber`)\r\n);\r\nALTER TABLE numbers ADD INDEX idx1 (startNumber);\r\n\r\nINSERT INTO numbers VALUES\r\n(1),(2),(3),(4),(5),(6),(8),(9),(20),(21),(22),(53),(54),\r\n(71),(72),(74),(80);<\/pre>\n<p>\u0e08\u0e32\u0e01\u0e15\u0e32\u0e23\u0e32\u0e07\u0e14\u0e31\u0e21\u0e21\u0e35\u0e48\u0e02\u0e49\u0e32\u0e07\u0e15\u0e49\u0e19\u0e1c\u0e25\u0e25\u0e31\u0e1e\u0e18\u0e4c\u0e17\u0e35\u0e48\u0e40\u0e23\u0e32\u0e15\u0e49\u0e2d\u0e07\u0e01\u0e32\u0e23\u0e04\u0e37\u0e2d\u0e15\u0e31\u0e27\u0e40\u0e25\u0e02 <code class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">7, 10-19, 23-52, 55-70, 73, 75-79\u00a0<\/code><\/p>\n<p>\u0e25\u0e2d\u0e07\u0e40\u0e02\u0e35\u0e22\u0e19\u0e04\u0e33\u0e2a\u0e31\u0e48\u0e07\u0e40\u0e1e\u0e37\u0e48\u0e2d\u0e43\u0e2b\u0e49\u0e44\u0e14\u0e49\u0e04\u0e33\u0e15\u0e2d\u0e1a\u0e14\u0e49\u0e32\u0e19\u0e1a\u0e19\u0e2b\u0e25\u0e32\u0e22\u0e27\u0e34\u0e18\u0e35 \u0e27\u0e34\u0e18\u0e35\u0e41\u0e23\u0e01\u0e15\u0e49\u0e2d\u0e07\u0e43\u0e0a\u0e49\u0e15\u0e32\u0e23\u0e32\u0e07 Sequence Number \u0e43\u0e19 MySQL \u0e2d\u0e32\u0e08\u0e15\u0e49\u0e2d\u0e07\u0e17\u0e33 Manual \u0e44\u0e1b\u0e01\u0e48\u0e2d\u0e19<\/p>\n<p><strong>\u0e27\u0e34\u0e18\u0e35\u0e41\u0e23\u0e01<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">CREATE TABLE seq (`seqno` INTEGER UNSIGNED, PRIMARY KEY (`seqno`));\r\nINSERT INTO `seq` VALUES (0),(1),(2),(3),(4),(5),(6),(7);\r\n\r\n\r\nINSERT INTO `seq` SELECT seqno+8 from `seq`;\r\nINSERT INTO `seq` SELECT seqno+16 from `seq`;\r\nINSERT INTO `seq` SELECT seqno+32 from `seq`;\r\nINSERT INTO `seq` SELECT seqno+64 from `seq`;\r\nINSERT INTO `seq` SELECT seqno+128 from `seq`;\r\nINSERT INTO `seq` SELECT seqno+256 from `seq`;\r\nINSERT INTO `seq` SELECT seqno+512 from `seq`;<\/pre>\n<p>\u0e15\u0e32\u0e21\u0e14\u0e49\u0e27\u0e22\u0e43\u0e0a\u0e49\u0e27\u0e34\u0e18\u0e35\u0e01\u0e32\u0e23 Left Join<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">SELECT\r\n  s.*\r\nFROM seq s \r\n  LEFT JOIN numbers n ON s.seqno = n.startNumber\r\nWHERE n.startNumber IS NULL \r\nAND s.seqno &lt; (SELECT MAX(startNumber) FROM numbers);<\/pre>\n<p><strong>\u0e27\u0e34\u0e18\u0e35\u0e17\u0e35\u0e48 2<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">SELECT nstart, \r\n       nend\r\nFROM (SELECT m.startNumber + 1 AS nstart,\r\n              (SELECT MIN(startNumber) - 1 \r\n                 FROM numbers x \r\n                 WHERE x.startNumber &gt; m.startNumber) AS nend\r\n          FROM numbers m \r\n                LEFT JOIN\r\n                   (SELECT startNumber-1 startNumber \r\n                      FROM numbers r) r \r\n                ON (m.startNumber = r.startNumber)\r\n         WHERE r.startNumber IS NULL\r\n       ) x\r\nWHERE nend IS NOT NULL\r\nORDER BY nstart;<\/pre>\n<p><strong>\u0e27\u0e34\u0e18\u0e35\u0e17\u0e35\u0e48 3 \u0e43\u0e0a\u0e49 NOT IN\u00a0<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">SELECT\r\n  s.*\r\nFROM seq s \r\nWHERE s.seqno NOT IN (SELECT startNumber FROM numbers) \r\nAND s.seqno &lt; (SELECT MAX(startNumber) FROM numbers);<\/pre>\n<p><strong>\u0e27\u0e34\u0e18\u0e35\u0e17\u0e35\u0e48 4 \u0e43\u0e0a\u0e49 GROUP BY \u0e40\u0e1e\u0e37\u0e48\u0e2d\u0e43\u0e0a\u0e49 SQL aggregate functions<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">SELECT a.startNumber+1 AS start, MIN(b.startNumber) - 1 AS end\r\nFROM numbers AS a, numbers AS b\r\nWHERE a.startNumber &lt; b.startNumber\r\nGROUP BY a.startNumber\r\nHAVING start &lt; MIN(b.startNumber);<\/pre>\n<p>\u0e1c\u0e25\u0e25\u0e31\u0e1e\u0e18\u0e4c\u0e17\u0e35\u0e48\u0e44\u0e14\u0e49\u0e01\u0e47\u0e44\u0e1b\u0e25\u0e2d\u0e07\u0e40\u0e25\u0e48\u0e19\u0e15\u0e32\u0e21\u0e19\u0e35\u0e49 \u0e44\u0e14\u0e49\u0e40\u0e25\u0e22 <a href=\"http:\/\/sqlfiddle.com\/#!9\/bbc3e7\/3\">http:\/\/sqlfiddle.com\/#!9\/bbc3e7\/3<\/a><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-932 size-large\" src=\"https:\/\/www.oopsbox.com\/wp-content\/uploads\/Screenshot-from-2022-11-04-16-02-03-1024x452.png\" alt=\"\" width=\"740\" height=\"327\" srcset=\"https:\/\/www.oopsbox.com\/wp-content\/uploads\/Screenshot-from-2022-11-04-16-02-03-1024x452.png 1024w, https:\/\/www.oopsbox.com\/wp-content\/uploads\/Screenshot-from-2022-11-04-16-02-03-300x133.png 300w, https:\/\/www.oopsbox.com\/wp-content\/uploads\/Screenshot-from-2022-11-04-16-02-03-768x339.png 768w, https:\/\/www.oopsbox.com\/wp-content\/uploads\/Screenshot-from-2022-11-04-16-02-03-1536x679.png 1536w, https:\/\/www.oopsbox.com\/wp-content\/uploads\/Screenshot-from-2022-11-04-16-02-03.png 1890w\" sizes=\"auto, (max-width: 740px) 100vw, 740px\" \/><br \/>\n\u0e17\u0e49\u0e32\u0e22\u0e17\u0e35\u0e2a\u0e38\u0e14\u0e41\u0e25\u0e49\u0e27\u0e21\u0e31\u0e19\u0e2d\u0e32\u0e08\u0e21\u0e35\u0e27\u0e34\u0e18\u0e35\u0e01\u0e32\u0e23\u0e17\u0e35\u0e48\u0e21\u0e32\u0e01\u0e01\u0e27\u0e48\u0e32\u0e19\u0e35\u0e49\u0e41\u0e2b\u0e25\u0e48\u0e30 \u0e1c\u0e25\u0e25\u0e31\u0e1e\u0e18\u0e4c\u0e01\u0e47\u0e44\u0e14\u0e49\u0e44\u0e14\u0e49\u0e40\u0e0a\u0e48\u0e19\u0e01\u0e31\u0e19\u0e41\u0e15\u0e48\u0e04\u0e27\u0e32\u0e21\u0e41\u0e15\u0e01\u0e15\u0e48\u0e32\u0e07\u0e04\u0e37\u0e2d Big O \u0e04\u0e19\u0e0b\u0e35\u0e40\u0e23\u0e35\u0e22\u0e2a\u0e01\u0e47\u0e04\u0e27\u0e23\u0e0b\u0e35\u0e40\u0e23\u0e35\u0e22\u0e2a 5555<\/p>\n<div id=\"s-share-buttons\" class=\"\"><div class=\"s-single-share\">\n\t\t\t\t\t\t\t\t\t\t\t\t<div class=\"fb-share-button\" data-href=\"https:\/\/www.oopsbox.com\/?p=929\" data-type=\"button_count\"><\/div><\/div><div class=\"s-single-share\"><a href=\"https:\/\/twitter.com\/share\" class=\"twitter-share-button\"><\/a>\n\t\t\t\t\t\t\t\t\t\t\t\t<\/div><div class=\"s-single-share\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t<div class=\"g-plusone\" data-size=\"medium\"><\/div>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div><div class=\"s-single-share\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t<a href=\"\/\/www.pinterest.com\/pin\/create\/button\/\" data-pin-do=\"buttonBookmark\"  data-pin-color=\"red\"><img decoding=\"async\" src=\"\/\/assets.pinterest.com\/images\/pidgets\/pinit_fg_en_rect_red_20.png\" \/><\/a>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<script type=\"text\/javascript\" async defer src=\"\/\/assets.pinterest.com\/js\/pinit.js\"><\/script>\n\t\t\t\t\t\t\t\t\t\t\t\t<\/div><div class=\"s-single-share\">\n\t\t\t\t\t\t\t\t\t\t\t\t<a href=\"mailto:?Subject=MySQL%20get%20missing%20IDs&Body=Here%20is%20the%20link%20to%20the%20article:%20https:\/\/www.oopsbox.com\/?p=929\" title=\"Email\" class=\"s3-email\"><img decoding=\"async\" src=\"https:\/\/www.oopsbox.com\/wp-content\/plugins\/simple-social-share\/includes\/..\/images\/share-email.png\"><\/a>\n\t\t\t\t\t\t\t\t\t\t\t<\/div><\/div>","protected":false},"excerpt":{"rendered":"<p>\u0e40\u0e04\u0e2a\u0e41\u0e23\u0e01\u0e2b\u0e25\u0e48\u0e30\u0e01\u0e31\u0e19 \u0e01\u0e23\u0e13\u0e35\u0e01\u0e33\u0e2b\u0e19\u0e14 Key \u0e40\u0e1b\u0e47\u0e19 Auto increment \u0e43\u0e19 MySQL \u0e41\u0e25\u0e49\u0e27\u0e40\u0e25\u0e02\u0e25\u0e33\u0e14\u0e31\u0e1a\/Key\/IDs \u0e1a\u0e32\u0e07\u0e15\u0e31\u0e27\u0e2b\u0e32\u0e22\u0e44\u0e1b (\u0e2b\u0e25\u0e32\u0e22\u0e2a\u0e32\u0e40\u0e2b\u0e15\u0e38) \u0e25\u0e2d\u0e07\u0e21\u0e32\u0e17\u0e33 Dummy Table \u0e17\u0e14\u0e2a\u0e2d\u0e1a\u0e14\u0e39\u0e01\u0e31\u0e19 CREATE TABLE numbers( startNumber INTEGER UNSIGNED, PRIMARY KEY (`startNumber`) ); ALTER TABLE numbers ADD INDEX idx1 (startNumber); INSERT INTO numbers VALUES (1),(2),(3),(4),(5),(6),(8),(9),(20),(21),(22),(53),(54), (71),(72),(74),(80); \u0e08\u0e32\u0e01\u0e15\u0e32\u0e23\u0e32\u0e07\u0e14\u0e31\u0e21\u0e21\u0e35\u0e48\u0e02\u0e49\u0e32\u0e07\u0e15\u0e49\u0e19\u0e1c\u0e25\u0e25\u0e31\u0e1e\u0e18\u0e4c\u0e17\u0e35\u0e48\u0e40\u0e23\u0e32\u0e15\u0e49\u0e2d\u0e07\u0e01\u0e32\u0e23\u0e04\u0e37\u0e2d\u0e15\u0e31\u0e27\u0e40\u0e25\u0e02 7, 10-19, 23-52, 55-70, 73, 75-79\u00a0 \u0e25\u0e2d\u0e07\u0e40\u0e02\u0e35\u0e22\u0e19\u0e04\u0e33\u0e2a\u0e31\u0e48\u0e07\u0e40\u0e1e\u0e37\u0e48\u0e2d\u0e43\u0e2b\u0e49\u0e44\u0e14\u0e49\u0e04\u0e33\u0e15\u0e2d\u0e1a\u0e14\u0e49\u0e32\u0e19\u0e1a\u0e19\u0e2b\u0e25\u0e32\u0e22\u0e27\u0e34\u0e18\u0e35 \u0e27\u0e34\u0e18\u0e35\u0e41\u0e23\u0e01\u0e15\u0e49\u0e2d\u0e07\u0e43\u0e0a\u0e49\u0e15\u0e32\u0e23\u0e32\u0e07 Sequence Number \u0e43\u0e19 MySQL \u0e2d\u0e32\u0e08\u0e15\u0e49\u0e2d\u0e07\u0e17\u0e33 Manual \u0e44\u0e1b\u0e01\u0e48\u0e2d\u0e19 \u0e27\u0e34\u0e18\u0e35\u0e41\u0e23\u0e01 &hellip; <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/www.oopsbox.com\/?p=929\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;MySQL get missing IDs&#8221;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":930,"comment_status":"closed","ping_status":"open","sticky":true,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[44],"tags":[12,20],"class_list":["post-929","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-how-to","tag-mysql","tag-sql","entry"],"views":2187,"_links":{"self":[{"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=\/wp\/v2\/posts\/929","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=929"}],"version-history":[{"count":2,"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=\/wp\/v2\/posts\/929\/revisions"}],"predecessor-version":[{"id":933,"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=\/wp\/v2\/posts\/929\/revisions\/933"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=\/wp\/v2\/media\/930"}],"wp:attachment":[{"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=929"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=929"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=929"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}