mysql存储过程--创建表以及根据已有的表进行分表分库

恰子李

已于 2022-03-08 18:41:31 修改

阅读量3.8k

点赞数

分类专栏：数据库文章标签： mysql 数据库 sql

于 2018-05-04 18:30:04 首次发布

本文链接：https://blog.csdn.net/qiaziliping/article/details/80198924

版权

数据库专栏收录该内容

11 篇文章 0 订阅

订阅专栏

1 创建多表的存储过程

/* -----------------------t_user分表SQL--------------------------------*/
drop PROCEDURE if exists import_user_data;
create PROCEDURE import_user_data(IN table_num int)
-- table_num 表示有多少张表
-- 导出数据到user的分表
BEGIN
		DECLARE table_name VARCHAR(30); 
    declare table_pre varchar(20) default 't_user_'; -- 表前缀
		declare temp_text text default '';
    declare alter_text text default '';

    set @temp_table_num = table_num; -- 分了多少张表赋给一个临时变量

    while table_num > -1 DO   
        set table_num = table_num - 1;

        set table_name =  CONCAT(table_pre,table_num);

        set temp_text = CONCAT('CREATE TABLE ',table_name,' AS SELECT * FROM t_user WHERE id%',@temp_table_num,'=',table_num);
        SELECT temp_text; 
        -- 执行SQL
			  SET @sql_text=temp_text;
			  PREPARE stmt FROM @sql_text;
			  EXECUTE stmt;
			  DEALLOCATE PREPARE stmt;  
        -- 创建主键
				set temp_text = CONCAT('ALTER TABLE ',table_name,' ADD CONSTRAINT PK_ID PRIMARY KEY (id)');
				SELECT temp_text; 
        -- 执行SQL
			  SET @sql_text=temp_text;
			  PREPARE stmt FROM @sql_text;
			  EXECUTE stmt;
			  DEALLOCATE PREPARE stmt;  
    end while;
end;
-- 调用如下：
-- set @table_num = 10;
-- call import_user_data(@table_num); 
/* -----------------------t_user分表SQL--------------------------------*/

2 通用的–根据已有的表（表数据比较大时）进行分库分表

-- 说明：此存储过程唯一定死的就是user_id,是user_id的个位取模db_num个为数据库后缀，user_id个位以上取模table_num为表后缀；
-- 老铁们可根据自己项目的需要，自行改编


drop PROCEDURE if exists common_split_db_table;
CREATE PROCEDURE `common_split_db_table`(IN db_num int,IN table_num int,IN db_pre varchar(30),IN table_pre varchar(30),IN data_table_name varchar(30))
BEGIN
		-- 存储过程说明，通用的分库分表存储过程
    -- db_num	分多少个数据库	
    -- table_num  每个数据库多少张表		
		-- db_pre 数据库名称前缀
		-- table_pre 分表的前缀
		-- data_table_name 原数据表的名称，如： 将t_sport表的数据拆分为10数据库，10张表，则data_table_name为 t_sport

    --  数据库相关定义
		declare db_name varchar(30) default '';  -- 数据库名称  
		-- declare db_pre varchar(30) default 'hesvit_sport_';  -- 数据库名称前缀
		declare db_sql_text text default '';
		declare db_num_tp int(10) default 0; -- 取模的基数
		--   table相关定义
		declare table_name varchar(30) default '';   -- 分表的名称
		-- declare table_pre varchar(20) default 't_sport_'; -- 分表的前缀
    declare table_sql_text text default '';
	  declare table_num_tp int(10) default 0; -- 分表取模的基数
    

		set db_num_tp = db_num;
		
		-- ················进行分库操作··············
		while db_num_tp > 0 DO
				set db_num_tp = db_num_tp - 1;

				set db_name =  CONCAT(db_pre,db_num_tp);
				-- 拼接创建数据执行的SQL
        set db_sql_text = CONCAT('create database if not exists ',db_name,' DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci');
				
				select db_sql_text;

				SET @sql_text=db_sql_text;
				PREPARE stmt FROM @sql_text;
				EXECUTE stmt;
				DEALLOCATE PREPARE stmt;  

				-- ················进行分表操作··············
				set table_num_tp = table_num;
				while table_num_tp > 0 DO
						set table_num_tp = table_num_tp - 1;
						set table_name = CONCAT(table_pre,table_num_tp);
						-- 拼接执行的SQL
						-- 一下SQL是根据user_id的个位取模db_num个为数据库后缀，user_id个位以上取模table_num为表后缀
						-- select * from t_sport where SUBSTR(user_id,1 ,LENGTH(user_id)-1)% table_num = table_num_tp and SUBSTR(user_id,LENGTH(user_id)) % db_num = db_num_tp
						set table_sql_text = CONCAT('create table ',db_name,'.',table_name,'  AS SELECT * FROM ',data_table_name,' WHERE SUBSTR(user_id,1 ,LENGTH(user_id)-1)%'
						,table_num,'=',table_num_tp,' and SUBSTR(user_id,LENGTH(user_id)) %',db_num,' = ',db_num_tp);
						
						
						select table_sql_text;
						
						SET @sql_text_1=table_sql_text;
						PREPARE stmt FROM @sql_text_1;
						EXECUTE stmt;
						DEALLOCATE PREPARE stmt;

						-- 给各个sport的分表添加主键
						set table_sql_text = CONCAT('ALTER TABLE ',db_name,'.',table_name,' ADD CONSTRAINT PK_ID PRIMARY KEY (id)');
						-- 执行SQL
						SET @sql_text_1=table_sql_text;
						PREPARE stmt FROM @sql_text_1;
						EXECUTE stmt;
						DEALLOCATE PREPARE stmt;
				end while;

    end while;

end


set @db_num = 5;
set @table_num = 20;
set @db_pre = 'hesvit_db_';
set @table_pre = 't_sport_';
set @data_table_name = 't_sport1';
call common_split_db_table(@db_num,@table_num,@db_pre,@table_pre,@data_table_name);

3 运用到游标插入表数据

我的理解，游标就相当于一个集合，再从游标中迭代数据

CREATE DEFINER=`root`@`%` PROCEDURE `create_data_item_reason`()
BEGIN
		DECLARE text_sql text;  -- 执行的SQL语句
		DECLARE str_1 varchar(50) DEFAULT '推送太频繁';
		DECLARE str_2 varchar(50) DEFAULT '内容重复';
		DECLARE str_3 varchar(50) DEFAULT '不想被打扰';
		DECLARE str_4 varchar(50) DEFAULT '数据偏差';
		DECLARE str_5 varchar(50) DEFAULT '对我无作用';
		DECLARE str_6 varchar(50) DEFAULT '其他';
		DECLARE str_7 varchar(50) DEFAULT '-1';
		DECLARE t_date_time datetime;

		DECLARE temp_id int(11) default 0;
		DECLARE temp_name varchar(50) default '';

		DECLARE cur_end int(11) default 1;
		-- 定义一个游标
		declare cur_reson cursor for select id,name from t_message_item;
		DECLARE EXIT HANDLER FOR NOT FOUND SET cur_end = 0;
		-- 打开游标
		open cur_reson;
		
		set t_date_time = SYSDATE();
    while cur_end > 0 DO
			    -- 迭代游标
				fetch cur_reson into temp_id,temp_name;
				select temp_id,temp_name;
				-- 1
				-- 注意：''''转义之后是 ',如：'''',t_date_time,'''' 转义之后未 '2022-01-01 01:01:01'
				set text_sql = CONCAT('insert into t_message_item_reason (item_id,reason,remark,create_user_id,create_time,update_user_id,update_time) values (',temp_id,',','''',str_1,'''',',');				
				set text_sql = CONCAT(text_sql,'''','','''',',1,','''',t_date_time,'''',',1,','''',t_date_time,'''',')');	
				select text_sql;	
				
				SET @sql_text = text_sql;
				PREPARE stmt FROM @sql_text;
				EXECUTE stmt;
				DEALLOCATE PREPARE stmt;
        -- 2
				set text_sql = CONCAT('insert into t_message_item_reason (item_id,reason,remark,create_user_id,create_time,update_user_id,update_time) values (',temp_id,',','''',str_2,'''',',');				
				set text_sql = CONCAT(text_sql,'''','','''',',1,','''',t_date_time,'''',',1,','''',t_date_time,'''',')');	
				select text_sql;	
				
				SET @sql_text = text_sql;
				PREPARE stmt FROM @sql_text;
				EXECUTE stmt;
				DEALLOCATE PREPARE stmt;
				-- 3
				set text_sql = CONCAT('insert into t_message_item_reason (item_id,reason,remark,create_user_id,create_time,update_user_id,update_time) values (',temp_id,',','''',str_3,'''',',');				
				set text_sql = CONCAT(text_sql,'''','','''',',1,','''',t_date_time,'''',',1,','''',t_date_time,'''',')');	
				select text_sql;	
				
				SET @sql_text = text_sql;
				PREPARE stmt FROM @sql_text;
				EXECUTE stmt;
				DEALLOCATE PREPARE stmt;
-- 4
				set text_sql = CONCAT('insert into t_message_item_reason (item_id,reason,remark,create_user_id,create_time,update_user_id,update_time) values (',temp_id,',','''',str_4,'''',',');				
				set text_sql = CONCAT(text_sql,'''','','''',',1,','''',t_date_time,'''',',1,','''',t_date_time,'''',')');	
				select text_sql;	
				
				SET @sql_text = text_sql;
				PREPARE stmt FROM @sql_text;
				EXECUTE stmt;
				DEALLOCATE PREPARE stmt;
-- 5
				set text_sql = CONCAT('insert into t_message_item_reason (item_id,reason,remark,create_user_id,create_time,update_user_id,update_time) values (',temp_id,',','''',str_5,'''',',');				
				set text_sql = CONCAT(text_sql,'''','','''',',1,','''',t_date_time,'''',',1,','''',t_date_time,'''',')');	
				select text_sql;	
				
				SET @sql_text = text_sql;
				PREPARE stmt FROM @sql_text;
				EXECUTE stmt;
				DEALLOCATE PREPARE stmt;
				-- 6
				set text_sql = CONCAT('insert into t_message_item_reason (item_id,reason,remark,create_user_id,create_time,update_user_id,update_time) values (',temp_id,',','''',str_6,'''',',');				
				set text_sql = CONCAT(text_sql,'''','','''',',1,','''',t_date_time,'''',',1,','''',t_date_time,'''',')');	
				select text_sql;	
				
				SET @sql_text = text_sql;
				PREPARE stmt FROM @sql_text;
				EXECUTE stmt;
				DEALLOCATE PREPARE stmt;
-- 7
				set text_sql = CONCAT('insert into t_message_item_reason (item_id,reason,remark,create_user_id,create_time,update_user_id,update_time) values (',temp_id,',','''',str_7,'''',',');				
				set text_sql = CONCAT(text_sql,'''','','''',',1,','''',t_date_time,'''',',1,','''',t_date_time,'''',')');	
				select text_sql;	
				
				SET @sql_text = text_sql;
				PREPARE stmt FROM @sql_text;
				EXECUTE stmt;
				DEALLOCATE PREPARE stmt;
		end while;
		-- 关闭游标
		close cur_reson; 
end

4 清除某个数据库下的全部数据，不删表结构

0000

drop PROCEDURE if EXISTS clear_all_table_data;
create PROCEDURE clear_all_table_data(in dbname varchar(50))
BEGIN
	DECLARE tp_sql varchar(500) default '';

	DECLARE cur_end int(11) default 1;
	-- 定义一个游标
	-- declare cur_reson cursor for select id,name from t_message_item;
	declare cur_reson cursor for SELECT CONCAT( 'truncate TABLE ', '`',table_schema,'`', '.', TABLE_NAME, ';' )  FROM INFORMATION_SCHEMA.TABLES 
																WHERE table_schema IN (dbname);
	DECLARE EXIT HANDLER FOR NOT FOUND SET cur_end = 0;
	-- 打开游标
	open cur_reson;

	while cur_end > 0 DO
					-- 迭代游标
				 fetch cur_reson into tp_sql;
				 select tp_sql;

				 SET @sql_text = tp_sql;
				 PREPARE stmt FROM @sql_text;
				 EXECUTE stmt;
				 DEALLOCATE PREPARE stmt;

	end while;
	-- 关闭游标
	close cur_reson;
end
;
-- set @dbname = 'spring-cloud';
-- call clear_all_table_data(@dbname);

5 修改数据名称

mysql 8.0.22 版本根据 https://www.cnblogs.com/xiongzaiqiren/p/14254666.html进行修改

drop PROCEDURE if EXISTS test_pro;
create PROCEDURE test_pro(in old_dbname varchar(50),in new_dbname varchar(50))
-- old_dbname 老数据库名称，new_dbname 新数据库名称
BEGIN
	DECLARE tp_sql varchar(500) default '';

	DECLARE temp_tname varchar(50) default '';
	DECLARE temp_tschema varchar(50) default '';

	DECLARE cur_end int(11) default 1;
	-- 定义一个游标
	-- declare cur_reson cursor for select id,name from t_message_item;
	declare cur_reson cursor for SELECT table_name,TABLE_SCHEMA FROM information_schema.TABLES WHERE TABLE_SCHEMA= old_dbname ;
	
	DECLARE EXIT HANDLER FOR NOT FOUND SET cur_end = 0;
	-- 打开游标
	open cur_reson;

	while cur_end > 0 DO
					-- 迭代游标
				 fetch cur_reson into temp_tname,temp_tschema;
				 -- select temp_tname,temp_tschema;



				set tp_sql = CONCAT('RENAME TABLE ',old_dbname,'.',temp_tname,' TO ',new_dbname,'.',temp_tname,';');				
				select tp_sql;
				SET @sql_text = tp_sql;
				PREPARE stmt FROM @sql_text;
				EXECUTE stmt;
				DEALLOCATE PREPARE stmt;

	end while;
	-- 关闭游标
	close cur_reson;
	

end
;
-- demo是将 e_archive 数据库数据迁移到 aaa数据库
-- set @old_dbname = 'e_archive';
-- set @new_dbname = 'aaa';
-- call test_pro(@old_dbname,@new_dbname);

恰子李

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
1
评论
mysql存储过程--创建表以及根据已有的表进行分表分库

创建多表的存储过程/* -----------------------t_user分表SQL--------------------------------*/drop PROCEDURE if exists import_user_data;create PROCEDURE import_user_data(IN table_num int)-- table_num 表示有多少张表...
复制链接

扫一扫

专栏目录