SQLite3 多字段联合唯一性,trigger使用分析

前端之家收集整理的这篇文章主要介绍了SQLite3 多字段联合唯一性,trigger使用分析前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

已经不是第一次用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,unique(id,dev_num) );
 
这种方法,其实是分别规定了id和dev_num的惟一性,所以,任何一个字段重复,都无法插入新的条目,并不能够满足我们的要求。、 
 

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

sql = "create table fpgaData( id int primary key,\
			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,w) select <span style="color:#ff0000;">min,present_p</span> 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,NULL,&zErr);
		sqlite3_free(zErr);
	sql = "CREATE TRIGGER insert_fpgaData  after INSERT ON tableA \
    			BEGIN \
     			insert or replace into singlePD(min,new.dev_num,new.present_p from fpgaData; \
     			insert or replace into totalWD(min,&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的。

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

至此,讲完了。

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

原文链接:https://www.f2er.com/sqlite/200338.html

猜你在找的Sqlite相关文章