SQLite的CREATE INDEX 和 SQLite的 on CONFLICT 子句语法

SQLite的SQL语法

CREATE INDEX

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

CREATE INDEX命令由”CREATE INDEX”关键字后跟新索引的名字,关键字”ON”,待索引表的名字,以及括弧内的用于索引键的字段列表构成。每个字段名可以跟随”ASC” 或”DESC”关键字说明排序法则,但在当前版本中排序法则被忽略。排序总是按照上升序。

每个字段名后跟COLLATE子句定义文本记录的比较顺序。缺省的比较顺序是由CREATE TABLE语句说明的比较顺序。 若不定义比较顺序,则使用内建的二进制比较顺序。

附加到单个表上的索引数目没有限制,索引中的字段数也没有限制。

若UNIQUE关键字出现在CREATE和INDEX之间,则不允许重名的索引记录。试图插入重名记录将会导致错误。

每条CREATE INDEX语句的文本储存于sqlite_mastersqlite_temp_master表中,取决于被索引的表是否临时表。 每次打开数据库时,所有的CREATE INDEX语句从sqlite_master表中读出,产生SQLite的索引样式的内部结构。

若使用可选的IF NOT EXISTS子句,且存在同名索引,则该命令无效。

使用DROP INDEX命令删除索引。

易语言使用栗子:

数据库.执行SQL语句 (“CREATE UNIQUE INDEX IF NOT EXISTS name_index ON ” + 数据表名 + “( 字段名 )”)

数据库.执行SQL语句 (“DROP INDEX IF EXISTS name_index”)

 

SQLite on conflict子句

conflict-clause ::= ON CONFLICT conflict-algorithm
conflict-algorithm ::= ROLLBACK | ABORT | FAIL | IGNORE | REPLACE

ON CONFLICT子句不是独立的SQL命令。这是一条可以出现在许多其他SQL命令中的非标准的子句。由于它并不是标准的SQL语言,这里单独介绍它。

ON CONFLICT子句的语法在如上的CREATE TABLE命令中示出。对于INSERT和UPDATE,关键词”ON CONFLICT”由”OR”替代,这样语法显得自然。例如,不用写”INSERT ON CONFLICT IGNORE”而是”INSERT OR IGNORE”.二者表示相同的意思。

ON CONFLICT子句定义了解决约束冲突的算法。有五个选择:ROLLBACK, ABORT, FAIL, IGNORE, 和REPLACE.缺省方案是ABORT.选项含义如下:

ROLLBACK
当发生约束冲突,立即ROLLBACK,即结束当前事务处理,命令中止并返回SQLITE_CONSTRAINT代码。若当前无活动事务(除了每一条命令创建的默认事务以外),则该算法与ABORT相同。

ABORT
当发生约束冲突,命令收回已经引起的改变并中止返回SQLITE_CONSTRAINT。但由于不执行ROLLBACK,所以前面的命令产生的改变将予以保留。缺省采用这一行为。

FAIL
当发生约束冲突,命令中止返回SQLITE_CONSTRAINT。但遇到冲突之前的所有改变将被保留。例如,若一条UPDATE语句在100行遇到冲突100th,前99行的改变将被保留,而对100行或以后的改变将不会发生。

IGNORE
当发生约束冲突,发生冲突的行将不会被插入或改变。但命令将照常执行。在冲突行之前或之后的行将被正常的插入和改变,且不返回错误信息。

REPLACE
当发生UNIQUE约束冲突,先存在的,导致冲突的行在更改或插入发生冲突的行之前被删除。这样,更改和插入总是被执行。命令照常执行且不返回错误信息。当发生NOT NULL约束冲突,导致冲突的NULL值会被字段缺省值取代。若字段无缺省值,执行ABORT算法。

当冲突应对策略为满足约束而删除行时,它不会调用删除触发器。但在新版中这一特性可能被改变。

INSERT或UPDATE的OR子句定义的算法会覆盖CREATE TABLE所定义的。ABORT算法将在没有定义任何算法时缺省使用。

 

应用场景:sqlite数据库 大量插入数据时如何避免重复插入(如果存在就更新,不存在就插入)

CREATE TABLE "main"."film" (
"filmno"  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"filmname"  TEXT(100),
"language"  TEXT(10),
"type"  TEXT(10),
CONSTRAINT "不重复规则" UNIQUE ("filmname""language"ON CONFLICT REPLACE/*就这段是重点*/
)
易语言栗子:
数据库.执行SQL语句 (“INSERT OR REPLACE INTO 数据表名  (字段名) VALUES  (‘字段值’)”)