The data for a field in a data table has a problem with the continuous generation of faults now?

There is a data table mem has field ID SN (member number) The original member number is manually filled out such as data 03117777777, 03100005555,03100005556 SN is 11 bits but the data SN has a continuous discontinuity, it is possible to fill in the largest number, After filling in the smallest number now change the demand can not move the original data want to continuous generation can not be repeated, each registered a member to generate a membership number to the member, how to ensure that the membership number is not repeated and can reduce the contrast query, there are multiple member registration of the concurrency problems to ask you have a good solution

Reply to discussion (solution)

Find it first? In all??? All the Big???。
For example, in the largest??? It's 03100005556, so next???? 03100010000, the difference between the parts of the place?
Then use 03100010000+id as a????。 for each new use?

Like new?? With the id=5678, that???? Is 03100015678

New??? First insert the???? ID and then Update sn,?? You can avoid??? A sudden. Use the Lord to do the only thing.

Find it first? In all??? All the Big???。
For example, in the largest??? It's 03100005556, so next???? 03100010000, the difference between the parts of the place?
Then use 03100010000+id as a????。 for each new use?

Like new?? With the id=5678, that???? Is 03100015678

New??? First insert the???? ID and then Update sn,?? You can avoid??? A sudden. Use the Lord to do the only thing.

Thank you for your enthusiastic answer, I am very inspired, but now the data table is a bit confusing, according to the ID number has not been done, because the data is manually filled out, all kinds of data can exist, the largest membership number is 03109999999

1. Take all your IDs out, get the maximum ID, the later new data, according to the maximum value added.
2. The user registers concurrently, can through the memcache or the Redis, the data writes the cache.

You don't fully understand Fdipzone's algorithm.
What he meant was:
There is an ID and SN two column with an ID of self-increment.
If Max (sn) = 03109999999
Then there is a constant n = max (sn)-MAX (ID)
Make N + (max (id) +1) = max (sn) = 03110000000
This is going to come out of the chaos, and of course it's missing a significant amount of SN resources

So you can also use the following algorithm to complete the trap operation
Select A.sn, b.sn as BSN from table A LEFT JOIN table B on a.sn+1=b.sn have BSN is null limit 1
So we find a discontinuous SN.
SN + 1 is the SN that is inserted
Solve concurrency problems by simply building a unique index on the SN and trying again when the insert fails
After the interval of SN in the table is full, then use the Dipzone algorithm (after all, the database is much less computationally)

Thank you for your warm-hearted answer a little bit understand the foundation is not good enough to slowly ponder

