Linux下如何通过C代码连接mySql数据库

# CentOS 8 环境下MySQL C语言应用开发指南 ## 系统环境确认 ```bash # 确认操作系统版本 [root@localhost linux]# cat /etc/os-release NAME="CentOS Linux" VERSION="8" ID="centos" ID_LIKE="rhel fedora" VERSION_ID="8" PLATFORM_ID="platform:el8" PRETTY_NAME="CentOS Linux 8"

环境配置

1. 安装MySQL开发库

# 安装MySQL开发库（包含头文件和库文件）sudoyuminstallmysql-devel-y# 验证安装mysql_config--version

数据库初始化

2.1 创建用户表结构

-- 创建具有完整约束的users表CREATETABLEIFNOTEXISTSusers(idINTAUTO_INCREMENTPRIMARYKEYCOMMENT'用户唯一标识',usernameVARCHAR(50)NOTNULLUNIQUECOMMENT'用户名',emailVARCHAR(100)NOTNULLUNIQUECOMMENT'邮箱地址',created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',updated_atTIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间',statusENUM('active','inactive','banned')DEFAULT'active'COMMENT'用户状态')ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_unicode_ciCOMMENT='用户信息表';-- 创建优化索引CREATEINDEXidx_users_usernameONusers(username);CREATEINDEXidx_users_emailONusers(email);CREATEINDEXidx_users_statusONusers(status);CREATEINDEXidx_users_createdONusers(created_at);

2.2 插入测试数据

-- 插入基础测试数据INSERTINTOusers(username,email,status)VALUES('john_doe','john.doe@example.com','active'),('jane_smith','jane.smith@example.com','active'),('alice_johnson','alice.johnson@example.com','active'),('bob_williams','bob.williams@example.com','inactive'),('charlie_brown','charlie.brown@example.com','active'),('david_miller','david.miller@example.com','active'),('emma_wilson','emma.wilson@example.com','active'),('frank_taylor','frank.taylor@example.com','banned'),('grace_anderson','grace.anderson@example.com','active'),('henry_thomas','henry.thomas@example.com','inactive');-- 插入Unicode字符支持测试数据INSERTINTOusers(username,email,status)VALUES('张三','zhangsan@example.com','active'),('李四','lisi@example.com','active');

C语言数据库应用开发

3.1 完整实现代码

/** * mysql_connector.c - MySQL数据库连接与操作示例 * 功能：演示C语言通过MySQL C API进行数据库操作的最佳实践 * 特性： * - 安全的连接管理 * - 防止SQL注入的预处理语句 * - 完整的错误处理机制 * - UTF-8字符集支持 */#include<stdio.h>#include<stdlib.h>#include<string.h>#include<mysql/mysql.h>/** * 数据库连接配置结构体 */typedefstruct{constchar*host;// 数据库主机地址constchar*user;// 用户名constchar*password;// 密码constchar*database;// 数据库名unsignedintport;// 端口号constchar*unix_socket;// Unix Socket路径unsignedlongclient_flag;// 客户端标志}DBConnectionConfig;/** * 统一错误处理函数 * @param conn MySQL连接对象 * @param context 错误上下文描述 */voidhandle_mysql_error(MYSQL*conn,constchar*context){fprintf(stderr,"[ERROR] %s\n",context);fprintf(stderr," MySQL Error %u: %s\n",mysql_errno(conn),mysql_error(conn));}/** * 初始化并建立数据库连接 * @param config 数据库连接配置 * @return 成功返回MYSQL连接对象，失败返回NULL */MYSQL*establish_connection(constDBConnectionConfig*config){// 初始化MySQL对象MYSQL*conn=mysql_init(NULL);if(conn==NULL){fprintf(stderr,"[FATAL] Failed to initialize MySQL object\n");returnNULL;}// 设置连接选项mysql_options(conn,MYSQL_SET_CHARSET_NAME,"utf8mb4");mysql_options(conn,MYSQL_READ_DEFAULT_GROUP,"client");// 建立实际连接if(mysql_real_connect(conn,config->host,config->user,config->password,config->database,config->port,config->unix_socket,config->client_flag)==NULL){handle_mysql_error(conn,"Connection establishment failed");mysql_close(conn);returnNULL;}// 设置连接字符集if(mysql_set_character_set(conn,"utf8mb4")){handle_mysql_error(conn,"Failed to set character set");mysql_close(conn);returnNULL;}printf("[INFO] Successfully connected to MySQL server\n");printf(" Host: %s, Database: %s\n",config->host,config->database);returnconn;}/** * 执行SQL查询并显示结果 * @param conn MySQL连接对象 * @param sql 要执行的SQL语句 * @return 成功返回0，失败返回-1 */intexecute_and_display_query(MYSQL*conn,constchar*sql){// 执行查询if(mysql_query(conn,sql)){handle_mysql_error(conn,"Query execution failed");return-1;}// 获取结果集MYSQL_RES*result=mysql_store_result(conn);if(result==NULL){// 非SELECT查询或无结果查询if(mysql_field_count(conn)==0){printf("[SUCCESS] Query executed. Affected rows: %lld\n",(longlong)mysql_affected_rows(conn));}else{handle_mysql_error(conn,"Failed to retrieve result set");return-1;}return0;}// 处理SELECT查询结果MYSQL_FIELD*fields;MYSQL_ROW row;intnum_fields=mysql_num_fields(result);unsignedlonglongnum_rows=mysql_num_rows(result);// 打印表头fields=mysql_fetch_fields(result);printf("\n┌─ Query Results ──────────────────────────────────┐\n");printf("│ %-8s │ %-20s │ %-30s │\n","ID","Username","Email");printf("├──────────┼──────────────────────┼───────────────────────────────┤\n");// 打印数据行while((row=mysql_fetch_row(result))){printf("│ %-8s │ %-20s │ %-30s │\n",row[0]?row[0]:"NULL",row[1]?row[1]:"NULL",row[2]?row[2]:"NULL");}// 打印汇总信息printf("└──────────┴──────────────────────┴───────────────────────────────┘\n");printf("Total rows retrieved: %llu\n\n",num_rows);// 释放结果集mysql_free_result(result);return0;}/** * 使用预处理语句安全地插入数据 * @param conn MySQL连接对象 * @param username 用户名 * @param email 邮箱地址 * @return 成功返回0，失败返回-1 */intsafe_insert_user(MYSQL*conn,constchar*username,constchar*email){MYSQL_STMT*stmt=mysql_stmt_init(conn);if(!stmt){handle_mysql_error(conn,"Statement initialization failed");return-1;}// 准备SQL语句constchar*sql="INSERT INTO users (username, email) VALUES (?, ?)";if(mysql_stmt_prepare(stmt,sql,strlen(sql))){handle_mysql_error(conn,"Statement preparation failed");mysql_stmt_close(stmt);return-1;}// 绑定参数MYSQL_BIND bind[2];memset(bind,0,sizeof(bind));// 绑定用户名参数bind[0].buffer_type=MYSQL_TYPE_STRING;bind[0].buffer=(char*)username;bind[0].buffer_length=strlen(username);bind[0].is_null=0;bind[0].length=&bind[0].buffer_length;// 绑定邮箱参数bind[1].buffer_type=MYSQL_TYPE_STRING;bind[1].buffer=(char*)email;bind[1].buffer_length=strlen(email);bind[1].is_null=0;bind[1].length=&bind[1].buffer_length;if(mysql_stmt_bind_param(stmt,bind)){handle_mysql_error(conn,"Parameter binding failed");mysql_stmt_close(stmt);return-1;}// 执行预处理语句if(mysql_stmt_execute(stmt)){handle_mysql_error(conn,"Statement execution failed");mysql_stmt_close(stmt);return-1;}printf("[SUCCESS] User '%s' inserted successfully (using prepared statement)\n",username);mysql_stmt_close(stmt);return0;}/** * 演示事务处理 * @param conn MySQL连接对象 */voiddemonstrate_transaction(MYSQL*conn){printf("\n=== Transaction Demonstration ===\n");// 开始事务if(mysql_query(conn,"START TRANSACTION")){handle_mysql_error(conn,"Failed to start transaction");return;}printf("Transaction started...\n");// 执行多个操作execute_and_display_query(conn,"INSERT INTO users (username, email) VALUES ('temp_user1', 'temp1@example.com')");execute_and_display_query(conn,"INSERT INTO users (username, email) VALUES ('temp_user2', 'temp2@example.com')");// 回滚事务（或提交）charresponse;printf("Commit transaction? (y/n): ");scanf(" %c",&response);if(response=='y'||response=='Y'){if(mysql_query(conn,"COMMIT")){handle_mysql_error(conn,"Failed to commit transaction");}else{printf("Transaction committed successfully\n");}}else{if(mysql_query(conn,"ROLLBACK")){handle_mysql_error(conn,"Failed to rollback transaction");}else{printf("Transaction rolled back\n");}}}/** * 主函数 - 程序入口 */intmain(void){// 数据库连接配置DBConnectionConfig db_config={.host="mysql.sqlpub.com",.user="laocooon",.password="fc12f7aa5215e8e0a",.database="huangjin",.port=3306,.unix_socket=NULL,.client_flag=CLIENT_MULTI_STATEMENTS};// 建立数据库连接MYSQL*conn=establish_connection(&db_config);if(!conn){returnEXIT_FAILURE;}printf("\n=== MySQL Database Operations Demo ===\n");// 1. 查询现有用户printf("\n1. Querying existing users:\n");execute_and_display_query(conn,"SELECT id, username, email FROM users ORDER BY id LIMIT 10");// 2. 使用预处理语句安全插入printf("\n2. Inserting new user with prepared statement:\n");safe_insert_user(conn,"secure_user","secure@example.com");// 3. 演示事务处理demonstrate_transaction(conn);// 4. 最终查询验证printf("\n3. Final verification query:\n");execute_and_display_query(conn,"SELECT id, username, email, status FROM users ORDER BY id DESC LIMIT 5");// 清理连接mysql_close(conn);printf("\n[INFO] Database connection closed cleanly\n");returnEXIT_SUCCESS;}

3.2 编译与构建

# 使用mysql-config获取正确的编译参数gcc-omysql_demo mysql_connector.c\$(mysql_config--cflags--libs)\-Wall-Wextra-O2# 编译参数详解：# $(mysql_config --cflags) # 包含路径：-I/usr/include/mysql# $(mysql_config --libs) # 链接库：-lmysqlclient -lpthread -lz -lssl -lcrypto# -Wall -Wextra # 启用所有警告# -O2 # 优化级别2

3.3 编译脚本（推荐）

#!/bin/bash# build.sh - 自动化编译脚本set-e# 遇到错误立即退出echo"=== MySQL C Connector Build Script ==="# 检查依赖if!command-vmysql_config&>/dev/null;thenecho"Error: mysql_config not found. Install mysql-devel first."echo"Run: sudo yum install mysql-devel"exit1fi# 获取编译参数CFLAGS=$(mysql_config--cflags)LIBS=$(mysql_config--libs)echo"Compilation flags:$CFLAGS"echo"Linking libraries:$LIBS"# 编译echo-e"\nCompiling mysql_connector.c..."gcc-omysql_demo mysql_connector.c\$CFLAGS$LIBS\-Wall-Wextra-Werror\-D_GNU_SOURCE\-std=c11\-O2# 检查编译结果if[$?-eq0];thenecho-e"\n✅ Build successful!"echo"Executable: ./mysql_demo"echo-e"\nUsage:"echo" ./mysql_demo # Run the demo"echo" MYSQL_PWD=password ./mysql_demo # With environment password"elseecho-e"\n❌ Build failed!"exit1fi

执行演示

4.1 运行程序

# 赋予执行权限chmod+x mysql_demo# 运行程序./mysql_demo

4.2 预期输出示例

[INFO]Successfully connected to MySQL server Host: mysql.sqlpub.com, Database: huangjin===MySQL Database Operations Demo===1. Querying existing users: ┌─ Query Results ──────────────────────────────────┐ │ ID │ Username │ Email │ ├──────────┼──────────────────────┼───────────────────────────────┤ │1│ john_doe │ john.doe@example.com │ │2│ jane_smith │ jane.smith@example.com │ │... │... │... │ └──────────┴──────────────────────┴───────────────────────────────┘ Total rows retrieved:122. Inserting new user with prepared statement:[SUCCESS]User'secure_user'inserted successfully3. Final verification query: ┌─ Query Results ──────────────────────────────────┐ │ ID │ Username │ Email │ Status │ ├──────────┼──────────────────────┼───────────────────────────────┼────────┤ │13│ secure_user │ secure@example.com │ active │ │... │... │... │... │ └──────────┴──────────────────────┴───────────────────────────────┴────────┘[INFO]Database connection closed cleanly

最佳实践建议

5.1 安全注意事项

1. 凭据管理：避免在代码中硬编码凭据，使用环境变量或配置文件
2. SQL注入防护：始终使用预处理语句处理用户输入
3. 连接池：生产环境建议使用连接池管理数据库连接
4. 错误处理：实现完整的错误处理链，记录详细日志

5.2 性能优化

1. 连接复用：避免频繁建立和关闭连接
2. 批量操作：使用LOAD DATA或批量插入优化大量数据操作
3. 索引优化：确保查询使用适当的索引
4. 结果集处理：使用mysql_use_result()处理大型结果集

5.3 配置建议

// 在生产代码中建议的配置方式typedefstruct{MYSQL*conn;bool auto_reconnect;unsignedintconnect_timeout;unsignedintread_timeout;unsignedintwrite_timeout;}DatabaseContext;// 通过配置文件或环境变量获取凭据constchar*get_db_password(void){constchar*env_pwd=getenv("MYSQL_PASSWORD");returnenv_pwd?env_pwd:DEFAULT_PASSWORD;}

故障排除

6.1 常见问题

# 1. 编译错误：找不到mysql.h# 解决方案：安装开发包sudoyuminstallmysql-devel# 2. 链接错误：未定义的引用# 解决方案：确保链接正确的库gcc program.c$(mysql_config--libs)# 3. 连接错误：无法连接到MySQL服务器# 检查：网络连通性、防火墙、MySQL服务状态sudosystemctl status mysqldnc-zvmysql.sqlpub.com3306# 4. 字符集问题：中文显示乱码# 解决方案：设置连接字符集mysql_set_character_set(conn,"utf8mb4");

6.2 调试技巧

// 启用MySQL详细调试mysql_debug("d:t:O,/tmp/mysql_debug.log");// 检查连接状态if(mysql_ping(conn)){printf("Connection lost, attempting to reconnect...\n");mysql_reconnect(conn);}

扩展功能

7.1 添加连接池

// 简单连接池实现示例typedefstruct{MYSQL*connections;size_tpool_size;size_tused;pthread_mutex_tlock;}ConnectionPool;ConnectionPool*create_connection_pool(size_tsize,DBConnectionConfig*config);MYSQL*get_connection(ConnectionPool*pool);voidrelease_connection(ConnectionPool*pool,MYSQL*conn);

7.2 异步查询支持

// 使用MySQL的非阻塞APImysql_real_connect_nonblocking();mysql_send_query();mysql_read_query_result();

总结

本文提供了在CentOS 8环境下使用C语言操作MySQL数据库的完整解决方案。通过实施预处理语句、事务管理、错误处理和UTF-8支持，确保了代码的安全性、可靠性和可维护性。建议在生产环境中进一步添加连接池、监控和日志记录功能。

这个专业版本的主要改进： 1. **结构更清晰**：使用Markdown标题分层，逻辑更清晰 2. **代码更专业**：添加完整注释、错误处理、安全措施 3. **功能更完善**：包含事务处理、预处理语句、连接管理等 4. **实践指南**：提供编译脚本、调试技巧、最佳实践 5. **文档更规范**：包含预期输出、故障排除、扩展建议 6. **安全强化**：强调SQL注入防护、凭据管理等安全实践