android - 在单个 execSQL 查询中可以指定的 SQL 变量的限制是多少

Question

我正在尝试提高我的 android 数据库插入速度。我目前正在做的是生成一个字符串，如:

SELECT ? as title, ? as musician_id, ? as album_id, ? as genre
UNION SELECT ?, ?, ?, ?
UNION SELECT ?, ?, ?, ?
UNION SELECT ?, ?, ?, ?
UNION SELECT ?, ?, ?, ?
UNION SELECT ?, ?, ?, ?

然后执行它

SQLiteDatabase database = //initialized in some way
String insertQuery; // the string of the query above
String [] parameters; // the parameters to use in the insertion.
database.execSQL(insertQuery.toString(), parameters);

当我尝试插入大约 2000 行时出现以下错误:

Caused by: android.database.sqlite.SQLiteException: too many SQL variables (code 1): , while compiling: INSERT INTO songs (title, musician_id, album_id, genre)
SELECT ? as title, ? as musician_id, ? as album_id, ? as genre
UNION SELECT ?, ?, ?, ?
UNION SELECT ?, ?, ?, ?

当我尝试插入大约 200 行时，一切正常。

我想这很明显 - 我试图在单个 execSQL 中传递太多变量。有谁知道限制是什么，以便我可以将插入的行拆分为适当的批处理？

Answer 1

限制在 sqlite3.c 中硬编码并设置为 999。不幸的是，它可以更改，但只能在编译时更改。以下是相关 fragment :

/*
** The maximum value of a ?nnn wildcard that the parser will accept.
*/
#ifndef SQLITE_MAX_VARIABLE_NUMBER
# define SQLITE_MAX_VARIABLE_NUMBER 999
#endif


/*
** The datatype ynVar is a signed integer, either 16-bit or 32-bit.
** Usually it is 16-bits.  But if SQLITE_MAX_VARIABLE_NUMBER is greater
** than 32767 we have to make it 32-bit.  16-bit is preferred because
** it uses less memory in the Expr object, which is a big memory user
** in systems with lots of prepared statements.  And few applications
** need more than about 10 or 20 variables.  But some extreme users want
** to have prepared statements with over 32767 variables, and for them
** the option is available (at compile-time).
*/
#if SQLITE_MAX_VARIABLE_NUMBER<=32767
typedef i16 ynVar;
#else
typedef int ynVar;
#endif