美文网首页
mysql存储过程

mysql存储过程

作者: yichen_china | 来源:发表于2021-11-17 09:28 被阅读0次

一、执行过程
1、创建数据库表

DROP TABLE IF EXISTS `code_info`;
CREATE TABLE `code_info`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL DEFAULT 0,
  `code` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `uid`(`uid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 37093 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

2、写入存储过程


DROP PROCEDURE if exists insert_code;
 
DELIMITER ;;
CREATE PROCEDURE `insert_code`()
BEGIN
 
DECLARE i int;
SET i = 1;
 
WHILE  i <= 100000000 DO
    INSERT INTO code_info(id, uid, code) VALUES (i, floor(1+rand()*1000), floor(1+rand()*100));
    SET i = i + 1;
END WHILE;
 
END;;
DELIMITER ;

3、执行

call insert_code()

[mysql 存储过程海量数据写入和查询]

循环插入10万条数据
sql拼接 批量插入10万条数据
sql拼接 批量插入1000万条数据

BEGIN
 
DECLARE i int;
DECLARE j int;
DECLARE str VARCHAR(65535);
SET i = 0;
WHILE i < 10000 DO
        SET j = 0;
        SET i = i + 1;
        SET str = '';
        WHILE j < 1000 DO
            SET j = j + 1;
            SET str = CONCAT(str,',(',rand() * 10000,')');
        END WHILE;
        set str = SUBSTRING(str, 2);
        set @exesql = concat("insert into `all_user` (`username`) values ", str);
        prepare stmt from @exesql;
        execute stmt;
        DEALLOCATE prepare stmt;
END WHILE;
 
 
END

mysql 存储过程判断重复的不插入数据

mysql存储过程中

下面是一个较常见的场景,判断表中某列是否存在某值,如果存在执行某操作

需要注意的是不能用if exists;

exists可以在where后面或者在create object是使用,但是在if语句中不可以使用,只能用变通的方法。

delimiter $$
create procedure proc_add_book(in $bookName varchar(200),in $price float)
begin
  declare $existsFlag int default 0;
  select bookId into $existsFlag from book where bookName = $bookName limit 1;
  if bookId > 0 then
  #if not exists (select * from book where bookNumber = $bookName) then
    insert into book(bookNumber,price) values($bookName,$price);
  end if;
end$$
delimiter ;

二、优化处理
1、创建的数据库表引擎先转换成MyISAM,数据导入完后再转为InnoDB,转innodb时需要一点时间

2、更改mysql配置文件配置详情如下:

innodb_log_file_size = 1024M

日志组中的每个日志文件的大小,设置较大的值可以减少脏数据刷新到磁盘的次数

innodb_log_buffer_size = 256M  

将日志写入日志磁盘文件前的缓冲大小,默认是16M,设置较大的值可以使日志在缓冲时不需要被保存到磁盘,减少磁盘读取次数

innodb_flush_log_at_trx_commit = 0

将日志中的缓冲数据写入磁盘,默认为1,即每次提交事务都会写入磁盘,改为0后将减少写入磁盘的次数

innodb_buffer_pool_size = 4G

该值用来高速缓冲数据和索引内存缓冲大小,也是减少磁盘IO来增加速度,一般设置为物理存储的60%~70%

innodb_buffer_pool_instances = 4 

增加多个缓冲池,提高并行内存读写能力

innodb_write_io_threads = 8  &&  innodb_read_io_threads = 8

增加IO线程数,提高读写能力,根据机器的处理器来设置

innodb_io_capacity = 500

提高刷新脏页数量和合并插入数量,改善磁盘I/O处理能力,默认200

以上参数修改都是通过减少对磁盘的IO操作来实现的,包括关闭了MySQL默认的事务存储到磁盘的操作,增大缓冲区的容量,这样子容易造成MySQL服务器宕机的时候,数据大量丢失,建议不要在生产环境下修改这些配置,根据实际情况修改

3、合并多条 insert 为一条

4、其他

Myisam : 对于Myisam类型的表,可以通过以下方式快速的导入大量的数据。

ALTER TABLE tblname DISABLE KEYS; loading the data ALTER TABLE tblname ENABLE KEYS; 这两个命令用来打开或者关闭Myisam表非唯一索引的更新。在导入大量的数据到一个非空的Myisam表时,通过设置这两个命令,可以提高导入的效率。对于导入大量数据到一个空的Myisam表,默认就是先导入数据然后才创建索引的,所以不用进行设置

Innodb : 对于Innodb类型的表,有以下几种方式可以提高导入的效率:

1、因为Innodb类型的表是按照主键的顺序保存的,所以将导入的数据按照主键的顺序排列,可以有效的提高导入数据的效率。如果Innodb表没有主键,那么系统会默认创建一个内部列作为主键,所以如果可以给表创建一个主键,将可以利用这个优势提高导入数据的效率。

2、在导入数据前执行SET UNIQUE_CHECKS=0,关闭唯一性校验,在导入结束后执行SET UNIQUE_CHECKS=1,恢复唯一性校验,可以提高导入的效率。

3、如果应用使用自动提交的方式,建议在导入前执行SET AUTOCOMMIT=0,关闭自动提交,导入结束后再执行SET AUTOCOMMIT=1,打开自动提交,也可以提高导入的效率。

备注:如果需要中断执行中的存储过程
1、输入show processlist;命令查看正在执行的任务,
2、查看Info找到任务对应的Id,执行kill命令

相关文章

  • 15 MySQL 存储过程

    MySQL 存储过程 [toc] 存储过程概述 存储过程介绍 存储过程,相当于是 MySQL 语句组成的脚本 指的...

  • 存储过程

    使用存储过程 1.存储过程 ​ 需要MySQL5---->> MySQL5添加了对存储过程的支持。 ​ 迄...

  • MySQL存储过程详解 mysql 存储过程

    原文链接 MySQL存储过程详解 1.存储过程简介 我们常用的操作数据库语言SQL语句在执行的时候需要要先编译,然...

  • Mysql存储过程

    阅读目录:MySQL存储过程_创建-调用-参数 存储过程:SQL中的“脚本” 创建存储过程 调用存储过程 存储过程...

  • 面试官突然问我MySQL存储过程,我竟然连基础都不会!(详细)

    MySQL存储过程 一、存储过程 1.1 什么是存储过程 存储过程(Stored Procedure)是在大型数据...

  • MySQL存储过程创建及调用方法

    MySQL存储过程是一个sql语句,那么我们如何创建呢,MySQL存储过程创建及修改,删除操作。 1,存储过程创建...

  • 存储过程

    创建存储过程 mysql: 执行存储过程 mysql: 使用OUT参数和INOUT参数: PREPARE和EXEC...

  • SQL基本语句(以MySQL为例)-- 第五部分

    19、使用存储过程MySQL的存储过程的语句跟这本书上的差太远了吧,以下参考自:MySQL存储过程教程 20、管理...

  • MySQL存储过程和存储函数

    一、存储过程 MySQL存储过程存储过程和存储函数参考文章 SQL语句需要先编译然后执行,而存储过程(Stored...

  • MySQL存储过程

    1、关于MySQL的存储过程 存储过程是数据库存储的一个重要的功能,但是MySQL在5.0以前并不支持存储过程,这...

网友评论

      本文标题:mysql存储过程

      本文链接:https://www.haomeiwen.com/subject/sbntaltx.html