mysql分库还是分表_MySQL分库分表分库准备(6th)

-- 创建新库

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 '用户数据所在数据库名';