1, C_API

使用C语言调用MySQL数据库涉及到使用MySQL的C API.

这是一组C语言函数，提供了从C语言程序连接和操作MySQL数据库的能力。(而这个库文件是MySQL官方提供).

1.1 安装这个库

我们可以通过如下命令安装这个库.

xxxxxxxxxx
1
1
sudo apt install libmysqlclient-dev

注意1:

• 我们可以通过: locate mysql.h 找到这个库的头文件

• 我们可以通过: locate libmysqlclient.so 找到这个库的动态库文件

注意2:

• 在调用函数之前，需要包含头文件<mysql/mysql.h>

• 在生成可执行程序的链接阶段时，需要加入链接选项 -lmysqlclient

1.2 使用流程

1.2.1 初始化连接

使用mysql_init()初始化连接:

xxxxxxxxxx
5
1
MYSQL *mysql_init(
2
MYSQL *mysql // 一个指向MYSQL结构的指针,通常传入NULL，函数会为你分配一个新的MYSQL结构。
3
            // 如果你已经有一个MYSQL结构的空间，也可以传入该结构的指针，函数将在该结构上进行初始化。
4
);
5
// 返回值: 成功时，返回一个指向MYSQL结构的指针。如果内存分配失败，返回NULL。

ps: mysql_init函数线程不安全: 所以当在一个进程中的多个线程中准备同时创建多个数据库连接的时候, 建议先加锁(connect之后再解锁).

1.2.2 建立连接

使用mysql_real_connect()建立到MySQL服务器的连接:

xxxxxxxxxx
11
1
MYSQL *mysql_real_connect(
2
   MYSQL *mysql,//指向MYSQL结构的指针
3
   const char *host,//数据库服务器的主机名或IP地址(传递NULL或"localhost"将尝试连接到本地机器上的服务器。)
4
   const char *user,//用于登录数据库的用户名
5
   const char *passwd,//与用户名相对应的密码 
6
   const char *db,//要连接的数据库名
7
   unsigned int port,//数据库服务器的端口号。如果指定为0，将使用默认端口（通常是3306）
8
   const char *unix_socket,//连接本地MySQL服务器时Unix套接字文件路径。可以填NULL
9
   unsigned long client_flag//用于控制连接行为的标志。填0即可。
10
);
11
//成功时，返回MYSQL结构的指针。失败时，返回NULL。

1.2.3 执行SQL语句

使用mysql_query()执行SQL语句:

• 这个函数可以用来执行增删改查的SQL指令。

xxxxxxxxxx
5
1
int mysql_query(
2
   MYSQL *mysql,//建立连接的MYSQL结构的指针
3
   const char *query//要执行的SQL语句的字符串(这个SQL字符串不应以分号结尾，并且必须是以空字符结束)
4
);
5
// 返回值:成功返回0。失败返回非0值

1.2.4 处理结果

对于返回数据的查询，使用mysql_store_result()和mysql_fetch_row()等函数来处理结果:

mysql_store_result:

xxxxxxxxxx
7
1
// 获取由mysql_query()函数返回的结果
2
MYSQL_RES *mysql_store_result(
3
    MYSQL *mysql//建立连接的MYSQL结构的指针
4
);
5
// 返回值:
6
//      成功：返回一个指向MYSQL_RES结构的指针，该结构代表了从服务器返回的结果集。通过这个结构，可以进一步获取查询结果
7
//      失败：如果SQL没有返回数据(eg:INSERT/UPDATE/DELETE操作,或者SELECT没有找到结果,或者其它错误)，则返回NULL

mysql_free_result:

xxxxxxxxxx
4
1
// 释放数据结构MYSQL_RES占据的内存空间
2
void mysql_free_result(
3
MYSQL_RES *result//指向MYSQL_RES结构的指针
4
);

mysql_num_rows:

xxxxxxxxxx
5
1
// SQL语句结果的行数
2
my_ulonglong mysql_num_rows(
3
MYSQL_RES *result//指向MYSQL_RES结构的指针
4
);
5
// 返回值: 结果集的行数(my_ulonglong: 是一个无符号的64位整数); 如果result是NULL函数返回0。

mysql_num_fields:

xxxxxxxxxx
5
1
// SQL语句结果的列数
2
unsigned int mysql_num_fields(
3
MYSQL_RES *result//指向MYSQL_RES结构的指针
4
);
5
// 返回值: 返回结果集中的列数; 如果result是NULL函数返回0

mysql_fetch_row:

xxxxxxxxxx
8
1
// 从结果当中取出一行
2
MYSQL_ROW mysql_fetch_row(
3
MYSQL_RES *result//指向MYSQL_RES结构的指针
4
);
5
// 返回值: 
6
//      返回MYSQL_ROW类型(一个字符串数组，每个元素对应结果集中一行的一个字段的值)。
7
//      如果字段值为NULL，相应的数组元素也将是NULL。
8
//      如果没有更多行了,或结果集为空，函数返回NULL。

1.2.5 关闭连接

使用mysql_close()关闭与MySQL的连接:

xxxxxxxxxx
3
1
void mysql_close(
2
MYSQL *mysql//建立连接的MYSQL结构的指针
3
);

1.2.6 mysql_error函数

对于MySQL的C API库, 我们需要一种检测函数出错的机制, 而mysql_error这个函数可以帮助你诊断函数，了解为什么一个特定的MySQL操作（如连接、查询等）失败。

xxxxxxxxxx
4
1
const char *mysql_error(
2
MYSQL *mysql//MYSQL结构的指针
3
);
4
// 返回值: 描述最近一次API调用失败原因

1.3 CODE

1.3.1 select

xxxxxxxxxx
35
1
#include <55header.h>
2
#include <mysql/mysql.h>
3

4
int main(int argc,char*argv[])
5
{
6
    MYSQL *conn;
7
    MYSQL_RES *result;
8
    MYSQL_ROW row;
9

10
    conn = mysql_init(NULL);
11
    if (!mysql_real_connect(conn, "localhost", "root", "zs123456", "test2", 0, NULL, 0)) {
12
        printf( "%s\n", mysql_error(conn));
13
        return 1;
14
    }
15
    mysql_set_character_set(conn, "utf8mb4");
16
    
17
    if (mysql_query(conn, "SELECT * FROM student")) {
18
        printf("%s\n", mysql_error(conn));
19
        return 1;
20
    }
21

22
    result = mysql_store_result(conn);
23

24
    while ((row = mysql_fetch_row(result))) {
25
        for(int i=0; i<mysql_num_fields(result); i++){
26
            printf("%s \t", row[i]);
27
        }
28
        printf(" \n");
29
    }
30

31
    mysql_free_result(result);
32
    mysql_close(conn);
33

34
    return 0;
35
}

1.3.2 update

xxxxxxxxxx
26
1
#include <55header.h>
2
#include <mysql/mysql.h>
3

4
int main(int argc,char*argv[])
5
{
6
    MYSQL *conn;
7
    MYSQL_RES *result;
8

9
    conn = mysql_init(NULL);
10
    if (!mysql_real_connect(conn, "localhost", "root", "zs123456", "test2", 0, NULL, 0)) {
11
        printf( "%s\n", mysql_error(conn));
12
        return 1;
13
    }
14

15
    if (mysql_query(conn, "update student set motto='1223' where id=10")) {
16
        printf("%s\n", mysql_error(conn));
17
        return 1;
18
    }
19

20
    result = mysql_store_result(conn);
21

22
    mysql_free_result(result);
23
    mysql_close(conn);
24

25
    return 0;
26
}

了解sql注入问题: 项目中讲

2, 事务Transaction

理解: 记住背会 (面试问的概率比较高)

数据基础

xxxxxxxxxx
5
1
CREATE TABLE `account` (
2
`id` int(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
3
`name` varchar(10) DEFAULT NULL,
4
`money` float DEFAULT NULL
5
);

xxxxxxxxxx
5
1
insert into account values(1, "鹅城黄四郎1",  30000 );
2
insert into account values(2, "鹅城家族2",  2000 );
3
insert into account values(3, "鹅城家族3",  2000 );
4
insert into account values(4, "鹅城家族4",  2000 );
5
insert into account values(5, "张麻子",  10 );

2.1 场景

假如我们在维护一个银行的用户表

xxxxxxxxxx
2
1
// 可能在我们维护这个表的过程中, 比较常见的行为就是转账问题.
2
// 假如在某个时刻两个用户存在相互转账的需求, 那么我们在实现这个转账逻辑中, 又有可能遇到什么问题那?(宕机?SQL错误?链接失败?....) 

xxxxxxxxxx
8
1
eg:
2
update account set money = money-200 where name like '%黄四郎%';
3

4
update account set money = money-200 where name like '%家族2%';
5
update account set money = money-200 where name like '%家族3%';
6
update account set money = money-200 where name like '%家族4%';
7

8
update account set money = money+800 where name like '张麻子';

2.2 事务

事务的概念:

组成逻辑的一组SQL操作，这个操作的各个单元/语句，要么都成功执行，要么都不成功执行.

构成事务的三个操作:

开启事务;

回滚;(非必要)

提交;

xxxxxxxxxx
12
1
eg:
2
-- start transaction: -- 开启事务
3
begin; -- 开启事务
4
update account set money = money+200 where name like '%黄四郎%';
5

6
update account set money = money+200 where name like '%家族2%';
7
update account set money = money+200 where name like '%家族3%';
8
update account set money = money+200 where name like '%家族4%';
9
rollback; -- 回滚事务之后，之前所有的操作都会失效。
10

11
update account set money = money-800 where name like '张麻子';
12
commit; -- 提交事务之后，从开启事务到提交事务之间所有的操作才会生效。

注意: 开启事务之后, 要注意提交(要么rollback, 要么commit).

2.3 事务特性ACID

做了一堆sql操作: 这些sql操作我们希望他是不可拆分的

原子性atomicity

xxxxxxxxxx
1
1
一个事务, 这个事务整体的所有sql操作都要必须要被视为一个不可分割的最小单元, 整个事务中的操作要么全部提交成功, 要么全部失败回滚, 对于一个事务来说, 不可能只执行其中的一部分操作, 这就是事务的原子性.(这是从操作角度来谈失误: 操作要么都成功, 要么都失败, 不可切割)

一致性consistency

xxxxxxxxxx
1
1
数据库总是从一个一致性的状态转换到定一个一致性的状态. 如前面的例子, 所谓一致性, 就是要么扣款和增加都成功, 要么未成功发生扣款的同时也未发生金额增加, 数据(金钱的总量是要前后一致的)是一致性变化的. (从事务的结果, 数据角度来谈:  数据是从一个状态, 通过事务, 要么不变, 要么一致性的演变到另外一种状态)

隔离性isolation

xxxxxxxxxx
2
1
就是事务和事务之间的关联性.
2
--事务之间的关联, 有可能强, 也有可能关联性弱 (这是可以设置的)

持久性durability

xxxxxxxxxx
1
1
事务的持久性是指 一旦事务提交后或者回滚，事务对数据库的改变应该是永久性的。 (因为: 提交和回滚,  都会结束事务)

2.4 隔离级别

隔离级别就是为了研究这么一种情况: 存在两个数据库连接, 一个连接的事务在修改(添加/删除/修改), 另一连接中的事务在查找

2.1.0 数据的操作可能遇到的问题

场景一: 脏读(Dirty read)现象

xxxxxxxxxx
5
1
当我们在操作数据库, 处理一个用户的转账动作的时候: 按照正常逻辑分析, 我们在这个转账过程中总共有两个行为, 第一是扣款方扣款, 第二是收款方收款.
2

3
我们要求在这个转账过程中, 数据保持一致性(钱不能莫名其妙丢失), 所以我们需要开启事务, 在一个事务中处理这两个动作(扣款和收款), 以保证数据操作结果正确.
4

5
当我们在Java代码中启动一个线程A来处理这个转账过程, 首先线程A开始事务, A开启事务之后, A让扣款方发生扣款, (但是在这一时刻A还没有来的急让收款方收款)此时有一个线程B在操作数据的时候, 发现了线程A还没有来的急提交的扣款结果(即在A未提交事务的情况下,对数据的修改/扣款, 已经被B感知到了).

一个事务读取到了另外一个事务还没提交的数据:即脏读(读到了脏数据)

场景二: 不可重复读(nonrepeatable read)

xxxxxxxxxx
5
1
如果同上的转账行为中.
2

3
当我们在Java代码中启动一个线程C用来统计汇总用户信息,  C线程开启事务, C线程在开启事务之后,(提交事务之前),  读取到的数据可能发生改变(比如: 在C事务中第一次读取zs有1000元, 第二次读取zs的时候有800元, 事务C还未结束提交, 就已经发现了自己操作的数据被别的非C事务的地方修改)
4

5
原因：在同一个事务内，读取到了另一个事务修改的数据(可能另一个事务已经提交, 也可能没提交)。

在同一个事务内，针对同一个数据，前后读取的数据不一样: 即不可重复读

场景三: 虚幻读/幻读/幻影读(phantom read)

xxxxxxxxxx
5
1
如果同上的转账行为中.
2

3
当我们在Java代码中启动一个线程D用来统计汇总用户信息, D线程开启事务, D线程在开启事务之后,(提交事务之前),  读取到的数据内容一直没有发生改变, 读取到的数据条目数可能发生改变.(比如: 在D事务中第一次读取zs有1000元, 第二次读取zs的时候还是1000元(即使另一个事务试图修改了这个数据); 但是在D事务中, 有可能刚开始读取到的数据条目数是5条, 在这个D事务中过了一段时间之后, 有可能读到的数据是6条(也就意味着D事务中可能读取到别的事务插入的数据))
4

5
原因：在一个事务内，读取到了别的事务插入（删除）的数据

在一个事务内，任何一条数据的内容前后读取一致, 但是数据条数前后不一定一致。

查看和修改隔离级别:

xxxxxxxxxx
10
1
SELECT @@GLOBAL.TX_ISOLATION; -- 整个数据的设置
2
SELECT @@SESSION.TX_ISOLATION; -- 一个连接的隔离级别
3
SET GLOBAL TRANSACTION ISOLATION LEVEL [SERIALIZABLE | REPEATABLE READ| ...];
4
SET SESSION TRANSACTION ISOLATION LEVEL [SERIALIZABLE | REPEATABLE READ| ...];
5

6
eg:
7
    select @@global.tx_isolation;
8
    select @@session.tx_isolation;
9
    set global transaction isolation level read uncommitted;
10
    set global transaction isolation level repeatable read;

查看隔离级别

修改隔离级别

2.1.1 读未提交

读未提交(READ UNCOMMITTED): 在READ UNCOMMITTED级别,

事务中的修改, 即使没有提交, 对其他事务也都是可见的.(在一个事物中, 读取到另外一个事物的未提交的数据)

会产生: 脏读, 不可重复读, 幻读

xxxxxxxxxx
3
1
set global transaction isolation level read uncommitted;
2
select @@global.tx_isolation;
3
-- select @@global.transaction_isolation;

修改隔离级别

操作

xxxxxxxxxx
5
1
select * from account;
2
start transaction;
3
update account set money = money-200 where id = 1;
4
rollback;
5
commit;

2.1.2 读已提交

读已提交(READ COMMITTED): 在READ COMMITTED级别,

一个事务开始之后, 只能看到自己的修改和别的已经提交的事务的修改.

(即: 一个事务在提交之前, 所作的修改对其它事务是不可见的)

只能在当前事物中读到别的连接已经提交的事务( 别的连接没有提交的事务, 就读不到), 所以不会引发脏读问题, 但是引发了不可重复读, 幻读问题.

xxxxxxxxxx
3
1
set global transaction isolation level read committed;
2
select @@global.tx_isolation;
3
-- select @@global.transaction_isolation;

修改隔离级别

操作

xxxxxxxxxx
5
1
select * from account;
2
start transaction;
3
update account set money = money-200 where id = 1;
4
update account set money = money+200 where id = 5;
5
commit;

2.1.3 可重复读

可重复读(REPEATABLE READ): 在REPEATABLE READ级别, 一个事务开始之后, 保证多次读取某同样的记录结果保持一致.

MySQL的默认隔离级别就是可重复读.

(但是要注意的是, 可重复读存在幻读问题; 而MySQL的InnoDB存储引擎解决了部分幻读问题/可并没有完全解决幻读问题)

不会再有脏读问题, 不会再有不可重复读问题, 演示结果上看没有幻读问题 (实际上在MySQl的可重复读隔离级别上是存在幻读的问题)

xxxxxxxxxx
3
1
set global transaction isolation level repeatable read;
2
select @@global.tx_isolation;
3
-- select @@global.transaction_isolation;

修改隔离级别

操作

xxxxxxxxxx
10
1
select * from account;
2
start transaction;
3
update account set money = money-200 where id = 1;
4
update account set money = money+200 where id = 5;
5
commit;
6

7
select * from account for update; (必须在事务中使用才有意义: 在这个sql语句中 for update的作用具有查看表的当前所有真实数据, 而非当前事务的的能看到的数据, 并且给查出来的所有数据加一个排他锁)
8
或者两边同时插入同一主键内容, 会有相互感知.
9

10
mysql中以Innodb为表驱动引擎的表, 在可重复读的级别上, 解决了一部分的幻读问题, 但是并没有完全解决. 而它解决一部分幻读问题的手段是通过和锁机制的mvcc机制实现的.

2.1.4 串行化/序列化

串行化(SERIALIZABLE): 在SERIALIZABLE级别，它强制事务串行执行,SERIALIZABLE级别会在读取的每一行数据上都加上锁, 以保证事务的先后顺序，那么就不存在多个事务同时执行情况(也就没有上面的安全性问题).

xxxxxxxxxx
2
1
set global transaction isolation level serializable;
2
select @@global.tx_isolation; 

修改隔离级别

操作

xxxxxxxxxx
4
1
select * from account;
2
start transaction;
3
update account set money = money+200 where id = 5;
4
commit;

对比:

事务隔离级别对比

隔离级别	脏读	不可重复读	虚幻读
读未提交（read uncommitted）	√	√	√
读已提交（read committed）	X	√	√
可重复读（repeatable read）	X	X	√-×
串行化（serializable）	X	X	X

xxxxxxxxxx
38
1
select * from account;
2

3
-- 开启事务
4
begin;
5
update account set money = money-200 where id =1;
6
update account set money = money+200 where id =5;
7
select * from account;
8
-- 回滚
9
rollback;
10

11

12
select * from account;
13

14
-- 开启事务
15
begin;
16
update account set money = money-200 where id =1;
17
update account set money = money+200 where id =5;
18
update account set money = money-200 where id =2;
19
update account set money = money+200 where id =5;
20
update account set money = money-200 where id =3;
21
update account set money = money+200 where id =5;
22
update account set money = money-200 where id =4;
23
update account set money = money+200 where id =5;
24

25
select * from account;
26

27
-- 提交事务
28
commit;
29

30
select * from account;
31

32

33
update account set money=money+200 where id =1;
34
update account set money=money+200 where id =2;
35
update account set money=money+200 where id =3;
36
update account set money=money+200 where id =4;
37
update account set money=money-800 where id =5;
38