Sqlite3中replace语句用法详解


由于自己的孤陋寡闻, 也由于之前的项目中, 很少参与过数据库模块的开发, 以至于前几天才知道Sqlite数据库也支持replace语句。 本文主要讲解在Sqlite中replace语句的行为,也算是学习笔记。此外, replace语句和update语句有相似的地方, 但是也有很多不同之处。 本文还要对比一下Sqlite中的 replace语句和update语句 。 


在本例中使用如下数据库表:


(图 1)


该表的表名为student, 存储学生信息。 所有字段的数据类型都是TEXT 。 其中id和name作为复合主键。 email字段加上了唯一约束。建表语句如下:

CREATE TABLE IF NOT EXISTS student (
		"id"  TEXT,
		"name"  TEXT NOT NULL,
		"sex"  TEXT,
		"email"  TEXT UNIQUE,
		"fenshu"  TEXT CHECK(fenshu > 0),
		"tecid"  TEXT REFERENCES teacher(id),
		"class"  TEXT, 
		PRIMARY KEY(id, name) 
		)



replace语句的行为


1 replace语句会删除原有的一条记录, 并且插入一条新的记录来替换原记录。


为了验证这个结论, 下面打开Sqlite命令行, 执行以下语句来替换id为2的记录。 

sqlite> replace into student (id, name, sex, email, fenshu, tecid, class) values
 (‘2‘, ‘lisi‘, ‘*F‘, ‘[email protected]‘, ‘80‘, ‘2‘, ‘1‘);

执行完这条语句之后, student表中的数据变成下图所示:


(图 2)


对比图1和图2 , 可以发现: 在图1中, id为2 的记录是表中的第一条记录, 当执行完上述的replace语句之后, id为2的记录位于整张表的最后。 这就说明, 这条replace语句删除了原有的id为2的记录, 有插入了一条新的id为2的记录。


2 一般用replace语句替换一条记录的所有列, 如果在replace语句中没有指定某列, 在replace之后这列的值被置空 。 


下面我们还是以id为2 的记录做实验, 执行如下语句:

sqlite> replace into student (id, name, sex, email, fenshu, tecid) values (‘2‘,
‘lisi‘, ‘*F‘, ‘[email protected]‘, ‘80‘, ‘2‘);

该语句还是替换id为2, name为lisi的记录, 只是在指定列的时候, 没有指定class列。 在执行完成之后, 表中的数据如下:


(图 3)


对比图2和图3 , 可以看到, id为2, name为lisi的记录的class字段没有值。


3 replace根据主键确定被替换的是哪一条记录


在该表中, 把id和name指定为复合主键。 在上面两条语句执行的时候, 都在values中指定了id为2, name为lisi 。 执行之后看到的结果也是id为2, name为lisi的记录被替换。 这就说明了replace语句根据主键的值确定被替换的是哪一条记录。


4 replace语句不能根据where子句来定位要被替换的记录


执行以下语句:

sqlite> replace into student (id, name, sex, email, fenshu, tecid) values (‘2‘,
‘lisi‘, ‘*F‘, ‘[email protected]‘, ‘80‘, ‘2‘) where id = ‘2‘;

会报如下错误:

Error: near "where": syntax error


5 如果执行replace语句时, 不存在要替换的记录, 那么就会插入一条新的记录。


在student表中, 我们让id和name成为复合主键。 下面我们使用replace语句替换id为100, name为a 的记录。 从图3中可以看到, 表中存在name为a的记录, 但是这条记录的id为7, 而不是100 。也就是说 id为100, name为a 的记录不存在。

执行如下语句:

sqlite> replace into student (id, name, sex, email, fenshu, tecid, class) values
 (‘100‘, ‘a‘, ‘*F‘, ‘[email protected]‘, ‘80‘, ‘2‘, ‘1‘);

执行完成之后, 表中的数据如下:


(图 4)


可以看到, 在表中插入了一条新的记录。


6 如果新插入的或替换的记录中, 有字段和表中的其他记录冲突, 那么会删除那条其他记录。


上面的第5步同时也说明了这个问题。 对比图4 和图5 , 发现在插入一条新的id为100, name为a的记录之后, 还删除了id为2, name为lisi的记录。 为什么会这样呢? 我们在开始的时候说过, 表中的email字段加上了唯一约束。 id为2的记录的email和新插入的id为100的记录中的email相同, 都是[email protected] 。 这就导致违反唯一约束, 所以在插入id为100的记录之前, 删除了id为2的记录。


下面再次验证一下。 现在我们替换id为5, name为lisi3 的记录, 将它的email替换为[email protected] 。 表中的id为5的记录的email字段也是[email protected] , 所以会导致违反唯一约束。 


执行下面的语句:

sqlite> replace into student (id, name, sex, email, fenshu, tecid, class) values
 (‘5‘, ‘lisi3‘, ‘F‘, ‘[email protected]‘, ‘80‘, ‘2‘, ‘1‘);

执行完这条语句之后, 表中的数据如下图:


(图 5)


对比图4 和 图5 , 发现id为5的记录被替换掉, 并且把这条记录的email设置为[email protected], 这和图4中原有的id为6的记录冲突, 所以导致id为6的记录被删除, 在图5 中已经没有id为6的那条记录了。



replace语句和update语句的对比


对于update语句, 因为经常使用到,应该算比较熟悉。 下面对比一下update和replace语句的行为, 只是简单陈述, 不再以具体实例说明。


update语句使用where子句定位被更新的记录;


update语句可以一次更新一条记录, 也可以更新多条记录, 只要这多条记录都复合where子句的要求;


update只会在原记录上更新字段的值, 不会删除原有记录, 然后再插入新纪录;


如果在update语句中没有指定一些字段, 那么这些字段维持原有的值, 而不会被置空;



Sqlite3中replace语句用法详解,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。