MySQL分库分表的实现过程详解介绍
MySQL分库分表基础表介绍
表基本模型结构
这里我们模拟一个商城的基本的表结。此结构由（用户、门店、导购、门店商品、订单、订单对应的商品）。其中，导购也是一个用户，门店是只属于一个店主的，同时店主本身也是一个导购也是一个普通用户。
结构图：
构造数据脚本
MySQL分库分表(1)-脚本
对业务场景进行模拟
场景1：购买者下订单。
1、从session中获得客户ID。
2、可以通过时间戳等拼凑一个订单ID（在创建表的时候为了方便我用自增的，在以下我们一直就吧订单ID看成不是自增的，是用程序生成的）。
3、从商品的店铺能获得到导购ID（获取规则我们这边认为是随机）。
4、可以从商品中计算出订单价格。
最终就能拼凑出下单的INSERT SQL语句(这边我就不真正写插入语句了)
SET autocommit=0;
START TRANSACTION;
-- 创建订单语句
INSERT INTO orders VALUES(订单ID, 导购ID, 购买用户ID, 订单价格, 订单状态);
-- 创建商品订单语句
INSERT INTO order_goods VALUES(NULL, 订单ID, 商品ID, 商品价格, 商品数量);
-- 可以给添加多个商品到订单中
......
COMMIT;
set autocommit=1;
以上就是一个客户下单时要操作的，订单ID（订单号）是程序生成的，订单ID（订单号）是程序生成的，重要的事要说三遍。
情况2：购买者浏览订单
当用户查看订单列表的时候可以通过分页一次性获得自己的订单列表。
-- 每一页10行(这边顺便展示一下单数据量大时优化后的sql语句)
-- 查找用户ID为100的订单
SELECT l_o.orders_id,
o.user_guide_id,
o.user_id,
o.price,
og.price
FROM (
SELECT orders_id
FROM orders
WHERE user_id = 100
LIMIT 0, 10
) AS l_o
LEFT JOIN orders AS o ON l_o.orders_id = o.orders_id
LEFT JOIN order_goods AS og ON l_o.orders_id = og.orders_id;
情况3：导购查看订单
-- 每个导购也可以查看他销售了多少的订单
-- 查找导购ID为1的销售情况
SELECT o.orders_id,
o.user_guide_id,
o.user_id,
o.price,
og.price
FROM orders AS o
LEFT JOIN order_goods AS og ON o.orders_id = og.orders_id
WHERE o.orders_id IN(
SELECT orders_id
FROM (
SELECT orders_id
FROM orders
WHERE user_guide_id=1
LIMIT 0, 10
) AS tmp
);
情况4：导购修改订单
-- 这边我们修改订单金额就好，修改ID为1000的订单
UPDATE orders SET price = '10000' WHERE orders_id=1000;
情况5：店主为店铺添加商品
1、我们可以根据操作的用户获得店铺名
-- 添加商品伪SQL
INSERT INTO goods VALUES(NULL, 商品名, 商品价格, 店铺名);
MySQL分库分表创建新表结构
分表介绍
当下有静态分表和动态分表两种：
静态分表：事先估算出表能达到的量，然后根据每一个表需要存多少数据直接算出需要创建表的数量。如：1亿数据每一个表 100W 条数据那就要建100张表，然后通过一定的hash算法计算每一条数据存放在那张表。其实就有点像是使用partition table 一样。静态分表有一个毙命就是当分的那么多表还不满足时，需要再扩展难度和成本就会很高。
动态分表：同样也是对大数据量的表进行拆分，他可以避免静态分表带来的后遗症。当然也需要在设计上多一些东西（这往往是我们能接受的）。
如果使用了分表的设计的数据库在一些查询上面会变的复杂一些。
我的选择
本着要让之后让表能更好的扩展，并能达到手工指定数据到自己想要的表，为了以后能自动化分表操作，我选择了动态分表。
业务分解
由于在我们的业务中每一个导购除了能卖东西，还能买东西，因此在逻辑上就分为两张表：出售订单表、购买订单表。
业务分解后表结构图如下：
我们潜规则
我们是按user表中的每一个用户去指定他的订单数据要在哪个表。
由于按用户分表后会涉及到是然购买者方便查询，还是让销售者方便查询的问题。我们这里选择的是让销售者查询方便来分表，因为销售者的查询和对订单的修改会频繁一些。因此，我们将出售订单表中存放着比较完整的订单信息，而在购买订单表中存放的是出售订单表的ID作为关联。
我们出购买订单表ID和售订单表ID保持一致。
小提示：你也可以在购买订单表中添加一些冗余字段为了更好的查询，但是建议冗余字段不要是业务上是可变的。
业务分解后数据迁移到新表
-- 创建出售订单表-sell_order_1
CREATE TABLE sell_order_1 LIKE orders;
-- 修改出售订单表ID字段名
ALTER TABLE sell_order_1
CHANGE orders_id sell_order_id INT unsigned NOT NULL AUTO_INCREMENT
COMMENT '出售订单ID';
-- 修改商品订单表的订单ID名为sell_order_id
ALTER TABLE order_goods
CHANGE orders_id sell_order_id INT unsigned NOT NULL
COMMENT '出售订单ID';
-- 将orders表数据转移到sell_order_1表
INSERT INTO sell_order_1
SELECT * FROM orders;
-- 迁移商品表到 goods_1
CREATE TABLE goods_1 LIKE goods;
-- 插入goods_1表数据
INSERT INTO goods_1
SELECT * FROM goods;
-- 迁移订单商品表到order_goods_1
CREATE TABLE order_goods_1 LIKE order_goods;
-- 插入order_goods_1
INSERT INTO order_goods_1
SELECT * FROM order_goods;
-- 创建购买订单表
CREATE TABLE buy_order_1(
buy_order_id BIGINT unsigned NOT NULL COMMENT '出售订单ID与出售订单相等',
user_id INT unsigned DEFAULT NULL COMMENT '下单用户ID',
user_guide_id INT unsigned DEFAULT NULL COMMENT '导购ID',
PRIMARY KEY(buy_order_id),
KEY idx$buy_order_1$user_id(user_id),
KEY idx$buy_order_1user_guide_id(user_guide_id)
);
-- 买订单表导入数据
INSERT INTO buy_order_1
SELECT sell_order_id,
user_id,
user_guide_id
FROM sell_order_1;
-- user表增加指定表标识字段
ALTER TABLE user
ADD table_flag TINYINT NOT NULL DEFAULT 1
COMMENT '分表标识';
如有疑问跟帖说明。
MySQL分库分表使用Snowflake全局ID生成器
Snowflake的使用
安装 requests
pip install requests
安装 pysnowflake
pip install pysnowflake
启动pysnowflake服务
snowflake_start_server \
--address=192.168.137.11 \
--port=30001 \
--dc=1 \
--worker=1 \
--log_file_prefix=/tmp/pysnowflask.log
--address：本机的IP地址默认localhost这里解释一下参数意思（可以通过--help来获取）：
--dc：数据中心唯一标识符默认为0
--worker：工作者唯一标识符默认为0
--log_file_prefix：日志文件所在位置
使用示例（这边引用官网的）
# 导入pysnowflake客户端
>>> import snowflake.client
# 链接服务端并初始化一个pysnowflake客户端
>>> host = '192.168.137.11'
>>> port = 30001
>>> snowflake.client.setup(host, port)
# 生成一个全局唯一的ID（在MySQL中可以用BIGINT UNSIGNED对应）
>>> snowflake.client.get_guid()
3631957913783762945
# 查看当前状态
>>> snowflake.client.get_stats()
{
'dc': 1,
'worker': 1,
'timestamp': 1454126885629, # current timestamp for this worker
'last_timestamp': 1454126890928, # the last timestamp that generated ID on
'sequence': 1, # the sequence number for last timestamp
'sequence_overload': 1, # the number of times that the sequence is overflow
'errors': 1, # the number of times that clock went backward
}
数据整理重建ID
重建ID是一个很庞大的工程，首先要很了解表的结构。不然，如果少更新了某个表的一列都会导致数据的不一致。
当然，如果你的表中有很强的外键以及设置了级联那更新一个主键会更新其他相关联的外键。这里我还是不建议去依赖外键级联更新来投机取巧毕竟如果有数据库的设计在项目的里程碑中经过了n次变化，也不能肯定设置的外键一定是级联更新的。
在这边我强烈建议重建ID时候讲MySQL中的检查外键的参数设置为0。
SET FOREIGN_KEY_CHECKS=0;
小提示：其实理论上我们是没有必要重建ID的因为原来的ID已经是唯一的了而且是整型，他兼容BIGINT。但是这里我还是做了重建，主要是因为以后的数据一致。并且如果有些人的ID不是整型的，而是有一定含义的那时候也肯定需要做ID的重建。
修改相关表ID的数据类型为BIGINT
-- 修改商品表 goods_id 字段
ALTER TABLE goods_1
MODIFY COLUMN goods_id BIGINT UNSIGNED NOT NULL
COMMENT '商品ID';
-- 修改出售订单表 goods_id 字段
ALTER TABLE sell_order_1
MODIFY COLUMN sell_order_id BIGINT UNSIGNED NOT NULL
COMMENT '出售订单ID';
-- 修改购买订单表 buy_order_id 字段
ALTER TABLE buy_order_1
MODIFY COLUMN buy_order_id BIGINT UNSIGNED NOT NULL
COMMENT '出售订单ID与出售订单相等';
-- 修改订单商品表 order_goods_id、orders_id、goods_id 字段
ALTER TABLE order_goods_1
MODIFY COLUMN order_goods_id BIGINT UNSIGNED NOT NULL
COMMENT '订单商品表ID';
ALTER TABLE order_goods_1
MODIFY COLUMN sell_order_id BIGINT UNSIGNED NOT NULL
COMMENT '订单ID';
ALTER TABLE order_goods_1
MODIFY COLUMN goods_id BIGINT UNSIGNED NOT NULL
COMMENT '商品ID';
使用python重建ID
使用的python 模块：
模块名 版本 备注
pysnowflake 0.1.3 全局ID生成器
mysql_connector_python 2.1.3 mysql python API
这边只展示主程序：完整的程序在附件中都有
if __name__=='__main__':
# 设置默认的数据库链接参数
db_config = {
'user' : 'root',
'password': 'root',
'host' : '127.0.0.1',
'port' : 3306,
'database': 'test'
}
# 设置snowflake链接默认参数
snowflake_config = {
'host': '192.168.137.11',
'port': 30001
}
rebuild = Rebuild()
# 设置数据库配置
rebuild.set_db_config(db_config)
# 设置snowflak配置
rebuild.set_snowflake_config(snowflake_config)
# 链接配置snowflak
rebuild.setup_snowflake()
# 生成数据库链接和
rebuild.get_conn_cursor()
##########################################################################
## 修改商品ID
##########################################################################
# 获得商品的游标
goods_sql = '''
SELECT goods_id FROM goods
'''
goods_iter = rebuild.execute_select_sql([goods_sql])
# 根据获得的商品ID更新商品表(goods)和订单商品表(order_goods)的商品ID
for goods in goods_iter:
for (goods_id, ) in goods:
rebuild.update_table_id('goods', 'goods_id', goods_id)
rebuild.update_table_id('order_goods', 'goods_id', goods_id, rebuild.get_current_guid())
rebuild.commit()
##########################################################################
## 修改订单ID, 这边我们规定出售订单ID和购买订单ID相等
##########################################################################
# 获得订单的游标
orders_sql = '''
SELECT sell_order_id FROM sell_order_1
'''
sell_order_iter = rebuild.execute_select_sql([orders_sql])
# 根据出售订单修改 出售订单(sell_order_1)、购买订单(buy_order_1)、订单商品(order_goods)的出售订单ID
for sell_order_1 in sell_order_iter:
for (sell_order_id, ) in sell_order_1:
rebuild.update_table_id('sell_order_1', 'sell_order_id', sell_order_id)
rebuild.update_table_id('buy_order_1', 'buy_order_id', sell_order_id, rebuild.get_current_guid())
rebuild.update_table_id('order_goods', 'sell_order_id', sell_order_id, rebuild.get_current_guid())
rebuild.commit()
##########################################################################
## 修改订单商品表ID
##########################################################################
# 获得订单商品的游标
order_goods_sql = '''
SELECT order_goods_id FROM order_goods
'''
order_goods_iter = rebuild.execute_select_sql([order_goods_sql])
for order_goods in order_goods_iter:
for (order_goods_id, ) in order_goods:
rebuild.update_table_id('order_goods', 'order_goods_id', order_goods_id)
rebuild.commit()
# 关闭游标
rebuild.close_cursor('select')
rebuild.close_cursor('dml')
# 关闭连接
rebuild.close_conn()
完整的python程序：rebuild_id.py
执行程序
python rebuild_id.py
最后查看表的结果
SELECT * FROM goods LIMIT 0, 1;
--------------------- ------------ --------- ----------
| goods_id | goods_name | price | store_id |
--------------------- ------------ --------- ----------
| 3791337987775664129 | goods1 | 9369.00 | 1 |
--------------------- ------------ --------- ----------
SELECT * FROM sell_order_1 LIMIT 0, 1;
--------------------- --------------- --------- --------- --------
| sell_order_id | user_guide_id | user_id | price | status |
--------------------- --------------- --------- --------- --------
| 3791337998693437441 | 1 | 10 | 5320.00 | 1 |
--------------------- --------------- --------- --------- --------
SELECT * FROM buy_order_1 LIMIT 0, 1;
--------------------- --------- ---------------
| buy_order_id | user_id | user_guide_id |
--------------------- --------- ---------------
| 3791337998693437441 | 10 | 1 |
--------------------- --------- ---------------
SELECT * FROM order_goods LIMIT 0, 1;
--------------------- --------------------- --------------------- --------------- --------- ------
| order_goods_id | sell_order_id | goods_id | user_guide_id | price | num |
--------------------- --------------------- --------------------- --------------- --------- ------
| 3792076554839789569 | 3792076377064214529 | 3792076372429508609 | 1 | 9744.00 | 2 |
--------------------- --------------------- --------------------- --------------- --------- ------
建议：如果在生产上有使用到snowflake请务必要弄一个高可用防止单点故障，具体策略看你们自己定啦。
MySQL分库分表单库分表和迁移数据
因为在分表的时候我们需要知道我们分的是第几个表，所以我们先需要初始化我们的分表号
-- 创建一个系统信息表为了记录下当前最大的分表号
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');
我们的目标
1、我们的目标是实现能手动指定创建多少张表，并且能手动指定哪些用户到哪些表中。
2、最终能通过一定的算法，自动化这些分表迁移数据的工作。
在这里我们来实现 '1' 手动指定, 以下可能比较枯燥都是代码了, 一般只要看主逻辑有一个思路，代码自己玩转它 ^_^
程序流程图
代码主逻辑展示
在附件中有完整的代码
if __name__=='__main__':
# 设置默认的数据库链接参数
db_config = {
'user' : 'root',
'password': 'root',
'host' : '127.0.0.1',
'port' : 3306,
'database': 'test'
}
sharding = ShardingTable()
# 设置数据库配置
sharding.set_db_config(db_config)
# 初始化游标
sharding.get_conn_cursor()
# 提供需要分表的个数，创建分表
sharding.create_tables(9)
# 指定用户迁移数据到指定表
sharding.move_data('username1', 2)
sharding.move_data('username6', 6)
sharding.move_data('username66', 9)
上面我们指定新分9个表，并且迁移 'username1-店主'、'username6-导购'、'username66-普通用户' 的数据到指定分表
完整的python代码：sharding_table.py MySQL分库分表(4)-脚本
查看迁移后的数据
-- 查看迁移后的数据-购买订单表
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;
-- 查看用户数据分布在哪个分表 table_flag
SELECT * FROM user WHERE user_id IN(1, 6, 66);
若有疑问，请跟帖说明。
MySQL分库分表分表后数据的查询
场景1：购买者下订单
1、在浏览商品的时候能获得商品的 门店ID 和 商品ID，至于导购ID这里我们能以随机的形式得到（需要根据业务来确定如何获取导购ID）
2、通过导购ID获得导购的用户信息从而得到导购的数据应该放在那张分表。
3、将下单数据存入出售者的分表，和购买者的分表。
下面展示的是伪代码(因为只用SQL不好展示具体业务逻辑)，其实是自己比较懒不想写Python了。^_^
-- 获得导购分表信息，和所在门店
SELECT u.table_flag AS guide_flag,
ug.store_id AS store_id
FROM user AS u, user_guide AS ug
WHERE u.user_id = ug.user_id
AND user_guide_id = 导购ID;
SET autocommit=0;
START TRANSACTION;
-- 创建销售订单 sell_order_2 通过程序拼凑出来的
INSERT INTO sell_order_2
VALUES(order_SnowflakeID, 导购ID, 购买者ID, 订单总额, 订单状态);
-- 记录此订单有哪些商品
INSERT INTO order_goods_2
VALUES(order_goods_SnowflakeID, order_SnowflakeID, 商品ID, 商品价格, 商品个数);
-- 记录购买订单表 buy_order_6 购买者所在的分表，上面的是出售者所在的分表别弄混了
-- 购买者订单ID 和 出售者订单ID是一样的
INSERT INTO buy_order_6
VALUES(order_SnowflakeID, 用户ID, 导购ID)
COMMIT;
SET autocommit=1;
情况2：购买者浏览订单
浏览购买者订单就是比较麻烦的，因为购买者订单信息和商品信息不是在同一分表中。
1、分页查找出购买者的订单列表。
2、将订单信息返回给浏览器后，使用ajax获取每个订单的商品。
-- 获得用户的分表信息 user_id = 66
SELECT table_flag FROM user WHERE user_id=66;
------------
| table_flag |
------------
| 9 |
------------
-- 获取用户订单, 这些信息值直接先返回给浏览器的
SELECT * FROM buy_order_9 WHERE user_id=66 LIMIT 0, 1;
--------------------- --------- ---------------
| buy_order_id | user_id | user_guide_id |
--------------------- --------- ---------------
| 3792111966815784961 | 66 | 1 |
--------------------- --------- ---------------
-- 获取 user_guide_id=1 用户的分表信息
SELECT u.table_flag AS guide_flag
FROM user AS u, user_guide AS ug
WHERE u.user_id = ug.user_id
AND user_guide_id = 1;
------------
| guide_flag |
------------
| 2 |
------------
-- 浏览器通过ajax获取商品信息进行展现
SELECT *
FROM order_goods_2
WHERE sell_order_id = 3792111966815784961
AND user_guide_id = 1;
--------------------- --------------------- --------------------- --------------- --------- ------
| order_goods_id | sell_order_id | goods_id | user_guide_id | price | num |
--------------------- --------------------- --------------------- --------------- --------- ------
| 3792112143781859329 | 3792111966815784961 | 3792111950445416449 | 1 | 3100.00 | 2 |
| 3792112160789762049 | 3792111966815784961 | 3792111951305248769 | 1 | 5810.00 | 1 |
--------------------- --------------------- --------------------- --------------- --------- ------
从上面的试验我们可以看到原本在 '分库分表(1)--基础表介绍' 中的关联查询就能获得出订单的数据现在需要被拆为多个部分来查询(是不可避免的, 这样做也未必不是好事)。
这里说一下我们为什么要使用ajax来获取并展现 '订单商品' 的数据：
1、我们不知道 '购买订单' 的导购的分表是哪一个，因此我们需要便利查询出的每一条 '购买订单'，如果有10个订单就需要便利10次去获取对应导购是哪个分表。
2、获得分表完之后还需要通过每个分表去关联 '订单商品' 获得商品信息。
3、获得到以上信息或需要整合成一个列表返回给浏览器。
通过上面一次性把说有数据返回给浏览器的方法，会影响到用户体验，让用户觉得很慢的感觉。并且需要写复杂的逻辑，难以维护。
我们将查询时间放大，一个查是 1s 如果有10个订单 一次性完成就可能需要 11s 以上的时间才返回给浏览器。如果先将查询的订单返回给浏览器。看上去就只需要 1s就吧数据返回给浏览器了。
情况3：导购查看订单
导购也是一个普通用户, 因此一登陆系统就知道 导购ID 和 用户ID
-- 获得导购的分表信息 user_id = 6, user_guide_id = 5
SELECT table_flag FROM user WHERE user_id=6;
------------
| table_flag |
------------
| 6 |
------------
-- 查询订单信息
SELECT * FROM sell_order_6 WHERE user_guide_id = 5 LIMIT 0, 3;
--------------------- --------------- --------- --------- --------
| sell_order_id | user_guide_id | user_id | price | status |
--------------------- --------------- --------- --------- --------
| 3792112033412943873 | 5 | 10 | 5197.00 | 1 |
| 3792112033429721089 | 5 | 10 | 6826.00 | 1 |
| 3792112033446498305 | 5 | 10 | 5765.00 | 1 |
--------------------- --------------- --------- --------- --------
-- 查询订单商品信息
SELECT * FROM order_goods_6
WHERE sell_order_id IN(
3792112033412943873,
3792112033429721089,
3792112033446498305
);
--------------------- --------------------- --------------------- --------------- --------- ------
| order_goods_id | sell_order_id | goods_id | user_guide_id | price | num |
--------------------- --------------------- --------------------- --------------- --------- ------
| 3792112273532653569 | 3792112033412943873 | 3792111951800176641 | 5 | 7826.00 | 1 |
| 3792112292964864001 | 3792112033412943873 | 3792111952559345665 | 5 | 3057.00 | 2 |
| 3792112273545236481 | 3792112033429721089 | 3792111952660008961 | 5 | 8540.00 | 1 |
| 3792112292981641217 | 3792112033429721089 | 3792111951863091201 | 5 | 8545.00 | 1 |
| 3792112273566208001 | 3792112033446498305 | 3792111952110555137 | 5 | 8383.00 | 2 |
| 3792112292998418433 | 3792112033446498305 | 3792111952966193153 | 5 | 3282.00 | 2 |
--------------------- --------------------- --------------------- --------------- --------- ------
情况4：导购修改订单
-- 修改订单价格
UPDATE sell_order_6 SET price = 1000.00 WHERE sell_order_id = 3792112033412943873;
情况5：店主为店铺添加商品
添加商品只有店铺的店主有权限。然而店主也是一个普通用户。
-- 获得店主的分表信息 user_id = 1
SELECT table_flag FROM user WHERE user_id=1;
------------
| table_flag |
------------
| 2 |
------------
-- 店主添加商品
INSERT INTO goods_2 VALUES(SnowflakeID, 商品名称, 商品价格, 门店ID);
如有疑问请跟帖说明。
MySQL分库分表分库准备
分库思路
在分库前我们需要确定一下我们应该如何去分库：
1、我们是根据用户ID来进行分库，和分表的思路一样。
2、我们需要在用户表中标记一下用户的数据是在哪个库。
3、在系统设置表中应该记录下当前最大分库数量。
4、在系统设置表中应该记录现在所有分库的库名。
5、在系统设置表中应该记录每个分库的数据库连接描述符信息。
分库规则
我们以 '数字' 为分库标识最终分库的名称如：test_1、test_2、test_3 ...
在新增加库的时候，我们在新库中创建的表的数量是在系统设置表中的最大分表数。如在系统设置表中 name='max_sharding_table_num' 的 value='10'，这时我们会初始化每个分表的个数为10个。
数据迁移
和分表一样我们应该很清楚哪些表是需要进行分库，我们需要分库的表有 buy_order_n、goods_n、sell_order_n、order_goods_n。
我们应该将之前的数据的库名进行统一。如之前test库的数据要先迁移到 test_1 上
提醒：数据迁移慎重，不是说迁移就迁移的。其实也可以不用迁移的，如果不迁移之后的自动分库的代码就需要做多一点的判断。这为了统一我就做了迁移。
数据迁移SQL
-- 创建新库
CREATE DATABASE test_1;
use test;
-- 拼出需要创建的表
SELECT CONCAT('CREATE TABLE test_1.',
TABLE_NAME,
' LIKE ',
TABLE_SCHEMA, '.', TABLE_NAME, ';'
)
FROM information_schema.tables
WHERE TABLE_SCHEMA = 'test';
-- 创建表这边我们不迁移公用的表:user、store、user_guide、system_setting
CREATE TABLE test_1.buy_order_1 LIKE test.buy_order_1;
CREATE TABLE test_1.buy_order_10 LIKE test.buy_order_10;
CREATE TABLE test_1.buy_order_2 LIKE test.buy_order_2;
CREATE TABLE test_1.buy_order_3 LIKE test.buy_order_3;
CREATE TABLE test_1.buy_order_4 LIKE test.buy_order_4;
CREATE TABLE test_1.buy_order_5 LIKE test.buy_order_5;
CREATE TABLE test_1.buy_order_6 LIKE test.buy_order_6;
CREATE TABLE test_1.buy_order_7 LIKE test.buy_order_7;
CREATE TABLE test_1.buy_order_8 LIKE test.buy_order_8;
CREATE TABLE test_1.buy_order_9 LIKE test.buy_order_9;
CREATE TABLE test_1.goods_1 LIKE test.goods_1;
CREATE TABLE test_1.goods_10 LIKE test.goods_10;
CREATE TABLE test_1.goods_2 LIKE test.goods_2;
CREATE TABLE test_1.goods_3 LIKE test.goods_3;
CREATE TABLE test_1.goods_4 LIKE test.goods_4;
CREATE TABLE test_1.goods_5 LIKE test.goods_5;
CREATE TABLE test_1.goods_6 LIKE test.goods_6;
CREATE TABLE test_1.goods_7 LIKE test.goods_7;
CREATE TABLE test_1.goods_8 LIKE test.goods_8;
CREATE TABLE test_1.goods_9 LIKE test.goods_9;
CREATE TABLE test_1.order_goods_1 LIKE test.order_goods_1;
CREATE TABLE test_1.order_goods_10 LIKE test.order_goods_10;
CREATE TABLE test_1.order_goods_2 LIKE test.order_goods_2;
CREATE TABLE test_1.order_goods_3 LIKE test.order_goods_3;
CREATE TABLE test_1.order_goods_4 LIKE test.order_goods_4;
CREATE TABLE test_1.order_goods_5 LIKE test.order_goods_5;
CREATE TABLE test_1.order_goods_6 LIKE test.order_goods_6;
CREATE TABLE test_1.order_goods_7 LIKE test.order_goods_7;
CREATE TABLE test_1.order_goods_8 LIKE test.order_goods_8;
CREATE TABLE test_1.order_goods_9 LIKE test.order_goods_9;
CREATE TABLE test_1.sell_order_1 LIKE test.sell_order_1;
CREATE TABLE test_1.sell_order_10 LIKE test.sell_order_10;
CREATE TABLE test_1.sell_order_2 LIKE test.sell_order_2;
CREATE TABLE test_1.sell_order_3 LIKE test.sell_order_3;
CREATE TABLE test_1.sell_order_4 LIKE test.sell_order_4;
CREATE TABLE test_1.sell_order_5 LIKE test.sell_order_5;
CREATE TABLE test_1.sell_order_6 LIKE test.sell_order_6;
CREATE TABLE test_1.sell_order_7 LIKE test.sell_order_7;
CREATE TABLE test_1.sell_order_8 LIKE test.sell_order_8;
CREATE TABLE test_1.sell_order_9 LIKE test.sell_order_9;
-- 生成插入表的数据
SELECT CONCAT('INSERT INTO ',
TABLE_SCHEMA, '.', TABLE_NAME,
' SELECT * FROM test', '.', TABLE_NAME, ';'
)
FROM information_schema.tables
WHERE TABLE_SCHEMA = 'test_1';
-- 插入数据
INSERT INTO test_1.buy_order_1 SELECT * FROM test.buy_order_1;
INSERT INTO test_1.buy_order_10 SELECT * FROM test.buy_order_10;
INSERT INTO test_1.buy_order_2 SELECT * FROM test.buy_order_2;
INSERT INTO test_1.buy_order_3 SELECT * FROM test.buy_order_3;
INSERT INTO test_1.buy_order_4 SELECT * FROM test.buy_order_4;
INSERT INTO test_1.buy_order_5 SELECT * FROM test.buy_order_5;
INSERT INTO test_1.buy_order_6 SELECT * FROM test.buy_order_6;
INSERT INTO test_1.buy_order_7 SELECT * FROM test.buy_order_7;
INSERT INTO test_1.buy_order_8 SELECT * FROM test.buy_order_8;
INSERT INTO test_1.buy_order_9 SELECT * FROM test.buy_order_9;
INSERT INTO test_1.goods_1 SELECT * FROM test.goods_1;
INSERT INTO test_1.goods_10 SELECT * FROM test.goods_10;
INSERT INTO test_1.goods_2 SELECT * FROM test.goods_2;
INSERT INTO test_1.goods_3 SELECT * FROM test.goods_3;
INSERT INTO test_1.goods_4 SELECT * FROM test.goods_4;
INSERT INTO test_1.goods_5 SELECT * FROM test.goods_5;
INSERT INTO test_1.goods_6 SELECT * FROM test.goods_6;
INSERT INTO test_1.goods_7 SELECT * FROM test.goods_7;
INSERT INTO test_1.goods_8 SELECT * FROM test.goods_8;
INSERT INTO test_1.goods_9 SELECT * FROM test.goods_9;
INSERT INTO test_1.order_goods_1 SELECT * FROM test.order_goods_1;
INSERT INTO test_1.order_goods_10 SELECT * FROM test.order_goods_10;
INSERT INTO test_1.order_goods_2 SELECT * FROM test.order_goods_2;
INSERT INTO test_1.order_goods_3 SELECT * FROM test.order_goods_3;
INSERT INTO test_1.order_goods_4 SELECT * FROM test.order_goods_4;
INSERT INTO test_1.order_goods_5 SELECT * FROM test.order_goods_5;
INSERT INTO test_1.order_goods_6 SELECT * FROM test.order_goods_6;
INSERT INTO test_1.order_goods_7 SELECT * FROM test.order_goods_7;
INSERT INTO test_1.order_goods_8 SELECT * FROM test.order_goods_8;
INSERT INTO test_1.order_goods_9 SELECT * FROM test.order_goods_9;
INSERT INTO test_1.sell_order_1 SELECT * FROM test.sell_order_1;
INSERT INTO test_1.sell_order_10 SELECT * FROM test.sell_order_10;
INSERT INTO test_1.sell_order_2 SELECT * FROM test.sell_order_2;
INSERT INTO test_1.sell_order_3 SELECT * FROM test.sell_order_3;
INSERT INTO test_1.sell_order_4 SELECT * FROM test.sell_order_4;
INSERT INTO test_1.sell_order_5 SELECT * FROM test.sell_order_5;
INSERT INTO test_1.sell_order_6 SELECT * FROM test.sell_order_6;
INSERT INTO test_1.sell_order_7 SELECT * FROM test.sell_order_7;
INSERT INTO test_1.sell_order_8 SELECT * FROM test.sell_order_8;
INSERT INTO test_1.sell_order_9 SELECT * FROM test.sell_order_9;
-- 向系统表中添加当前最大分库数量
INSERT INTO test.system_setting
VALUES(NULL, 'max_sharding_database_num', 1);
-- 向系统表中添加分库名前缀
INSERT INTO test.system_setting
VALUES(NULL, 'sharding_database_prefix', 'test');
-- 向系统表中添加当前有哪些分库
INSERT INTO test.system_setting
VALUES(NULL, 'sharding_database', 'test_1');
-- 修改系统表字段类value型为varchar(120)
ALTER TABLE test.system_setting
MODIFY `value` varchar(120) NOT NULL COMMENT '系统设置值';
-- 向系统表添加响应数据库链接描述符
INSERT INTO test.system_setting
VALUES(NULL, 'test_1', '{"user":"root","password":"root","host":"127.0.0.1","port":3306,"database":"test_1"}');
-- 初始化用户所在库为test_1
ALTER TABLE user
ADD db_name VARCHAR(45) NOT NULL DEFAULT 'test_1'
COMMENT '用户数据所在数据库名';
您可能感兴趣的文章：
学习MySQL数据分页查询(limit用法)
分享:Mysql 5.0存储过程学习总结
MySQL常见错误代码解析
Mysql分表、分区的区别有哪些
分享:mysql导出数据到txt文件
Mysql内存表有什么作用
mysql命令之备份数据库
mysql数据库性能优化技巧
mysql命令行导入与导出数据库
mysql分页实现代码 mysql limit分页