mysql存储过程

一、执行过程
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命令