1、創(chuàng)建測(cè)試表

CREATE TABLE `mysql_genarate` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uuid` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=5999001 DEFAULT CHARSET=utf8;

2、創(chuàng)建一個(gè)循環(huán)插入的存儲(chǔ)過程

CREATE DEFINER=`root`@`localhost` PROCEDURE `test_two1`( ) BEGIN DECLARE i INT DEFAULT 0; WHILE i < 3000 DO INSERT INTO mysql_genarate ( uuid ) VALUES( UUID( ) ); SET i = i + 1; END WHILE;END

調(diào)用測(cè)試call test_two1(), 測(cè)試10000條數(shù)據(jù)耗時(shí)幾分鐘，如果是千萬級(jí)數(shù)據(jù)，這個(gè)速度將無法忍受。

3、優(yōu)化存儲(chǔ)過程

使用批量插入的sql語句

CREATE DEFINER=`root`@`localhost` PROCEDURE `insertPro`( IN sum INT ) BEGIN DECLARE count INT DEFAULT 0; DECLARE i INT DEFAULT 0; SET @exesql = concat( 'insert into mysql_genarate(uuid) values' ); SET @exedata = ''; SET count = 0; SET i = 0; WHILE count < sum DO SET @exedata = concat( @exedata, ',(UUID())' ); SET count = count + 1; SET i = i + 1; IF i % 1000 = 0 THEN SET @exedata = SUBSTRING( @exedata, 2 ); SET @exesql = concat( 'insert into mysql_genarate(uuid) values ', @exedata ); PREPARE stmt FROM @exesql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exedata = ''; END IF; END WHILE; IF length( @exedata ) > 0 THEN SET @exedata = SUBSTRING( @exedata, 2 ); SET @exesql = concat( 'insert into mysql_genarate(uuid) values ', @exedata ); PREPARE stmt FROM @exesql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF;END

調(diào)用 call insertPro(10000) ，耗時(shí)零點(diǎn)幾秒，這個(gè)速度可以接受。

以上就是MySQL循環(huán)插入千萬級(jí)數(shù)據(jù)的詳細(xì)內(nèi)容，更多關(guān)于MySQL循環(huán)插入的資料請(qǐng)關(guān)注好吧啦網(wǎng)其它相關(guān)文章！