Usamos cookies para medir audiência e melhorar sua experiência. Você pode aceitar ou recusar a qualquer momento. Veja sobre o iMasters.
Olá!
tenho um banco de dados com +2M de registros, o problema é: eu precisei apagar uns 10 mil, e agora fiquei com este problema, os IDs ficarão faltando, ex:
ID: 100
ID: 102
ID: 103
...
Como organizar isto? Ou seja, trocar todos os IDs subsequentes para não haver "buracos" com este? Lembrando, eu tenho + de 2 milhões de registros (15 mil a mais por dia), manualmente é inviável.
Amigo, na verdade aquilo foi só um exemplo.
Eu tenho registros assim em todo o meu banco de dados, pois quando eu precisei excluir aqueles registros, eles não eram sequenciais.
Deixo novamente um exemplo:
ID: 101
ID: 500
ID: 15000
Eu preciso que fique assim:
ID: 101
ID: 102
ID: 103
Obrigado
ola este registro e auto_increment, chave primaria, e do tipo=inteiro ?
É realmente preciso não ter as "banguelas" ?
Como é feita a sequencia da inclusão dos novos.
--
Uma forma de fazer (entre várias)
Uma tabela guarda o último número utilizado.
A sequencia é feita por meio de trigger de before insert :
inicio trigger
___Ler o último número usado
___Localiza o próximo número não utilizado na tabela
____Enquanto não fim
______soma 1
____fim
____atualiza o campo da sequnêcia com este valor
fim trigger
Quando todas as "banguelas" morrerem o próximo número é a sequencia mais 1.
Mais acho isto uma complicação somente se as sequencias não puderem ter falhas, apólices de seguro e notas fiscais por exemplo não podem,fora isto trabalheira de graça.
Ajudou ?
>
ola este registro e auto_increment, chave primaria, e do tipo=inteiro ?
Sim, auto incremento e chave primária inteiro.
>
É realmente preciso não ter as "banguelas" ?
Como é feita a sequencia da inclusão dos novos.
--
Uma forma de fazer (entre várias)
Uma tabela guarda o último número utilizado.
A sequencia é feita por meio de trigger de before insert :
inicio trigger
___Ler o último número usado
___Localiza o próximo número não utilizado na tabela
____Enquanto não fim
______soma 1
____fim
____atualiza o campo da sequnêcia com este valor
fim trigger
Quando todas as "banguelas" morrerem o próximo número é a sequencia mais 1.
Mais acho isto uma complicação somente se as sequencias não puderem ter falhas, apólices de seguro e notas fiscais por exemplo não podem,fora isto trabalheira de graça.
Ajudou ?
Ajudou sim Motta, me deu uma visão geral de como resolver este problema. vou pesquisar mais sobre as triggers (mysql não é o meu forte..)
>
É realmente preciso não ter as "banguelas" ?
Como é feita a sequencia da inclusão dos novos.
--
Uma forma de fazer (entre várias)
Uma tabela guarda o último número utilizado.
A sequencia é feita por meio de trigger de before insert :
inicio trigger
___Ler o último número usado
___Localiza o próximo número não utilizado na tabela
____Enquanto não fim
______soma 1
____fim
____atualiza o campo da sequnêcia com este valor
fim trigger
Quando todas as "banguelas" morrerem o próximo número é a sequencia mais 1.
Mais acho isto uma complicação somente se as sequencias não puderem ter falhas, apólices de seguro e notas fiscais por exemplo não podem,fora isto trabalheira de graça.
Ajudou ?
Motta, pra efeito de quem ta aprendendo sql , me esclarece 2 duvidas
1 - Existe uma função que le o ultimo número usado ? porque max(id) vai retornar o ultimo numero só.
2 - próximo numero não usado na tabela ? como assim ? isso seria um while ?
da pra ser mais didatico ? rs.. as triggers que pesquisei eram mais simples.
Não conheço MySql a fundo a ponto de saber os atalhos.
No Oracle existe uma forma de saber estes intervalos, veja isto.
Mas MySql não tem o rownum 1, talvez tenha algo parecido.
Achando o intervalo é fácil achar o menor.
Talvez achar este "próximo" número torne a inserção tão lenta que não vala a pena.
Qualquer coisa tamos aí.
----------
1-Coluna artificial que conta os números das linhas.
Só uma pergunta, porque você precisa fazer isto ? dessa forma você está obstruindo qualquer referencia que seja aplicada nessa tabela .. ou futura referencia, eu não acho uma boa idéia ..
É o que eu venho tentando dizer Andrey, um trabalho deste tipo deve ter um motivo forte.
E tem outra Motta, ele vai consumir muito fazendo esse tipo de coisa, tá certo que 15 mil registros não é muita coisa, mas como ele disse: "(15 mil a mais)" ou seja, vai executar 15 mil vezes a trigger, agora, de inicio ele não vai ter muitos problemas, afinal .. o volume de dados ainda vai ser baixo, mas quando isso for aumentando, você pode até ferrar o servidor ..
Sabendo que: 15.000 = 1 Dia, pra 1 Semana temos 150.000 registros , logo 150.000 execuções de trigger fazendo varredura e tampando os buracos, sem falar das outras querys, agora, se 1 semana você tem 150.000, 1 mês você vai ter 465.000 registros e 465.000 execuções de trigger .. fazendo o mesmo processo, não tem como você ignorar esse processo pois o loop vai atingir linha por linha checando a próxima .. e daí meu amigo, já foram 465.000 selects, 465.000 updates, sem falar do problema das referencias.
Acredito que pra você tomar essa atitude, tem que ter um motivo daqueles, porque é um preço muito alto a se pagar, e dependendo do que você precise, exista outra maneira de resolver o problema, e pra deixar claro aqui, o conceito do auto_increment não é esse, muito pelo contrário ele é uma chave significativa dentro da própria tabela.
Outra coisa, se esses dados forem utilizados por usuários e esse seu ID for exibido, pode haver o risco de alguém estar imprimindo algo de importância ou estar guardando algo como referencia, como um ID de cliente .. e na hora que você rodar esse seu "trigger imaginário" o cliente perca a seleção desse cara, é só um exemplo baseado em fatos irreais, mas dependendo do que você está fazendo, vai ferrar muita coisa, ainda mais se for uma aplicação movimentada ..
Suponha que seja uma aplicação que gerencia pedidos de várias outras empresas, o pedido tem um ID único, todos sabemos disso, daí quer deixar os registros sequenciais na tabela, ou seja .. um pedido pode ter sido solicitado para exclusão, e daí já abre uma brecha, então você tem lá '1,2,3,4 [ 5 foi deletado ], ... ' e então todo o resto vai ser deduzido .. daí vem por exemplo 6000 registros, e essas empresas imprimem uma folha com os códigos e valores dos pedidos, o que acontece se você altera ? seu sistema não está regulamentado .. ou seja você estaria alterando algo que deve ÚNICO, não estou falando que este é o seu caso .. mas imagine algo do tipo .. se você altera um lá do inicio todo resto deve ser alterado.
Entendi gente, na verdade é apenas para organização dos registros, e para facilitar a minha leitura (Eu olho todos os registros). Este banco MySQL é pessoal, ou seja, ninguém lê os seus dados com alguma página. Eu estou usando o campo em auto incremento, portanto ele ignora o campo apagado e continua gravando com o próximo número.
Lendo a descrição do Andrey, vou considerar a questão de performance, visto que isso pode prejudicar o sistema.
Eu estava pensando em fazer assim: Crio uma tabela, começo a selecionar todos os registros, um a um da tabela antiga e vou fazendo insert nesta nova tabela. Como o campo ID será auto incremento a partir do 0, o problema seria resolvido..Faço em localhost para não prejudicar o servidor...
Ainda é desperdício, da mesma forma você ainda vai estar fazendo o dobro de execuções .. esquece essa idéia cara, vai por mim, é furada.
No entanto, você pode fazer o seguinte também ..
mysql> use db ;
Database changed
mysql> select * from data ;
+----+--------+
| id | coluna |
+----+--------+
| 1 | V |
| 2 | V |
| 3 | V |
| 4 | V |
| 5 | V |
| 7 | V |
| 8 | V |
| 9 | V |
| 10 | V |
| 11 | V |
| 13 | V |
| 14 | V |
| 16 | V |
+----+--------+
13 rows in set (0.00 sec)
mysql> alter table data drop column id ;
Query OK, 13 rows affected (0.07 sec)
Records: 13 Duplicates: 0 Warnings: 0
mysql> alter table data add column id integer not null auto_increment primary key ;
Query OK, 13 rows affected (0.05 sec)
Records: 13 Duplicates: 0 Warnings: 0
mysql> select * from data ;
+--------+----+
| coluna | id |
+--------+----+
| V | 1 |
| V | 2 |
| V | 3 |
| V | 4 |
| V | 5 |
| V | 6 |
| V | 7 |
| V | 8 |
| V | 9 |
| V | 10 |
| V | 11 |
| V | 12 |
| V | 13 |
+--------+----+
13 rows in set (0.00 sec)
Fazer um drop na coluna em questão, e adicionar esta novamente .. ainda sim é perda de processamento.
Entendi, acho que não vale a pena mesmo fazer isso.
Obrigado a todas que me ajudaram e lerem esse tópico
Perfeito... Tinha o mesmo problema e agora mudei de idéia rsrs
o que o Andrey Knupp falou caiu perfeitamente no meu sistema... realmente não tinha olhado por este lado...
Obrigado =)
Sua necessidade tem sentido sim, quando você define uma coluna em uma tabela como PK (Primary Key) qualquer SGBD, seja MySQL, Oracle, SQL Server, cria um índice que geralmente tem o mesmo nome da PK, para poder indexar os registros da tabela. Quando você executar a query, no Oracle por exemplo, você olha o custo da query, vai poder enxergar que quanto mais buracos na PK existir maior o custo da query. Pois existem muitos intervalos nos índices.
Mas não faça lógica com while (laço), pois você tem 2 milhões de registros, e se o buraco estiver no final da tabela, vai ficar esperando muito tempo pra saber a PK, despejando muita memória do servidor.
No Oracle, como tem query dinâmica, criei uma function, que executa 6 selects, e com isso sei onde está o buraco e bem rápido.
Vou pesquisar como fazer a query dinâmica no MySQL pra poder ajudar.
Correção, foram 4 selects
Tive que criar uma procedure, pois function como no Oracle não deu certo, segue abaixo o código:
CREATE DEFINER=root@localhost PROCEDURE FreeCode(iColumnName varchar(4000), iTableName varchar(4000), iStart int)
BEGIN
DECLARE STR_QUERY VARCHAR(4000) DEFAULT '';
DECLARE STR_QUERY_1 VARCHAR(4000) DEFAULT '';
DECLARE STR_QUERY_2 VARCHAR(4000) DEFAULT '';
DECLARE STR_QUERY_3 VARCHAR(4000) DEFAULT '';
/*****************************************/
/* Query para buscar os buracos na chave */
/*****************************************/
SET STR_QUERY_1 = concat('select coalesce(min(x.id_corrente) + 1, 0) proximo
from', char(13), '
(
select
' , iColumnName , ' id_corrente,
(select min(b.' , iColumnName , ')
from ' , iTableName , ' b
where b.' , iColumnName , ' > a.' , iColumnName , ') posterior
from
' , iTableName , ' a
where
' , iColumnName , ' >= ' , iStart , '
order by a.' , iColumnName , '
) x
where
posterior - id_corrente > 1');
/*****************************************/
/* Query para buscar o maximo + 1 */
/*****************************************/
SET STR_QUERY_2 = concat('select if((select coalesce(max(', iColumnName, ') + 1, 0)
from ', iTableName, ')=0, 1, (select max(', iColumnName, ') + 1
from ', iTableName, ')) proximo
from dual');
/*****************************************/
/* Query verifica se start esta livre */
/*****************************************/
SET STR_QUERY_3 = concat('select if((select ', iColumnName,
' from ', iTableName,
' where ', iColumnName, ' = ', iStart, ') is null, ', iStart, ', 0) proximo
from dual');
/*****************************************/
/* Query que concatena todas as querys */
/*****************************************/
SET STR_QUERY = CONCAT('SELECT min(proximo) INTO @v_free_code from (\n\n', STR_QUERY_1, '\n\nUNION\n\n ', STR_QUERY_2, '\n\nUNION\n\n ', STR_QUERY_3, '\n\n) tb_prox \n\nWhere proximo !=0');
SET @qry = STR_QUERY;
prepare statement from @qry;
execute statement;
deallocate prepare statement;
select @v_free_code;END
ola amigo você que excluir registro de 0 a 100 ex.
nao testei o codigo