tencent cloud

TDSQL Boundless

DokumentasiTDSQL BoundlessDeveloper GuideDeveloper Guide (MySQL Compatibility Mode)Sample ProgramC

C

PDF
Mode fokus
Ukuran font
Terakhir diperbarui: 2026-04-03 16:07:29
MySQL Connector/C, commonly referred to as the MySQL C API, provides a set of C language functions and data structures that enable developers to connect to, query, and manage databases using C/C++. This API allows developers to work directly with MySQL protocol-compliant databases within their C/C++ applications.
This article describes how to build an application using the MySQL Connector/C (libmysqlclient) driver and TDSQL Boundless database to perform basic operations such as creating tables, inserting data, and querying data.

Prerequisites

Before MySQL Connector/C (libmysqlclient) is installed and used, ensure that a basic database development environment is set up with the following requirements:
GCC version 3.4.6 and above, with version 4.8.5 recommended.
CMake version 2.8.12 and above.
You have deployed the TDSQL Boundless database and created the corresponding database instance.

Steps

1. Obtain TDSQL Boundless database connection information.
2. Install the MySQL Connector/C driver.
3. Write the application.
4. Compile the application.
5. Run the application.

Step 1: Obtain the Database Connection String

mysql -h$host -P$port -u$user_name -p$password -D$database_name
Parameter description:
Parameter
Description
$host
TDSQL Boundless database connection IP address or domain name and port (default is 3306).
OLTP Access Address: To obtain the peer node VIP address and port number, log in to the console, navigate to the Instance Details page, and locate the Basic Info section. The peer node VIP can be found in the Private Network Address field, and the peer node Port is available in the Private Port field.
$port
$user_name
Database connection account username and password. For details, see Creating an Account.
$password
$database_name
The name of the database to be accessed.
Note:
The user connecting to the database must have the CREATE, INSERT, DROP, and SELECT privileges on the database.
Example:
mysql -h192.168.1.100 -P3306 -utest_user -p****** -Dtest_db

Step 2: Install the MySQL Connector/C Driver

Select the appropriate installation command based on your operating system:
## On Ubuntu/Debian systems, please use the following code for installation:
sudo apt-get update
sudo apt-get install libmysqlclient-dev

## On CentOS/RHEL systems, please use the following code for installation:
sudo yum install mysql-devel

## Verify whether the installation was successful
mysql_config --version
If the installation is successful, the mysql_config --version command will output the version number of the installed MySQL client library.

Step 3: Develop the Application

Open your text editor, edit the example test.c file in the text editor and save it. The code is as follows:
#include <stdio.h>
#include <stdlib.h>
#include <mysql/mysql.h>

int main() {
    MYSQL *conn = mysql_init(NULL); // Initialize the MySQL connection

    if (conn == NULL) {
        fprintf(stderr, "mysql_init() failed\\n");
        return 1;
    }

    // Connect to the TDSQL Boundless database server
    // Replace the following parameters with actual connection information
    if (mysql_real_connect(conn, "host", "user", "passwd", "db", 3306, NULL, 0) == NULL) {
        fprintf(stderr, "mysql_real_connect() failed: %s\\n", mysql_error(conn));
        mysql_close(conn);
        return 1;
    }

    printf("Successfully connected to the TDSQL Boundless database!\\n");

    // Set the character set to utf8mb4
    if (mysql_set_character_set(conn, "utf8mb4") != 0) {
        fprintf(stderr, "Failed to set character set: %s\\n", mysql_error(conn));
    }

    // Create a user table
    if (mysql_query(conn, "CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT, name VARCHAR(255), email VARCHAR(255), PRIMARY KEY(id))") != 0) {
        fprintf(stderr, "Failed to create table: %s\\n", mysql_error(conn));
        mysql_close(conn);
        return 1;
    }
    printf("Table created successfully!\\n");

    // Insert data.
    if (mysql_query(conn, "INSERT INTO users (name, email) VALUES ('Xiaoming', 'xiaoming@example.com')") != 0) {
        fprintf(stderr, "Failed to insert data: %s\\n", mysql_error(conn));
        mysql_close(conn);
        return 1;
    }
    printf("Data inserted successfully!\\n");

    // Query data.
    if (mysql_query(conn, "SELECT * FROM users") == 0) {
        MYSQL_RES *result = mysql_store_result(conn);
        if (result != NULL) {
            int num_fields = mysql_num_fields(result);
            MYSQL_ROW row;
            
            printf("\\nQuery results:\\n");
            printf("----------------------------------------\\n");
            
            while ((row = mysql_fetch_row(result))) {
                for (int i = 0; i < num_fields; i++) {
                    printf("%s ", row[i] ? row[i] : "NULL");
                }
                printf("\\n");
            }
            
            printf("----------------------------------------\\n");
            printf("Total %lu records\\n", (unsigned long)mysql_num_rows(result));
            
            mysql_free_result(result);
        } else {
            fprintf(stderr, "Failed to obtain result set: %s\\n", mysql_error(conn));
        }
    } else {
        fprintf(stderr, "Failed to query data: %s\\n", mysql_error(conn));
    }

    mysql_close(conn); // Close the connection
    printf("Database connection closed.\\n");
    
    return 0;
}
According to the information in Step 1: Obtain database connection information, modify the database connection information in the project file test.c.
In a Linux environment, you can use the vi test.c or vim test.c command to edit the test.c file and modify the database connection information to ensure it matches the actual situation.
test.c The database connection information in the file is as follows:
// This connection information needs to be modified to the actual connection string information you obtained, as shown in the following example
if (mysql_real_connect(conn, "192.168.1.100", "test_user", "your_password", "test_db", 3306, NULL, 0) == NULL) {
    fprintf(stderr, "mysql_real_connect() failed: %s\\n", mysql_error(conn));
    mysql_close(conn);
    return 1;
}

Step 4: Compile the Application

After the code is edited, it can be compiled using the following command.
gcc -o test test.c `mysql_config --cflags --libs`
Alternatively, manually specify the header file and library paths:
gcc -o test test.c -I/usr/include/mysql -L/usr/lib/mysql -lmysqlclient
After successful compilation, an executable file test will be generated.

Step 5: Run the Application

Run the application using the following command.
./test
The output is as follows, indicating that the database connection is successful and the example statement is executed correctly:
Successfully connected to the TDSQL Boundless database!
Table created successfully!
Data inserted successfully!

Query results:
----------------------------------------
1 Xiaoming xiaoming@example.com
----------------------------------------
There is 1 record.

Test table cleaned.
Database connection closed.

More Examples

Example 1: Insert Data Using Prepared Statement

Prepared statements can effectively prevent SQL injection and improve the performance of repeatedly executing the same SQL statements.
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <mysql/mysql.h>

int main() {
    MYSQL *conn = mysql_init(NULL);
    MYSQL_STMT *stmt;
    MYSQL_BIND bind[2];
    
    char name[64] = "Zhang San";
    char email[128] = "zhangsan@example.com";
    unsigned long name_length;
    unsigned long email_length;

    if (conn == NULL) {
        fprintf(stderr, "mysql_init() failed\\n");
        return 1;
    }

    // Connect to the TDSQL Boundless database
    if (mysql_real_connect(conn, "host", "user", "passwd", "db", 3306, NULL, 0) == NULL) {
        fprintf(stderr, "mysql_real_connect() failed: %s\\n", mysql_error(conn));
        mysql_close(conn);
        return 1;
    }

    // Set the character set
    mysql_set_character_set(conn, "utf8mb4");

    // Ensure the table exists
    mysql_query(conn, "CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT, name VARCHAR(255), email VARCHAR(255), PRIMARY KEY(id))");

    // Initialize the prepared statement
    stmt = mysql_stmt_init(conn);
    if (stmt == NULL) {
        fprintf(stderr, "mysql_stmt_init() failed\\n");
        mysql_close(conn);
        return 1;
    }

    // Prepare the SQL statement
    const char *sql = "INSERT INTO users (name, email) VALUES (?, ?)";
    if (mysql_stmt_prepare(stmt, sql, strlen(sql))) {
        fprintf(stderr, "mysql_stmt_prepare() failed: %s\\n", mysql_stmt_error(stmt));
        mysql_stmt_close(stmt);
        mysql_close(conn);
        return 1;
    }

    // Bind parameters
    memset(bind, 0, sizeof(bind));

    // Parameter 1: name
    name_length = strlen(name);
    bind[0].buffer_type = MYSQL_TYPE_STRING;
    bind[0].buffer = name;
    bind[0].buffer_length = sizeof(name);
    bind[0].length = &name_length;

    // Parameter 2: email
    email_length = strlen(email);
    bind[1].buffer_type = MYSQL_TYPE_STRING;
    bind[1].buffer = email;
    bind[1].buffer_length = sizeof(email);
    bind[1].length = &email_length;

    if (mysql_stmt_bind_param(stmt, bind)) {
        fprintf(stderr, "mysql_stmt_bind_param() failed: %s\\n", mysql_stmt_error(stmt));
        mysql_stmt_close(stmt);
        mysql_close(conn);
        return 1;
    }

    // Execute the prepared statement
    if (mysql_stmt_execute(stmt)) {
        fprintf(stderr, "mysql_stmt_execute() failed: %s\\n", mysql_stmt_error(stmt));
        mysql_stmt_close(stmt);
        mysql_close(conn);
        return 1;
    }

    printf("Prepared statement executed successfully! Insert ID: %lu\\n", (unsigned long)mysql_stmt_insert_id(stmt));

    mysql_stmt_close(stmt);
    mysql_close(conn);
    
    return 0;
}

Example 2: Transaction Processing

A transaction ensures that a set of database operations either all succeed or all fail.
#include <stdio.h>
#include <stdlib.h>
#include <mysql/mysql.h>

int main() {
    MYSQL *conn = mysql_init(NULL);
    int success = 1;

    if (conn == NULL) {
        fprintf(stderr, "mysql_init() failed\\n");
        return 1;
    }

    // Connect to the TDSQL Boundless database
    if (mysql_real_connect(conn, "host", "user", "passwd", "db", 3306, NULL, 0) == NULL) {
        fprintf(stderr, "mysql_real_connect() failed: %s\\n", mysql_error(conn));
        mysql_close(conn);
        return 1;
    }

    mysql_set_character_set(conn, "utf8mb4");

    // Disable auto-commit, begin transaction.
    if (mysql_autocommit(conn, 0)) {
        fprintf(stderr, "Failed to disable auto-commit: %s\\n", mysql_error(conn));
        mysql_close(conn);
        return 1;
    }

    printf("Begin transaction...\\n");

    // Execute the first SQL statement
    if (mysql_query(conn, "UPDATE accounts SET balance = balance - 100 WHERE user_id = 1")) {
        fprintf(stderr, "SQL 1 execution failed: %s\\n", mysql_error(conn));
        success = 0;
    }

    // Execute the second SQL statement
    if (success && mysql_query(conn, "UPDATE accounts SET balance = balance + 100 WHERE user_id = 2")) {
        fprintf(stderr, "SQL 2 execution failed: %s\\n", mysql_error(conn));
        success = 0;
    }

    if (success) {
        // Commit the transaction.
        if (mysql_commit(conn)) {
            fprintf(stderr, "Failed to commit the transaction: %s\\n", mysql_error(conn));
        } else {
            printf("Transaction committed successfully!\\n");
        }
    } else {
        // Roll back transaction
        if (mysql_rollback(conn)) {
            fprintf(stderr, "Failed to rollback the transaction: %s\\n", mysql_error(conn));
        } else {
            printf("Transaction rolled back\\n");
        }
    }

    // Re-enable auto-commit
    mysql_autocommit(conn, 1);

    mysql_close(conn);
    return 0;
}

Example 3: Batch Query and Result Traversal

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

int main() {
    MYSQL *conn = mysql_init(NULL);
    MYSQL_RES *result;
    MYSQL_ROW row;
    MYSQL_FIELD *fields;
    unsigned int num_fields;
    unsigned int i;

    if (conn == NULL) {
        fprintf(stderr, "mysql_init() failed\\n");
        return 1;
    }

    // Connect to the TDSQL Boundless database
    if (mysql_real_connect(conn, "host", "user", "passwd", "db", 3306, NULL, 0) == NULL) {
        fprintf(stderr, "mysql_real_connect() failed: %s\\n", mysql_error(conn));
        mysql_close(conn);
        return 1;
    }

    mysql_set_character_set(conn, "utf8mb4");

    // Execute the query.
    if (mysql_query(conn, "SELECT id, name, email FROM users ORDER BY id DESC LIMIT 100")) {
        fprintf(stderr, "Query failed: %s\\n", mysql_error(conn));
        mysql_close(conn);
        return 1;
    }

    // Obtain the result set
    result = mysql_store_result(conn);
    if (result == NULL) {
        fprintf(stderr, "Failed to obtain result set: %s\\n", mysql_error(conn));
        mysql_close(conn);
        return 1;
    }

    // Obtain the field count and field information
    num_fields = mysql_num_fields(result);
    fields = mysql_fetch_fields(result);

    // Print column names
    printf("\\n");
    for (i = 0; i < num_fields; i++) {
        printf("%-20s", fields[i].name);
    }
    printf("\\n");
    
    // Print the separator line
    for (i = 0; i < num_fields; i++) {
        printf("--------------------");
    }
    printf("\\n");

    // Traverse the result set
    while ((row = mysql_fetch_row(result)) != NULL) {
        for (i = 0; i < num_fields; i++) {
            printf("%-20s", row[i] ? row[i] : "NULL");
        }
        printf("\\n");
    }

    printf("\\nTotal %lu rows of data\\n", (unsigned long)mysql_num_rows(result));

    // Free the result set
    mysql_free_result(result);

    mysql_close(conn);
    return 0;
}

Commonly Used API Reference

Function
Description
mysql_init()
Initialize the MySQL connection handle
mysql_real_connect()
Connect to the database server
mysql_close()
Close the database connection
mysql_query()
Execute the SQL statement
mysql_store_result()
Obtain the complete result set
mysql_use_result()
Obtain the result set row by row
mysql_fetch_row()
Obtain the next row of data
mysql_num_rows()
Obtain the number of rows of the result set
mysql_num_fields()
Obtain the number of columns of the result set
mysql_fetch_fields()
Obtain field information
mysql_affected_rows()
Obtain the number of affected rows
mysql_free_result()
Release the result set
mysql_error()
Get Error Information
mysql_errno()
Obtain error code
mysql_set_character_set()
Set character set
mysql_autocommit()
Set autocommit mode
mysql_commit()
Commit transaction
mysql_rollback()
Roll back transaction
mysql_stmt_init()
Initialize prepared statement
mysql_stmt_prepare()
Prepare prepared statement.
mysql_stmt_bind_param()
Bind parameters to the prepared statement.
mysql_stmt_execute()
Execute the prepared statement.
mysql_stmt_close()
Close the prepared statement.

FAQs

1. Cannot Find the mysql.h Header File During Compilation

Problem:fatal error: mysql/mysql.h: No such file or directory
Solutions: Ensure that the MySQL development library is installed and use the correct compilation parameters:
# Installing Development Libraries
sudo apt-get install libmysqlclient-dev  # Ubuntu/Debian
sudo yum install mysql-devel              # CentOS/RHEL

# Use mysql_config to obtain the correct compilation parameters
gcc -o test test.c `mysql_config --cflags --libs`

2. Cannot Find libmysqlclient.so at Runtime

Problem:error while loading shared libraries: libmysqlclient.so
Solutions:
# Method 1: Add the library path to the environment variables
export LD_LIBRARY_PATH=/usr/lib/mysql:$LD_LIBRARY_PATH

# Method 2: Update the dynamic link library cache
sudo ldconfig

3. Chinese Characters Display Garbled

Problem: Query results display Chinese characters as garbled text.
Solutions: Set the character set to utf8mb4 after connecting.
mysql_set_character_set(conn, "utf8mb4");

4. Connection Timeout

Problem: Timed out while connecting to the database.
Solutions: Set timeout parameters before connecting:
unsigned int timeout = 30;
mysql_options(conn, MYSQL_OPT_CONNECT_TIMEOUT, &timeout);

References

Topik sebelumnya: GoTopik selanjutnya: Python

Bantuan dan Dukungan

Apakah halaman ini membantu?

masukan