mysql分表+分页查询_分表分页查询

背景

我们都知道，数据量大了，都要对数据库进行分库分表。奈何一直对分表及分表查询没什么概念，这里先不讲那么多概念，先直接演示一个demo。我们直接上车，请坐稳扶好。

分表

创建一个测试的数据库，并创建一个用户表

create database test;
use test;

create table tb_member(
	id bigint primary key auto_increment ,
	name varchar(20),
	age tinyint not null default '0'
	)engine=InnoDB  default charset=utf8 AUTO_INCREMENT=1 comment='用户表';

1
2
3
4
5
6
7
8
9

插入100条随机测试数据

INSERT INTO tb_member (name, age)
SELECT 
    CONCAT('Member', FLOOR(RAND() * 1000)) AS name,
    FLOOR(RAND() * 100) AS age
FROM
    information_schema.tables
LIMIT 100;

1
2
3
4
5
6
7
8

这里拆分了2张表

# 表一
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 ; 

1
2
3
4
5
6
7
8

# 表二
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 ;

1
2
3
4
5
6
7
8

接下来开始进行拆分

insert into tb_member1(id,name,age) select id,name,age from tb_member where id%2=0;
insert into tb_member2(id,name,age) select id,name,age from tb_member where id%2=1;

1
2
3

分页查询

接下来，我们需要考虑的是一张user表被拆分成2张表，那分页如何实现呢？

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 ;

1
2
3
4
5
6
7

我们会发现，你在member1或者member2中创建数据member_all表中也会出现同样的数据。其实tb_member_all表里面是没有存储数据，它就是一个外壳，里面的数据是tb_member1，tb_member2的并集，数据的存储是放在分表中；做数据查询的时候，就直接用tb_member_all来做就可以了。

查询数据

SELECT * FROM tb_member_all order by id LIMIT 10 OFFSET 10;
1

删除数据

delete from tb_member_all where id = 11;
1

好了，这个教程先到这里吧，断更好久了，趁着端午搞一篇吧。都是亲自实操演示过的教程，喜欢的点个关注吧。

插入数据

-- 声明变量
SET @name = 'zhangsan';
SET @age = 18;

-- 判断id的奇偶性并构造动态SQL
SET @table_name = IF(MOD((SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tb_member'), 2) = 0, 'tb_member1', 'tb_member2');

-- 执行插入操作
INSERT INTO tb_member (name, age) VALUES (@name, @age);

-- 获取最新插入的ID
SET @last_insert_id = LAST_INSERT_ID();

-- 更新到相应表的列中
IF @table_name = 'tb_member1' THEN  UPDATE tb_member1 SET id = @last_insert_id; END IF;

IF @table_name = 'tb_member2' THEN    UPDATE tb_member2 SET id = @last_insert_id; END IF;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

好了，分享就到这里。欢迎关注同名公众号，你的关注是我的无限动力。