欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

解决MySQL插入新记录不在最后一行的问题

程序员文章站 2022-03-31 22:13:14
问题的提出出现这个问题的背景是需要设计一张规定长度的数据表,用来记录过去24小时的PM2.5监测情况,当表写到规定长度后,当需要写入新的记录时,必须删除掉最早的一条数值。但是系统运行满24小时之后,我惊奇地发现数据库里似乎不再写入数据了,但是写入端这边却提示的是写入成功,实在是有些匪夷所思。尝试向数据表中手动插入一些数据,会发现新插入记录的id和表中保留的最后一条记录的id之间丢失了很多,于是猜测是MySQL删错了。把刚刚插入的那条数据又删除掉了。其间还发现如果先删除再插入,行得通,但是新插入的记录总...

问题的提出

出现这个问题的背景是需要设计一张规定长度的数据表,用来记录过去24小时的PM2.5监测情况,当表写到规定长度后,当需要写入新的记录时,必须删除掉最早的一条数值。但是系统运行满24小时之后,我惊奇地发现数据库里似乎不再写入数据了,但是写入端这边却提示的是写入成功,实在是有些匪夷所思。

尝试向数据表中手动插入一些数据,会发现新插入记录的id和表中保留的最后一条记录的id之间丢失了很多,于是猜测是MySQL删错了。把刚刚插入的那条数据又删除掉了。

其间还发现如果先删除再插入,行得通,但是新插入的记录总是替代被删除的那条记录的位置,表中的id不是按照顺序排列的,这将不利于后端组织数据。

兜兜转转,看到CSDN上有遇到同样问题的人,说是需要在主键上建立聚集索引;查了一下,又有人说只要主键指定AUTO_INCREMENT,本身就是带聚集索引的。

但最终在此问题的解答中了解到InnoDB会建立索引,突然反应到最近几次在建表的时候都没有指定ENGINE,所以立即在建表的时候指定了ENGINE,果不其然,删除后再插入终于正常了。

建表语句

CREATE TABLE realtime_pm_site0(
id INT NOT NULL AUTO_INCREMENT,
datetime VARCHAR(20) NOT NULL,
concPM1_0_CF1 INT NOT NULL,
concPM2_5_CF1 INT NOT NULL,
concPM10_0_CF1 INT NOT NULL,
concPM1_0_ATM INT NOT NULL,
concPM2_5_ATM INT NOT NULL,
concPM10_0_ATM INT NOT NULL,
rawGt0_3um INT NOT NULL, 
rawGt0_5um INT NOT NULL, 
rawGt1_0um INT NOT NULL,
rawGt2_5um INT NOT NULL, 
rawGt5_0um INT NOT NULL, 
rawGt10_0um INT NOT NULL,
PRIMARY KEY(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

写入端的语句

if(count == TABLE_MAXLEN_REALTIME):
	sql_delete = "DELETE FROM realtime_pm_site2 ORDER BY id ASC LIMIT 1;"
	cursor.execute(sql_delete)
	db.commit()
	try:
		# 定义SQL语句
		sql = "INSERT INTO realtime_pm_site2(datetime, concPM1_0_CF1, concPM2_5_CF1, concPM10_0_CF1, concPM1_0_ATM, concPM2_5_ATM, concPM10_0_ATM, rawGt0_3um, rawGt0_5um, rawGt1_0um, rawGt2_5um, rawGt5_0um, rawGt10_0um) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s');" % (str(nowtime_str), str(concPM1_0_CF1), str(concPM2_5_CF1), str(concPM10_0_CF1), str(concPM1_0_ATM), str(concPM2_5_ATM), str(concPM10_0_ATM), str(rawGt0_3um), str(rawGt0_5um), str(rawGt1_0um), str(rawGt2_5um), str(rawGt5_0um), str(rawGt10_0um))
		# 执行SQL语句
		cursor.execute(sql)
		db.commit()
		print("插入一条新数据,时间是: %s" % nowtime_str)
	except:
		# 发生错误时回滚
		print("即时数据插入错误,回滚")
		db.rollback()
	db.close()

(经过检验,不论是上面的先删再插还是先插再删,都没有什么问题了)

本文地址:https://blog.csdn.net/qq_34087914/article/details/107666257

相关标签: database