mysql - 如何使用Mysql查询分离不同行中的结果值

Question

我在我的sql数据库(测试)中创建了这个表(结果)

CREATE DATABASE `test`; 
USE `test`;
CREATE TABLE `results` (
`number` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`id_machine` int(10) unsigned NOT NULL,
`value` float NOT NULL,
`datetime` datetime NOT NULL,
PRIMARY KEY (`number`),
UNIQUE KEY `indice_UNIQUE` (`number`)
) ENGINE=InnoDB AUTO_INCREMENT=100; 

我的外部设备给我这些结果:

+DATA: 43 BYTES FROM 0000:0000 (045)
Machine_8: (T=22.22, HR=42.56, Dw=8.95, VCC=3.64V)

通过使用 strtok，我得到了这些结果的一些值，并将它们保存在数据库中:

Results: 8, 22.22, 42.56, 8.95, 3.64

我想以这种方式将数据保存在表中:

101, 8, 22.22, 2013-06-05 14:03:00
102, 8, 42.56, 2013-06-05 14:03:00
103, 8, 8.95, 2013-06-05 14:03:00
104, 8, 3.64, 2013-06-05 14:03:00

这是我迄今为止在函数中的代码

int learn_port2(int fd)
{
 MYSQL *conn;
 MYSQL_RES *res;
 MYSQL_RES *res1;
 MYSQL_ROW row;
 char *server = "127.0.0.1";
 char *user = "root";
 char *password = "***";  // got tot keep my data secret
 char *database = "test";

 conn = mysql_init(NULL);
 if(!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0))
{
    fprintf(stderr, "%s\n", mysql_error(conn));
    return -1;
    //finish_with_error(conn);
}
 int n, i;
 char buff[300];
 memset(buff, 0, sizeof(buff));
 for (int x = 0; x<1; x++)
 //for (;;)
  {
   char id_machine[35] = "";
   char temp[35] = "";
   char hum[35] = "";
   char dw[35] = "";
   char vol[45] = "";
   char* ptr;
   int i,nodo,nodo1;
   float temp, hum, dw, vcc;
   n=read(fd,buff,sizeof(buff));
   sleep(1);
   printf("%s", buff);
   printf("\n");

  if (buff[37] == 'N' || buff[38] == 'N' || buff[39] == 'N' || buff[40] == 'N' )
   {
    ptr = strtok(buff, "Machine_,=T:HR:DW:Vcc()");
    i = 0;

    while (ptr != NULL)
    {
        if (i == 9)
            strcat(id_machine, ptr); // copies Nodo
        if (i == 10)
            strcat(temp, ptr); // copies T
        if (i == 11)
            strcat(hum, ptr); // copies HR
        if (i == 13)
            strcat(dw, ptr); // copies DW
        if (i == 15)
            strcat(vol, ptr); // copies Vcc
        ptr = strtok(NULL, "Machine_,=T:HR:DW:Vcc()");
        i++;
    }

    printf("Results: %s, %s, %s, %s, %s\n", id_machine, temp, hum, dw, vol);
     }


char query[]="INSERT INTO results(`id_machine`,`value`,`valor`) VALUES(id_machine,'14',value)";


if(mysql_query(conn, query))

{
    fprintf(stderr, "%s\n", mysql_error(conn));
    return -1;
}

res = mysql_use_result(conn);
}
}  

如何修改 char query[] 以获得我想要的结果？或者是否有一些这样的例子。

Answer 1

要进行参数化查询，您可以使用类似以下内容的内容...

该示例使用 vsnprintf()接受类似 printf 的格式字符串和后面可变数量的参数，将查询打印到临时缓冲区中，然后将缓冲区传递给 MYSQL 查询。通过这种方式，您可以获得参数化查询...

vsnprintf() 接受可变参数列表 va_list并且缓冲区溢出也是安全的。

#include <stdio.h>
#include <stdarg.h>
#include <string.h>

#define BUFSTRLEN 255

/* Returns -1 if the internal buffer was not large enough to hold the formatted
 * string, or a format error occurred. If neither condition occurred then the
 * result of the MYSQL query is returned...  */
int DoMysqlQuery(MYSQL *conn, char const *printfstring, ...)
{
  int len;
  va_list args;
  char buffer[BUFSTRLEN + 1];
  memset(buffer, '\0', sizeof(buffer));

  va_start(args, printfstring);
  len = vsnprintf(buffer, BUFSTRLEN, printfstring, args);
  va_end(args);

  /* Did the buffer print work ? */
  if( len < 0 || len >= BUFSTRLEN + 1 )
      return -1;

  return mysql_query(conn, buffer);
}


int main(int argc, char const *argv[])
{
    int result;
    MYSQL mysqlConn = ...;

    /* Example stuff to send to the function.... */
    char *id_machine= "8";
    char *value= "1234";
    char *valor= "1";

    /* Send the query */
    result = DoMysqlQuery(
        &mysqlConn, 
        "INSERT INTO results(`id_machine`,`value`,`valor`) VALUES(%s,'%s',%s);",
        id_machine, value, valor);

    if( result )
    {
        /* handle error etc... *.
    }
}

在此示例中，将发送到 mysql_query() 的查询字符串是:

INSERT INTO results(id_machine,value,valor) VALUES(8,'1234',1);

希望这有帮助:)