Initializing a MySQL Connection

The first step in using the MySQL C API is initializing a connection handle:

MYSQL *conn = mysql_init(NULL);

This allocates and initializes a MYSQL structure used throughout the session.

Establishing a Database Connection

After initialization, connect to the database server:

MYSQL *mysql_real_connect(
    MYSQL *conn,
    const char *host,
    const char *user,
    const char *password,
    const char *database,
    unsigned int port,
    const char *unix_socket,
    unsigned long client_flag
);

To correctly handle UTF-8 encoded text (especially Chinese characters), explicitly set the character set after connecting:

mysql_set_character_set(conn, "utf8");

By default, MySQL uses latin1, which may cause encoding issues.

Executing SQL Queries

Use mysql_query() to send SQL statements:

int status = mysql_query(conn, "SELECT * FROM users");

A return value of 0 indicates success.

Retrieving Query Results

For SELECT queries, fetch the result set:

MYSQL_RES *result = mysql_store_result(conn);

This function reads the entire result set in to memory. Always release it later with mysql_free_result() to prevent memory leaks.

Inspecting Result Metadata

Get the number of rows and columns:

my_ulonglong row_count = mysql_num_rows(result);
unsigned int col_count = mysql_num_fields(result);

Retrieve column information:

MYSQL_FIELD *fields = mysql_fetch_fields(result);
for (unsigned int i = 0; i < col_count; i++) {
    printf("%s ", fields[i].name);
}
printf("\n");

Fetching Data Rows

Iterate through result rows:

MYSQL_ROW row;
while ((row = mysql_fetch_row(result))) {
    for (unsigned int j = 0; j < col_count; j++) {
        printf("%s ", row[j] ? row[j] : "NULL");
    }
    printf("\n");
}

Each MYSQL_ROW is a char**, where each element corresponds to a column value (or NULL if the field is NULL).

Cleaning Up Resources

Free the result set:

mysql_free_result(result);

Close the connection when done:

mysql_close(conn);