如何用C语言将树莓派2采集的温湿度数据存入MySQL数据库？

Hey there! I’ve debugged tons of similar issues where sensor data shows up on screen but won’t save to MySQL—let’s work through this step by step to get your data stored properly.

1. First, Verify Your MySQL Connection is Actually Working #

Most of the time, the problem starts here: your code might be failing to connect to the database, but you’re not checking for errors. Add explicit error checking right after your connection call to catch this:

MYSQL *conn = mysql_init(NULL);
if (!mysql_real_connect(conn, "localhost", "your_username", "your_password", "your_database", 0, NULL, 0)) {
    fprintf(stderr, "Connection failed: %s\n", mysql_error(conn));
    mysql_close(conn);
    return 1;
}

Run your code again—if you see a connection error message, that’s your first fix (check credentials, database name, or MySQL service status).

2. Test Database Permissions Manually #

Your Raspberry Pi user (usually pi) might not have permission to insert data into the target table. Open a terminal and run:

mysql -u your_username -p your_database

Then try running the exact INSERT statement your code uses, like:

INSERT INTO sensor_data (temperature, humidity, reading_time) VALUES (22.5, 60.2, NOW());

If this fails, you’ll get a clear error (e.g., "table doesn’t exist", "permission denied"). Fix the permissions or table structure before going back to your code.

3. Check for SQL Syntax or Data Type Mismatches #

Even if your query looks right, small mistakes can break inserts:

• Did you forget quotes around string values? (Unlikely for temp/humidity, but if you’re adding labels or timestamps as strings, this matters.)
• Are your sensor data types matching the table columns? For example, if your temperature column is INT, but you’re inserting a float like 22.5, MySQL might truncate it or reject the insert.
• Print the final SQL query from your code before executing it, then run it manually in MySQL to confirm it works:

char insert_query[256];
snprintf(insert_query, sizeof(insert_query), 
         "INSERT INTO sensor_data (temperature, humidity) VALUES (%.2f, %.2f)", temp, humidity);
printf("Executing query: %s\n", insert_query); // Print this to debug
if (mysql_query(conn, insert_query) != 0) {
    fprintf(stderr, "Insert failed: %s\n", mysql_error(conn));
}

4. Make Sure You’re Linking the MySQL Library During Compilation #

C code needs to link against the MySQL client library to talk to the database. If your compile command doesn’t include -lmysqlclient, the code might compile but fail silently at runtime. Use a command like:

gcc your_sensor_code.c -o sensor_reader -lmysqlclient

If you get "undefined reference" errors during compilation, install the dev package first:

sudo apt-get install libmysqlclient-dev

5. Add Error Checking for Every Database Operation #

Don’t assume mysql_query succeeds! Always check its return value and print the error message, like the example in step 3. This will tell you exactly why the insert is failing—whether it’s a syntax error, missing column, or permission issue.

6. Double-Check MySQL Service Status #

Sometimes MySQL just isn’t running on your Pi. Verify with:

sudo systemctl status mysql

If it’s inactive, start it with:

sudo systemctl start mysql

And set it to run on boot:

sudo systemctl enable mysql

Quick Working Example Snippet #

Here’s a stripped-down version of how the database insert part should look with proper error handling:

#include <mysql/mysql.h>
#include <stdio.h>

int main() {
    float temp = 22.3; // Replace with your sensor reading
    float humidity = 58.1;

    MYSQL *conn = mysql_init(NULL);
    if (!mysql_real_connect(conn, "localhost", "pi", "your_password", "sensor_db", 0, NULL, 0)) {
        fprintf(stderr, "Connection error: %s\n", mysql_error(conn));
        mysql_close(conn);
        return 1;
    }

    char query[256];
    snprintf(query, sizeof(query), 
             "INSERT INTO readings (temperature, humidity) VALUES (%.2f, %.2f)", temp, humidity);
    
    if (mysql_query(conn, query) != 0) {
        fprintf(stderr, "Insert error: %s\n", mysql_error(conn));
    } else {
        printf("Data inserted successfully!\n");
    }

    mysql_close(conn);
    return 0;
}

Start with checking connection errors and manual query testing—those are the most common culprits. If you hit a specific error message, we can dig deeper!

内容的提问来源于stack exchange，提问作者Vinh