【实操】分库分表，存储过程

分库分表，通过存储过程操作数据库（创建表、索引操作）。

前言：

STATISTICS （表）提供有关表索引的信息。

CREATE DEFINER=`mysql_dev`@`%` PROCEDURE `生成表`()
BEGIN
DECLARE i INT;
DECLARE table_name VARCHAR(40);
SET i = 0;
 
WHILE i<8 DO
SET table_name = CONCAT("detail_info_",i); 
SET @csql = CONCAT(
'CREATE TABLE ',table_name,'(
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT "主键（自增）",
  `msg_id` varchar(64) NOT NULL COMMENT "原始消息id",
  `msg_time` datetime NOT NULL COMMENT "消息发送时间（时间戳，单位：ms）",
  `msg_type` varchar(12) NOT NULL COMMENT "消息类型",
  `seq` bigint(20) NOT NULL COMMENT "消息位点",
  `content` varchar(2048) NOT NULL COMMENT "消息内容",
  PRIMARY KEY (`id`),
  KEY `idx_msg_id` (`msg_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT="详情记录表";'
);
 
PREPARE create_stmt FROM @csql;
EXECUTE create_stmt;
SET i = i+1;
END WHILE;
 
END

执行后，将生成8张表，分别为detail_info_0、detail_info_1、… … 、 detail_info_7;

CREATE DEFINER=`mysql_dev`@`%` PROCEDURE `add_idx_msgid_msgtype`()
    DETERMINISTIC
    COMMENT '为消息id和消息type添加索引'
BEGIN
	DECLARE
		i INT;
		
DECLARE
	table_name VARCHAR (40);
SET i = 0;

WHILE i < 8 DO
SET table_name = CONCAT(
	"detail_info_",
	i
);

SET @csql = CONCAT(
	'ALTER TABLE ',
	table_name,
	' ADD INDEX `idx_msgid_msgtype` (`msg_id`,`msg_type`)'
);

PREPARE create_stmt
FROM
	@csql;

EXECUTE create_stmt;
SET i = i + 1;

END WHILE;
END

CREATE DEFINER=`mysql_dev`@`%` PROCEDURE `将普通索引改为唯一索引`()
BEGIN
DECLARE
		i INT;

DECLARE
	table_name VARCHAR (40);
SET i = 0;

WHILE i < 8 DO
SET table_name = CONCAT(
	"detail_info_",
	i
);

SET @csql = CONCAT(
	'ALTER TABLE ',
	table_name,
	' DROp INDEX IF EXISTS `idx_msg_id`,
    ADD UNIQUE INDEX `uniqueidx_msg_id` (`msg_id`) USING BTREE'
);

PREPARE create_stmt
FROM
	@csql;
EXECUTE create_stmt;
SET i = i + 1;
END WHILE;		#Routine body goes here...
END

CREATE DEFINER=`mysql_dev`@`%` PROCEDURE `batch_add_index`()
BEGIN
	DECLARE
		i INT;
DECLARE
	table_name VARCHAR (40);
DECLARE
	current_database VARCHAR (100);

SET i = 0;

#查询当前数据库
SELECt
	DATAbase () INTO current_database;

WHILE i < 8 DO
	#拼接表名
SET table_name = CONCAT(
	"detail_info_",
	i
);

#查询索引是否存在，存在则删除索引
SELECT
	count(*) INTO @cnt
FROM
	information_schema.statistics #提供有关表索引的信息
WHERe
	TABLE_SCHEMA = current_database
AND TABLE_NAME = table_name
AND INDEX_NAME = 'idx_msg_id';

IF @cnt > 0 THEN

SET @dsql = CONCAT(
	'ALTER TABLE ',
	table_name,
	' DROp INDEX `idx_msg_id`'
);

PREPARE drop_stmt
FROM
	@dsql;
EXECUTE drop_stmt;
END IF;

#查询索引是否存在，不存在则创建
SELECT
	count(*) INTO @cntt
FROM
	information_schema.statistics
WHERe
	TABLE_SCHEMA = current_database
AND TABLE_NAME = table_name
AND INDEX_NAME = 'uniqueidx_msg_id';

IF @cntt = 0 THEN

SET @asql = CONCAT(
	'ALTER TABLE ',
	table_name,
	' ADD UNIQUE INDEX `uniqueidx_msg_id` (`msg_id`) USING BTREE'
);

PREPARE add_stmt
FROM
	@asql;
EXECUTE add_stmt;
END IF;

SET i = i + 1;

END WHILE;
END

# num作为执行函数的入参
CREATE DEFINER=`mysql_dev`@`%` PROCEDURE `multiInsertM`(IN `num` int)
BEGIN
	#Routine body goes here...

declare i int default 0;
start transaction; 
while i 
2. 删除重复数据 
CREATE DEFINER=`mysql_dev`@`%` PROCEDURE `delete_repeat_data`()
BEGIN
DECLARE i INT;
DECLARE table_name VARCHAR(40);
SET i = 0;
 WHILE i<8 DO
SET table_name = CONCAT("detail_info_",i); 
SET @csql = CONCAT(
'DELETE ',table_name, ' FROM ', table_name,' LEFT JOIN( SELECt MAX(wmg.id) AS id FROM ', table_name, ' AS wmg GROUP BY wmg.member_id, wmg.external_user_id,wmg.msg_id) AS tmp USING (id) WHERe tmp.id IS NULL');
PREPARE create_stmt FROM @csql;
EXECUTE create_stmt;
IF i = 1  
  THEN  
  SET i = i+2;
 ELSEIF i = 3  
    THEN  
        SET i = i+2;   
ELSEIF i = 5 
    THEN  
        SET i = i+2; 
ELSE
SET i = i+1;
END IF;
END WHILE;
END
 
3. 增加字段 
CREATE DEFINER=`mysql_dev`@`%` PROCEDURE `聊天记录  添加来源`()
BEGIN
DECLARE i INT;
DECLARE table_name VARCHAR(40);
SET i = 0;
 
WHILE i<8 DO
SET table_name = CONCAT("detail_info_",i); 
SET @csql = CONCAT(
'ALTER TABLE ',table_name,' ADD COLUMN `source_type` int(2) COMMENT "消息来源", ADD `recall_time` datetime DEFAULT NULL COMMENT "消息撤回时间"'
);
 
PREPARE create_stmt FROM @csql;
EXECUTE create_stmt;
SET i = i+1;
END WHILE;
 
END
 
4. 修改数据 
CREATE DEFINER=`mysql_dev`@`%` PROCEDURE `批量修改记录表`()
BEGIN
DECLARE i INT;
DECLARE table_name VARCHAR(40);
SET i = 0;
 
WHILE i<8 DO
SET table_name = CONCAT("detail_info_",i); 
SET @csql = CONCAT(
'update ',table_name,' set msg_time = created_time where created_time < msg_time'
);
 
PREPARE create_stmt FROM @csql;
EXECUTE create_stmt;
SET i = i+1;
END WHILE;
 
END