學習+翻譯《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