SQLite | 第三部分

SQLite –主键 (SQLite – PRIMARY KEY)

The primary key constraint will create a UNIQUE constraint on the column or columns in question, however, and contrary to the standard, the PRIMARY KEY SQLite allows entries with NULL. It is a breach of the standard, and this gap could be addressed in future versions. It is therefore advisable to add the NOT NULL constraint to the declaration of a primary key.

主键约束将在所讨论的一个或多个列上创建UNIQUE约束，但是，与标准相反，PRIMARY KEY SQLite允许条目为NULL。 这违反了标准，将来的版本中可能会解决此差距。 因此建议将NOT NULL约束添加到主键的声明中。

ROWID和AUTOINCREMENT (ROWID and AUTOINCREMENT)

Each row of a table is identified by a 64-bit signed integer called ROWID. When a table is declared with a single column and a INTEGER PRIMARY KEY, this column is an alias for the ROWID. The use of an alias identifier ROWID can increase the speed of research, they can be up to two times faster than a normal primary key associated with its index of uniqueness.

表的每一行都由称为ROWID的64位带符号整数标识。 当用单列和INTEGER PRIMARY KEY声明表时，此列是ROWID的别名。 别名标识符ROWID的使用可以提高研究速度，它们的速度可以快于与其唯一性索引相关联的普通主键的两倍。

When the table is empty, the algorithm assigns a value to the identifier that increments for each new record, reaching the limit of a 64-bit signed integer (263-1 = 9223372036854775807). Once this limit is reached, it will reuse the space freed by deleted records. The assignment of identifiers is no longer incremental but uncertain.

当该表为空时，该算法为标识符分配一个值，该标识符针对每个新记录递增，达到64位有符号整数的限制(263-1 = 9223372036854775807)。 一旦达到此限制，它将重用已删除记录释放的空间。 标识符的分配不再是增量的，而是不确定的。

It is possible to use the keyword AUTOINCREMENT. This slightly changes the algorithm: once the integer limit is reached, it will not be possible to insert a new record. This ensures that the same identifier will never be carried by two separate records, even if they do not coexist at the same time.

可以使用关键字AUTOINCREMENT。 这会稍微改变算法：一旦达到整数限制，将无法插入新记录。 这样可以确保即使两个标识符不在同一时间共存，同一标识符也永远不会被两个单独的记录携带。

外键 (FOREIGN KEY)

Since version 3.6.19, SQLite is able to manage the foreign key constraints.

从3.6.19版本开始，SQLite能够管理外键约束。

For backward compatibility, support for foreign keys is not enabled by default. The activation is done by the pragma foreign_keys.

为了向后兼容，默认情况下不启用对外键的支持。 激活由编译指示Foreign_keys完成。

Any column referenced by a foreign key must be declared as UNIQUE (PRIMARY KEY creates a unique key). SQLite does not yet take into account the MATCH clause in the definition of foreign keys.

外键引用的任何列都必须声明为UNIQUE(PRIMARY KEY创建唯一键)。 SQLite尚未在外键定义中考虑MATCH子句。

扳机 (Triggers)

SQLite manages a fairly complete triggers (triggers in English). Triggers BEFORE, AFTER or INSTEAD OF can be declared. SQLite supports the optional FOR EACH ROW (default setting) but not FOR EACH STATEMENT.

SQLite管理着相当完整的触发器(英语触发)。 可以声明BEFORE，AFTER或INSTEAD OF触发器。 SQLite支持可选的FOR EACH ROW(默认设置)，但不支持FOR EACH STATEMENT。

观看次数 (Views)

SQLite allows the creation of views to reduce the length of queries.

SQLite允许创建视图以减少查询的时间。

The views are read-only, but it is possible to use Triggers with INSTEAD OF property to simulate the possibility of modifying them.

视图是只读的，但是可以使用具有INSTEAD OF属性的触发器来模拟修改它们的可能性。

交易次数 (Transactions)

All SQL commands to change the state of the database (almost any other order as SELECT) involving the creation of a transaction that is dedicated to them, provided that a transaction that includes the command is already created. This means that all commands are atomic. If running the command does not cause an error, the change is automatically committed (autocommit), but if this is not the case then the entirety of changes made by the command is canceled.

所有用于更改数据库状态SQL命令(几乎所有其他命令都是SELECT)，涉及创建专用于它们的事务，前提是已经创建了包含该命令的事务。 这意味着所有命令都是原子的。 如果运行该命令不会引起错误，则更改将自动提交(自动提交)，但是如果不是这种情况，则该命令所做的全部更改将被取消。

All changes to the database are serialized: a single change is made to both the base and is locked in reading when a change.

对数据库的所有更改都是序列化的：对基础都进行了一次更改，并且在更改时被锁定在读取中。

SQLite allows the creation of transactions as well as the creation of turning points (SAVEPOINT) but does not handle different levels of insulation. In a transaction, the first call to a read command, a shared lock is activated which allows read access but prohibits any modification of data by another transaction during the first appeal in writing, all the base is locked in reading and writing for other transactions.

SQLite允许创建事务以及创建转折点(SAVEPOINT)，但不能处理不同级别的隔离。 在事务中，首次调用读取命令时，激活了共享锁，该共享锁允许进行读取访问，但在首次提出书面上诉期间禁止另一事务对数据进行任何修改，所有基础均锁定在读写其他事务中。

酸 (ACID)

Although at first sight SQLite meets all the ACID properties, which determine the reliability of a transactional system, it is possible to place the database in an inconsistent state because the types are not forced: it is possible For example, to insert a string in a column whose type affinity is defined as an integer. In their strict interpretations, SQLite does not meet all the ACID properties.

尽管乍一看SQLite符合所有ACID属性，这些属性决定了事务系统的可靠性，但是由于不强制使用类型，因此可以将数据库置于不一致的状态：例如，可以在类型相似性定义为整数的列。 在严格的解释中，SQLite并不满足所有的ACID属性。

临时表 (Temporary Tables)

SQLite allows the creation of temporary tables which are defined. Data and indexes are not saved in the database and are therefore lost during the closure of it.

SQLite允许创建已定义的临时表。 数据和索引没有保存在数据库中，因此在关闭过程中会丢失。

虚拟表 (Virtual tables)

It is possible to create, directly from the library, its own storage engine to simulate a table in the database. Creating a virtual table is done by implementing a set of functions. Access to the table is done completely transparent, except the absence of some features (not create triggers or index or to modify the table structure).

可以直接从库中创建其自己的存储引擎，以模拟数据库中的表。 创建虚拟表是通过实现一组功能来完成的。 对表的访问是完全透明的，但缺少某些功能(不创建触发器或索引或修改表结构)。

This mechanism allows access using the SQL language to any kind of data source like CSV or XML.

这种机制允许使用SQL语言访问任何类型的数据源，例如CSV或XML。

功能 (Functions)

SQLite provides a programming interface, through its library for creating user-defined functions. The set of functions defined by the library can be overridden, to redefine their implementation. Some operators such as LIKE using sub-layer functions eponymous which may then be replaced by user-defined functions.

SQLite通过其库提供了一个编程接口，用于创建用户定义的函数。 该库定义的函数集可以被覆盖，以重新定义其实现。 一些运营商，例如使用子层功能的LIKE同名，然后可以用用户定义的功能代替。

SQLite does not support the creation of procedures, but their need is less because of the embedded architecture.

SQLite不支持创建程序，但是由于具有嵌入式体系结构，因此对它们的需求较少。

指数 (Index)

SQLite allows the creation of indexes on one or more columns. Indexes can be amounts (ASC) or descending (DESC) and single DateTime (this is akin to creating a unique constraint). SQLite uses its indexes to tree B.

SQLite允许在一个或多个列上创建索引。 索引可以是数量(ASC)或降序(DESC)以及单个DateTime(类似于创建唯一约束)。 SQLite使用其索引到树B。

SQLite brought on keyword EXPLAIN for describing the steps necessary to execute a command and the index used.

SQLite引入了关键字EXPLAIN来描述执行命令所必需的步骤以及所使用的索引。

语 (Pragmas)

Pragmas are pairs of keys / values SQLite configuration. They are internal to a database and can describe how SQLite interpreting certain operations. They can also enable or disable certain features, especially for backward compatibility.

语法是对键/值对SQLite配置。 它们在数据库内部，可以描述SQLite如何解释某些操作。 它们还可以启用或禁用某些功能，尤其是为了向后兼容。

采用 (Adoption)

In addition to its formal establishment in C, bindings for other languages exist (C + +, Perl, Ruby, TCL, using languages .NET framework via an ADO.NET driver).

除了使用C进行正式建立外，还存在其他语言的绑定(C ++，Perl，Ruby，TCL，通过ADO.NET驱动程序使用语言.NET框架)。

Some programming languages include SQLite in their standard library, is the case, among others, Python (since version 2.5) and PHP (since version 5).

某些编程语言就是在其标准库中包括SQLite，例如Python(自2.5版起)和PHP(自5版起)。

SQLite is used in many open source software like Mozilla Firefox, in many GNU / Linux in server operating systems like Solaris and desktop or mobile as Android or Symbian, in some software from Apple, Google, Adobe and McAfee as well as some devices from Philips.

SQLite用于许多开源软件(如Mozilla Firefox)，许多GNU / Linux，服务器操作系统(如Solaris和台式机或移动设备，如Android或Symbian)，苹果，谷歌，Adobe和McAfee的某些软件以及飞利浦的某些设备。 。

The draft covering the storage of a SQL database on the side of the web browser published by the W3C also states that locating the software functionality must be able to correctly interpret the dialect in SQLite version 3.6.19. Although SQLite is not imposed by the W3C, Google Chrome, Apple Safari and Opera Browser use for this purpose.

W3C发布的有关在Web浏览器侧面存储SQL数据库的草案还指出，定位软件功能必须能够正确解释SQLite 3.6.19版中的方言。 尽管W3C并未使用SQLite，但是Google Chrome，Apple Safari和Opera Browser均用于此目的。

不同版本 (Different versions)

SQLite exists in two main versions: 2.x and 3.x Versions 2 and 3 of SQLite are distinguished by several developments:

SQLite存在两个主要版本：2.x和3.x SQLite的版本2和3具有以下几种开发功能：

• The database files are not always mutually compatible. This means that basic format SQLite 2 can not be read certainly by SQLite 3 and vice versa;
  数据库文件并不总是相互兼容的。 这意味着SQLite 3无法肯定地读取基本格式SQLite 2，反之亦然；
• SQL syntax highlighting does not exist in SQLite 2: IF NOT EXISTS for queries, and CREATE TABLE, ADD COLUMN and RENAME COLUMN to queries ALTER TABLE;
  SQLite 2中不存在SQL语法高亮显示：如果查询不存在，则创建CREATE TABLE，ADD COLUMN和RENAME COLUMN以查询ALTER TABLE。
• Only the latest versions of SQLite support some more advanced features, such as management of foreign keys or CHECK constraints;
  只有最新版本SQLite支持某些更高级的功能，例如外键或CHECK约束的管理。
• SQLite 3 supports standard UTF-8 and UTF-16;
  SQLite 3支持标准的UTF-8和UTF-16；
• SQLite 3 encodes identifiers lines of 64 bits instead of 32 bits, allowing a virtually unlimited number of lines;
  SQLite 3对标识符行进行编码，而不是32位，而是64位，从而允许几乎无限制的行数。
• PHP uses a class or PDO with PHP 5.3, function sqlite3_ * () to manage SQLite 3, so it uses functions sqlite_ * () for SQLite 2.
  PHP将类或PDO与PHP 5.3一起使用，函数sqlite3_ *()来管理SQLite 3，因此它对SQLite 2使用函数sqlite_ *()。

Study: From Wikipedia, the free encyclopedia. The text is available under the Creative Commons.

研究：来自维基百科，免费的百科全书。 该文本可在“ 知识共享”下找到 。

翻译自: https://www.eukhost.com/blog/webhosting/sqlite-part-3/