MySQL水平拆分(取模算法)

一:准备数据库表结构

create table user0(
id int unsigned primary key ,
name varchar(32) not null default ‘‘,
pwd  varchar(32) not null default ‘‘)
engine=myisam charset utf8;

create table user1(
id int unsigned primary key ,
name varchar(32) not null default ‘‘,
pwd  varchar(32) not null default ‘‘)
engine=myisam charset utf8;

create table user2(
id int unsigned primary key ,
name varchar(32) not null default ‘‘,
pwd  varchar(32) not null default ‘‘)
engine=myisam charset utf8;


create table uuid(
id int unsigned primary key auto_increment)engine=myisam charset utf8;

二:准备依赖

<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

三:配置文件

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/mydata
spring.datasource.username=root
spring.datasource.password=123

四:业务代码

package com.yjc.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class register {
       @Autowired
       JdbcTemplate jdbcTemplate;

       @RequestMapping("/register")
       public  String register(String name,String pwd){
              //往uuid表中添加一条空记录,主键自增
              String insertSql="INSERT INTO uuid VALUES (NULL);";
              jdbcTemplate.update(insertSql);
              //获取最后一次添加数据的主键
              Long aLong = jdbcTemplate.queryForObject("select last_insert_id()", Long.class);
              //取余,看看存到哪张表里
              String table="user"+aLong%3;
              //插入到该去的表中
              String insertUserSql = "INSERT INTO " + table + " VALUES (‘" + aLong + "‘,‘" + name + "‘,‘" + pwd + "‘);";
              jdbcTemplate.update(insertUserSql);
              return "success";
       }

       @RequestMapping("/get")
       public String get(Long id){
              //去拿张表里取数据
              String table ="user"+id%3;
              String sql = "select name from " + table + "  where id="+id;
              String name = jdbcTemplate.queryForObject(sql, String.class);
              return name;
       }
 }

五:测试

启动项目请求,模拟用户注册情况

http://localhost:8080/register?name=123&pwd=123

http://localhost:8080/get?id=8