how generate serial number in mysql database?

Question

I have table like this:

table1:

sr no. | id | name

id is the primary key here. I want sr no. to be cyclic.
What attributes i should give to sr no. field?

For example I have 5 records with sr no 1,2,3,4,5 and id 1,2,3,4,5 and i delete 3rd record then id will be 1,2,4,5 and sr no. will be 1,2,3,4 sr no should not lost its contentiousness.

it should be like: 1,2,3,4,5,6..... if any record gets deleted then it should update again like 1,2,3,4,5... it should be not like 1,2,3,5,6.... if 4 no. record is deleted — Harjeet Jadeja, Feb 27 '13 at 11:25
I don't understand for what is serial number. However, take a look on http://stackoverflow.com/questions/715251/out-of-curiosity-how-are-serial-numbers-generated-hints-algorithms — Mihai8, Feb 27 '13 at 11:26
can you let us know what purpose sr no is it going to be used ? you can think of creating trigger on delete action and UPDATE the records which having id greater than one which is deleted. — Minesh, Feb 27 '13 at 11:33

score 4 · Accepted Answer · answered Feb 27 '13 at 12:05

4

SELECT  @a:=@a+1 serial_number, 
        name  FROM    table1,
        (SELECT @a:= 0) AS a;

answered Feb 27 '13 at 12:05

Harjeet Jadeja

1,594
4
19
39

You just missed the `order by id` – Clodoaldo Neto Feb 27 '13 at 12:07

score 1 · Answer 2 · edited May 23 '17 at 10:25

id is already your primary key, so what do you need sr no. for? Would suggest to forget about it.

If you really need it, see my answer here for the several reasons why this is a really bad idea. The most important reason:

There are reasons why MySQL doesn't automatically decrease the autoincrement value when you delete a row. Those reasons are

danger of broken data integrity (imagine multiple users perform deletes or inserts...doubled entries may occur or worse)

errors may occur when you use master slave replication or transactions

and so on ...

No need to worry about gaps.

To answer your question how to do it, just add auto_increment in the column definition and include it in the primary key.

score 1 · Answer 3 · answered Feb 27 '13 at 11:35

I do not know about the real purpose of sr no.

But you can create Trigger on DELETE operation for this table, now you can update the records which are greater than current ID so that sr no is decremented by 1.

e.g. you can write below SQL in Trigger,

UPDATE table1 SET sr_no = sr_no -1 WHERE id > XYZ;

how generate serial number in mysql database?

3 Answers3