Auto fix the auto increment in MySQL

Question

I have a table, and I have admin panel which can delete the records from the db, so that the ID becomes like 1, 4, 7, 9, 11, 24, etc. But am using the ID count for a chart.

The problem is that I need the table to auto fix the auto increment so that I can show the correct data in the chart! Is there any solution for this? I am a beginner so kindly consider this.

score 1 · Answer 1 · answered Aug 15 '17 at 14:49

You are not counting the records, you are using the last value as the count. What you need to do is count the records.

SELECT COUNT(*) "ID total" FROM table;

That will give you the total number of records irregardless of how many have been deleted. So if you have 50 records total, but the last auto increment is 73. You count is still 50.

score 1 · Answer 2 · answered Aug 15 '17 at 16:53

Generally, avoid using internal ID values for external reporting. It simply shouldn't matter if there are gaps in your IDs.

A better solution may be to provide a row number over the data in your result set:

SELECT t.*, 
       @rownum := @rownum + 1 AS rank
  FROM YOUR_TABLE t 
       (SELECT @rownum := 0) r
 ORDER BY your_column
;

This initializes the variable @rownum to 0 as the query starts, and adds 1 to it for each row, providing a sequential numbering of your rows.

Taken from the second answer to the question "ROW_NUMBER() in MySQL"

Auto fix the auto increment in MySQL

2 Answers2