How to disable rowid to optimize SQLite database

How do I disable rowid tuning SQLite databases? I believe that most people have not yet learned this skill, in order to let you learn, to give you a summary of the following content, do not say much, let's move on.

1.0 introduction

By default, each row in the SQLite database has a special column called rowid that uniquely identifies a row in the database table. However, if you add the WITHOUT ROWID keyword after the CREATE TABLE statement, the rowid column is disabled. Sometimes omitting rowid can lead to space and performance improvements.

1.1 Grammar

Now the most direct way to create a WITHOUT ROWID table is to add the keyword WITHOUT ROWID. Exe after the CREATE TABLE statement. For example:

CREATE TABLE IF NOT EXISTS wordcount (

Word TEXT PRIMARY KEY

Cnt INTEGER

) WITHOUT ROWID

Like all other SQL grammars, keywords are case-insensitive. Any of the following ways are acceptable:

WITHOUT rowid, withoutrowid, WIThOut rOwId. They represent the same meaning.

Note: if the table specifies the WITHOUT ROWID attribute, the table must specify PRIMARY KEY. Otherwise, an error is returned when the table is created, which roughly means that the creation table statement of the specified WITHOUT ROWID is missing PRIMARY KEY.

In most cases, rowid and oid or _ rowid_ mean the same thing. However, only rowid can appear in the statement that creates the table.

1.2 compatibility

Only programs after SQLite version 3.8.2 can use the WITHOUT ROWID keyword. Earlier versions of SQLite opened a table with the WITHOUT ROWID attribute and returned an error of malformed database schema.

1.3 Quirks (particularity)

So far, WITHOUT ROWID can only be used by SQLite databases, and other database engines are not compatible with this keyword. In an orthodox system, even without the WITHOUT ROWID keyword, all tables should behave the same as WITHOUT ROWID. However, when SQLite was first designed, it used integer ROWID to simplify the implementation. This method has worked well for many years. However, as the demand for SQLite grows, (the need for tables in which the PRIMARY KEY really did correspondto the underlying rowkey grew more acute) becomes more urgent to specify PRIMARY KEY attributes for tables, so the contradiction with rowkey becomes more acute. In order to be compatible with programs that have been used for a long time in the past, the concept of without rowid was finally added to SQLite (The WITHOUT ROWID concept was added in order to meet that needwithout breaking backwards compatibility with the billions of SQLite databasesalready in use at the time (circa 2013).)

In order to be seamlessly compatible with thousands of programs in the past, the concept of WITHOUT ROWID was added to SQLite.

2. 0 is different from carrying a Rowid table

WITHOUT ROWID is an optimization option. She didn't provide any new features (capablilities). The same syntax is used for manipulating tables that carry ROWID tables and tables that operate without ROWID. The only advantage is that it saves disk space and is faster than a normal table.

In most cases, carrying rowid tables and not carrying rowid tables are interchangeable (interchangeable). But the WITHOUT ROWID table has some limitations that a normal rowid table does not have.

1. The table with specified WITHOUT ROWID must specify PRIMARYKEY, otherwise the table creation fails.

2 tables that specify WITHOUT ROWID cannot specify INTEGERPRIMARY KEY. In an ordinary table, "INTEGER PRIMARY KEY" means that thecolumn is an alias for the rowid. But since there is no rowid in a WITHOUTROWID table, that special meaning no longer applies. An "INTEGER PRIMARYKEY" column ina WITHOUT ROWID table works like an "INT PRIMARYKEY" column in an ordinary table: It is a PRIMARYKEY that has integeraffinity.

3 tables that specify WITHOUT ROWID cannot use the AUTOINCREMENT keyword

This is the end of the method of disabling rowid to optimize SQLite database. I hope the above content can be helpful to you and learn more knowledge. If you like this article, you might as well share it for more people to see.