Mysql02---插表的各种操作

Intro

  批量写入mysql的两种情形：

• 存在则删除，再全量写入
• 存在则忽略，不存在则写入
• 存在则更新，不存在则写入

分别看下

数据准备

用python操作下，方便看操作之后结果。

建表：

CREATE TABLE `student` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`stu_id` bigint(20)  NOT NULL  COMMENT '学号',
`name` varchar(32) NOT NULL DEFAULT '' COMMENT '姓名',
`sex` varchar(32) NOT NULL DEFAULT '' COMMENT '性别',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `uk_stu_id_` (`stu_id`,`name`) USING BTREE
) 

• 自增主键id
• 唯一约束，表中不会出现重复的stu_id和name

import pymysql
import pandas as pd

# 打开数据库连接
db_conn = pymysql.connect(host='localhost',
                     user='root',
                     password='12345',
                     database='test')

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db_conn.cursor()

create_sql = """
CREATE TABLE `student` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`stu_id` bigint(20)  NOT NULL  COMMENT '学号',
`name` varchar(32) NOT NULL DEFAULT '' COMMENT '姓名',
`sex` varchar(32) NOT NULL DEFAULT '' COMMENT '性别',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `uk_stu_id_` (`stu_id`,`name`) USING BTREE
) 
"""
cursor.execute(create_sql)
db.commit()
pd.read_sql("desc student",db_conn)

	Field	Type	Null	Key	Default	Extra
0	id	bigint unsigned	NO	PRI	None	auto_increment
1	stu_id	bigint	NO	MUL	None
2	name	varchar(32)	NO
3	sex	varchar(32)	NO
4	create_time	timestamp	NO		CURRENT_TIMESTAMP	DEFAULT_GENERATED
5	update_time	timestamp	NO		CURRENT_TIMESTAMP	DEFAULT_GENERATED

写入测试数据

query_sql = "SELECT * FROM student"
insert_sql = """
INSERT INTO student ( stu_id, name,sex )
VALUES ( 101, '小明','男')
       ,( 102, '小红','女');
"""
cursor.execute(insert_sql)
db.commit()
pd.read_sql(query_sql,db_conn)

	id	stu_id	name	sex	create_time	update_time
0	1	101	小明	男	2021-11-16 11:19:48	2021-11-16 11:19:48
1	2	102	小红	女	2021-11-16 11:19:48	2021-11-16 11:19:48

存在则删除，再重新写入

表中已有的数据，先删除再重新写入。判断数据重复的逻辑，根据PRIMARY KEY和UNIQUE KEY

insert_sql = """
REPLACE INTO student (id, stu_id, name,sex )
VALUES (1, 103, '小明','男')
"""
cursor.execute(insert_sql)
db.commit()
pd.read_sql(query_sql,db_conn)

	id	stu_id	name	sex	create_time	update_time
0	1	103	小明	男	2021-11-16 11:24:41	2021-11-16 11:24:41
1	2	102	小红	女	2021-11-16 11:19:48	2021-11-16 11:19:48

如上所示，id重复的1，原始数据被删除，后写入新数据
再看，UNIQUE KEY重复的情况

insert_sql = """
REPLACE INTO student ( stu_id, name,sex )
VALUES (103, '小明','南上加南')
"""
cursor.execute(insert_sql)
db.commit()
pd.read_sql(query_sql,db_conn)

	id	stu_id	name	sex	create_time	update_time
0	2	102	小红	女	2021-11-16 11:19:48	2021-11-16 11:19:48
1	3	103	小明	南上加南	2021-11-16 11:25:55	2021-11-16 11:25:55

UNIQUE KEY(stu_id+name)有重复，把id=1数据删除，重新写入一条数据，且id自增为3

存在则忽略，反之写入

insert_sql = """
INSERT IGNORE INTO student ( stu_id, name,sex )
VALUES (103, '小明','南上加南')
       ,(104, '小静','女');
"""
cursor.execute(insert_sql)
db.commit()
pd.read_sql(query_sql, db_conn)

	id	stu_id	name	sex	create_time	update_time
0	2	102	小红	女	2021-11-16 11:19:48	2021-11-16 11:19:48
1	3	103	小明	南上加南	2021-11-16 11:25:55	2021-11-16 11:25:55
2	4	104	小静	女	2021-11-16 11:30:21	2021-11-16 11:30:21

103已经存在，直接忽略，写入104这条数据

存在则更新，反之则写入

insert_sql = """
INSERT  INTO student ( stu_id, name,sex )
VALUES ( 103, '小明','男')
       ,( 105, '小花','猫')
ON DUPLICATE KEY 
UPDATE name=VALUES(name)
       ,sex=VALUES(sex)
       ,update_time=VALUES(update_time)
"""
cursor.execute(insert_sql)
db.commit()
pd.read_sql(query_sql, db_conn)

	id	stu_id	name	sex	create_time	update_time
0	2	102	小红	女	2021-11-16 11:19:48	2021-11-16 11:19:48
1	3	103	小明	男	2021-11-16 11:25:55	2021-11-16 11:36:03
2	4	104	小静	女	2021-11-16 11:30:21	2021-11-16 11:30:21
3	6	105	小花	猫	2021-11-16 11:36:03	2021-11-16 11:36:03

• stu_id=103,sex字段被更新,update_time字段被更新
• stu_id=105,非重复数据，直接insert
• id=5这个索引被用掉了，虽然没有生成数据，原理不太了解，记录下

db.close()

Ref

[1] https://www.cnblogs.com/-wenli/p/13328559.html
[2]https://blog.csdn.net/t894690230/article/details/77996355

                                2021-11-16 于南京市江宁区九龙湖