How to insert 1 000 000 rows in MySQL Server 5.5 quickly with Qt
-
I decided to write an algorithm that would be quickly inserted into the base of several hundred million rows in MySQL
While stopped in to check how many of the same row is inserted into a cycle:
@
db = QSqlDatabase::addDatabase("QMYSQL","myFirstConnection"); db.setHostName("localhost"); db.setDatabaseName("vova"); db.setUserName("root"); db.setPassword("root"); qDebug() << db.open();
qDebug()<<QTime::currentTime();
if (query.prepare("INSERT INTO mainpackage (phoneNumberA,phoneNumberB,phoneNumberC,phoneNumberD,phoneNumberE)"
"VALUES (?,?,?,?,?)"))
{
for (int i=0; i<1000000; i++)
{
query.addBindValue(QString("1234567891011"));
query.addBindValue(QString("1234567891012"));
query.addBindValue(QString("1234567891013"));
query.addBindValue(QString("1234567891014"));
query.addBindValue(QString("1234567891015"));}
qDebug() << query.exec() << "blablabla";
qDebug() << query.lastError().databaseText();
qDebug()<<QTime::currentTime();
}Instead (?,?,?,?,?) be substituted after a string 1234567891011, etc.
but when the error falls out: "No data supplied for parameters in prepared statement"
how to overcome this problem?
How to apply for high performance query can iterate through 1000 to complete the request (exec)?
@ -
You do not execute your query one million times, you bind the values one million times and execute your query just once. Besides that your example works fine for me.
Be aware that you will have to prepare your database before inserting such a large amount of data (setting up apropriate caching, disable indexing and so on).