Mysql 水平分表小案例

一、场景：把订单表水平分表。总表为order表，根据订单编号的最后一位数字划（0-9）分为order0--order9订单分表。并把总表设置为只读，分表为可读可写。查询数据用总表，写入数据用分表。

二、注意：水平分表的各个表的结构必须相同，包括字段名、字段类型、索引、引擎等等。总表应使用MyISAM引擎。

三、相关sql语句。

①创建分表。

CREATE TABLE `order0` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '' COMMENT '收件人姓名',
  `phone` varchar(40) NOT NULL DEFAULT '' COMMENT '收件人手机号',
  `goods_name` text COMMENT '货品名字',
  `area` varchar(255) NOT NULL COMMENT '省市区',
  `address` varchar(255) NOT NULL DEFAULT '' COMMENT '详细地址',
  `pay_time` varchar(20) NOT NULL DEFAULT '' COMMENT '支付时间',
  `trade_no` varchar(50) NOT NULL DEFAULT '' COMMENT '订单号',
   .......    ............(省略相关字段)
  `create_time` int(11) NOT NULL DEFAULT '0' COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `name` (`name`) USING BTREE,
  KEY `phone` (`phone`) USING BTREE,
  KEY `trade_no` (`trade_no`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='订单数据分表';

CREATE TABLE order1 like order0;
CREATE TABLE order2 like order0;
CREATE TABLE order3 like order0;
CREATE TABLE order4 like order0;
CREATE TABLE order5 like order0;
CREATE TABLE order6 like order0;
CREATE TABLE order7 like order0;
CREATE TABLE order8 like order0;
CREATE TABLE order9 like order0;

②创建总表

CREATE TABLE `erp_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '' COMMENT '收件人姓名',
  `phone` varchar(40) NOT NULL DEFAULT '' COMMENT '收件人手机号',
  `goods_name` text COMMENT '货品名字',
  `area` varchar(255) NOT NULL COMMENT '省市区',
  `address` varchar(255) NOT NULL DEFAULT '' COMMENT '详细地址',
  `pay_time` varchar(20) NOT NULL DEFAULT '' COMMENT '支付时间',
  `trade_no` varchar(50) NOT NULL DEFAULT '' COMMENT '订单号',
   .......    ............(省略相关字段)
  `create_time` int(11) NOT NULL DEFAULT '0' COMMENT '创建时间',
  KEY `id` (`id`) USING BTREE,
  KEY `name` (`name`) USING BTREE,
  KEY `phone` (`phone`) USING BTREE,
  KEY `trade_no` (`trade_no`) USING BTREE
) ENGINE=MRG_MyISAM  DEFAULT CHARSET=utf8 UNION=(`order0`,`order1`,`order2`,`order3`,`order4`,`order5`,`order6`,`order7`,`order8`,`order9`) COMMENT='订单数据主表';

③创建10个分表的触发器，保证所有表的id值唯一。

create table order_ids(id int);
insert into order_ids values(1);

//由于这里的编辑器显示错乱问题，此处sql语句用图片的方式展示。（以此类推，创建10个分表的触发器）

④其他相关sql语句。

格式化触发器

truncate order_ids;
insert into order_ids values(1);

清空表数据

TRUNCATE TABLE order0;
TRUNCATE TABLE order1;
TRUNCATE TABLE order2;
TRUNCATE TABLE order3;
TRUNCATE TABLE order4;
TRUNCATE TABLE order5;
TRUNCATE TABLE order6;
TRUNCATE TABLE order7;
TRUNCATE TABLE order8;
TRUNCATE TABLE order9;