Use of integer primary key autoincrement and rowid in SQLite

When designing a table with SQLite, I suddenly thought of a problem. In the table I designed, each table has its own integer id value as the primary key. In fact, this ID value can not be specified, SQLite adds a rowid to each table. This rowid can be used as an implicit field, but is maintained by the SQLite engine. Before 3.0, rowid is a 32-bit integer, 3.0 and later are 64-bit integers. Why not use this internal rowid as the primary key for each table's ID. Immediately look for the SQLite document to see the difference between specifying integer primary key autoincrement and rowid without specifying auto-increment fields. Related documents are listed here: Http://www.sqlite.org/autoinc.html Http://www.sqlite.org/faq.html Using an auto-increment field as the primary key has many problems, such as maintenance or primary key conflict resolution in large distributed applications. In some large distributed applications, GUID is generally used for primary keys, which can effectively avoid primary key conflicts and reduce the project for primary key maintenance. Of course, for small and medium-sized applications, the advantages of Self-increasing fields are more simple and fast. In SQLite, an auto-increment field is defined as integer primary key autoincrement. when inserting a new data, you only need to specify the value of this field as null to automatically set its value by the engine, the engine is set to the maximum rowid + 1. Of course, you can also set a non-null number to specify this value, but you must be careful not to cause a conflict. When the value of rowid is greater than the maximum value 9223372036854775807 (the maximum value of rowid in 3.0 and later versions, the new value of rowid will randomly find an unused value before the maximum number. Therefore, before rowid reaches the maximum value, the value of rowid increases monotonically. Integer primary key autoincrementAlgorithmIt is slightly different from rowid. FirstWhen the maximum value is reached, rowid will find the rowid corresponding to the Deleted field as the new value, and the auto-increment field will throw a sqlite_full error. SecondWhen the auto-increment field adds a new value, it finds a rowid that has never been used as the new value, while rowid is to find the largest existing rowid + 1. In this case, the impact on the application will be relatively large, especially some meta records that depend on the ID value. It is only applicable to auto-increment fields but not rowid. For example, we design a metadata record table: Create Table meta_struct (ID integer primary key autoincrement, name varchar, type Integer ); Then, define a level-1 table to describe the structure of other tables: Create Table meta_table (tableid integer, table_field integer) Finally, our application can generate the actually used Level 2 table based on this level 1 table. To ensure compatibility, the IDs in meta_struct must be unique. If a field is deleted, the ID value of this field cannot be reused. Otherwise, when the database is merged, first-level tables and second-level tables are messy. Therefore, the primary key in the meta_struct table can only use auto-increment fields, but not rowid. ThirdThe engine automatically generates a sqlite_sequence table using the auto-increment field to record the used maximum value of the auto-increment field of each table. You can see that, you can also use update, delete, and insert operations, but it is not recommended to use these operations, which will confuse the engine. If rowid is used, there will also be such an internal table. You can maintain the rowid value, but you cannot see it. In this case, if you directly use rowid to replace the auto-increment field, you need to pay attention to whether it conflicts with your own application based on the slight differences between the two. If there is no conflict, it will be faster to use rowid.