------------------------------------------------------------------
-- SQL 代码
------------------------------------------------------------------
-- 创建一个系统信息表为了记录下当前最大的分表号
DROP TABLE system_setting;
CREATE TABLE system_setting(
  system_setting_id INT unsigned NOT NULL AUTO_INCREMENT COMMENT '系统设置表ID',
  name VARCHAR(45) NOT NULL COMMENT '系统设置项目名',
  value VARCHAR(45) NOT NULL COMMENT '系统设置值',
  PRIMARY KEY(system_setting_id)  
);

-- 初始化当前最大分表号
INSERT INTO system_setting VALUES(NULL, 'max_sharding_table_num', 1);

-- 指定需要有哪些表需要分，为了下面分表时进行锁表
INSERT INTO system_setting VALUES(NULL, 'sharding_table', 'sell_order');
INSERT INTO system_setting VALUES(NULL, 'sharding_table', 'buy_order');
INSERT INTO system_setting VALUES(NULL, 'sharding_table', 'goods');
INSERT INTO system_setting VALUES(NULL, 'sharding_table', 'order_goods');

-- 需要分表的表是通过什么字段来分表的
INSERT INTO system_setting VALUES(NULL, 'sharding_sell_order_by', 'user_guide_id');
INSERT INTO system_setting VALUES(NULL, 'sharding_buy_order_by', 'user_id');
INSERT INTO system_setting VALUES(NULL, 'sharding_goods_by', 'store_id');
INSERT INTO system_setting VALUES(NULL, 'sharding_order_goods_by', 'user_guide_id');

-- 普通用户需要分那张表
INSERT INTO system_setting VALUES(NULL, 'normal_user_sharding', 'buy_order');
-- 导购需要分的表
INSERT INTO system_setting VALUES(NULL, 'user_guide_sharding', 'buy_order');
INSERT INTO system_setting VALUES(NULL, 'user_guide_sharding', 'sell_order');
INSERT INTO system_setting VALUES(NULL, 'user_guide_sharding', 'order_goods');
-- 店主需要分哪些表
INSERT INTO system_setting VALUES(NULL, 'store_owner_sharding', 'buy_order');
INSERT INTO system_setting VALUES(NULL, 'store_owner_sharding', 'sell_order');
INSERT INTO system_setting VALUES(NULL, 'store_owner_sharding', 'order_goods');
INSERT INTO system_setting VALUES(NULL, 'store_owner_sharding', 'goods');


-- 查看迁移后的数据-购买订单表
SELECT * FROM buy_order_2 LIMIT 0, 1;
SELECT * FROM buy_order_6 LIMIT 0, 1;
SELECT * FROM buy_order_9 LIMIT 0, 1;
-- 查看迁移后的数据-商品表
SELECT * FROM goods_2 LIMIT 0, 1;
SELECT * FROM goods_6 LIMIT 0, 1;
SELECT * FROM goods_9 LIMIT 0, 1;
-- 查看迁移后的数据-出售订单表
SELECT * FROM sell_order_2 LIMIT 0, 1;
SELECT * FROM sell_order_6 LIMIT 0, 1;
SELECT * FROM sell_order_9 LIMIT 0, 1;
-- 查看迁移后的数据-订单商品表
SELECT * FROM order_goods_2 LIMIT 0, 1;
SELECT * FROM order_goods_6 LIMIT 0, 1;
SELECT * FROM order_goods_9 LIMIT 0, 1;



------------------------------------------------------------------------
-- 还原数据到分表前
------------------------------------------------------------------------
-- 拼凑 删除test库 表的 SQL
SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', table_name, ';')
FROM information_schema.tables
WHERE TABLE_SCHEMA='test';

-- 删除分的表
DROP TABLE test.buy_order_10;
DROP TABLE test.buy_order_2;
DROP TABLE test.buy_order_3;
DROP TABLE test.buy_order_4;
DROP TABLE test.buy_order_5;
DROP TABLE test.buy_order_6;
DROP TABLE test.buy_order_7;
DROP TABLE test.buy_order_8;
DROP TABLE test.buy_order_9;
DROP TABLE test.goods_10;
DROP TABLE test.goods_2;
DROP TABLE test.goods_3;
DROP TABLE test.goods_4;
DROP TABLE test.goods_5;
DROP TABLE test.goods_6;
DROP TABLE test.goods_7;
DROP TABLE test.goods_8;
DROP TABLE test.goods_9;
DROP TABLE test.order_goods_10;
DROP TABLE test.order_goods_2;
DROP TABLE test.order_goods_3;
DROP TABLE test.order_goods_4;
DROP TABLE test.order_goods_5;
DROP TABLE test.order_goods_6;
DROP TABLE test.order_goods_7;
DROP TABLE test.order_goods_8;
DROP TABLE test.order_goods_9;
DROP TABLE test.sell_order_10;
DROP TABLE test.sell_order_2;
DROP TABLE test.sell_order_3;
DROP TABLE test.sell_order_4;
DROP TABLE test.sell_order_5;
DROP TABLE test.sell_order_6;
DROP TABLE test.sell_order_7;
DROP TABLE test.sell_order_8;
DROP TABLE test.sell_order_9;

-- 跟新系统表最大分表标记为 1
UPDATE system_setting 
SET value=1 
WHERE name = 'max_sharding_table_num';

-- 跟新用户的所有初始表为 1
UPDATE user 
SET table_flag = 1;