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

numero consecutivo mysql

Question

Tenemos una base de datos con una tabla cuyos valores se importaron de otro sistema. Hay una columna de autoincremento y no hay valores duplicados, 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 números que faltan? Por ejemplo, los registros pueden existir para id. 1-70 y 83-100, pero no hay registros con id''s de 71-82. Quiero regresar 71, 72, 73, etc.

es posible?

Answer 1

Actualizar

ConfexianMJS proporcionó una answer mucho mejor en términos de rendimiento.

La respuesta (no tan rápido como sea 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

Answer 2

Aunque todo esto parece funcionar, el conjunto de resultados vuelve en un tiempo muy largo cuando hay 50,000 registros.

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;

Answer 3

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

Answer 4

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

Answer 5

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 cuenta que el orden de las columnas expected y got es crítico.

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

(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)

Si necesita realizar algún tipo de tarea de script de shell en los ID faltantes, también puede usar esta variante para producir directamente una expresión que puede iterar en bash.

SELECT GROUP_CONCAT(IF(z.got-1>z.expected, CONCAT(''$('',z.expected,'' '',z.got-1,'')''), z.expected) SEPARATOR " ") AS missing

FROM (SELECCIONE @rownum: = @ rownum + 1 COMO se esperaba, IF (@ rownum = height, 0, @rownum: = height) AS got FROM (SELECCIONE @rownum: = 0) AS JOIN block ORDER BY height) AS z DONDE z.got! = 0;

Esto produce una salida como tal

$(seq 1 99) $(seq 666 667) 50000 $(seq 66419 66456)

A continuación, puede copiar y pegar en un bucle for en un terminal bash para ejecutar un comando para cada ID

for ID in $(seq 1 99) $(seq 666 667) 50000 $(seq 66419 66456); do
  echo $ID
  # fill the gaps
done

Es lo mismo que arriba, solo que es legible y ejecutable. Al cambiar el comando "CONCAT" anterior, se puede generar sintaxis para otros lenguajes de programación. O tal vez incluso SQL.

Answer 6

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

Escribimos un programa almacenado que tomó un número como el valor máximo. El proceso almacenado luego creó una tabla temporal con una sola columna. La tabla contenía todos los números 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.

Answer 7

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

Answer 8

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/

Answer 9

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 cuenta que la consulta no contiene ninguna subselección que sepamos que el planificador de MySQL no maneja de manera performante.

Eso devolverá una entrada 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 declaración 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.

Answer 10

en función de la respuesta dada anteriormente por Lucek, este procedimiento almacenado le permite especificar los nombres de tabla y columna que desea probar para encontrar registros 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