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

古城微笑少年丶 2022-05-25 00:45 133阅读 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
    				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);