萬盛學電腦網 >> 數據庫 >> mysql教程 >> MySQL分庫分表的實現過程詳解介紹
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 '用戶數據所在數據庫名';
