mysql 存储过程

1. 存储过程

1.1 循环

delimiter 		-- 分块
-- 创建
drop procedure if exists test;
create procedure test(in count int)
begin
	declare i int;
	set i = 1;
	while (i < count) do
		select * from job;
		set i = i + 1;
	end while;
end; 
-- 执行
set @count = 10;
call test(@count);

另一种循环

loop_label:loop
	select * from job;
    set i = i + 1;
	if i >= count THEN
		leave loop_label;
	end if;
end loop;

1.2 动态sql

例：实现自动寻找取模分表

-- 创建
drop procedure if exists test;
create procedure query(in `table_name` varchar(50), in `table_count` int, in `key_name` varchar(50), in `key` long)
begin
 declare SQL_FOR_SELECT varchar(500);
  set SQL_FOR_SELECT = CONCAT("SELECT * FROM ", @table_name, "_", @key % @table_count, " where ", @key_name, " = ",@key);
  set @sql = SQL_FOR_SELECT;
  PREPARE stmt FROM @sql; -- 预处理动态sql语句
  EXECUTE stmt; -- 执行sql语句
  deallocate prepare stmt; -- 释放prepare
end;
-- 创建存储过程完毕。调用
set @table_name = "test_info";
set @key_name = "id";
set @key = 88124751873;
set @table_count = 256;
-- 存储过程
-- @table_name 表名
-- @table_count 分表数量
-- @key_name 分片键名
-- @key 分片键值
call query(@table_name, @table_count, @key_name, @key);

-----------待更------------

函数

触发器