赞
踩
/* -----------------------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--------------------------------*/
-- 说明:此存储过程唯一定死的就是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);
我的理解,游标就相当于一个集合,再从游标中迭代数据
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
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);
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);
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。