October 2016
M T W T F S S
 12
3456789
10111213141516
17181920212223
24252627282930
31  
[post_grid id="69"]

Partición de tabla, y llenar automaticamente sus registros

Para fragmentar tabla horizontalete, presentamos el siguiente Script.

create table tablanormal(llave bigint primary key, dato varchar(15))
ENGINE= MYISAM
create table tablaparticionada(llave bigint primary key, dato varchar(15))
ENGINE= MYISAM
partition by range (llave)(
partition p0 values less than (100000),
partition p1 values less than (200000),
partition p2 values less than (300000),
partition p3 values less than (400000),

partition p4 values less than (500000),
partition p5 values less than (600000),
partition p6 values less than (700000),
partition p7 values less than (800000),
partition p8 values less than (900000),
partition p9 values less than (1000000),
partition p10 values less than (1100000),
partition p11 values less than (1200000),
partition p12 values less than (1300000),
partition p13 values less than (1400000),
partition p14 values less than (1500000),
partition p15 values less than (1600000),
partition p16 values less than (1700000),
partition p17 values less than (1800000),
partition p18 values less than (1900000),
partition p19 values less than (2000000),

partition p20 values less than (2500000),
partition p21 values less than (3000000),
partition p22 values less than (3500000),
partition p23 values less than (4000000),
partition p24 values less than (4500000),
partition p25 values less than (5000001)
);

delimeter//
create PROCEDURE insertatablanormal(v1 bigint )
BEGIN
while v1> 0 Do
insert into tablanormal values(v1,’123456789′);
set v1=v1-1;
end while;
end
//
delimeter //
create PROCEDURE insertatablaparticionada(v1 bigint )
BEGIN
while v1> 0 Do
insert into tablaparticionada  values(v1,’123456789′);
set v1=v1-1;
end while;
end
//

SET SQL_SAFE_UPDATES=0;
truncate tablanormal;
truncate tablaparticionada;
call insertatablanormal(1000000);
call insertatablaparticionada(1000000);
SELECT * FROM mibase.tablanormal limit 0, 1000000;
SELECT * FROM mibase.tablaparticionada limit 0, 1000000;
UPDATE mibase.tablanormal SET dato = ’00ABCDEFGHI123′;
UPDATE mibase.tablaparticionada SET dato = ‘AAABCDEFG12345’;
DELETE FROM mibase.tablanormal;
DELETE FROM mibase.tablaparticionada;

Leave a Reply

Your email address will not be published.