Table of Contents
例子
增、删、改代码:
返回数据的语句:select
一次提取所有数据:mysql_store_result
一次提取一行数据:mysql_use_result
处理返回的数据
代码示例:
Home Database Mysql Tutorial Linux下C语言执行MySQL语句_MySQL

Linux下C语言执行MySQL语句_MySQL

Jun 01, 2016 pm 01:26 PM
c language linux mysql string

bitsCN.com

执行SQL语句的增、删、改、查的主要API函数为:

int mysql_query(MYSQL *connection, const char *query);
Copy after login

函数接收参数连接句柄和字符串形式的有效SQL语句(没有结束的分号,这与mysql工具不同)。如果成功,它返回0。

如果包含二进制数据的查询,要使用mysql_real_query.

检查受查询影响的行数:

my_ulonglong mysql_affected_rows(MYSQL *connection);
Copy after login
my_ulonglong是无符号长整形,为%lu格式
这个函数返回受之前执行update,insert或delete查询影响的行数。

例子

数据库中有一个student表

CREATE TABLE student (		student_no varchar(12) NOT NULL PRIMARY KEY,		student_name varchar(12) NOT NULL		); 
Copy after login
/

增、删、改代码:

#include <stdio.h>#include <stdlib.h>#include <string.h>#include "mysql.h"#include "errmsg.h"#include "mysqld_error.h"MYSQL conn;void connection(const char* host, const char* user, const char* password, const char* database) {	mysql_init(&conn); // 注意取地址符&	if (mysql_real_connect(&conn, host, user, password, database, 0, NULL, 0)) {		printf("Connection success!/n");	} else {		fprintf(stderr, "Connection failed!/n");		if (mysql_errno(&conn)) {			fprintf(stderr, "Connection error %d: %s/n", mysql_errno(&conn), mysql_error(&conn));		}		exit(EXIT_FAILURE);	}}void insert() {	int res = mysql_query(&conn, "INSERT INTO student(student_no,student_name) VALUES(&#39;123465&#39;, &#39;Ann&#39;)");	if (!res) {		printf("Inserted %lu rows/n", (unsigned long)mysql_affected_rows(&conn));	} else {		fprintf(stderr, "Insert error %d: %s/n", mysql_errno(&conn), mysql_error(&conn));	}}void update() {	int res = mysql_query(&conn, "UPDATE student SET student_name=&#39;Anna&#39; WHERE student_no=&#39;123465&#39;");	if (!res) {		printf("Update %lu rows/n", (unsigned long)mysql_affected_rows(&conn));	} else {		fprintf(stderr, "Update error %d: %s/n", mysql_errno(&conn), mysql_error(&conn));	}}void delete() {	int res = mysql_query(&conn, "DELETE from student WHERE student_no=&#39;123465&#39;");	if (!res) {		printf("Delete %lu rows/n", (unsigned long)mysql_affected_rows(&conn));	} else {		fprintf(stderr, "Delete error %d: %s/n", mysql_errno(&conn), mysql_error(&conn));	}}int main (int argc, char *argv[]) {	connection("localhost", "root", "shuang", "shuangde");	delete();	mysql_close(&conn);	exit(EXIT_SUCCESS);}
Copy after login

返回数据的语句:select

SQL最常见的用法是提取数据而不是插入或更新数据。数据是用select语句提取的

C应用程序提取数据一般需要4个步骤:

1、执行查询

2、提取数据

3、处理数据

4、必要的清理工作

就像之前的insert和update一样,使用mysql_query来发送SQL语句,然后使用mysql_store_result或mysql_use_result来提取数据,具体使用哪个语句取决于你想如何提取数据。接着,将使用一系列mysql_fetch_row来处理数据。最后,使用mysql_free_result释放查询占用的内存资源。

一次提取所有数据:mysql_store_result

// 相关函数:// 这是在成功调用mysql_query之后使用此函数,这个函数将立刻保存在客户端中返回的所有数据。它返回一个指向结果集结构的指针,如果失败返回NULLMYSQL_RES *mysql_store_result(MYSQL *connection);// 这个函数接受由mysql_store_result返回的结果结构集,并返回结构集中的行数my_ulonglong mysql_num_rows(MYSQL_RES *result);// 这个函数从使用mysql_store_result得到的结果结构中提取一行,并把它放到一个行结构中。当数据用完或发生错误时返回NULL.MYSQL_ROW mysql_fetch_row(MYSQL_RES *resutl);// 这个函数用来在结果集中跳转,设置将会被下一个mysql_fetch_row操作返回的行。参数offset是一个行号,它必须是在0~结果总行数-1的范围内。传递// 0将会导致下一个mysql_fetch_row调用返回结果集中的第一行。void mysql_data_seek(MYSQL_RES *result, my_ulonglong offset);// 返回一个偏移值,它用来表示结果集中的当前位置。它不是行号,不能把它用于mysql_data_seekMYSQL_ROW_OFFSET mysql_row_tell(MYSQL_RES *result);// 这将在结果集中移动当前的位置,并返回之前的位置MYSQL_ROW_OFFSET mysql_row_seek(MYSQL_RES *result, MYSQL_ROW_OFFSET offset);// 完成所有对数据的操作后,必须总是调用这个来善后处理void mysql_free_result(MYSQL_RES *result);
Copy after login

示例代码:

#include <stdio.h>#include <stdlib.h>#include <string.h>#include "mysql.h"#include "errmsg.h"#include "mysqld_error.h"MYSQL conn;MYSQL_RES *res_ptr;MYSQL_ROW sqlrow;void connection(const char* host, const char* user, const char* password, const char* database) {	mysql_init(&conn); // 注意取地址符&	if (mysql_real_connect(&conn, host, user, password, database, 0, NULL, 0)) {		printf("Connection success!/n");	} else {		fprintf(stderr, "Connection failed!/n");		if (mysql_errno(&conn)) {			fprintf(stderr, "Connection error %d: %s/n", mysql_errno(&conn), mysql_error(&conn));		}		exit(EXIT_FAILURE);	}}int main (int argc, char *argv[]) {	connection("localhost", "root", "shuang", "shuangde");	int res = mysql_query(&conn, "SELECT * from student");	if (res) {		fprintf(stderr, "SELECT error: %s/n", mysql_error(&conn));	} else {		res_ptr = mysql_store_result(&conn);		if (res_ptr) {			printf("Retrieved %lu rows/n", (unsigned long)mysql_num_rows(res_ptr));				while ((sqlrow = mysql_fetch_row(res_ptr))) {				printf("Fetched data.../n")	;			}			if (mysql_errno(&conn)) {				fprintf(stderr, "Retrive error: %s/n", mysql_error(&conn));			}			mysql_free_result(res_ptr);		} 	}	mysql_close(&conn);	exit(EXIT_SUCCESS);}
Copy after login

一次提取一行数据:mysql_use_result

使用方法和mysql_store_result完全一样,把上面代码的mysql_store_result改为mysql_use_result即可。

mysql_use_result具备资源管理方面的实质性好处,更好地平衡了网络负载,以及减少了可能非常大的数据带来的存储开销,但是不能与mysql_data_seek、mysql_row_seek、mysql_row_tell、mysql_num_rows一起使用。如果数据比较少,用mysql_store_result更好。

处理返回的数据

// 相关函数和定义:// 返回结果集中的字段(列)数目unsigned int mysql_field_count(MYSQL *connection);// 将元数据和数据提取到一个新的结构中MYSQL_FIELD *mysql_fetch_field(MYSQL *result);// 这个函数用来覆盖当前的字段编号,该编号会随着每次mysql_fetch_field调用而自动增加。如果给offset传递0,那么将跳回第1列MYSQL_FIELD_OFFSET mysql_field_seek(MYSQL *result, MYSQL_FIELD_OFFSET offset);// MYSQL_FIELD定义在sql.h中,是指向字段结构数据的指针,有关于列的信息。有成员:char *name;		// 列名,为字符串char *table;	// 列所属表名char *def;		// 如果调用mysql_list_fields,它将包含该列的默认值enum enum_field_types type;  // 列类型unsigned int length;		 // 列宽unsigned int max_length;	 // 如果使用mysql_store_result,它将包含以字节为单位的提取的最长列值的长度,如果使用mysql_use_result,将不会被设置unsigned int flags;			 // 关于列定义的标志,与得到的数据无关.常见的标志的含义有:						     //	NOT_NULL_FLAG							 // PRI_KEY_FLAG						     //	UNSIGNED_FLAG							 // AUTO_INCREMENT_FLAG							 // BINARY_FLAG等unsigned int decimals;		 // 小数点后的数字个数。// 列类型相当广泛,完整的列表见头文件mysql_com.h,常见的有://	FIELD_TYPE_DECIMAL//	FIELD_TYPE_LONG//	FIELD_TYPE_STRING//	FIELD_TYPE_VAR_STRING//一个特别有用的预定义宏: IS_NUM,当字段类型为数字时,返回true
Copy after login

代码示例:

#include <stdio.h>#include <stdlib.h>#include <string.h>#include "mysql.h"#include "errmsg.h"#include "mysqld_error.h"MYSQL conn;MYSQL_RES *res_ptr;MYSQL_ROW sqlrow;void connection(const char* host, const char* user, const char* password, const char* database) {	mysql_init(&conn); // 注意取地址符&	if (mysql_real_connect(&conn, host, user, password, database, 0, NULL, 0)) {		printf("Connection success!/n");	} else {		fprintf(stderr, "Connection failed!/n");		if (mysql_errno(&conn)) {			fprintf(stderr, "Connection error %d: %s/n", mysql_errno(&conn), mysql_error(&conn));		}		exit(EXIT_FAILURE);	}}void display_row() {	unsigned int field_count = mysql_field_count(&conn);	int i = 0;	while (i < field_count) {		if (sqlrow[i]) printf("%s ", sqlrow[i]);		else printf("NULL");		i++;	}	printf("/n");}void display_header() {	MYSQL_FIELD *field_ptr;	printf("Column details:/n");	while ((field_ptr = mysql_fetch_field(res_ptr)) != NULL) {		printf("/t Name: %s/n", field_ptr->name);			printf("/t Table: %s/n", field_ptr->table);			printf("/t Type: ");		if (IS_NUM(field_ptr->type)) {			printf("Numeric field/n");			} else {			switch(field_ptr->type) {				case FIELD_TYPE_VAR_STRING:					printf("VARCHAR/n");					break;				case FIELD_TYPE_LONG:					printf("LONG");					break;				default:					printf("Type is %d, check in msyql_com.h/n", field_ptr->type);			}			}		printf("/t Max width %ld/n", field_ptr->length);		if (field_ptr->flags & AUTO_INCREMENT_FLAG)			printf("/t Auto increments/n");		printf("/n");	}}int main (int argc, char *argv[]) {	connection("localhost", "root", "shuang", "shuangde");	int res = mysql_query(&conn, "SELECT * from student");	if (res) {		fprintf(stderr, "SELECT error: %s/n", mysql_error(&conn));	} else {		res_ptr = mysql_use_result(&conn);		if (res_ptr) {			int first = 1;			while ((sqlrow = mysql_fetch_row(res_ptr))) {				if (first) {					display_header();					first = 0;					}				display_row();			}			if (mysql_errno(&conn)) {				fprintf(stderr, "Retrive error: %s/n", mysql_error(&conn));			}			mysql_free_result(res_ptr);		} 	}	mysql_close(&conn);	exit(EXIT_SUCCESS);}
Copy after login
bitsCN.com
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

MySQL's Role: Databases in Web Applications MySQL's Role: Databases in Web Applications Apr 17, 2025 am 12:23 AM

The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.

How to run java code in notepad How to run java code in notepad Apr 16, 2025 pm 07:39 PM

Although Notepad cannot run Java code directly, it can be achieved by using other tools: using the command line compiler (javac) to generate a bytecode file (filename.class). Use the Java interpreter (java) to interpret bytecode, execute the code, and output the result.

What is the main purpose of Linux? What is the main purpose of Linux? Apr 16, 2025 am 12:19 AM

The main uses of Linux include: 1. Server operating system, 2. Embedded system, 3. Desktop operating system, 4. Development and testing environment. Linux excels in these areas, providing stability, security and efficient development tools.

How to check the warehouse address of git How to check the warehouse address of git Apr 17, 2025 pm 01:54 PM

To view the Git repository address, perform the following steps: 1. Open the command line and navigate to the repository directory; 2. Run the "git remote -v" command; 3. View the repository name in the output and its corresponding address.

How to set important Git configuration global properties How to set important Git configuration global properties Apr 17, 2025 pm 12:21 PM

There are many ways to customize a development environment, but the global Git configuration file is one that is most likely to be used for custom settings such as usernames, emails, preferred text editors, and remote branches. Here are the key things you need to know about global Git configuration files.

SQL and MySQL: Understanding the Relationship SQL and MySQL: Understanding the Relationship Apr 16, 2025 am 12:14 AM

The relationship between SQL and MySQL is the relationship between standard languages ​​and specific implementations. 1.SQL is a standard language used to manage and operate relational databases, allowing data addition, deletion, modification and query. 2.MySQL is a specific database management system that uses SQL as its operating language and provides efficient data storage and management.

How to set shortcut keys for sublime How to set shortcut keys for sublime Apr 16, 2025 am 09:15 AM

To set the shortcut keys for Sublime Text, follow these steps: Open the shortcut key settings file Key Bindings - User. Add shortcut key settings using the format { "keys": ["key combination"], "command": "command" }. Save changes. Reload the shortcut key settings for the changes to take effect.

vscode setting Chinese tutorial vscode setting Chinese tutorial Apr 15, 2025 pm 11:45 PM

VS Code supports Chinese settings, which can be completed by following the steps: Open the settings panel and search for "locale". Set "locale.language" to "zh-CN" (Simplified Chinese) or "zh-TW" (Traditional Chinese). Save settings and restart VS Code. The settings menu, toolbar, code prompts, and documents will be displayed in Chinese. Other language settings can also be customized, such as file tag format, entry description, and diagnostic process language.

See all articles