iOS / SQLite，使用guid高效更新大量的recoreds？

I'm trying to figure out the fastest method of adding a (different) guid to a new field, where there could be thousands of records.

我正在试图找出一种向新字段添加(不同)guid的最快方法,其中可能有数千条记录。

I'm pretty sure SQLite doesn't provide a function which will create a guid. So I'm going need to somehow have to create this for each row.

我很确定SQLite没有提供一个可以创建guid的函数。所以我需要以某种方式为每一行创建这个。

Ideas I've come up with do far.

我想出的想法做得很远。

Simply executing an update query where the field is blank and limit 1. Obviously this is going to be slow even within a transaction.

只需执行更新查询,其中字段为空并且限制1.显然,即使在事务中,这也会很慢。
Use cte (common table extension) with a temp table where I build up the values including the guid, the update / join. However I can't seem to get temp tables to work in iOS, I keep getting a library called out of sequence error.

使用cte(公用表扩展)和临时表,我建立包括guid,update / join的值。但是我似乎无法在iOS中使用临时表,我不断得到一个名为乱序错误的库。
Using inline sub queries with the update query. However I don't know how to identify the rows which require the guid, other than them being blank. I'd need to use recursion, which again would require cte, I believe?

使用带有更新查询的内联子查询。但是我不知道如何识别需要guid的行,除了它们是空白的。我需要使用递归,这又需要cte,我相信?

EDIT: Here's my solution, credit to @CL. for the ideas...

编辑:这是我的解决方案,归功于@CL。对于这些想法......

void newguid(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    if (argc != 1 || sqlite3_value_type(argv[0]) != SQLITE_TEXT) {
        sqlite3_result_null(context);
        return;
    }

    @autoreleasepool {
        CFUUIDRef uuid = CFUUIDCreate(kCFAllocatorDefault);
        NSString *uuidString = (__bridge_transfer NSString *)CFUUIDCreateString(kCFAllocatorDefault, uuid);
        CFRelease(uuid);

        sqlite3_result_text(context, [uuidString UTF8String], -1, SQLITE_TRANSIENT);
    }
}

- (void)createSqlGuidFunction:(sqlite3*)db
{
    if (sqlite3_create_function_v2(db, "newguid", 1, SQLITE_ANY, NULL, &newguid, NULL, NULL, NULL) != SQLITE_OK)
    {
        NSLog(@"%s: sqlite3_create_function_v2 error: %s", __FUNCTION__, sqlite3_errmsg(db));
    }
}

2 个解决方案

#1

The documentation says that

文档说明了这一点

applications can generate globally unique identifiers using this function together with hex() and/or lower() like this:

应用程序可以使用此函数以及hex()和/或lower()生成全局唯一标识符,如下所示:
hex(randomblob(16))

lower(hex(randomblob(16)))

But if you want to use a different mechanism to generate the GUID, you can register your own function.

但是如果您想使用不同的机制来生成GUID,您可以注册自己的函数。

#2

I would recommend doing something like this

我建议做这样的事情

ALTER TABLE newTable RENAME TO oldTable;
CREATE TABLE newTable (//Define your columns here along with the GUID column);

Then you can select all the values from oldTable and insert into newTable along with the GUID. Once you're done, use

然后,您可以从oldTable中选择所有值,并将其与GUID一起插入到newTable中。完成后,使用

DROP TABLE oldTable

#1