[Resolvido] Organizar registros no MySQL

[Lucas Peperaio 20]

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.

[junaooaks 3]

ola amigo você que excluir registro de 0 a 100 ex.

 

 

nao testei o codigo

DELETE FROM table_name WHERE id <= 100;

faz um teste em uma tabela separada

[Lucas Peperaio 20]

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

[junaooaks 3]

ola este registro e auto_increment, chave primaria, e do tipo=inteiro ?

[Motta 645]

É 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 ?

[Lucas Peperaio 20]

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

[vega! 0]

É 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.

[Motta 645]

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 ¹, 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.

[Andrey Knupp Vital 136]

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 ..

[Motta 645]

É o que eu venho tentando dizer Andrey, um trabalho deste tipo deve ter um motivo forte.

[Andrey Knupp Vital 136]

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.

[Lucas Peperaio 20]

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...

[Andrey Knupp Vital 136]

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.

[Lucas Peperaio 20]

Entendi, acho que não vale a pena mesmo fazer isso.

Obrigado a todas que me ajudaram e lerem esse tópico

[Luiz Fernando - Ampol 2]

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

[Leonardo Brito 0]

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.

[Leonardo Brito 0]

Correção, foram 4 selects

[Leonardo Brito 0]

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