1

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.

Community
  • 1
  • 1
point71echo
  • 330
  • 1
  • 4
  • 12
  • see this questions: [Reset AutoIncrement][1] [1]: http://stackoverflow.com/questions/510121/reset-autoincrement-in-sqlserver-after-delete – Abdullah Dawoud Oct 23 '14 at 19:10

2 Answers2

0

The current "next" auto-increment for an InnoDB table is reset the current MAX value in the auto-increment column when you restart mysqld. So the simplest solution is /etc/init.d/mysql* restart. Though this may interrupt service, so let's consider other options.

You can TRUNCATE TABLE, which will reset the auto-increment value back to 1. But if you want to delete some of the rows but not all, this isn't the right solution.

You can change the auto-increment value with ALTER TABLE:

ALTER TABLE mytable AUTO_INCREMENT=1;

You can safely set it to the value 1 even if there are other rows in the table. It will automatically self-adjust up to the largest value currently in the table.

Example:

mysql> insert into mytable values (42);

mysql> insert into mytable values (142);

mysql> delete from mytable where id > 100;

mysql> show create table mytable\G

CREATE TABLE `mytable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=143 DEFAULT CHARSET=latin1

That's as expected, the auto_increment is still 143 even though we deleted the last row. Now let's adjust the auto_increment:

mysql> alter table mytable auto_increment=1;

mysql> show create table mytable\G

CREATE TABLE `mytable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=latin1

See? It self-adjusted to be greater than the row with id=42.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
0

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
point71echo
  • 330
  • 1
  • 4
  • 12