用存储过程向数据库添加大量数据【mysql】

然后是存储过程:

CREATE PROCEDURE `init_map`(in count int)
BEGIN
    declare jump int DEFAULT count;
    WHILE jump>0 DO BEGIN
        DECLARE i INT DEFAULT 1;
        DECLARE c INT DEFAULT jump;
        IF c>10000 THEN
            SET c=10000;
        END IF;
        SET @sq=concat("INSERT INTO map(gp) VALUES (st_geomfromtext(‘POINT(",(rand()*50+75)," ",(rand()*50+75),")‘))");
        WHILE i<c DO
          SET @sq=concat(@sq,",(st_geomfromtext(‘POINT(",(rand()*50+75)," ",(rand()*50+75),")‘))");
          SET i=i+1;
        END WHILE;
        SELECT @sq;
        PREPARE s FROM @sq;
        EXECUTE s;
        DEALLOCATE PREPARE s;
        SET jump=jump-c;
    END;
    END WHILE;
END

 然后是执行存储过程，10万行，观察时间，当然是很快的。

CALL init_map(100000);

用存储过程向数据库添加大量数据【mysql】

标签：innodb   比较   nod   js等   auto   观察   oca   table   用户id