批处理命令操作mysql数据库 mysql批量处理

转载

墨色天香 2023-08-15 19:45:36

文章标签 批处理命令操作mysql数据库 python mysql 数据库数据 文章分类 MySQL 数据库

1、数据的批量处理——executemany

2、数据不存在则添加，存在则更新或者跳过

（1）存在则跳过，不存在则新增

（2）存在则更新，不存在则新增

近一直在做的就是各种读写数据的问题。。。。虽然我也不知道为什么。

1、数据的批量处理——executemany

个人觉得这个方法还是蛮好用的，主要的做法就是，把需要insert或者update的数据按相应的格式生成一个list，list的元素对应要写入的数据，用法如下。可以看到，它其实就是之前的单条执行SQL语句的批量执行版，所以只要把对应的数据构造好就可以了，也就是说，任何单条可执行的语句，只要数据打包好，都可以用这个来执行。

最神奇的是，这个可批量写的数据还蛮大的，今天用这个一次性批量写了80万条记录，速度也还行吧，几分钟，主要是没有跟单条的对比过时间花费。

data = []
for i in range(1000):
    data.append((1+i,2+i,3+i))    # 每条记录对应的是一个tuple，其实我试过list也是可以的
insert_cmd = "insert into table (column1, column2, column3) values(%s,%s,%s)"
update_cmd = "update table set column1=%s, column2=%s where column3=%s"

con = pymysql.connect(host=host,port=port,user=user,password=password,db=database,charset=charset)
cur = con.cursor()

try:
    cur.executemany(insert_cmd, data)     # 批量插入的用例
    cur.executemany(update_cmd, data)     # 批量更新的用例   
except Exception as e:
    logging.info(e)

2、数据不存在则添加，存在则更新或者跳过

（1）存在则跳过，不存在则新增

insert ignore into这个方法应用的前提是你有一个唯一的主键或者表索引，为的就是在写入数据时，根据这个主键或者索引判断是插入数据，还是忽略即跳过这条。

感觉在有唯一键或索引的时候，这个方法非常好用。以下面的句子为例，假设我已经将a列设为唯一索引了，如果现在table里面已经存在一条数据为(1,5,8)，那么下面这条语句不会插入任何数据，因为新插入的记录a=1已经在table中存在了；如果table中的数据为(2,2,3)，那么下面这条语句就会insert (1,2,3)这条记录。

insert ignore into table (a, b, c) values(1,2,3)

（2）存在则更新，不存在则新增

方法1、

INSERT ... ON DUPLICATE KEY UPDATE，这个用法也是基于主键或者唯一索引的，个人感觉跟上面的理解异曲同工。都是基于索引或者键是否存在，存在的话执行某种操作，不存在就执行另一种操作。看了网上各种一模一样的例子，实在是讲得不清不楚，今天自己试着跑了一下，终于理解了怎么用了。

以下面句子为例，假设表在a上建立了唯一索引，现在执行下面这条语句，做的事情就是，如果table中不存在a=1的记录，就实现on前面的句子，也就是直接插入(1,2,3)这条记录；如果table中存在a=1这样的记录，那么就执行update后面的操作，即更新a=1这条记录，将这条记录的b更新为2，c更新为3。emmmm，其实就很好理解，总是判断哪个唯一的键或者索引存不存在，再做相应的操作。

insert into table (a,b,c) values (1,2,3) on duplicate key update b=2, c=3

方法2、

replace into的方式，这个也是根据主键或者唯一索引来的。但是这个方法比较坑的地方是，如果已经存在相应记录，做的事情是先删除这条记录，然后再新增一条记录，还是以例子来说。

假设a建立了唯一索引，现在运行下面的SQL语句。如果table中有a=1的记录存在，那么系统做的就是删除原来那条a=1的记录，再插入底下这条(1,2,3)的记录。如果不存在就直接新增一条。这个有个问题就是，如果你现在给表一个自增的id，通过这种方式更新数据的话，自增id会不间断的持续增大。个人不是很建议使用这种方式更新数据，之前也有看有的博主说到这个方法会产生碎片化的问题。