Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to block the self-added ID function in the database, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

In the usual project development, I believe that a large number of people are using this database to self-add ID, using the database to self-increase ID has both advantages and disadvantages. Usually in the development of projects, generally do not use the database of self-increasing ID. The following editor to explain how to block the self-adding ID function in the database?

How to shield the self-adding ID function in the database

Solution idea

1: define a table dedicated to storing all the table names that require a unique ID and the ID values currently used by the table.

2: write a stored procedure that is specifically used to get the ID value from the table in the previous step.

This idea is very simple. I'm not going to explain it. Let's just take a look at my implementation:

Step 1: create a table

Createtabletable_key (table_namevarchar (50) notnullprimarykey,key_valueintnotnull)

Step 2: create a stored procedure to fetch the self-incremented ID

Createprocedureup_get_table_key (@ table_namevarchar (50), @ key_valueintoutput) asbeginbegintrandeclare@keyint--initializethekeywith2set@key=1--whetherthespecifiedtableisexistifnotexists (selecttable_namefromtable_keywheretable_name=@table_name) begininsertintotable_keyvalues (@ table_name,@key)-- defaultkeyvlaue:1end--stepincreaseelsebeginselect@key=key_valuefromtable_keywith (nolock) wheretable_name=@table_nameset@key=@key+1--updatethekeyvaluebytablenameupdatetable_keysetkey_value=@keywheretable_name=@table_nameend--setouputvalueset@key_value=@key--committrancommittranif@@error > 0rollbacktranend

For a record that does not exist in the table, directly return a key value with a default value of 1, and insert the record into the table_ key table. For existing records, the key value is directly added to the original key by 1. 5.

How to shield the self-adding ID function in the database

To sum up, this method is very simple, and I'll talk about its advantages and disadvantages.

Advantages:

The 1:ID value is controllable. Users can assign ID values from specified segments, which is very convenient when distributed data is required to be synchronized with data, and solves the problem of ID duplication.

2: in the writing program, the ID value is visible, for example, when re-inserting the associated record, compared with the use of database self-incrementing ID, this method does not need to insert a database record, and then get the self-increment ID value, and then use the ID value to insert the associated record. We can use transactions to insert associated records at one time.

3: for the need to insert data in bulk, we can rewrite the above stored procedure, return the ID of the beginning of a segment, and then update the table. It is not the original simple increment of 1, but the total number of records you want to insert.

Disadvantages:

1: efficiency problem. Every time you take the ID value, you need to call the stored procedure to retrieve it from the database. In this case, I don't think efficiency is a big problem, because SQLserver will cache the stored procedures that we often call, and the data in this table should not be very large, up to thousands of tables in a project. So retrieval is not a problem, let alone based on the table name (the table column is already the primary key).

After reading the above, do you have any further understanding of how to block the self-added ID function in the database? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.