How to shield the auto-increment ID function of databases

 

During Normal project development, I believe that a large batch of people are using this database's auto-incremental ID, which has both advantages and disadvantages.

Advantage: it saves time, and you don't have to worry about how to identify unique records. Writing a program is also simple. The database helps us maintain this batch of ID numbers.

Disadvantage: for example: When data synchronization is required for distributed databases, this auto-increment ID will cause serious problems because you cannot use this ID to uniquely identify records. At the same time, there may also be various problems during database migration. In short, problems may occur when auto-increment IDs are dependent. I absolutely believe that many people in the garden have been hurt by this "useful thing!

I usually do not use the auto-increment ID of the database when developing projects, so I would like to share my solutions.

Solution

1: Define a table to store the names of all tables with unique IDs and the IDS currently used by the table.

2: Write a stored procedure to obtain the ID value from the table in the previous step.

This idea is very simple. I will not explain it. Let's look at my implementation method directly:

Step 1: Create a table

 

Step 2: Create a stored procedure to retrieve the incremental ID

 

If no record exists in the table, a key value with the default value of 1 is directly returned, and the record is inserted into the table_key table. For existing records, the key value is directly added to the original key.

To sum up, this method is very simple. Let me talk about its advantages and disadvantages.

Advantages:

1: The ID value is controllable. You can assign ID values from a specified segment, which is very convenient for Distributed Data Synchronization and solves the problem of duplicate IDs.

2: in programming, the ID value is visible. For example, when you insert an associated record, this method does not need to get the auto-increment ID value after inserting a database record, and then use this ID value to insert the associated record. We can use transactions to insert associated records at one time.

3: When data needs to be inserted in batches, we can rewrite the above stored procedure, return the start ID of a segment, and then note when updating the table, instead of simply increasing 1, instead, increase the total number of records you want to insert.

Disadvantages:

1: efficiency problem. Every time you get the ID value, you need to call the stored procedure to retrieve it from the database. In this case, I think the efficiency is not a big problem, because SQL server will cache the stored procedures that we often call. Another point, the data in this table should not be very large, there can be a maximum of thousands of tables in a project ). Therefore, retrieval is not a problem, not to mention Retrieval Based on the table name (the table name is already the primary key ).