mysql批量插入数据_MySQL中批量插入数据

例1：

方法一：SQL语句操作

delimiter $$                              //以delimiter来标记用$表示存储过程结束

create procedure pre()           //创建pre()存储方法

begin

declare i int;                          //定义i变量

set i=2;

while i<53 do

insert into lineinfo SET ID=i, lineName=concat('北京地铁',i,'号线');

set i=i+1;

end while;

end

$$

call pre();

DROP procedure pre;

select * from lineinfo            //查询结果

方法二：python语句操作

import pymysql

# 链接数据库

db = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='mydata', charset='utf8')

# 创建游标对象cursor

cursor = db.cursor()

# 查询数据库版本

cursor.execute("select version()")

data = cursor.fetchone()

print(" Database Version:%s" % data)

# 删除数据

sql = "delete from lineinfo where ID>=2"

cursor.execute(sql)

db.commit()

# 查看删除后的结果

sql = "select * from lineinfo"

cursor.execute(sql)

data = cursor.fetchone()

print("删除后lineinfo表：" + "\n", data)

# 插入数据

sql = "insert into lineinfo (ID, lineName) values (%s, %s)"

for i in range(2, 10, 1):

lineName = "test北京地铁"+str(i)+"号线"

cursor.execute(sql, (i, lineName)) # 传值

db.commit() # 提交事务

# 查看插入后的结果

sql2 = "select * from lineinfo"

cursor.execute(sql2)

data2 = cursor.fetchone()

print("插入后lineinfo表：" + "\n", data2)

# 关闭数据库连接

db.close()

例2：

delimiter $$

create procedure pre()

begin

declare i int;      //列车数

declare j int;      //车厢数

declare k int;     //数据总数

set i=1;

set j=1;

set k=1;

WHILE i<=20 DO

WHILE j<=3 DO

INSERT into deviceinfo set ID=k, trainNum=concat(i,'00'),carName=concat(trainNum,j),deviceName=concat('第',k,'个设备'),deviceIP=concat('192.168.100.',k),deviceRemarks=k;

set j=j+1;

set k=k+1;

END WHILE;

set i=i+1;

set j=1;

END WHILE;

END

$$

call pre();

DROP procedure pre;

SELECT * from deviceinfo

结果：