安卓：使用UUID作为SQLite的主键 [英] Android: Use UUID as primary key in SQLite
问题描述
我的应用程序需要得到同步与其他应用程序的用户（上有自己的设备）。我还希望支持离线编辑，被同步到其他协作用户，当用户被连接到互联网。
因此，用户A的变化（虽然他是离线）的一些数据（ohter的话，他将更新数据库条目），或添加新的记录到数据库中。当用户A被连接到互联网，所有变化和新记录被传递到其他协作用户。因此，用户B将得到更改/更新，并可以插入/更新它们进入用户烧烤本地设备数据库。
不过，我需要确保数据库条目的ID是沿着整个系统中是唯一。因此，我需要使用类似UUID。
我的问题：这是个糟糕的主意，用一个整数的UUID（串/ VARCHAR）作为主键一款Android SQLite数据库表而不是将自动递增
我想会有性能问题通过使用字符串（UUID的有36个字符）作为主键。
我想索引的UUID，而不是整数需要较长的时间（比较字符串与比较整数）。我也想，使用UUID，每一个新的数据库记录/条目已被插入时数据库需要重新索引的主键列，当林因为它们的主键索引是不是在有序了（这将是我会用整数自动递增的主键，因为每一个未来的记录添加到了最后，因为新的自动递增的主键始终是最多的，到目前为止，这样的指标将自动排序顺序排列）。我还需要做的是JOINS超过2 - 3桌。我也想这对比较字符串JOINS，而不是整数会减慢数据库查询。
不过，我看不到实现这样的合作同步系统中任何其他的可能性，所以我必须用UUID，对吧？
另一种可能性是使用一个整数自动增量的主键，并使用第二列UUID。因此，为了工作的用户本地设备上，我会用这个主键（整数）用于连接的等等，而我会用UUID列与其他用户同步。
你们有什么想想办法还是到大量的工作你的意见，因为你不会直接尤斯UUID作为主键预期大显著的性能问题？
任何其他建议？
这是个糟糕的主意，用一个整数，将自动递增的UUID（串/ VARCHAR）作为主键一款Android SQLite数据库的表呢？
唯一的，一定的问题，我能想到的是，你将无法使用的CursorAdapter 及其子类，用于显示该表上的查询结果。 的CursorAdapter 需要一个唯一的整数 _id 列中的光标，和presumably你不会有其中的一个。你将不得不建立自己的适配器，也许延长 BaseAdapter ，处理它。
我想会有性能问题通过使用字符串（UUID的有36个字符）作为主键。
有可能，但我会，如果它变成对设备大小的数据库的材料问题，有些惊讶。
不过，我看不到实现这样的合作同步系统中任何其他的可能性，所以我必须用UUID，对吧？
您需要某种形式的UUID的网络协议。 presumably，您将需要UUID在数据库中。这是否UUID必须是表的主键，我不能说，因为我不知道你的架构。
另一种可能性是使用一个整数自动增量的主键，并使用第二列UUID。因此，为了工作的用户本地设备上，我会用这个主键（整数）用于连接的等等，而我会用UUID列与其他用户同步。
正确的。你将有一个UUID->本地整数ID映射表，使用的UUID在你的网络协议，并保留本地数据库大多使用本地的整数ID。这是否将是一个显著的性能提升（特别是考虑到增加的数据库架构的复杂性），我不能说。
你们有什么想想办法还是到大量的工作你的意见，因为你不会直接尤斯UUID作为主键预期大显著的性能问题？
恕我直言，无论是运行一些性能测试，所以你得到一些具体的可比数据，或者只关心它，如果你的数据库I / O，似乎呆滞。
My app needs to get synced with other app users (on there own devices). I also want to support offline editing, that are synchronized to the other collaborative users when the user gets connected to the internet.
So the User A changes (while he is offline) some data (in ohter words he would update database entries) or add new records to the database. When User A gets connected to the internet, all changes and new records are delivered to the other collaborative Users. So User B will get the changes/updates and can insert/update them into User Bs local device database.
But I need to ensure that the ids of the database entries are unique along the whole system. Therefore I need to use something like UUID.
My question: Is it a bad idea to use a UUID (String / Varchar) as primary key in a android sqlite database table instead of an integer that would be auto incremented?
I guess there would be performance issues by using strings (a UUID has 36 characters) as primary key.
I guess indexing uuids instead of integers takes longer (comparing string vs. comparing integers). I also guess that when Im using UUID, every time a new database record/entry has been inserted the database needs to reindex the primary key column, since they primary key index is not in a sorted order anymore (which would be when I would use integer auto increment primary key, because every future record is added at the end, because the new auto incremented primary key is always the greatest number so far, so the index will automatically be in sorted order). What i also need to do is JOINS over 2 - 3 tables. I also guess that comparing strings on JOINS instead of integer would slow down the database query.
However I cant see any other possibility to implement such a collaborative syncing system, so I must use UUID, right?
Another possibility would be to use a integer auto increment primary key and to use a second column uuid. So to work on the users local device, i would use this primary key (integer) for JOINS etc., while I would use the uuid column for syncing with the other users.
What do you guys think about that approach or is it in your opinion to much work, since you wont expect a big significant performance issue by ussing UUID directly as primary key?
Any other suggestions?
Is it a bad idea to use a UUID (String / Varchar) as primary key in a android sqlite database table instead of an integer that would be auto incremented?
The only for-certain problem that I can think of is that you will not be able to use CursorAdapter and its subclasses for displaying the results of queries on that table. CursorAdapter requires a unique integer _id column in the Cursor, and presumably you will not have one of those. You would have to create your own adapter, perhaps extending BaseAdapter, that handles it.
I guess there would be performance issues by using strings (a UUID has 36 characters) as primary key.
Possibly, but I will be somewhat surprised if it turns into a material problem on device-sized databases.
However I cant see any other possibility to implement such a collaborative syncing system, so I must use UUID, right?
You need some sort of UUID for your network protocol. Presumably, you will need that UUID in your database. Whether that UUID needs to be the primary key of a table, I can't say, because I don't know your schema.
Another possibility would be to use a integer auto increment primary key and to use a second column uuid. So to work on the users local device, i would use this primary key (integer) for JOINS etc., while I would use the uuid column for syncing with the other users.
Correct. You would have a UUID->local integer ID mapping table, use the UUIDs in your network protocol, and keep the local database mostly using the local integer IDs. Whether or not this will be a significant performance improvement (particularly given the increased database schema complexity), I can't say.
What do you guys think about that approach or is it in your opinion to much work, since you wont expect a big significant performance issue by ussing UUID directly as primary key?
IMHO, either run some performance tests so you get some concrete comparable data, or only worry about it if your database I/O seems sluggish.
这篇关于安卓：使用UUID作为SQLite的主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
