MySQL 基础与 C 语言开发实战

MySQL 是世界上最流行的开源关系型数据库之一，无论是后端开发、数据分析还是运维管理，掌握 MySQL 都是一项必备技能。本文将从 MySQL 的安装配置讲起，覆盖 SQL 基础语法、进阶查询技巧，再到 C 语言通过 MySQL C API 进行数据库编程，最后总结实际开发中的最佳实践。

一、MySQL 安装与配置

1.1 安装 MySQL

在 Ubuntu/Debian 系统上安装 MySQL 非常简单：

sudo apt-get install mysql-server

安装完成后，MySQL 服务会自动启动。可以通过以下命令检查服务状态：

sudo systemctl status mysql

1.2 配置远程连接

默认情况下，MySQL 只允许本地连接。如果需要远程访问，需要进行以下配置。

第一步：修改配置文件

sudo vi /etc/mysql/my.cnf

将 bind-address 修改为 0.0.0.0，表示监听所有网络接口：

bind-address = 0.0.0.0

第二步：配置用户权限

MySQL 提供了两种方式来允许远程连接：

方式一：改表法

-- 登录 MySQL
mysql -u root -p

-- 将 root 用户的 host 改为 %
UPDATE user SET host = '%' WHERE user = 'root';
SELECT host, user FROM user;

方式二：授权法（推荐）

-- 允许 myuser 从任何主机连接
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

-- 允许 myuser 从指定 IP 连接
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.6' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

-- 使修改生效
FLUSH PRIVILEGES;

第三步：防火墙放行

别忘了检查防火墙配置。如果是云服务器（如阿里云），还需要在安全组中开放 3306 端口。

# Linux 防火墙放行 3306
sudo ufw allow 3306

[图片占位符：远程连接配置流程图]

1.3 连接数据库

# 连接远程数据库
mysql -h 192.168.5.116 -P 3306 -u root -p

# 连接本地数据库
mysql -h localhost -u root -p

二、SQL 基础语法

2.1 增删查改（CRUD）

SQL 的核心操作可以概括为四个字：增、删、查、改。

查询数据（SELECT）

-- 查询所有字段
SELECT * FROM tb_users;

-- 查询指定字段
SELECT column1, column2 FROM table_name;

-- 去重查询
SELECT DISTINCT column_name FROM table_name;

插入数据（INSERT）

-- 方式一：不指定列名，按顺序插入
INSERT INTO table_name VALUES (value1, value2, value3, ...);

-- 方式二：指定列名插入（推荐）
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

更新数据（UPDATE）

UPDATE table_name SET column1=value1, column2=value2
WHERE some_column=some_value;

注意：更新数据时务必使用 WHERE 子句进行过滤，否则整个表的对应字段都会被更新。

删除数据（DELETE）

DELETE FROM table_name WHERE some_column=some_value;

2.2 WHERE 条件过滤

WHERE 子句用于提取满足指定条件的记录，支持以下运算符：

运算符 描述
= 等于
<> / != 不等于
> / < 大于 / 小于
>= / <= 大于等于 / 小于等于
BETWEEN 在某个范围内
LIKE 搜索某种模式
IN 指定多个可能值 
-- AND 和 OR 组合条件
SELECT * FROM table_name
WHERE column1 > 23 AND (column2 < 34 OR column2 != 9);

2.3 ORDER BY 排序

-- 默认升序
SELECT * FROM table_name WHERE column1 > 20 ORDER BY column1;

-- 降序
SELECT * FROM table_name ORDER BY column1 DESC;

2.4 GROUP BY 分组查询

GROUP BY 是实际业务中使用最多的查询语句之一。它的核心作用是将数据根据指定字段进行分组，然后对每个分组进行聚合计算，而不是对整个结果集进行聚合。

-- 统计每个订单编号包含的商品数量
SELECT order_num, SUM(quantity) AS total_count
FROM orderitems
GROUP BY order_num;

GROUP BY 使用规则：

  1. 可以包含任意数目的列，支持多字段分组
  2. SELECT 中的列必须与 GROUP BY 后面的字段保持一致（除聚合函数外）
  3. GROUP BY 必须出现在 WHERE 之后、ORDER BY 之前
  4. 如果 SELECT 中不包含聚合函数，GROUP BY 的效果与 DISTINCT 相同
  5. 可使用 WITH ROLLUP 对分组数据进行汇总
-- 多字段分组 + HAVING 过滤
SELECT DATE_FORMAT(order_date, "%Y-%m") AS dt, cust_id, COUNT(*) AS total_count
FROM orders
GROUP BY dt, cust_id
HAVING total_count >= 2;

2.5 子查询

子查询是指 SELECT 中嵌套 SELECT 的查询方式，常见的使用场景有两种：

场景一：利用子查询进行过滤

-- 查询订购物品 TNT2 的所有客户名称
SELECT cust_name FROM customers
WHERE cust_id IN (
    SELECT cust_id FROM orders
    WHERE order_num IN (
        SELECT order_num FROM orderitems WHERE prod_id='TNT2'
    )
);

场景二：作为计算字段使用子查询

-- 显示每个客户的订单总数
SELECT cust_id, cust_name,
    (SELECT COUNT(*) FROM orders
     WHERE orders.cust_id = customers.cust_id) AS total_count
FROM customers;

2.6 联表查询

联表查询是将多张表连接成一张表来查询。常见的连接类型有：

内连接（INNER JOIN）：只返回满足连接条件的记录。

SELECT vendors.vend_id, products.prod_id
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

左连接（LEFT JOIN）：返回左表所有记录，以及右表中匹配的记录。

SELECT a.*, b.*
FROM TableA a LEFT JOIN TableB b
ON a.id = b.id;

左连接的重要用途：可以统计字段为空的记录，这在实际业务中非常有用。

右连接（RIGHT JOIN）：返回右表所有记录，以及左表中匹配的记录。

2.7 常用函数

MySQL 提供了丰富的内置函数，以下是一些常用的：

-- count 函数加条件统计（必须加 or null）
SELECT COUNT(num > 200 OR NULL) FROM a;
SELECT COUNT(IF(num > 200, 1, NULL)) FROM a;

-- CASE WHEN 值映射
SELECT CASE StationId
    WHEN '09000E29' THEN '文景山公园'
    WHEN '09000E2B' THEN '西安工大武德路'
    ELSE '未知站点'
END AS 站点, COUNT(id) AS 总数
FROM recharge_records GROUP BY StationId;

-- 时间转换
SELECT FROM_UNIXTIME(1684942362);           -- 时间戳转日期
SELECT UNIX_TIMESTAMP('2023-05-24 15:34:38'); -- 日期转时间戳
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- 日期格式化

-- 字符串拼接
SELECT CONCAT_WS('-', 'aaa', 'bbb');   -- 带分隔符拼接
SELECT GROUP_CONCAT(user) FROM mysql.user; -- 列连接

-- Base64 编解码
SELECT TO_BASE64('zhouwy');
SELECT FROM_BASE64('emhvdXd5');

-- AES 加解密
SELECT TO_BASE64(AES_ENCRYPT('content_text', 'aes_screct'));
SELECT AES_DECRYPT(FROM_BASE64('INqLpiYygcWuLu4kiqYhFw=='), 'aes_screct');

2.8 事务隔离级别

MySQL 的 InnoDB 引擎默认开启事务。理解事务隔离级别对于编写高并发应用至关重要：

隔离级别 脏读 不可重复读 幻读 加锁读
读未提交 有 有 有 无
读已提交 无 有 有 无
可重复读（MySQL默认） 无 无 有 无
串行化 无 无 无 有
  • 脏读：事务 A 读到了事务 B 未提交的数据，事务 B 回滚后，事务 A 的数据就不正确了。
  • 不可重复读：事务 A 两次读取同一行数据，结果不一致（被事务 B 修改并提交了）。
  • 幻读：事务 A 两次执行相同的范围查询，结果集行数不同（被事务 B 新增或删除了记录）。

不论哪种事务隔离级别，当两个事务同时操作同一行数据时，都会产生行锁。

[图片占位符：事务隔离级别对比图]

三、MySQL C API 编程

MySQL 提供了 C 语言 API（libmysqlclient），允许开发者在 C/C++ 程序中直接操作 MySQL 数据库。核心 API 只有少数几个函数，掌握它们就能完成大部分数据库操作。

3.1 核心 API 简介

函数 功能
mysql_init() 初始化连接句柄
mysql_real_connect() 连接到 MySQL 服务器
mysql_real_query() 执行 SQL 语句
mysql_store_result() 获取完整结果集
mysql_fetch_row() 逐行获取结果
mysql_num_rows() 获取结果行数
mysql_num_fields() 获取字段数
mysql_free_result() 释放结果集内存
mysql_close() 关闭连接
mysql_error() 获取错误信息

3.2 查询数据完整示例

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

int main(int argc, const char *argv[])
{
    MYSQL       mysql;
    MYSQL_RES   *res = NULL;  // 总的查询结果
    MYSQL_ROW   row;           // 存取一行数据
    char        *query_str = NULL;
    int         rc, i, fields;
    int         rows;

    // 1. 初始化连接句柄
    if (NULL == mysql_init(&mysql)) {
        printf("mysql_init(): %s\n", mysql_error(&mysql));
        return -1;
    }

    // 2. 连接数据库
    //    参数：句柄, 主机, 用户名, 密码, 数据库名, 端口, unix_socket, 客户端标志
    if (NULL == mysql_real_connect(&mysql,
                "localhost",
                "root",
                "shallnet",
                "db_users",
                0,
                NULL,
                0)) {
        printf("mysql_real_connect(): %s\n", mysql_error(&mysql));
        return -1;
    }
    printf("Connected MySQL successful!\n");

    // 3. 执行查询
    query_str = "select * from tb_users";
    rc = mysql_real_query(&mysql, query_str, strlen(query_str));
    if (0 != rc) {
        printf("mysql_real_query(): %s\n", mysql_error(&mysql));
        return -1;
    }

    // 4. 获取结果集
    res = mysql_store_result(&mysql);
    if (NULL == res) {
        printf("mysql_store_result(): %s\n", mysql_error(&mysql));
        return -1;
    }

    // 5. 遍历结果
    rows = mysql_num_rows(res);
    fields = mysql_num_fields(res);
    printf("Total rows: %d, Total fields: %d\n", rows, fields);

    while ((row = mysql_fetch_row(res))) {
        for (i = 0; i < fields; i++) {
            printf("%s\t", row[i]);
        }
        printf("\n");
    }

    // 6. 释放资源
    mysql_free_result(res);
    mysql_close(&mysql);
    return 0;
}

3.3 插入与删除数据示例

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

int main(int argc, const char *argv[])
{
    MYSQL       mysql;
    MYSQL_RES   *res = NULL;
    MYSQL_ROW   row;
    char        *query_str = NULL;
    int         rc, i, fields;
    int         rows;

    if (NULL == mysql_init(&mysql)) {
        printf("mysql_init(): %s\n", mysql_error(&mysql));
        return -1;
    }

    if (NULL == mysql_real_connect(&mysql,
                "localhost",
                "root",
                "shallnet",
                "db_users",
                0, NULL, 0)) {
        printf("mysql_real_connect(): %s\n", mysql_error(&mysql));
        return -1;
    }
    printf("Connected MySQL successful!\n");

    // 执行插入操作
    query_str = "INSERT INTO tb_users VALUES (12345, 'justtest', '2015-5-5')";
    rc = mysql_real_query(&mysql, query_str, strlen(query_str));
    if (0 != rc) {
        printf("mysql_real_query(): %s\n", mysql_error(&mysql));
        return -1;
    }

    // 执行删除操作
    query_str = "DELETE FROM tb_users WHERE userid=10006";
    rc = mysql_real_query(&mysql, query_str, strlen(query_str));
    if (0 != rc) {
        printf("mysql_real_query(): %s\n", mysql_error(&mysql));
        return -1;
    }

    // 查询插入和删除之后的数据
    query_str = "SELECT * FROM tb_users";
    rc = mysql_real_query(&mysql, query_str, strlen(query_str));
    if (0 != rc) {
        printf("mysql_real_query(): %s\n", mysql_error(&mysql));
        return -1;
    }

    res = mysql_store_result(&mysql);
    if (NULL == res) {
        printf("mysql_store_result(): %s\n", mysql_error(&mysql));
        return -1;
    }

    rows = mysql_num_rows(res);
    fields = mysql_num_fields(res);
    printf("Total rows: %d, Total fields: %d\n", rows, fields);

    while ((row = mysql_fetch_row(res))) {
        for (i = 0; i < fields; i++) {
            printf("%s\t", row[i]);
        }
        printf("\n");
    }

    mysql_free_result(res);
    mysql_close(&mysql);
    return 0;
}

3.4 编译命令

编译 MySQL C 程序时，需要链接 MySQL 客户端库：

gcc -o mysql_demo mysql_demo.c $(mysql_config --cflags --libs)

或者手动指定：

gcc -o mysql_demo mysql_demo.c -I/usr/include/mysql -lmysqlclient

[图片占位符：MySQL C API 调用流程图]

四、API 最佳实践（13 条重要注意事项）

在实际的数据库编程中，正确使用 API 至关重要。以下是经过实战总结的 13 条经验：

4.1 事务处理

第 1 条：开启事务前先 rollback

// 开启事务之前先 rollback 连接句柄，清理可能存在的垃圾状态
mysql_real_query(&mysql, "ROLLBACK", strlen("ROLLBACK"));
mysql_real_query(&mysql, "BEGIN", strlen("BEGIN"));

第 2 条：事务尽量短小

事务使用完毕后立即 COMMIT 或 ROLLBACK，不要开启过大的事务。大事务会长时间持有锁，阻塞其他操作。

第 3 条：隐式事务与 autocommit

如果使用事务（BEGIN/COMMIT），即使 autocommit 为 1 也可以正常工作。如果不使用事务，则必须显式设置 SET autocommit=1，因为无法确定某个长连接中是否有人设置了 SET autocommit=0。

4.2 连接管理

第 4 条：连接超时与重连

// 设置连接超时时间，特别是处理重连逻辑时，以免程序堵死
unsigned int timeout = 5;  // 5秒超时
mysql_options(&mysql, MYSQL_OPT_CONNECT_TIMEOUT, &timeout);

// mysql_ping 失败时，程序需要处理重连逻辑
if (mysql_ping(&mysql) != 0) {
    // 执行重连逻辑
}

第 5 条：多线程环境注意事项

在多线程环境下必须使用 libmysqlclient_r 库（线程安全版本），而不是 libmysqlclient。mysql_real_connect 和 mysql_init 在多线程环境下调用时需要加锁。

4.3 SQL 执行

第 6 条：mysql_query vs mysql_real_query

  • mysql_query() 执行的 SQL 语句以 \0 结尾，不能包含二进制数据
  • mysql_real_query() 通过参数指定字符串长度，可以包含二进制数据

实际使用中，推荐使用 mysql_real_query。

第 7 条：SQL 语句不需要分号

通过 MySQL C API 执行的 SQL 语句不需要以分号 ; 结尾。

第 8 条：转义字符串

使用 mysql_real_escape_string 时，目标缓冲区的大小必须是 2 * length + 1：

char escaped[2 * strlen(input) + 1];
mysql_real_escape_string(&mysql, escaped, input, strlen(input));

4.4 错误处理与验证

第 9 条：检查 UPDATE 影响的行数

所有 UPDATE 语句执行后，建议通过 mysql_affected_rows() 检查受影响的行数，确认是否符合预期值。

第 10 条：校验返回值和错误码

程序 rollback 时，需要习惯性地校验返回的错误码，避免错误码未赋值导致调用者误以为调用成功。

4.5 内存管理

第 11 条：释放结果集

mysql_store_result() 会分配内存存储完整的结果集，使用完毕后必须调用 mysql_free_result() 释放，否则会造成内存泄漏。

4.6 索引与性能

第 12 条：SELECT 必须走索引

SELECT 语句必须使用索引。WHERE 条件中避免使用 OR 或运算表达式，否则会导致索引失效。

联合索引可以替代单独的索引。已有联合索引 (f1, f2) 时，可以省略 f1 的单独索引，但不能省略 f2 的单独索引。

WHERE 条件的结果集不要太大，如果超过 30% 的行，索引会失效导致全表扫描。不确定时，使用 EXPLAIN 做检测。

第 13 条：锁相关注意事项

  • MySQL 单表记录保持在 1000 万以下以获得较好的性能
  • 数据库连接数保持在 200 以下
  • 修改锁等待时间 innodb_lock_wait_timeout（默认 50 秒），避免 FOR UPDATE 等待时间过长
  • FOR UPDATE 语句的 WHERE 条件请使用主键，避免锁一个非主键时默认同时锁定主键导致死锁
  • MySQL 隔离级别建议使用 Read Committed，避免产生锁间隙问题
-- 查看执行计划
EXPLAIN SELECT * FROM tablename WHERE condition;

-- 查看当前活动线程
SHOW FULL PROCESSLIST;

-- 查找重复数据
SELECT * FROM transactions
WHERE TransactionHash IN (
    SELECT TransactionHash FROM transactions
    GROUP BY TransactionHash
    HAVING COUNT(*) > 1
)
ORDER BY TransactionHash;

[图片占位符：MySQL 性能优化检查清单]

五、总结

MySQL 的学习可以从三个层次来理解：

  1. 运维层：安装、配置、用户权限管理、性能监控
  2. SQL 层：CRUD、聚合查询、联表查询、子查询、事务控制
  3. 编程层：C API 调用、连接池管理、ORM 框架

掌握这三个层次，就能在实际项目中游刃有余地使用 MySQL。特别是在 C/C++ 开发中，理解 MySQL C API 的正确使用方式和注意事项，能够避免很多难以排查的线上问题。

来源：整理自 yisiderui/mysql.cpp、zyh/mysql.sql 学习笔记

# mysql # 数据库 # c语言
Logo
https://edu.csdn.net/learn/39067/627173?utm_source=2019755004

汇聚全球AI编程工具，助力开发者即刻编程。

更多推荐

cover

每天 1000 万 Token，免费云端 AI 编程平台 MonkeyCode 上手指南

avatar AI编程社区
cover

Vibe Coding 下半场：8 月 19 日的十个信号，AI 正从“炫技“转向“干活“

avatar AI编程社区
cover

AI Agent 的真相：水面之下，90% 是工程

avatar AI编程社区