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函数为:

1

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

Copy after login

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

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

检查受查询影响的行数:

1

my_ulonglong mysql_affected_rows(MYSQL *connection);

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

例子

数据库中有一个student表

1

CREATE TABLE student (      student_no varchar(12) NOT NULL PRIMARY KEY,        student_name varchar(12) NOT NULL       );

Copy after login
/

增、删、改代码:

1

#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

1

// 相关函数:// 这是在成功调用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

示例代码:

1

#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更好。

处理返回的数据

1

// 相关函数和定义:// 返回结果集中的字段(列)数目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

代码示例:

1

#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

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

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)

deepseek web version entrance deepseek official website entrance deepseek web version entrance deepseek official website entrance Feb 19, 2025 pm 04:54 PM

DeepSeek is a powerful intelligent search and analysis tool that provides two access methods: web version and official website. The web version is convenient and efficient, and can be used without installation; the official website provides comprehensive product information, download resources and support services. Whether individuals or corporate users, they can easily obtain and analyze massive data through DeepSeek to improve work efficiency, assist decision-making and promote innovation.

How to fix mysql_native_password not loaded errors on MySQL 8.4 How to fix mysql_native_password not loaded errors on MySQL 8.4 Dec 09, 2024 am 11:42 AM

One of the major changes introduced in MySQL 8.4 (the latest LTS release as of 2024) is that the &quot;MySQL Native Password&quot; plugin is no longer enabled by default. Further, MySQL 9.0 removes this plugin completely. This change affects PHP and other app

How to install deepseek How to install deepseek Feb 19, 2025 pm 05:48 PM

There are many ways to install DeepSeek, including: compile from source (for experienced developers) using precompiled packages (for Windows users) using Docker containers (for most convenient, no need to worry about compatibility) No matter which method you choose, Please read the official documents carefully and prepare them fully to avoid unnecessary trouble.

BITGet official website installation (2025 beginner's guide) BITGet official website installation (2025 beginner's guide) Feb 21, 2025 pm 08:42 PM

BITGet is a cryptocurrency exchange that provides a variety of trading services including spot trading, contract trading and derivatives. Founded in 2018, the exchange is headquartered in Singapore and is committed to providing users with a safe and reliable trading platform. BITGet offers a variety of trading pairs, including BTC/USDT, ETH/USDT and XRP/USDT. Additionally, the exchange has a reputation for security and liquidity and offers a variety of features such as premium order types, leveraged trading and 24/7 customer support.

Ouyi okx installation package is directly included Ouyi okx installation package is directly included Feb 21, 2025 pm 08:00 PM

Ouyi OKX, the world's leading digital asset exchange, has now launched an official installation package to provide a safe and convenient trading experience. The OKX installation package of Ouyi does not need to be accessed through a browser. It can directly install independent applications on the device, creating a stable and efficient trading platform for users. The installation process is simple and easy to understand. Users only need to download the latest version of the installation package and follow the prompts to complete the installation step by step.

Get the gate.io installation package for free Get the gate.io installation package for free Feb 21, 2025 pm 08:21 PM

Gate.io is a popular cryptocurrency exchange that users can use by downloading its installation package and installing it on their devices. The steps to obtain the installation package are as follows: Visit the official website of Gate.io, click "Download", select the corresponding operating system (Windows, Mac or Linux), and download the installation package to your computer. It is recommended to temporarily disable antivirus software or firewall during installation to ensure smooth installation. After completion, the user needs to create a Gate.io account to start using it.

Ouyi Exchange Download Official Portal Ouyi Exchange Download Official Portal Feb 21, 2025 pm 07:51 PM

Ouyi, also known as OKX, is a world-leading cryptocurrency trading platform. The article provides a download portal for Ouyi's official installation package, which facilitates users to install Ouyi client on different devices. This installation package supports Windows, Mac, Android and iOS systems. Users can choose the corresponding version to download according to their device type. After the installation is completed, users can register or log in to the Ouyi account, start trading cryptocurrencies and enjoy other services provided by the platform.

gate.io official website registration installation package link gate.io official website registration installation package link Feb 21, 2025 pm 08:15 PM

Gate.io is a highly acclaimed cryptocurrency trading platform known for its extensive token selection, low transaction fees and a user-friendly interface. With its advanced security features and excellent customer service, Gate.io provides traders with a reliable and convenient cryptocurrency trading environment. If you want to join Gate.io, please click the link provided to download the official registration installation package to start your cryptocurrency trading journey.

See all articles