Ownsight

?找回密码
?立即注册
搜索
热搜: 活动 交友 discuz
查看: 1175|回复: 2
打印 上一主题 下一主题

sqlite unique 多列 trigger

[复制链接]

803

主题

903

帖子

3201

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
3201
跳转到指定楼层
楼主
发表于 2018-8-11 11:27:46 | 只看该作者 回帖奖励 |正序浏览 |阅读模式
转自: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。这种情况,会插入一条新的记录。

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



  • sql = "insert or replace into fpgaData(id, dev_num, state_flg, error_cd, present_p, power) values(?, ?, ?, ?, ?, ?)";



  • ? ? ? ? ? ? ? ? if(sqlite3_prepare_v2(db, sql, -1,??&stmt, NULL) == SQLITE_OK){



  • ? ? ? ? ? ? ? ? ? ? ? ? //bind data for insert new line, total 1 line



  • ? ? ? ? ? ? ? ? ? ? ? ? sqlite3_bind_int(stmt, 1, 1);? ? ? ?



  • ? ? ? ? ? ? ? ? ? ? ? ? .....



  • ? ? ? ? ? ? ? ? ? ? ? ? sqlite3_step(stmt);



  • ? ? ? ? ? ? ? ? ? ? ? ? sqlite3_finalize(stmt);? ? ? ? //finalize the stmt



  • ? ? ? ? ? ? ? ? }else{



  • ? ? ? ? ? ? ? ? ? ? ? ? printf("SQL error2: %s\n", sqlite3_errmsg(db));



  • ? ? ? ? ? ? ? ? ? ? ? ? sqlite3_finalize(stmt);? ? ? ?



  • ? ? ? ? ? ? ? ? ? ? ? ? return 1;



  • ? ? ? ? ? ? ? ? }



这么做的原因是,保证数据库不会无限扩张,我的业务需求,也是需要数据在一定范围内的。开始使用主键定义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 = "create table fpgaData( id int primary key, dev_num int not null, \



  • ? ? ? ? ? ? ? ? ? ? ? ? state_flg int, error_cd int, present_p int, power int, year int, month int, day int, min int ); \



  • ? ? ? ? ? ? ? ? ? ? ? ? CREATE UNIQUE INDEX fpgaData_I ON fpgaData(id, dev_num);";


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

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

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

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

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

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



  • sql = "CREATE TRIGGER update_fpgaData??after UPDATE OF min ON tableA \



  • ? ? ? ? ? ? ? ? ? ? ? ? ? ? BEGIN \



  • ? ???? ? ? ? ? ? ? ? ? ? ? ? insert or replace into singlePD(min, dev_num, w) select style="color:#ff0000;">min, dev_num, present_p from fpgaData; \



  • ? ???? ? ? ? ? ? ? ? ? ? ? ? insert or replace into totalWD(min, w) select new.min, sum(new.present_p) from fpgaData; \



  • ? ???? ? ? ? ? ? ? ? ? ? ? ? insert or replace into totalPwY(wh, year, month, day) select sum(new.present_p), new.year, new.month, new.day from fpgaData; \



  • ? ? ? ? ? ? ? ? ? ? ? ? ? ? END; ";



  • ? ? ? ? ? ? ? ? rc = sqlite3_exec(db, sql, NULL, NULL, &zErr);



  • ? ? ? ? ? ? ? ? sqlite3_free(zErr);



  • ? ? ? ? sql = "CREATE TRIGGER insert_fpgaData??after INSERT ON tableA \



  • ? ? ? ? ? ? ? ? ? ? ? ? ? ? BEGIN \



  • ? ???? ? ? ? ? ? ? ? ? ? ? ? insert or replace into singlePD(min, dev_num, w) select new.min, new.dev_num, new.present_p from fpgaData; \



  • ? ???? ? ? ? ? ? ? ? ? ? ? ? insert or replace into totalWD(min, w) select new.min, sum(new.present_p) from fpgaData; \



  • ? ???? ? ? ? ? ? ? ? ? ? ? ? insert or replace into totalPwY(wh, year, month, day) select sum(new.present_p), new.year, new.month, new.day from fpgaData; \



  • ? ? ? ? ? ? ? ? ? ? ? ? ? ? END; ";



  • ? ? ? ? ? ? ? ? rc = sqlite3_exec(db, sql, NULL, NULL, &zErr);



  • ? ? ? ? ? ? ? ? sqlite3_free(zErr);


请自行注意语法格式。

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的。

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

至此,讲完了。

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


803

主题

903

帖子

3201

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
3201
板凳
?楼主| 发表于 2018-8-11 18:32:02 | 只看该作者
显示数据库中的所有的索引
SELECT * FROM sqlite_master WHERE type = 'index'

803

主题

903

帖子

3201

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
3201
沙发
?楼主| 发表于 2018-8-11 18:14:16 | 只看该作者
补充说明,经过测试,对于空值,将对于唯一性的索引无效,必须对应的UNIQUE INDEX列都进行赋值才可以进行限制
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

快速回复 返回顶部 返回列表