当前位置：首页 > news >正文

MySQL分表后，如何做分页查询？

news 2025/8/16 0:57:05

参考: https://blog.csdn.net/qq_44732146/article/details/127616258

user.sql 完整的执行一遍，可以做到分表和分页

数据是实时的，往一张子表里插入之后，all表就能立刻查询到

在这里实现分页查询的是MyIsam引擎，这个引擎不支持事务，那么如果要支持事务该怎么办呢？


DROP table IF EXISTS dd_user;
create table dd_user(id bigint primary key auto_increment ,name varchar(20),age tinyint not null default '0'
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;DELIMITER $$
CREATE PROCEDURE insert_data_dd_user(count_ INT)
BEGIN
DECLARE i INT;
SET i =1;
WHILE(i<=count_) DO
INSERT into dd_user(id,name,age) VALUES (i,concat('张三',i),20);
SET i = i+1;
END WHILE;
COMMIT;
END;$$
DELIMITER ;
call insert_data_dd_user(20000);DROP table IF EXISTS tb_member1;
create table tb_member1(id bigint primary key auto_increment ,name varchar(20),age tinyint not null default '0'
)ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
DROP table IF EXISTS tb_member2;
create table tb_member2(id bigint primary key auto_increment ,name varchar(20),age tinyint not null default '0'
)ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
insert into tb_member1(id,name,age) select id,name,age from dd_user where id%2=0;
insert into tb_member2(id,name,age) select id,name,age from dd_user where id%2=1;DROP table IF EXISTS tb_member_all;
create table tb_member_all(
id bigint primary key auto_increment ,
name varchar(20),
age tinyint not null default '0'
)ENGINE=MERGE UNION=(tb_member1,tb_member2) INSERT_METHOD=LAST CHARSET=utf8 AUTO_INCREMENT=1 ;select * from dd_user order by id asc limit 3,5;
select * from tb_member_all order by id asc limit 3,5;