mysql - 如何提高qt中mysql数据库的写入速度

Question

我正在使用 Qt 和 MySQL 将传感器数据存储到 MyMySQL 数据库。我每秒接收 100 个值，在接收接下来的 100 个样本之前，我必须将其写入 MySQL。

Qt 和 MySQL 数据库之间的通信工作正常，但写入单个值需要 12 到 20 毫秒(大约 14 毫秒)。因此写入 100 个值需要 1 到 2 秒。

但在此数据库更新结束之前我将收到接下来的 100 个值。如何降低值的写入速度或如何加快 Qt 和 MySQL 之间的通信。我使用循环逐个发送每个值的查询，是否可以一次发送 100 个样本？

谢谢你的回答。这是我的代码。如何使用这个bindvalue(xx)一次更新多个数据。

void Worker::init()
{
QSqlDatabase db1 = QSqlDatabase::addDatabase("QMYSQL");
db1.setHostName("localhost");
db1.setDatabaseName("test");
db1.setUserName("root");
db1.setPassword("CDAC");
if (!db1.open())
{

    qDebug()<<"Database is not open ";
}


CreateTable(1);CreateTable(2);CreateTable(3);CreateTable(4);
}

void Worker::CreateTable(unsigned char TableNo)
{
QSqlQuery query;
QString name1 = "LoadSensorData1";
QString name2 = "LoadSensorData2";
QString name3 = "LoadSensorData3";
QString name4 = "LoadSensorData4";
    switch(TableNo)
{
 case 1:
    qDebug() << "Database 1 entered ";

    query.exec( "CREATE TABLE " + name1 + "(id1 int primary key,"
                " Sensor1 varchar(20), TimeStamp1 varchar(20))");

    query.prepare("INSERT INTO " + name1 + "(id1, Sensor1, TimeStamp1) "
                      "VALUES (:id1, :Sensor1,:TimeStamp1)");
    qDebug() << "Database 1 created ";
      break;
case 2:
    qDebug() << "Database 2 entered ";
    query.exec("CREATE TABLE " + name2 + "(id2 int primary key, "
           "Sensor2 varchar(20), TimeStamp2 varchar(20))");

    query.prepare("INSERT INTO " + name2 + "(id2, Sensor2, TimeStamp2) "
                  "VALUES (:id2, :Sensor2,:TimeStamp2)");
    qDebug() << "Database 2 created ";

break;
case 3:
    qDebug() << "Database 3 entered ";
    query.exec("CREATE TABLE " + name3 + "(id3 int primary key, "
           "Sensor3 varchar(20), TimeStamp3 varchar(20))");

    query.prepare("INSERT INTO " + name3 + "(id3, Sensor3, TimeStamp3) "
                  "VALUES (:id3, :Sensor3,:TimeStamp3)");
    qDebug() << "Database 3 created ";
       break;
case 4:
    qDebug() << "Database 4 entered ";
    query.exec("CREATE TABLE " + name4 + "(id4 int primary key, "
               "Sensor4 varchar(20), TimeStamp4 varchar(20))");

    query.prepare("INSERT INTO " + name4 + "(id4, Sensor4, TimeStamp4) "
                  "VALUES (:id4, :Sensor4,:TimeStamp4)");
    qDebug() << "Database 4 created ";
     break;
}

}

void Worker::WriteData(unsigned int id,unsigned int  data,QString            Time,unsigned char TableNo)
{
QString name1 = "LoadSensorData1";
QString name2 = "LoadSensorData2";
QString name3 = "LoadSensorData3";
QString name4 = "LoadSensorData4";
// QString dts = "create table "+name;
//QString dts1 = "insert into "+name;
QSqlQuery query;
switch(TableNo)
{
 case 1:
   query.prepare("INSERT INTO " + name1 + "(id1, Sensor1, TimeStamp1) "
                      "VALUES (:id1, :Sensor1, :TimeStamp1)");

    query.addBindValue(id);
    query.addBindValue(data);
    query.addBindValue(Time);
    query.exec();
break;
case 2:

    query.prepare("INSERT INTO " + name2 + "(id2, Sensor2, TimeStamp2) "
                     "VALUES (:id2, :Sensor2, :TimeStamp2)");
query.bindValue(":id2", id);
query.bindValue(":Sensor2",data);
query.bindValue(":TimeStamp2", Time);
  query.exec();
       break;
case 3:

   query.prepare("INSERT INTO " + name3 + "(id3, Sensor3, TimeStamp3) "
                     "VALUES (:id3, :Sensor3, :TimeStamp3)");
   query.bindValue(":id3", id);
query.bindValue(":Sensor3", data);
query.bindValue(":TimeStamp3", Time);
    query.exec();
break;
case 4:

   query.prepare("INSERT INTO " + name4 + "(id4, Sensor4, TimeStamp4) "
                     "VALUES (:id4, :Sensor4, :TimeStamp4)");
   query.bindValue(":id4", id);
query.bindValue(":Sensor4", data);
query.bindValue(":TimeStamp4", Time);
  query.exec();

break;

}
}


// slot function to update database once I receive full set of data  //

void Worker::UpdateDatabase()
{

if(Data1ReceivedFlag==1)
{
    Data1ReceivedFlag=0;
   // CreateTable(1);
    for (int i=1;i<=1000;i++){datBseId1++;WriteData(datBseId1,str1[i],TimeStr1[i],1);}
    qDebug() << "Database 1 updated ";
}
if(Data2ReceivedFlag==1)
{
    Data2ReceivedFlag=0;
   // CreateTable(2);
    for (int i1=1;i1<=1000;i1++){datBseId2++;WriteData(datBseId2,str2[i1],TimeStr2[i1],2);}
    qDebug() << "Database 2 updated ";
}
if(Data3ReceivedFlag==1)
{
    Data3ReceivedFlag=0;
   // CreateTable(3);
    for (int i2=1;i2<=1000;i2++){datBseId3++;WriteData(datBseId3,str3[i2],TimeStr3[i2],3);}
    qDebug() << "Database 3 updated ";
}
if(Data4ReceivedFlag==1)
{
    Data4ReceivedFlag=0;
   // CreateTable(4);
    for (int i3=1;i3<=1000;i3++){datBseId4++;WriteData(datBseId4,str4[i3],TimeStr4[i3],4);}
    qDebug() << "Database 4 updated ";
}
}

Answer 1

使用类似这样的东西(有 4 个样本而不是 100 个):

INSERT INTO table (a,b,c) VALUES (1,2,3),(1,2,3),(1,2,3),(1,2,3)

如果每个样本有多个(三个)值或

INSERT INTO table (a) VALUES (1),(1),(1),(1)

如果每个样本只有一个值。