WebSQL 插入数据有上限

前言

      Web SQL 数据库可以在最新版的 Safari, Chrome 和 Opera 浏览器中工作,混合开发中用来存储大量数据也非常高效和方便(Web SQL操作指南 摸我)。

复盘

        项目需求一次要导入大量数据到应用程序中，数据的插入过程也很简单，如果插入数据量少于500条跑起来是能够正常运行的，但超过500条时部分手机就会出错(部分手机一次插入超过500条虽然也能运行，但效率不高)，代码片段如下：

let sql = 'insert into U_TABLE_NAME( '
        + 'per_id,org_id'
    + ' ) values ';
let val = ''
data.forEach(element => {
    val += "("
        + "'" + (element.per_id?element.per_id:"") + "'" + ","
        + "'" + (element.org_id?element.org_id:"") + "'"
    + "),"
});
sql = sql + val.substr(0,val.length-1)
    
this.db.transaction(tx => {
    tx.executeSql(sql,
    [],
    (tx,result) => {      
        success()
    },(tx,err) => {
        this.$loading.close()
        console.log('U_TABLE_NAME:' + err.message);
        this.$toast('数据导入异常')
    })
})

异常日志如下见:

D/SystemWebChromeClient: file:///android_asset/www/static/js/1.cdd1f9cf85ab9df9c70c.js: Line 1 : U_TABLE_NAME:could not prepare statement (1 too many terms in compound SELECT)
I/chromium: [INFO:CONSOLE(1)] "U_TABLE_NAME:could not prepare statement (1 too many terms in compound SELECT)", source: file:///android_asset/www/static/js/1.cdd1f9cf85ab9df9c70c.js (1)

解决方案

分批次进行插入即可：

for(var i=0;i<data.length;i+=400){
    let sql = 'insert into U_TABLE_NAME( '
        + 'per_id,org_id'
    + ' ) values ';
    let val = ''
    data.slice(i,i+400).forEach(element => {
        val += "("
            + "'" + (element.per_id?element.per_id:"") + "'" + ","
            + "'" + (element.org_id?element.org_id:"") + "'"
        + "),"
    });

    sql = sql + val.substr(0,val.length-1)
    
    this.db.transaction(tx => {
        tx.executeSql(sql,
        [],
        (tx,result) => {      
            success()
        },(tx,err) => {
            this.$loading.close()
            console.log('U_TABLE_NAME:' + err.message);
            this.$toast('数据导入异常')
        })
    })
}