¿Cómo encontrar espacios en la numeración secuencial en mysql?

Tenemos una database con una tabla cuyos valores se importaron de otro sistema. Hay una columna de autoincremento y no hay valores duplicates, pero faltan valores. Por ejemplo, ejecutando esta consulta:

select count(id) from arrc_vouchers where id between 1 and 100 

debería devolver 100, pero devuelve 87 en su lugar. ¿Hay alguna consulta que pueda ejecutar que devuelva los valores de los numbers que faltan? Por ejemplo, los loggings pueden existir para id. 1-70 y 83-100, pero no hay loggings con id's de 71-82. Quiero regresar 71, 72, 73, etc.

es posible?

Aquí hay una versión que funciona en la tabla de cualquier tamaño (no solo en 100 filas):

 SELECT (t1.id + 1) as gap_starts_at, (SELECT MIN(t3.id) -1 FROM arrc_vouchers t3 WHERE t3.id > t1.id) as gap_ends_at FROM arrc_vouchers t1 WHERE NOT EXISTS (SELECT t2.id FROM arrc_vouchers t2 WHERE t2.id = t1.id + 1) HAVING gap_ends_at IS NOT NULL 
  • gap_starts_at – primera identificación en la brecha actual
  • gap_ends_at – última identificación en la brecha actual

Esto funcionó para mí para encontrar las lagunas en una tabla con más de 80k filas:

 SELECT CONCAT(z.expected, IF(z.got-1>z.expected, CONCAT(' thru ',z.got-1), '')) AS missing FROM ( SELECT @rownum:=@rownum+1 AS expected, IF(@rownum=YourCol, 0, @rownum:=YourCol) AS got FROM (SELECT @rownum:=0) AS a JOIN YourTable ORDER BY YourCol ) AS z WHERE z.got!=0; 

Resultado:

 +------------------+ | missing | +------------------+ | 1 thru 99 | | 666 thru 667 | | 50000 | | 66419 thru 66456 | +------------------+ 4 rows in set (0.06 sec) 

Tenga en count que el order de las columnas expected y got es crítico.

Si sabe que YourCol no comienza en 1 y eso no importa, puede replace

 (SELECT @rownum:=0) AS a 

con

 (SELECT @rownum:=(SELECT MIN(YourCol)-1 FROM YourTable)) AS a 

Nuevo resultado:

 +------------------+ | missing | +------------------+ | 666 thru 667 | | 50000 | | 66419 thru 66456 | +------------------+ 3 rows in set (0.06 sec) 

Consulta rápida y sucia que debería hacer el truco:

 SELECT a AS id, b AS next_id, (b - a) -1 AS missing_inbetween FROM ( SELECT a1.id AS a , MIN(a2.id) AS b FROM arrc_vouchers AS a1 LEFT JOIN arrc_vouchers AS a2 ON a2.id > a1.id WHERE a1.id <= 100 GROUP BY a1.id ) AS tab WHERE b > a + 1 

Esto le dará una tabla que muestra el ID que tiene identificadores que faltan por encima de él, y next_id que existe, y cuántos faltan entre … por ejemplo

 
 id next_id missing_inbetween
  1 4 2
 68 70 1
 75 87 11

Cree una tabla temporal con 100 filas y una sola columna que contenga los valores 1-100.

Exterior Unir esta tabla a su tabla arrc_vouchers y seleccionar los valores de una sola columna donde el id arrc_vouchers es nulo.

Codificando esta ciega, pero debería funcionar.

 select tempid from temptable left join arrc_vouchers on temptable.tempid = arrc_vouchers.id where arrc_vouchers.id is null 

Una solución alternativa que requiere una consulta + algún código que realice algún procesamiento sería:

 select l.id lValue, c.id cValue, r.id rValue from arrc_vouchers l right join arrc_vouchers c on l.id=IF(c.id > 0, c.id-1, null) left join arrc_vouchers r on r.id=c.id+1 where 1=1 and c.id > 0 and (l.id is null or r.id is null) order by c.id asc; 

Tenga en count que la consulta no contiene ninguna subselección que sepamos que el planificador de MySQL no maneja de manera performante.

Eso devolverá una input por valorValor central (cValor) que no tiene un valor menor (lValor) o un valor mayor (rValor), es decir:

 lValue |cValue|rValue -------+------+------- {null} | 2 | 3 8 | 9 | {null} {null} | 22 | 23 23 | 24 | {null} {null} | 29 | {null} {null} | 33 | {null} 

Sin entrar en más detalles (los veremos en los párrafos siguientes), esta salida significa que:

  • Sin valores entre 0 y 2
  • Sin valores entre 9 y 22
  • Sin valores entre 24 y 29
  • Sin valores entre 29 y 33
  • Sin valores entre 33 y MAX VALUE

Así que la idea básica es hacer un DERECHO e IZQUIERDA se une con la misma tabla para ver si tenemos valores adyacentes por valor (es decir: si el valor central es '3', entonces verificamos que 3-1 = 2 a la izquierda y 3 + 1 a derecha), y cuando una FILA tiene un valor NULO a la DERECHA o IZQUIERDA, entonces sabemos que no hay un valor adyacente.

El resultado bruto completo de mi tabla es:

 select * from arrc_vouchers order by id asc; 0 2 3 4 5 6 7 8 9 22 23 24 29 33 

Algunas notas:

  1. La statement SQL IF en la condición de unión es necesaria si define el campo 'id' como UNSIGNED, por lo tanto, no le permitirá disminuirlo bajo cero. Esto no es estrictamente necesario si mantiene el valor c> 0 como se indica en la siguiente nota, pero lo incluyo solo como doc.
  2. Estoy filtrando el valor central cero ya que no estamos interesados ​​en ningún valor anterior y podemos derivar el valor del post de la siguiente fila.

Si está usando un MariaDB , tiene una opción más rápida (800%)

 SELECT * FROM seq_1_to_50000 where seq not in (select col from table); 

https://mariadb.com/kb/en/mariadb/sequence/

Basándose en la respuesta dada anteriormente por Lucek, este procedimiento almacenado le permite especificar los nombres de tabla y columna que desea probar para encontrar loggings no contiguos, respondiendo así la pregunta original y también demostrando cómo se puede usar @var para representar tablas y / o columnas en un procedimiento almacenado.

 create definer=`root`@`localhost` procedure `spfindnoncontiguous`(in `param_tbl` varchar(64), in `param_col` varchar(64)) language sql not deterministic contains sql sql security definer comment '' begin declare strsql varchar(1000); declare tbl varchar(64); declare col varchar(64); set @tbl=cast(param_tbl as char character set utf8); set @col=cast(param_col as char character set utf8); set @strsql=concat("select ( t1.",@col," + 1 ) as starts_at, ( select min(t3.",@col,") -1 from ",@tbl," t3 where t3.",@col," > t1.",@col," ) as ends_at from ",@tbl," t1 where not exists ( select t2.",@col," from ",@tbl," t2 where t2.",@col," = t1.",@col," + 1 ) having ends_at is not null"); prepare stmt from @strsql; execute stmt; deallocate prepare stmt; end 

Esto puede no funcionar en MySQL, pero en el trabajo (Oracle) necesitábamos algo similar.

Escribimos un progtwig almacenado que tomó un número como el valor máximo. El process almacenado luego creó una tabla temporal con una sola columna. La tabla contenía todos los numbers de 1 a Max. Luego hizo una combinación NOT IN entre la tabla de temperatura y nuestra tabla de interés.

Si lo llamó con Max = Select max (id) de arrc_vouchers, devolvería todos los valores perdidos.

Aunque todo esto parece funcionar, el set de resultados vuelve en un time muy largo cuando hay 50,000 loggings.

Utilicé esto, y encontré el hueco o el siguiente disponible (último usado +1) con un retorno mucho más rápido de la consulta.

 SELECT a.id as beforegap, a.id+1 as avail FROM table_name a where (select b.id from table_name b where b.id=a.id+1) is null limit 1; 

Puede usar generar series para generar numbers del 1 al ID más alto de su tabla. A continuación, ejecute una consulta donde id no esté en esta serie.