本文档描述了在对MySQL数据库进行INSERT操作时遇到的ID不连续问题，分析了可能的原因，包括INSERT失败后的自动增1和DELETE操作后重新插入数据。为了解决这个问题，提供了使用Navicate Premium的TRUNCATE TABLE命令来重置表ID计数的步骤，该方法成功解决了ID不从1开始的问题。
当我对mysql数据库进行insert插入操作时，发现insert自动增长的id不是从1开始或不连续
这个是我对我的mysql中的数据库zx里的configuration表进行insert字典格式的数据操作的代码：
import json,MySQLdb,pymysql,xlrd,xlwt,sys
from operator import mod
from bs4 import BeautifulSoup
from xlutils.copy import copy
from JobSpider.excel导入数据库 import temp_dict
def write_to_excel(words, filename, sheet_name='sheet1'):
'''将item存储到excel中。
:param words: 保存item的list [{},{}]格式
:return:
'''
try:
# 1、创建工作薄
work_book = xlwt.Workbook(encoding='utf-8')
# 2、创建sheet表单
sheet = work_book.add_sheet(sheet_name)
# 3、写表头
# head = ['英文','中文']
head = []
for k in words[0].keys():
head.append(k)
for i in range(len(head)):
sheet.write(0, i, head[i])
# 4、添加内容
# 行号
i = 1
for item in words:
for j in range(len(head)):
sheet.write(i, j, item[head[j]])
# 写完一行，将行号+1
i += 1
# 保存
work_book.save(filename)
print('写入excel成功！')
except Exception as e:
print('写入excel失败！', e)
def append_to_excel(words, filename):
''' 追加数据到excel
:param words: 【item】 [{},{}]格式
:param filename: 文件名
:return:
'''
global sum
try:
# 打开excel
word_book = xlrd.open_workbook(filename)
# 获取所有的sheet表单。
sheets = word_book.sheet_names()
# 获取第一个表单
work_sheet = word_book.sheet_by_name(sheets[0])
# 获取已经写入的行数
old_rows = work_sheet.nrows
# 获取表头信息
heads = work_sheet.row_values(0)
# 将xlrd对象变成xlwt
new_work_book = copy(word_book)
# 添加内容
new_sheet = new_work_book.get_sheet(0)
i = old_rows
for item in words:
for j in range(len(heads)):
new_sheet.write(i, j, item[heads[j]])
i += 1
new_work_book.save(filename)
sum += 1
print('追加成功！')
except Exception as e:
print('追加失败！', e)
def open_excel(excel_file):
'''读取excel函数
args：excel_file（excel文件，目录在py文件同目录）
returns：book
'''
try:
book = xlrd.open_workbook(excel_file) # 文件名，把文件与py文件放在同一目录下
print(sys.getsizeof(book))
return book
except:
print("打开excel文件失败！！")
if __name__ == '__main__':
dict = [{
"level": "phase",
"id": 1,
"content": {
"period": 149,
"green_st": 0,
"green_end": 67.05,
"greenrate": 0.45
}
},
{
"level": "phase",
"id": 2,
"content": {
"period": 149,
"green_st": 67.05,
"green_end": 74.5,
"greenrate": 0.05
}
},
{
"level": "phase",
"id": 3,
"content": {
"period": 149,
"green_st": 74.5,
"green_end": 141.55,
"greenrate": 0.35
}
},
{
"level": "phase",
"id": 3,
"content": {
"period": 132,
"green_st": 66.0,
"green_end": 85.8,
"greenrate": 0.32
}
},
{
"level": "phase",
"id": 4,
"content": {
"period": 132,
"green_st": 85.8,
"green_end": 132,
"greenrate": 0.35
}
},
{
"level": "phase",
"id": 1,
"content": {
"period": 100,
"green_st": 0,
"green_end": 31.0,
"greenrate": 0.31
}
},
{
"level": "phase",
"id": 2,
"content": {
"period": 100,
"green_st": 31.0,
"green_end": 50.0,
"greenrate": 0.19
}
},
{
"level": "phase",
"id": 3,
"content": {
"period": 100,
"green_st": 50.0,
"green_end": 81.0,
"greenrate": 0.22
}
},
{
"level": "phase",
"id": 4,
"content": {
"period": 100,
"green_st": 81.0,
"green_end": 100,
"greenrate": 0.19
}
},
{
"level": "phase",
"id": 1,
"content": {
"period": 103,
"green_st": 0,
"green_end": 44.29,
"greenrate": 0.43
}
},
{
"level": "phase",
"id": 2,
"content": {
"period": 103,
"green_st": 44.29,
"green_end": 51.5,
"greenrate": 0.07
}
},
{
"level": "phase",
"id": 3,
"content": {
"period": 103,
"green_st": 51.5,
"green_end": 95.78999999999999,
"greenrate": 0.21
}
},
{
"level": "phase",
"id": 4,
"content": {
"period": 103,
"green_st": 95.78999999999999,
"green_end": 103,
"greenrate": 0.07
}
},
{
"level": "phase",
"id": 1,
"content": {
"period": 128,
"green_st": 0,
"green_end": 46.08,
"greenrate": 0.36
}
},
{
"level": "phase",
"id": 2,
"content": {
"period": 128,
"green_st": 46.08,
"green_end": 64.0,
"greenrate": 0.14
}
},
{
"level": "phase",
"id": 3,
"content": {
"period": 128,
"green_st": 64.0,
"green_end": 110.08,
"greenrate": 0.45
}
},
{
"level": "phase",
"id": 4,
"content": {
"period": 128,
"green_st": 110.08,
"green_end": 128,
"greenrate": 0.14
}
},
{
"level": "phase",
"id": 1,
"content": {
"period": 132,
"green_st": 0,
"green_end": 34.32,
"greenrate": 0.26
}
},
{
"level": "phase",
"id": 2,
"content": {
"period": 132,
"green_st": 34.32,
"green_end": 66.0,
"greenrate": 0.24
}
},
{
"level": "phase",
"id": 3,
"content": {
"period": 132,
"green_st": 66.0,
"green_end": 100.32,
"greenrate": 0.39
}
},
{
"level": "phase",
"id": 4,
"content": {
"period": 132,
"green_st": 100.32,
"green_end": 132,
"greenrate": 0.24
}
},
{
"level": "phase",
"id": 1,
"content": {
"period": 134,
"green_st": 0,
"green_end": 28.14,
"greenrate": 0.21
}
},
{
"level": "phase",
"id": 2,
"content": {
"period": 134,
"green_st": 28.14,
"green_end": 67.0,
"greenrate": 0.29
}
},
{
"level": "phase",
"id": 3,
"content": {
"period": 134,
"green_st": 67.0,
"green_end": 95.14,
"greenrate": 0.4
}
},
{
"level": "phase",
"id": 4,
"content": {
"period": 134,
"green_st": 95.14,
"green_end": 134,
"greenrate": 0.29
}
},
{
"level": "phase",
"id": 1,
"content": {
"period": 120,
"green_st": 0,
"green_end": 30.0,
"greenrate": 0.25
}
},
{
"level": "phase",
"id": 2,
"content": {
"period": 120,
"green_st": 30.0,
"green_end": 60.0,
"greenrate": 0.25
}
},
{
"level": "phase",
"id": 3,
"content": {
"period": 120,
"green_st": 60.0,
"green_end": 90.0,
"greenrate": 0.39
}
},
{
"level": "phase",
"id": 4,
"content": {
"period": 120,
"green_st": 90.0,
"green_end": 120,
"greenrate": 0.25
}
},
{
"level": "phase",
"id": 1,
"content": {
"period": 102,
"green_st": 0,
"green_end": 36.72,
"greenrate": 0.36
}
},
{
"level": "phase",
"id": 2,
"content": {
"period": 102,
"green_st": 36.72,
"green_end": 51.0,
"greenrate": 0.14
}
},
{
"level": "phase",
"id": 3,
"content": {
"period": 102,
"green_st": 51.0,
"green_end": 87.72,
"greenrate": 0.29
}
},
{
"level": "phase",
"id": 4,
"content": {
"period": 102,
"green_st": 87.72,
"green_end": 102,
"greenrate": 0.14
}
},
{
"level": "phase",
"id": 1,
"content": {
"period": 122,
"green_st": 0,
"green_end": 28.06,
"greenrate": 0.23
}
},
{
"level": "phase",
"id": 2,
"content": {
"period": 122,
"green_st": 28.06,
"green_end": 61.0,
"greenrate": 0.27
}
},
{
"level": "phase",
"id": 3,
"content": {
"period": 122,
"green_st": 61.0,
"green_end": 89.06,
"greenrate": 0.16
}
},
{
"level": "phase",
"id": 4,
"content": {
"period": 122,
"green_st": 89.06,
"green_end": 122,
"greenrate": 0.27
}
},
{
"level": "phase",
"id": 1,
"content": {
"period": 99,
"green_st": 0,
"green_end": 37.62,
"greenrate": 0.38
}
},
{
"level": "phase",
"id": 2,
"content": {
"period": 99,
"green_st": 37.62,
"green_end": 49.5,
"greenrate": 0.12
}
},
{
"level": "phase",
"id": 3,
"content": {
"period": 99,
"green_st": 49.5,
"green_end": 87.12,
"greenrate": 0.37
}
},
{
"level": "phase",
"id": 4,
"content": {
"period": 99,
"green_st": 87.12,
"green_end": 99,
"greenrate": 0.12
}
},
{
"level": "phase",
"id": 1,
"content": {
"period": 127,
"green_st": 0,
"green_end": 39.37,
"greenrate": 0.31
}
},
{
"level": "phase",
"id": 2,
"content": {
"period": 127,
"green_st": 39.37,
"green_end": 63.5,
"greenrate": 0.19
}
},
{
"level": "phase",
"id": 3,
"content": {
"period": 127,
"green_st": 63.5,
"green_end": 102.87,
"greenrate": 0.34
}
},
{
"level": "phase",
"id": 4,
"content": {
"period": 127,
"green_st": 102.87,
"green_end": 127,
"greenrate": 0.19
}
},
{
"level": "phase",
"id": 2,
"content": {
"period": 137,
"green_st": 26.03,
"green_end": 68.5,
"greenrate": 0.31
}
},
{
"level": "phase",
"id": 3,
"content": {
"period": 137,
"green_st": 68.5,
"green_end": 94.53,
"greenrate": 0.2
}
}
}
]
# write_to_excel(dict,'effect.xlsx')
# 创建数据库连接
conn = MySQLdb.connect(host='localhost', user='root', passwd='123456', db='zx', port=3306, charset='utf8')
# 创建数据库游标对象cursor
cur = conn.cursor()
temp_dict = {}
n = 1
for li in dict:
temp_dict['level'] = li['level']
temp_dict['effect_id'] = li['id'] / 15 + 1
temp_dict['period'] = li['content']['period']
temp_dict['green_st'] = li['content']['green_st']
temp_dict['green_end'] = li['content']['green_end']
temp_dict['greenrate'] = li['content']['greenrate']
temp_dict['effect_optimized_id'] = mod(li['id'],15)
n += 1
# 表名需修改
table = "configuration" # 表名
keys = ','.join(temp_dict.keys()) # 列字段
values = ', '.join(['%s'] * len(temp_dict)) # 行字段
sql = 'insert into {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
# 将字段的value转化为元祖存入
cur.execute(sql, tuple(temp_dict.values()))
conn.commit()
# 关闭游标
cur.close()
# 关闭数据库连接
conn.close()出现这种现象的原因是：
原因：
1.insert 失败后，id 已自动增加，会造成 id 不连续
2.delete 后，再重新插入数据后，id 会从删除前最后的 id 开始增加，造成 id 不是从1开始增加
当mysql数据库中的表被清空后，但是insert新插入的数据的id不是从1开始, 可以试试这个方法，亲测有效！！
解决方法：
我是在Navicate Premium15中进行mysql数据库的可视化的，
（1）先打开Navicate Premium15，连接好数据库之后，点击“新建查询”，就会出现这么一个页面:
（2）在这个新建的查询页面里，输入命令：
truncate table table_name[表名];注意：这块儿要写入自己数据库中存在的表名，不然会报错。 写入之后检查一遍是否写对。
（3）点击旁边的“运行”按钮之后，再次点开我的数据库zx中的表configuration，
发现这个id不是从1开始的问题消失了，好了，问题成功解决！！
OK，问题解决。
扫一扫
3809
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
