利用SQL函数批量插入测试数据

SQL Comments

学习过程中需要创建一张含有批量数据的测试表，可以使用高级语言插入，但感觉比较麻烦，搜了搜可以采用SQL函数，直接在命令行页面批量插入。

  1. 数据库名为studytest，表名为student：
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    CREATE DATABASE studytest;
    use studytest;
    CREATE TABLE `student` (
      `s_id` int(11) NOT NULL AUTO_INCREMENT,
      `s_name` varchar(100) DEFAULT NULL,
      `s_age` int(11) DEFAULT NULL,
      `s_phone` varchar(30) DEFAULT NULL,
      PRIMARY KEY (`s_id`),
      KEY `s_name` (`s_name`)
    ) ENGINE=InnoDB, CHARSET=utf8;
  2. SQL函数：
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    -- 定义分界符
    DELIMITER $$
    CREATE PROCEDURE insert_student(IN START INT(10),IN max_num INT(10))
    BEGIN
      DECLARE i INT DEFAULT START;
      -- 关闭自动提交
      SET autocommit=0;
      REPEAT
      SET i=i+1;
      INSERT INTO student (s_id,s_name,s_age,s_phone) VALUES (i,CONCAT('name_',i),MOD(i,70),CONCAT('phone_',i));
      UNTIL i=max_num
      END REPEAT;
      COMMIT;
    END $$
  3. 调用该函数：
    1
    2
    3
    -- 先把分隔符换回来，方便操作。不换回来也行，就是以后的；都要写成 $$
    DELIMITER ; $$
    CALL insert_student(0,100000);

  4. 恢复自动提交：
    MySQL默认开启自动提交。除非显式地开始一个事务，否则每个查询都被当做一个单独的事务自动执行。

    1
    SET autocommit=1;

    查看autocommit状态：
    命令：show variables like ‘autocommit’;
    结果：autocommit.png

  5. PS：也可以直接利用SQL工具，如Navicat创建函数，完成批量插入。详见：mysql使用函数批量插入数据

  6. 完成后的student表：
    result.png

参考博客：
mysql使用存储过程&函数实现批量插入
MySQL用存储过程与函数批量插入数据
MySQL事务autocommit自动提交

KarakaruaDeveloper

学无止境