dsideal2015 2014-10-23 19:04
浏览 39
已采纳

如何清除SQL中的自动增量列并从1开始插入一组新的数字? [重复]

This question already has an answer here:

I am working on a project that scrapes contact information from multiple sources and inserts that data into an SQL table. For better context, I currently pull each source's data separately using the methods described here - How do I account for missing xPaths and keep my data uniform when scraping a website using DOMXPath query method?.

Because the number of contacts has the potential of changing, I decided to clear the table completely before adding the first source's data. Every subsequent source's data is added by using this statement before insertion - DELETE FROM people WHERE newsstation='NBC San Diego'.

To keep the auto-increment column from starting its count on the number last generated, I set the first source's data to TRUNCATE TABLE people before inserting the data. Now that I have multiple sources being added to the table, I have changed the first data source's clearing method from TRUNCATE TABLE people to DELETE FROM people WHERE newsstation='San Diego CW 6'.

The problem is, whenever I update any of these sources in the future, the auto-increment column continues counting from the last number generated rather than the MAX number found in the auto-increment column.

Is there any way to effectively TRUNCATE the auto-increment column only and insert a new set of numbers starting from 1? I was even wondering if there is a way for me to just delete the auto-increment column and add a new auto-increment column to resolve this?

I know there are a lot of similar questions on this site, but after searching at great lengths to find an answer for this problem, none of the methods I found worked for this particular situation.

</div>
  • 写回答

2条回答 默认 最新

  • dongyong2906 2014-10-23 20:15
    关注

    The answer ended up being a lot simpler than I thought it would be.

    After I add any source's data to the table, I delete the auto-increment column from the table with the following code...

    ALTER TABLE people DROP COLUMN number
    

    ...and then I add a new auto-increment column to replace it...

    ALTER TABLE people ADD number INT( 100 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 C++使用Gunplot
  • ¥15 这个电路是如何实现路灯控制器的,原理是什么,怎么求解灯亮起后熄灭的时间如图?
  • ¥15 matlab数字图像处理频率域滤波
  • ¥15 在abaqus做了二维正交切削模型,给刀具添加了超声振动条件后输出切削力为什么比普通切削增大这么多
  • ¥15 ELGamal和paillier计算效率谁快?
  • ¥15 file converter 转换格式失败 报错 Error marking filters as finished,如何解决?
  • ¥15 Arcgis相交分析无法绘制一个或多个图形
  • ¥15 关于#r语言#的问题:差异分析前数据准备,报错Error in data[, sampleName1] : subscript out of bounds请问怎么解决呀以下是全部代码:
  • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
  • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)