mysql存储过程

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

50G:1500万条记录# mysql使用:https://www.cnblogs.com/-wenli/p/10352746.html# 创建数据库 myWebCREATE 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%';# 将某个字段设置为gbkalter 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);

来源:https://www.icode9.com/content-2-786651.html

(0)

相关推荐

  • 浅析mysql存储过程

    去年的强网杯,出了一道mysql堆叠注入叫随便注,这道题被好多比赛玩了一整年,直到现在还是有各种新姿势,但是今天我忽然想到似乎没有对这个题目有一个很认真的分析,因此这里总结一下这个题目的出题用意和原本 ...

  • mysql动态列存储过程

    CREATE PROCEDURE `GetResultShow`( evaluateId varchar(100) ) begin SET SESSION group_concat_max_len = ...

  • MySQL基础-存储过程

    存储过程 定义:将一批为了完成特定功能的SQL语句集,根据传入的参数(也可没有),调用,完成单个sql语句更复杂的功能 存储过程思想很简单,就是SQL语句层面上的代码封装和重用 优点:1) 可封装,并 ...

  • Python+MySQL随机试卷及答案生成程序

    一.背景 本文章主要是分享如何使用Python从MySQL数据库中面抽取试题,生成的试卷每一份都不一样. 二.准备工作 1.安装Python3 下载地址:https://www.python.org/ ...

  • UC头条:MySQL数据库定时备份到其他电脑

    最近我的一个学弟问了我一个问题,说他要实现一个功能,他老师给他提出来一个问题,让他的项目实现定时备份数据库到其他电脑,确保安全.他就来问我,还好我之前弄过,虽然我是干前端的,哈哈哈,之前学的比较杂. ...

  • Docker定时备份MySQL数据到七牛云

    前言:我Linux服务器安装了docker,docker容器跑了springboot项目,用到了mysql数据库.所以必须准备程序,数据备份功能,万一哪天系统挂了,数据丢了,我可以随时恢复.因为没钱开 ...

  • 面试被问MySQL 主从复制,怎么破?

    一.前言 随着应用业务数据不断的增大,应用的响应速度不断下降,在检测过程中我们不难发现大多数的请求都是查询操作. 此时,我们可以将数据库扩展成主从复制模式,将读操作和写操作分离开来,多台数据库分摊请求 ...

  • mysql进阶学习二之搭建主从

    前面说了主从复制的原理,现在我们搭建主从结构 1. 提前准备 我准备了两台主机,主节点是远程的centos7,从节点是本机windows 主从复制的原理在上一篇已经说了:主节点中mysql创建一个用户 ...

  • mysql进阶学习三之mycat读写分离和分库分表

    前面已经配置了mysql的主从复制,其实很容易,主节点写入了数据,从节点进行同步,所以写操作使用主节点,读操作使用从节点,这样就有效降低了数据库的压力 但是我们用java程序不可能去连接多个数据源,执 ...