解决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