Ownsight

标题: sqlite unique 多列 trigger [打印本页]

作者: craft? ? 时间: 2018-8-11 11:27
标题: sqlite unique 多列 trigger
转自:https://blog.csdn.net/caoyicheng1/article/details/38438199

已经不是第一次用sqlite了,但是,每次都忘记要记录一下。每次都要重新找资料,所以,做完实验我就写下来吧。

我的业务场景是这样子的,我板子上跑的是linux,一个通信程序需要调用数据库。期间我只有一张表用于接收数据,暂时称为pre表。其余的表都是通过这张表的数据去更新自己的数据。

首先,我遇到的问题是,需要多字段联合使用唯一性,比如:id和dev两个字段我要求其中有一个数据变动了,我就去更新这条数据。

如:表中id=1,dev_num=2。然后insert or replace id=1,dev_num=2。这种情况,只更新原来的条目。

表中id=1,dev_num=2。然后insert or replace id=1,dev_num=3。这种情况,会插入一条新的记录。

下面是我的更新或者插入语句的写法:


这么做的原因是,保证数据库不会无限扩张,我的业务需求,也是需要数据在一定范围内的。开始使用主键定义id,发现dev这个字段无法唯一,sqlite原则上允许多字段主键联合,听说有风险,官网推荐使用primary key而不是integer primary key。我这个人,对于有风险的东西,一般不会去问津。所以,我也推荐大家使用另外一种解决办法,创建联合唯一性。

这里的唯一性,有两种方法,第一个直接在创价表的时候,定义unique。

create table fpgaData( id int primary key, dev_num int not null, uniqueid,dev_num) );
这种方法,其实是分别规定了id和dev_num的惟一性,所以,任何一个字段重复,都无法插入新的条目,并不能够满足我们的要求。、

第二种创建联合索引可以达到我们的目的:

只要在表创建语句后面跟上一条创建索引的语句即可。这样sql解析器会告诉数据库,是计算两个字段的某一种联合值,以达到判断其中任何一个字段的更新,大家可以实践一下。

另外,上面业务逻辑里面说了要使用其中一张表去更新另外几张表的内容。我采用的是trigger触发器。

trigger总共有三种,insert,delete,update

命令格式,只有update比较特殊,可以检测到表中某个特定字段的更新,具体怎么写,大家自己去百度。网上大把。

下面讲一下,trigger的几个鲜为人知的特性,这些都是通过实验得出来的。

1.trigger是允许在同一张表中存在多个trigger的,你可以这样写:

请自行注意语法格式。

2.update 这个trigger在sqlite3中是默认针对每一行的update会更新一次的。当然,sqlite3也只有这种模式。这样会引起很多你意象不到的效果。这要结合new还有old来讲。

这是我的一张表,也就是update触发器所绑定的表。下面我来说一下update每一次只更新一条记录造成的问题。

上面的表中有一条记录,我们首先看我监听min字段,更新min后,被影响表的效果。其中present_p对应w

可以看到没有什么问题。可是在多条记录同时更新时问题就出来了。我们把min从10更新到20看对比图:

可以看到,明明只更新两条记录,为什么,会多插入一条到第二张图中?其实,是因为顺序执行的原因,每一次update一条记录都会触发触发器的action。

仔细看多出来min=10,dev_num=2的记录,为什么?因为,在更新第一条记录之后(我使用了after),会select表中该字段所有的当前记录。那么第二条记录还没来得及更新,min仍旧为10,这也是为什么总是会多出一条记录的原因。然后,在更新第二条记录的时候,同样也会select第一条已经更新的记录,只是这个过程你看不见而已,总的来说,每一次,都会select整张表。意思是你的记录越多,你多出来的无效记录也就越多,当然,假如你有这个需求,那我就不说什么了。

所以,sqlite官方,针对这个问题,提供了解决方案。使用new和old来区分,表中已经被update过的记录,old反之。所以我们可以在select中添加new来区分上一条被update过的记录。

用法:new.字段名? ?? ?? ?? ?? ?? ?? ?? ?? ?不要忘记中间的点,old和new行的所有属性(字段)都可以通过点号来引用。

值得注意的是,在没有触发器的表中,或者触发器外,你是无法使用new和old的。

你们可以实验一下,很多书上都没有讲清楚,在这里,将出来,大家分享一下。

至此,讲完了。

如有纰漏或者错误,请留言斧正!谢谢!



作者: craft? ? 时间: 2018-8-11 18:14
补充说明,经过测试,对于空值,将对于唯一性的索引无效,必须对应的UNIQUE INDEX列都进行赋值才可以进行限制
作者: craft? ? 时间: 2018-8-11 18:32
显示数据库中的所有的索引
SELECT * FROM sqlite_master WHERE type = 'index'




欢迎光临 Ownsight (http://bbs.ownsight.net/) Powered by Discuz! X3.2