mysql - 使用 MySQL Connector/Arduino 获取 select 中的 varchar 值

Question

我正在建立一个自动称重系统以进行质量控制。为此，我使用 MySQL Connector/Arduino图书馆。

一切准备就绪，只剩下以下步骤了:

1. 查询数据库，该数据库是我要评估的批处理号(批处理号以 VARCHAR 形式存储在 qcInsecta_get 数据表中)。

2. 将上面获得的值与称重传感器返回给我的重量值一起包含在 INSERT 中。

我使用过 basic_insert 的示例和 basic_select由该库的作者提供。在数据表中插入值我没有遇到任何问题(我之前用探针值完成过)。但是，当我执行 SELECT 操作时，如果数据库字段是数字(例如 INT)，则检索到的值始终是数字值。但如果数据库字段是VARCHAR，则返回0。

我不是 C 代码专家，但我已经看到它包含在示例中:

head_count = atol(row->values[0]);

并且 atol 将字符串转换为其数值。我尝试过使用其他形式或转换器来进行选择并从数据库中获取值，但这对我来说是不可能的。我该如何解决这个问题？

使用 NODEMCU V3 板执行 SELECT 的代码是:

#include <ESP8266WiFi.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

char ssid[] = ""; // SSID NAME
char pass[] = ""; // SSID PASSWORD

IPAddress server_addr(xxx, xxx, x, xxx);
char user[] = "";
char password[] = "";
char query[] = "SELECT batchnumber FROM registro.qcInsecta_get ORDER BY id DESC LIMIT 1";
WiFiClient client;
MySQL_Connection conn((Client *)&client);

void setup() {
  delay(500);
  Serial.begin(115200);
  WiFi.begin(ssid, pass);
  if (conn.connect(server_addr, 3306, user, password)) {
    delay(100);
    Serial.println(F("Connected"));    
  }
  else {
      Serial.println();
      conn.close();  
      if (conn.connect(server_addr, 3306, user, password)) {
      delay(500);
      } 
      else {
        Serial.println("...");
      }
  }
}

void loop() {
  delay(1000);
  row_values *row = NULL;
  long head_count = 0;

  Serial.println("1) Demonstrating using a cursor dynamically allocated.");
  // Initiate the query class instance
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  // Execute the query
  cur_mem->execute(query);
  // Fetch the columns (required) but we don't use them.
  column_names *columns = cur_mem->get_columns(); 

  // Read the row (we are only expecting the one)
  do {
    row = cur_mem->get_next_row();
    if (row != NULL) {
      head_count = atol(row->values[0]);
    }
  } while (row != NULL);
  // Deleting the cursor also frees up memory used
  delete cur_mem;

  // Show the result
  Serial.print("  Batch number = ");
  Serial.println(head_count);

  delay(500);
} 

关于数据库:

| qcInsecta | CREATE TABLE `qcInsecta` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `batchnumber` varchar(30) NOT NULL,
  `weight` float NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `batchnumber` (`batchnumber`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb4 |

MariaDB [registro]> SELECT * FROM qcInsecta;
+----+-------------+--------+---------------------+
| id | batchnumber | weight | created_at          |
+----+-------------+--------+---------------------+
|  5 | COL-01-05   |     35 | 2019-06-06 17:04:38 |
| 16 | COL-01-01   |     22 | 2019-06-06 18:57:02 |
| 17 | COL-01-01   |     22 | 2019-06-06 18:57:02 |
| 18 | COL-01-01   |     25 | 2019-06-06 21:52:02 |

Answer 1

使用head_count = atol(row->values[0]);，您可以将输出的每个字符转换为一个长整型数字值。因此，您应该仅对 NUMERIC 字段执行此操作。对于字符串字段，您只需要 row->values，它是一个 C 字符串数组。

基于this row->values 包含一个数组，其大小为 SELECT 列结果。例如，如果您选择一列(如当前的选择查询)，row->values 的大小将为 1，并且您的结果将位于 row->values[0] 中>.

如果您需要分隔每一列，请尝试为每一列进行选择，或者您可以按照 complex_select.ino示例并迭代列而不是行，然后分离每个列类型并将它们转换为所需的值。

#include <ESP8266WiFi.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

char ssid[] = ""; // SSID NAME
char pass[] = ""; // SSID PASSWORD

IPAddress server_addr(xxx, xxx, x, xxx);
char user[] = "";
char password[] = "";
char query[] = "SELECT batchnumber FROM registro.qcInsecta_get ORDER BY id DESC LIMIT 1";
WiFiClient client;
MySQL_Connection conn((Client *)&client);

void setup()
{
    delay(500);
    Serial.begin(115200);
    WiFi.begin(ssid, pass);
    if (conn.connect(server_addr, 3306, user, password))
    {
        delay(100);
        Serial.println(F("Connected"));
    }
    else
    {
        Serial.println();
        conn.close();
        if (conn.connect(server_addr, 3306, user, password))
        {
            delay(500);
        }
        else
        {
            Serial.println("...");
        }
    }
}

void loop()
{
    delay(1000);

    long head_count = 0;

    Serial.println("1) Demonstrating using a cursor dynamically allocated.");
    // Initiate the query class instance
    MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
    // Execute the query
    cur_mem->execute(query);
    // Fetch the columns (required) but we don't use them.
    Serial.println("Fetching with Columns");
    column_names *columns = cur_mem->get_columns();

    for (int f = 0; f < columns->num_fields; f++)
    {
        Serial.print(columns->fields[f]->name);
        if (f < columns->num_fields - 1)
        {
            Serial.print(',');
        }
    }
    Serial.println("Done");

    // Read the rows and print them
    Serial.println("Fetching with Rows");
    row_values *row = NULL;
    do
    {
        row = cur_mem->get_next_row();
        if (row != NULL)
        {
            //e.g convert to a float value
            // float batchnumber = 0;
            //   batchnumber = atol(row->values[0]);
            //   Serial.print("float value: ");
            //   Serial.println(batchnumber,2);
            String batchnumber_str = "";
            for (int f = 0; f < columns->num_fields; f++)
            {
                //just print the String value
                // Serial.print(row->values[f]);
                // convert value to String
                batchnumber_str = String(row->values[f]);
                Serial.print("batchnumber_str: ");
                Serial.println(batchnumber_str);
                if (f < columns->num_fields - 1)
                {
                    Serial.print(',');
                }
            }
            Serial.println();
        }
    } while (row != NULL);
    Serial.println("Done");
    // Deleting the cursor also frees up memory used
    delete cur_mem;

    // Show the result
    Serial.print("  Batch number = ");
    Serial.println(head_count);

    delay(500);
}