Premesis:
I know that the best way to use a primary key is to set the field as integer (autoincrement or not) due to indexes, but I'm in a particular situation...
我知道使用主键的最佳方法是将字段设置为整数(自动增量或不是因为索引),但我处于特定情况......
I have a program that works offline (on both pc and tablet) using sqlite as a database. The user can sync the datas between the program and the central server (like a cloud). When the user sync the datas, all the local update field are updated on server and viceversa.
我有一个使用sqlite作为数据库脱机工作的程序(在PC和平板电脑上)。用户可以在程序和*服务器之间同步数据(如云)。当用户同步数据时,所有本地更新字段都在服务器上更新,反之亦然。
Using a primary key autoincrement in this situation is a bad solution because if two users add a field in a table (on two different device) I'll have the primary key duplicated..
在这种情况下使用主键自动增量是一个糟糕的解决方案,因为如果两个用户在表中添加一个字段(在两个不同的设备上),我将重复主键。
Then I added another field that contains the ID of the user so I have an index formed by: USER_ID and FIELD_ID this way the situation change but still there can be problems...
然后我添加了另一个包含用户ID的字段,所以我有一个由以下形式构成的索引:USER_ID和FIELD_ID,这样情况会发生变化,但仍然存在问题......
1) I can't have the FIELD_ID as AUTO_INCREMENT 2) The problem described above still remains, if the User Paul has two device with the same database version (synced at the same time), and in the device 1 he does an insert on table_AAA and he does another insert on table_AAA but from device 2, I will have a duplicate index (USER_ID / FIELD_ID)
1)我不能将FIELD_ID作为AUTO_INCREMENT 2)如果用户Paul有两个具有相同数据库版本的设备(同时同步),并且在设备1中他执行了插入,则上述问题仍然存在。 table_AAA和他在table_AAA上做了另一个插入,但是从设备2,我将有一个重复的索引(USER_ID / FIELD_ID)
So I converted the FIELD_ID as a varchar(17) composed by YYYYMMDDHHMMSSmmm, this way is pretty impossible that an user make two operation at the same millisecond..
所以我将FIELD_ID转换为由YYYYMMDDHHMMSSmmm组成的varchar(17),这种方式几乎不可能是用户在同一毫秒内进行两次操作。
The Question:
Is there a better way to set the primary key in my situation???
在我的情况下有更好的方法来设置主键???
2 个解决方案
#1
2
Create a second, unique field, a guid. You get your fast indexing for regular usage, and uniqness for merging.
创建第二个独特的字段,一个guid。您可以获得常规使用的快速索引,以及合并的单一性。
#2
1
You should probably have UUID as the primary key, to add more clarity you can add a device_id field to distinguish rows, though UUID should suffice.
您可能应该将UUID作为主键,为了添加更多清晰度,您可以添加device_id字段来区分行,但UUID应该足够了。
By the way, UUIDs results ascend, that is table data fragmentation should not bother you much.
顺便说一句,UUIDs结果提升,即表数据碎片不应该打扰你。
#1
2
Create a second, unique field, a guid. You get your fast indexing for regular usage, and uniqness for merging.
创建第二个独特的字段,一个guid。您可以获得常规使用的快速索引,以及合并的单一性。
#2
1
You should probably have UUID as the primary key, to add more clarity you can add a device_id field to distinguish rows, though UUID should suffice.
您可能应该将UUID作为主键,为了添加更多清晰度,您可以添加device_id字段来区分行,但UUID应该足够了。
By the way, UUIDs results ascend, that is table data fragmentation should not bother you much.
顺便说一句,UUIDs结果提升,即表数据碎片不应该打扰你。