SQL 中的 UNIQUE 关键字

本文主要对工作中由UNIQUE唯一性约束更改导致数据库升级拷贝数据的时候导致数据丢失，引发VPN改变的问题做一个简单的笔记，顺便梳理一下UNIQUE关键字相关的知识。

• 起因
• 解决过程
• 涉及到的知识
  • 开启SQL log
    • 正常开启SQL log的方法
    • 通过修改prop文件进行开启log
    • Example

起因

前几天收到了用户提的一个故障，故障描述是这样的：从Android O版本通过fota升级到P版本的时候，会导致升级之后的VPN发生改变，由cmnet更改为cmwap。在用户手机上的表现为升级之后网络异常卡顿，微信消息接受缓慢，尤其聊天图片甚至发生发送不出去或者接收不到的情况。

解决过程

在 Android 中与 VPN相关的模块为 TelephonyProvider，而在设置里面加载默认APN的时候也是通过uri去 TelephonyProvider 中进行查询。所有运营商的 APN 相关的配置是通过需求从运营商拿到数据之后配置到apn-config.xml 文件中的，而手机第一次开机的时候 TelephonyProvider 会解析此 xml 文件，加载所有 APN，并保存到 telephony.db 数据库里面的 carriers 表中(具体路径为：/data/user_de/0/com.android.providers.telephony/databases/telephony.db，需要root才能看得到)。因此此次问题就可以定位到了 TelephonyProvider 中的数据库从O升级到P 的时候发生异常。

APN加载是用sharedPreference 保存的apn_id进行数据库查询，在升级onUpgrade完成之后利用此id更新一些必要的字段，在加载新版本的apn-config.xml文件之后反向利用这些字段查询apn_id.查看xml保存的字段内容发现查询到的该apn_id对应的字段和升级之前数据库中数据不一致，进而也说明了数据库升级的时候发生了异常。

接着再说说 TelephonyProvider 模块中和 apn 相关的数据库升级流程。由于要兼容各个 Android 版本，所以在provider的onUpgrade()方法中对不同版本的数据库进行了处理，主要做的工作有以下两点：

• 通过 ALTER 关键字对旧版本数据库增加列；
• 对旧表数据进行拷贝；
  • 创建一个carriers_tmp 表（新版本的建表语句）;
  • 查询 carriers 表中的所有数据并逐条插入到carriers_tmp表中；
  • 删除 carriers 表；
  • 将carriers_tmp 重命名为 carriers;

接到这个问题第一时间看了下log发现没有与表拷贝相关的输入，因此加了一些输出log并模拟fota升级流程，果不其然是上述红色步骤出了问题：在执行完上述第二步之后利用apn_id查询对应的数据库内容已经和第二步执行之前的数据库内容不一致(实际问题中差了一行)；

开启SQLite相关log发现在数据拷贝的过程中插入某一行的时候会插入失败，提示违反唯一性约束。新表比旧表少了一行数据，所以查询内容出错也就那么意外了。

接着对比升级前后的代码果然发现在升级后的数据库建表语句中减少了一个唯一性约束条件，这也就是这个问题的罪魁祸首了。

涉及到的知识

开启SQL log

正常开启SQL log的方法

Android上默认是关闭SQL log的，在对数据库进行操作的时候除非在java代码里面打印，否则看不到数据库操作详情，就算在java代码里面进行了打印，也不一定发现数据库操作发生的问题。

因此在做数据库相关分析的时候除了在java层进行打印，也应该打开SQL 相关的log进行分析；打开方法如下：

adb shell setprop log.tag.SQLiteLog VERBOSE
adb shell setprop log.tag.SQLiteStatements VERBOSE

但是上述方法有不好的一点就是如果手机重启，则上述方法会在重启之后失效。由于做TelephonyProvider升级相关的分析，手机必须得重启。因此上述方法不见得可行。

通过修改prop文件进行开启log

在Android 开机的时候会从/vendor/build.prop 和 system/build.prop以及/product/build.prop文件加载prop属性。因此只需要修改prop文件就可以达到重启可用的目的。

这里修改/vendor/build.prop`文件，加入以下代码：

log.tag.SQLiteLog=VERBOSE
log.tag.SQLiteStatements=VERBOSE

之后进行重启那么SQL log也不会关闭了。

###SQL语句中的 UNIQUE 关键字

UNIQUE是SQL中用来对字段进行唯一性约束的关键字，它可以约束一个键，也可以约束多个键（约束多个键时这个键值的组合唯一）。SQL 关键字中和 UNIQUE功能比较相似的是PRIMARY，这两个关键字虽然功能上很相似，但是还是有一定的区别：

• PRIMARY只可以约束一个键，被约束的键称为主键，而UNIQUE可以修饰多个键，但唯一性约束所在的列并不是表的主键列；

• UNIQUE 约束的键值可以为空，但是PRIMARY约束的键值不可以为空并不可重复，即：

  PRIMARY = UNIQUE + NOT NULL

• PRIMARY 约束是为了让外键引用；

• 一个表最多只有一个主键，但可以有很多UNIQUE约束的键；

Example

新建一个用户资料表，建表语句如下，其中_id为主键，name_text和id_card_number用唯一性约束。

CREATE TABLE unique_test ( 
	_id INTEGER AUTO_INCREMENT PRIMARY KEY , 
	name_text VARCHAR ( 20 ), 
	age INTEGER, 
	id_card_number VARCHAR ( 18 ), 
	UNIQUE ( name_text, id_card_number ) );

接着插入三条数据到该表中：

INSERT INTO unique_test(name_text,age,id_card_number)  VALUES("name1",10,"123456");
INSERT INTO unique_test(name_text,age,id_card_number)  VALUES("name2",20,"234561");
INSERT INTO unique_test(name_text,age,id_card_number)  VALUES("name1",30,"234561");

接着插入下面这条数据：

INSERT INTO unique_test(name_text,age,id_card_number)  VALUES("name1",30,"123456");

运行完这条语句则会报错，提示如下：

Duplicate entry 'name1-123456' for key 'name_text'

为什么呢？是因为name_text字段和id_card_number字段是被UNIQUE进行约束的，因此这两个字断的组合在数据库表中应该保持唯一，但是上面一天数据的组合name1-123456和插入的第一条数据重复，因此会提示错误。

如果要插入的时候不报错，则可以使用ignore或者update方法：

• ignore

  INSERT IGNORE INTO unique_test(name_text,age,id_card_number) VALUES("name1",30,"123456");

  这条插入的数据直接被忽略；

• update方法:

  INSERT INTO unique_test(name_text,age,id_card_number) VALUES("name1",30,"123456") ON DUPLICATE KEY UPDATE id_card_number = '654321';

  运行完毕后当前数据库表中的记录个数不会发生变化，但是已有的数据会中的id_card_number值会被更新为654321。