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

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

猜你在找的Sqlite相关文章