mysql存储过程

文章来源：https://www.cnblogs.com/geaozhang/p/6797357.html

50G：1500万条记录

# mysql使用：https://www.cnblogs.com/-wenli/p/10352746.html

# 创建数据库 myWeb
CREATE DATABASE myWeb;

# 创建数据表 person (改表中有4个字段，id,name,email,password)
CREATE TABLE person(id int(10),name char(20),email char(40),password char(20));


# 从person中删除数据
delete from person where password="KTF";

# 显示支持的字符集
show variables like '%char%';

# 将某个字段设置为gbk
alter table person modify  name char(20) character set gbk;

# 创建数据库
create table person(id int PRIMARY KEY AUTO_INCREMENT,name varchar(20) NOT NULL,email varchar(50) NOT NULL,password varchar(50) NOT NULL); # 创建自增表
# 删除数据表
drop table person

# 插入数据到person中
INSERT INTO person(name,email,password) VALUES("张三","Tian.Zhou@dbappsecurity.com.cn","Zs");
INSERT INTO person(name,email,password) VALUES("李四","ktf@dbappsecurity.com.cn","Ls");
INSERT INTO person(name,email,password) VALUES("王二","ShengKai.Chen@dbappsecurity.com.cn","We");
INSERT INTO person(name,email,password) VALUES("麻子","YongHong.Liu@dbappsecurity.com.cn","Mz");


# mysql创建存储过程

delimiter $
create procedure test_person(in num char(20))
begin 
  
  declare i int default 6;
  while i <= num do 
      INSERT INTO person VALUES(i,"康廷峰","ktf@dbappsecurity.com.cn","KTF");
      set i = i+1;
  end while;
  
end $

set @num=20;

#调用存储过程
call test_person(@num);

# 删除存储过程
drop procedure name;

注意：
mysql关键字必须用:`name` 否则mysql会将其判断其为一个函数，提示字段错误；
//如下字段
INSERT INTO alarms (action_id,rule_id,happentime,hostname,sip,sport,dip,dport,unique_id,msg_id,`match`,severity_id,tag_id,user_agent,`url`,url_hash,method,post,response_code,request_header,response_header,response_body,country,province,city) values (1,11060006, "2020-12-14 18:35:04","10.20.185.111","10.20.89.119",62394,"10.50.36.222",8002,"6906058750556110861",11060,".mdb",2,1106,"Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:83.0) Gecko/20100101 Firefox/83.0","10.20.185.111/1.mdb","e70dcc69844a7ab11a3183103739d8ca","GET","",403,"GET /1.mdb HTTP/1.1Host: 10.20.185.111User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:83.0) Gecko/20100101 Firefox/83.0Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8Accept-Language: zh-CN,zh;q=0.8,zh-TW;q=0.7,zh-HK;q=0.5,en-US;q=0.3,en;q=0.2Accept-Encoding: gzip, deflateUpgrade-Insecure-Requests: 1Cache-Control: max-age=0","HTTP/1.1 403","","LAN","","");

--------------------------
delimiter $
create procedure test_person(in num char(20))
begin 

  DECLARE rep_body text DEFAULT 'AASDFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFQWERQWEROHJKJ2435583O47582934HKSDNJF93445345abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
  declare i int default 1;
  while i <= num do 
      INSERT INTO alarms (action_id,rule_id,happentime,hostname,sip,sport,dip,dport,unique_id,msg_id,`match`,severity_id,tag_id,user_agent,`url`,url_hash,method,post,response_code,request_header,response_header,response_body,country,province,city) values (1,11060006, "2020-12-14 18:35:04","10.20.185.111","10.20.89.119",62394,"10.50.36.222",8002,"6906058750556110861",11060,".mdb",2,1106,"Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:83.0) Gecko/20100101 Firefox/83.0","10.20.185.111/1.mdb","e70dcc69844a7ab11a3183103739d8ca","GET","",403,"GET /1.mdb HTTP/1.1Host: 10.20.185.111User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:83.0) Gecko/20100101 Firefox/83.0Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8Accept-Language: zh-CN,zh;q=0.8,zh-TW;q=0.7,zh-HK;q=0.5,en-US;q=0.3,en;q=0.2Accept-Encoding: gzip, deflateUpgrade-Insecure-Requests: 1Cache-Control: max-age=0","HTTP/1.1 403",rep_body,"LAN","","");

      set i = i+1;
  end while;
  
end $

set @num=20;

#调用存储过程
call test_person(@num);

 

----------------------------------------------

来Mysql解释器一遇到;号时就结束，回车以后就执行了。但是现在并不希望Mysql这么做，因为存储过程中可能 包含很多分号的语句，所以怎么办了，
很简单Mysql给我们提供了delimiter关键字，delimiter作用就是把;分号替换成指定的符号，比如//或$$。当再出现//或$$时，Mysql解释器才会执行命令

# 创建存储过程
DELIMITER //
create procedure test_insert (in item integer)
begin
declare counter int;
set counter = item;
while counter >= 1 do
INSERT INTO alarms VALUES (NULL, 1,11010015,'2022-08-17 11:20:51','iwf-dev.viessmann.cn','10.4.1.4',10732,'10.2.0.13',80,'7132679873833779523',11010,'/api/workflow/formula/operateDefFunction?operate=save&id=&isEdit=false&languageid=7&code=/*\\x0a* TODO\\x0a* \\xe8\\xaf\\xb7\\xe5\\x9c\\xa8\\xe6\\xad\\xa4\\xe5\\xa4\\x84\\xe7\\xbc\\x96\\xe5\\x86\\x99\\xe5\\x87\\xbd\\xe6\\x95\\xb0\\xe8\\xaf\\xb4\\xe6\\x98',3,1101,'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/104.0.5112.81 Safari/537.36 Edg/104.0.1293.54','iwf-dev.viessmann.cn/api/workflow/formula/operateDefFunction?operate=save&id=&isEdit=false&languageid=7&code=%2F*%0A*%20TODO%0A*%20%E8%AF%B7%E5%9C%A8%E6%AD%A4%E5%A4%84%E7%BC%96%E5%86%99%E5%87%BD%E6%95%B0%E8%AF%B4%E6%98%8E%EF%BC%8C%E6%AD%A4%E8%AF%B4%E6%98%8E%E5%B0%86%E5%9C%A8%E5%87%BD%E6%95%B0%E5%88%97%E8%A1%A8%E4%B8%AD%E6%98%BE%E7%A4%BA%0A*%2F%0Afunction%20%E5%87%BD%E6%95%B0%E5%90%8D(%E5%87%BD%E6%95%B0%E5%8F%82%E6%95%B0...)%20%7B%0A%20%20%2F*%0A%20%20*%20TODO%0A%20%20*%20%E8%AF%B7%E5%9C%A8%E6%AD%A4%E5%A4%84%E7%BC%96%E5%86%99javascript%E4%BB%A3%E7%A0%81%0A%20%20*%2F%0A%7D&__random__=1660706655558','5ebb70c130b8c0103a9a248e857420fc','GET','',403,'GET /api/workflow/formula/operateDefFunction?operate=save&id=&isEdit=false&languageid=7&code=%2F*%0A*%20TODO%0A*%20%E8%AF%B7%E5%9C%A8%E6%AD%A4%E5%A4%84%E7%BC%96%E5%86%99%E5%87%BD%E6%95%B0%E8%AF%B4%E6%98%8E%EF%BC%8C%E6%AD%A4%E8%AF%B4%E6%98%8E%E5%B0%86%E5%9C%A8%E5%87%BD%E6%95%B0%E5%88%97%E8%A1%A8%E4%B8%AD%E6%98%BE%E7%A4%BA%0A*%2F%0Afunction%20%E5%87%BD%E6%95%B0%E5%90%8D(%E5%87%BD%E6%95%B0%E5%8F%82%E6%95%B0...)%20%7B%0A%20%20%2F*%0A%20%20*%20TODO%0A%20%20*%20%E8%AF%B7%E5%9C%A8%E6%AD%A4%E5%A4%84%E7%BC%96%E5%86%99javascript%E4%BB%A3%E7%A0%81%0A%20%20*%2F%0A%7D&__random__=1660706655558 HTTP/1.1\nHost: iwf-dev.viessmann.cn\nDNT: 1\nX-Requested-With: XMLHttpRequest\nUser-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/104.0.5112.81 Safari/537.36 Edg/104.0.1293.54\nContent-Type: application/x-www-form-urlencoded; charset=utf-8\nAccept: */*\nReferer: http://iwf-dev.viessmann.cn/wui/engine.html\nAccept-Encoding: gzip, deflate\nAccept-Language: zh-CN,zh;q=0.9,en;q=0.8,en-GB;q=0.7,en-US;q=0.6\nCookie: ecology_JSessionid=aaa43aODATSy2fXp-TLky; JSESSIONID=aaa43aODATSy2fXp-TLky; loginidweaver=1; languageidweaver=7; loginuuids=1; __randcode__=40e739ac-f9f6-4168-8344-bb7ddc3a85a8\n','HTTP/1.1 403\n','','LAN','','');
set counter = counter - 1;
end while;
commit;
end ;
//

# 调用存储过程
call test_insert();

# 查看存储过程
show procedure status；

# 查看存储过程语句
# show create procedure test_insert;

# 删除存储过程
drop procedure test_insert;