Shulou(Shulou.com)06/01 Report--

Translation: https://www.sqlite.org/autoinc.html

Overview

1 self-incrementing primary key (TheAUTOINCREMENT keyword) takes up extra CPU, memory, disk space, and increases the cost of disk disk O overhead, so if not necessary, it should be disabled. It is usually not needed.

2 in SQLite, specify that the attribute of one of the columns is INTEGERPRIMARY KEY, and the effect is the same as that of ROWID (unless WITHOUT_ROWID is specified when the table is created), and the storage type is 64-bit symbolic integer.

3 during the insert operation, if the ROWID or INTEGERPRIMARY KEY column does not specify a value, SQLITE will automatically populate an unused integer, usually greater than any ROWID value that has been used. The effect is the same whether or not the AUTOINCREMENT keyword has been specified.

4 if the AUTOINCREMENT keyword follows the INTEGEPRIMARY KEY keyword, it will change the allocation algorithm of ROWID and no longer use the ROWID value that has already been used, in other words, AUTOINCREMENT will prevent the reuse of ROWID, although the ROWID has not been used with the data deletion of a row.

Background

For SQLite, each row of each table has a 64-bit symbolic integer ROWID that is uniquely represented in the table (WITHOUT_ROWID table exception specified)

You can access the ROWID value in the SQLITE table through a special column name (such as ROWID,_ROWID, or OID). Unless you define a column name with the same name as above in the table, if you do, the name will only be applied to the defined column and not to the built-in ROWID.

If there is an attribute column defined as INTEGER PRIMARY KEY in the table, it will produce the same an alias for as ROWID. I want to translate it into an alias, but perhaps no one understands it. At this point, you can access ROWID in four different ways. Please refer to the previous paragraph for the first three ways, and the last way is naturally the column that specifies the INTEGER PRIMARY KEY attribute! They're all aliases.

When a new row is to be inserted into the SQLITE table, the ROWID can be specified in the insert statement or let the database engine assign the value automatically. An example of manually specifying ROWID is as follows:

CREATE TABLE test1 (an INT, b TEXT)

INSERT INTO test1 (rowid, a, b) VALUES (123,5)

If ROWID is not specified in the insert statement, or if ROWID specifies a null value, the database engine customizes to specify an appropriate ROWID value. The generic algorithm creates a new ROWID that is larger than any previously assigned ROWID value, reflecting the growth. If the table is empty, ROWID is designated as 1.If the largest ROWID is equal to the largest possibleinteger (9223372036854775807) then the database engine starts picking positivecandidate ROWIDs at random until it finds one that is not previously used.

If an unused ROWID value is not found, the insert will fail and a SQLITE_FULL error code will be returned! If ROWID does not show the specified value in the insert statement, the automatically generated ROWID value is always greater than 0.

The normal ROWID selection algorithm produces a monotonously incremental and unique ROWID, as long as you do not use the maximum ROWID value and never delete the largest ROWID data column. If you delete some rows, or if you have ever created a maximum ROWID value, the previously deleted exercise ROWID may be reused by newly inserted rows.

Self-increasing primary key

If a property column specifies the INTEGER PRIMARY KEYAUTOINCREMENT property, the ROWID selection algorithm is slightly different. The resulting ROWID is larger than the ROWID in records that already exist in the table. If the previous table is empty, ROWID assigns a value of 1. 0. If the user specifies that the value of ROWID is the maximum, then no insert will be allowed, and at the same time, any insert will return an error code of SQLITE_FULL. Only ROWID values frompreviously transactions that were committed are considered. ROWID values thatwere rolled back are ignored and can be reused.

SQLite internally maintains a table called "sqlite_sequence", which records the largest ROWID that a table has used. Whether or not a table has property values for the specified AUTOINCREMENT column, the table is automatically created and initialized. The table can also be edited and modified through general UPDATE,INSERT and DELETE statements. However, modifying the table will affect the AUTOINCREMENT generation algorithm. When you modify it, please make sure you know what you are doing!

The implementation of the specified AUTOINCREMENT keyword is different from the default implementation. For AUTOINCREMENT, the ROWID values for each column are unused for the same database and the same table. The generated ROWID is monotonously increasing. This is a very important attribute for some applications. But if your application doesn't need these attributes, you can just use the default instead of specifying AUTOINCREMENT. After all, AUTOINCREMENT needs to do some extra work during the insertion process, which leads to a slow insertion speed.

Please note that monotonous increment does not mean that ROWID always adds 1 (increases by exactly one). Plus 1 is the most common. However, if the insertion process fails, the assigned ROWID will not be reused by the next insert data, resulting in a gap in the ROWID sequence. AUTOINCREMENT ensures that ROWID is automatically incremented, but there is no guarantee that it is incremented continuously.

Because the AUTOINCREMENT keyword changes the selection algorithm of ROWID, AUTOINCREMENT cannot be applied to tables that specify WITHOUT_ROWID or other columns specified as INTEGER PRIMARYKEY. Any attempt to apply the AUTOINCREMENT keyword will result in an error in both cases.

Note:

1) if the WITHOUT_ROWID property is specified, AUTOINCREMENT can no longer be used

2) if a column in the table already specifies the INTEGER PRIMARY KEY attribute, AUTOINCREMENT cannot modify any other column of the table.

The following is the extended reading:

Extracted from: http://www.jb51.net/article/50049.htm

For users to use a custom self-increment primary key, or the system internal maintenance of ROWID, the above has been described very clearly, and the link has also provided a way to operate ROWID.

Now focus on discussion: in large-scale distributed applications, the use of self-increasing primary keys, of course, SQLite data can only be a sparrow, for us, there is no concept of large amount of data, for the author mentioned the concept of the use of first-level tables and secondary tables, there is a far-reaching curiosity, but there is no operating environment.

Excerpt from the article: there are many problems with using self-growing fields as primary keys, such as maintenance or primary key conflict resolution in large-scale distributed applications.

In some large-scale distributed applications, the primary key is generally selected guid, which can effectively avoid primary key conflicts and reduce the project of primary key maintenance.

Of course, for small and medium-sized applications, the benefits of self-growing fields are more simple and fast.

Using the reason of self-increasing primary key, under what circumstances, it is necessary to establish a primary key, especially a distributed database!

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.