使用场景:因为数据库单表增量较大,考虑将该表水平拆分,分成32张表,路由字段为用户id,按照用户id%32将数据散落到各个分表中,中间件采用sharding-sphere。表拆分完成以后,遇到尴尬的问题,生成相同结构的表,所以此处采用了存储过程辅助生成表。
根据现有表结构创建表
create table ${tableName} like ${copyTable};
存储过程:
存储过程将一段通用的操作封装在一起,这样在不同平台都可以通用了。
存储过程没有返回值,而且没有sql语句调用,只能是call 调用,而且不返回结果集,执行就执行了。
-- 创建建表存储过程
delimiter // #定义标识符为双斜杠
drop procedure if exists copyTable;
create procedure copyTable(in copyTableName varchar(250),in separatorName varchar(250),in cNums INT)
begin
declare i int;
set i = 0;
repeat
set @temp := CONCAT(copyTableName,separatorName,i); #拼接临时生成的表名
set @sqlTemp := CONCAT('create table ',@temp,' like ',copyTableName); #拼接要执行的sql语句
PREPARE stmt from @sqlTemp; #创建预sql
EXECUTE stmt; #执行预sql
DEALLOCATE PREPARE stmt; #释放执行中使用的所有数据库资源
select CONCAT(@temp,' create success !') as msg ; #打印
set i = i + 1;
until i > cNums end repeat;
end
//
一、输出参数(in,out,inout)
IN 输入参数
- 表示该参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值
- 仅需要将数据传入存储过程,并不需要返回计算后的该值。
- 只能当做传入参数
OUT 输出参数
- 该值可在存储过程内部被改变,并可返回
- 不接受外部传入的数据,仅返回计算之后的值。
- 只能当做转出参数
INOUT 输入输出参数
- 调用时指定,并且可被改变和返回
- 需要数据传入存储过程经过调用计算后,再传出返回值
- 可当做传入转出参数
二、变量声明(declare,set)
mysql存储过程中declare 和set 定义变量的区别:
1) declare :
declare 变量名 数据类型
例如:
declare i int;
修改变量值:
set 变量名 = 值;
例如:
set i = 0;
2)set:
使用set 或者select 直接赋值,变量名以@开头
例如:
set @temp=1;
可以在会话的任何地方声明,作用域是整个会话,称为会话变量,也是全局变量。
以declare关键字声明的变量,只能在存储过程中使用,称为存储过程变量。 这种变量需要设置变量类型,而且只存在begin....end 这段内
两者区别:
在调用存储过程时,declare声明的变量都会别初始化为null,而会话变量(以@开头的变量)则不会再被初始化,在一个会话内只被初始化一次,之后是对上一次会话的计算结果,相当于这个会话的全局变量。
三、打印参数(输出)
执行计划中,用select + 变量打印变量值。
四、调用存储过程
call + 存储过程
例如:
call copyTable('channel','',5);
五、查看删除存储过程
显示所有存储过程:
show procedure status;
删除指定存储过程:
drop procedure 存储过程名 ;
踩坑:mysql 存储过程中不能把变量赋值到表名处,所以,采用预sql,将要执行的sql批出来,再去执行,最后通过deallocate释放执行存储过程中使用的数据库资源。