在单个execSQL查询中可以指定的SQLvariables的限制是多less?

我正在尝试提高我的android数据库插入的速度。 我目前正在做的是产生一个string,如:

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传递太多的variables。 有谁知道什么是限制,以便我可以拆分我插入适当的批次行?

Solutions Collecting From Web of "在单个execSQL查询中可以指定的SQLvariables的限制是多less?"

该限制在sqlite3.c中被硬编码,并被设置为999.不幸的是,它可以改变,但只能在编译时。 这里是相关的片段:

 /* ** 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 

我正在尝试提高我的android数据库插入的速度 。 我目前正在做的是产生一个string,如:

你有没有想过使用TRANSACTION ? 我build议你用它来代替你的方法。 我认为使用UNION子句根本不是一个“胜利”,如何实现它,有更好更安全的方法。

 db.beginTransaction(); try { for (int i = 0 ; i < length ; i++ ) { // or another kind of loop etc. // make insert actions } db.setTransactionSuccessful(); // now commit changes } finally { db.endTransaction(); }