mysql快速插入/更新大量记录

最近的项目中需要将几个dump文件（文本格式、1~2G）的记录导入到mysql数据库中，由于数据量比较大（几百万、上千万条记录），有插入记录，也有更新记录的，导致插入\更新速度比较慢。

一开始，将单条sql语句交给mysql执行，测试下来，最快一次也要一个半小时。于是想办法改进之。

 

（1）针对插入记录，使用sql语句一次插入多条记录。实例：

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

具体参见sql语法：

http://dev.mysql.com/doc/refman/5.0/en/insert.html

 

在程序中，使用循环拼接sql语句，然后一次性丢给myql_query()处理即可。

需要注意的是，sql语句不是越长越好，太长了可能会超出mysql限制（听同事介绍，mysql的限制是63M）。

我在实测中，我限制sql长度在10M:

for(...)
{
    m_strSQL += ...;

    if (m_strSQL.length() > 10485760) //10M
    {
         m_pMysqlStatement->execute(m_strSQL.substr(0, m_strSQL.length() - 1));
         m_strSQL= "INSERT INTO location (locationid, name, alternatename) VALUES ";
    }

}

测试结果为：

8267787条记录，耗时754秒（数据库在局域网内的不同机器上），基本满足要求。

 

（2）对于更新记录，由于update语法不支持一次更新多条记录，无法使用类似“INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);”的形式进行更新，只能拼接多条sql语句一起更新：

update location set languages = 'zh' where locationid = '12344';update location set postalcode = '14343' where locationid = '3455';

但是这样一来，问题出现了:

（I）在连接数据库时需要启用multi-query支持，允许一次执行多条sql语句。参见：http://www.cnblogs.com/chutianyao/archive/2011/11/07/2239464.html

（II）还需要处理resultset的释放问题，否则mysql会报错："Commands out of sync; you can't run this command now"

（III）针对update语句，虽然并没有resultset返回，但仍然需要释放。而由于未知原因（可能是sql语句太长？），释放resultset非常耗时，最终算下来得不偿失。

 

综合以上情况，同事提出了另一种解决方法：针对更新记录，仍然使用insert语句，不过限制主键重复时，更新字段。如下：

INSERT INTO location (locationid, languages) VALUES('13243', 'zh'),VALUES('13244', 'en') ON DUPLICATE KEY UPDATE languages=VALUES(languages)

但是这样又引出了另外一个问题：在一条sql语句中只能更新某些指定的字段，而程序中可能会有不同的更新条件。比如：上一条记录只更新languages字段，而下一条记录则只更新postalcode字段，这样是没办法蟹岛一条sql语句中的。

解决办法是：针对这两个不同的字段，使用不同的sql语句，保证每个sql语句只更新某个字段：

for(...)
{
    m_strSQL_Language +=...
    m_strSQL_Postcode +=...
    if (m_strSQL_Language.length() > 10485760) //10M
    {
        m_strSQL_Language.erase(m_strSQL_Language.length() - 1);
        m_strSQL_Language += " ON DUPLICATE KEY UPDATE languages=VALUES(languages)";
        m_pMysqlStatement->execute(m_strSQL_Language);

        m_strSQL_Language = "INSERT INTO location (locationid, languages) VALUES ";
    }

    if (m_strSQL_Postcode.length() > 10485760) //10M
    {
        m_strSQL_Postcode.erase(m_strSQL_Alternate_Postcode.length() - 1);
        m_strSQL_Postcode += " ON DUPLICATE KEY UPDATE postalcode=VALUES(postalcode)";
        m_pMysqlStatement->execute(m_strSQL_Postcode);

        m_strSQL_Postcode = "INSERT INTO location (locationid, postalcode) VALUES ";
    }
}

问题得到了完美解决。