Geek Answers Handbook

How can you find identifier spaces in MySQL recordset?

The problem here is related to another question that I had ...

I have millions of records, and the identifier of each of these records automatically increases, unfortunately, sometimes the generated identifier is sometimes discarded, so there are many spaces between the identifiers.

I want to find spaces and reuse identifiers that were left.

What is the efficient way to do this in MySQL?

+5
source share
4 answers

, , ? INT UNSIGNED 4 294 967 295. " " . ( BIGINT UNSIGNED 18 446 744 073 709 551 615 .)

, MySQL , , , , MySQL.

- :

SELECT id + 1
FROM the_table
WHERE NOT EXISTS (SELECT 1 FROM the_table t2 WHERE t2.id = the_table.id + 1);

(, {1, 2, 3, 8, 10} {4,9}), , , , , , .

+17

"n" mytab:

/* cs will contain 1 row for each contiguous sequence of integers in mytab.n
   and will have the start of that chain.
   ce will contain the end of that chain */
create temporary table cs (row int auto_increment primary key, n int);
create temporary table ce like cs;
insert into cs (n) select n from mytab where n-1 not in (select n from mytab) order by n;
insert into ce (n) select n from mytab where n+1 not in (select n from mytab) order by n;
select ce.n + 1 as bgap, cs.n - 1 as egap
  from cs, ce where cs.row = ce.row + 1;

, , :

select cs.n as bchain, ce.n as echain from cs,ce where cs.row=ce.row;
+2

, 1:

SELECT
    1 AS gap_start,
    MIN(e.id) - 1 AS gap_end
FROM
    factura_entrada e
WHERE
    NOT EXISTS(
        SELECT
            1
        FROM
            factura_entrada
        WHERE
            id = 1
    )
LIMIT 1
UNION
    SELECT
        a.id + 1 AS gap_start,
        MIN(b.id)- 1 AS gap_end
    FROM
        factura_entrada AS a,
        factura_entrada AS b
    WHERE
        a.id < b.id
    GROUP BY
        a.id
    HAVING
        gap_start < MIN(b.id);
+1

MariaDB,

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

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

0

More articles:


All Articles