mysql插入存储的过程_mysql 插入存储过程

、创建表：

CREATE TABLE IF NOT EXISTS `blog` (

`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,

`title` char(120) NOT NULL,

`content` text NOT NULL,

`date` datetime NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8  ;

2、创建存储过程

delimiter $$

create procedure batch_insert()

begin

DECLARE max int;

DECLARE rc int;

set max =10000000;

set rc =1;

loop: while rc

insert into blog (title,content,date) values(CONCAT('title hre',rc),concat('content here',rc),now());

set rc=rc+1;

end while loop;

end$$

delimiter ;

3、调用存储过程：

call batch_insert();

一张表数据超过千万级别，mysql查询性能严重下降！

set @@profiling=1;

select * from blog where title='title hre27' ;

show profiles;

+----------+------------+----------------------------------------------+ | Query_ID | Duration   | Query                                        | +----------+------------+----------------------------------------------+ |        1 | 5.75001775 | select * from blog where title='title hre27' | |       +----------+------------+----------------------------------------------+