学习+翻译《SQL As Understood By SQLite》－－CREATE INDEX

SQL As Understood By SQLite

[Contents]

CREATE INDEX

sql-statement ::=	CREATE [UNIQUE] INDEX [database-name .] index-name ON table-name ( column-name [, column-name]* ) [ ON CONFLICT conflict-algorithm ]
column-name ::=	name [ COLLATE collation-name] [ ASC | DESC ]

The CREATE INDEX command consists of the keywords "CREATE INDEX" followed by the name of the new index, the keyword "ON", the name of a previously created table that is to be indexed, and a parenthesized list of names of columns in the table that are used for the index key. Each column name can be followed by one of the "ASC" or "DESC" keywords to indicate sort order, but the sort order is ignored in the current implementation. Sorting is always done in ascending order.

"CREATE INDEX"后面跟新的index（索引）的名称.将为关键字"ON"后接的表生成索引.索引是新生成的名称为"(" + 表中列名 + ")"的列.列可以用关键字"ASC"或"DESC"跟随来指明它的排列顺序.(当前版本忽略这两个关键字,统一为升序).

The COLLATE clause following each column name defines a collating sequence used for text entires in that column. The default collating sequence is the collating sequence defined for that column in the CREATE TABLE statement. Or if no collating sequence is otherwise defined, the built-in BINARY collating sequence is used.

(这一段究竟是什么意思?)COLLATE语句跟着各个列的名字,定义了列中输入内容的排列顺序.默认的排列顺序是CREATE TABLE时定义的.如果没有另行定义,编译成二进制的顺序被应用.

There are no arbitrary limits on the number of indices that can be attached to a single table, nor on the number of columns in an index.

(???)

If the UNIQUE keyword appears between CREATE and INDEX then duplicate index entries are not allowed. Any attempt to insert a duplicate entry will result in an error.

如果CREATE和INDEX之间使用了关键字UNIQUE,重复的索引是不允许的.试图插入重复的条目会导致错误.

The optional conflict-clause allows the specification of an alternative default constraint conflict resolution algorithm for this index. This only makes sense if the UNIQUE keyword is used since otherwise there are not constraints on the index. The default algorithm is ABORT. If a COPY, INSERT, or UPDATE statement specifies a particular conflict resolution algorithm, that algorithm is used in place of the default algorithm specified here. See the section titled ON CONFLICT for additional information.

The exact text of each CREATE INDEX statement is stored in the sqlite_master or sqlite_temp_master table, depending on whether the table being indexed is temporary. Every time the database is opened, all CREATE INDEX statements are read from the sqlite_master table and used to regenerate SQLite's internal representation of the index layout.

准确的CREATE INDEX的状态被保存在sqlite_master或sqlite_temp_master的表中(这取决于表是否是临时被生成索引).每当数据库被打开,sqlite_master会读取所有索引的状态用来生成SQLite内部的index布局.

Indexes are removed with the DROP INDEX command.

This page last modified on 2005/05/10 16:11:41