Posted on by John

Lge的数据库操作

Lge的数据库组件是框架的核心组件之一，该数据库组件的特点有：

1、底层基于PDO扩展，支持所有主流的数据库管理系统，并通过MySQL、PostgreSQL、Oracle、SQLServer及SQLite的测试使用；

2、数据库底层天然支持主从读写分离，只需通过配置文件简单配置项即可支持对主从数据库集群的操作；

3、数据库单例对象管理，在同一请求处理进程中，多次数据库操作只会保留同一份数据库链接；

4、数据库底层统一采用SQL预处理操作，防止SQL注入，增强数据库安全性；

5、方便灵活的CURD操作方法，单表封装对象；

6、支持数据库超时重连机制及数据表前缀；

一、数据库的配置

1、通用数据库配置

配置项 default 表示默认的数据库配置。

1
2
3
4
5
6
7
8
9
10
11
'default' => array(
    'host'     => '127.0.0.1', // 主机地址(使用IP防止DNS解析)
    'user'     => 'root',      // 账号
    'pass'     => '',          // 密码
    'port'     => '3306',      // 数据库端口
    'type'     => 'mysql',     // 数据库类型(mysql|pgsql|sqlite|oracle|mssql)
    'charset'  => 'utf8',      // 数据库编码
    'prefix'   => '',          // 表名前缀，这个时候缩略表名应当以'_'符号开头
    'database' => '',          // 数据库名称
    'linkinfo' => '',          // 可自定义PDO数据库连接信息，主要用于操作type项不支持，但PDO支持的数据库类型
),

 2、主从集群数据库配置

通过应用层实现最小成本的数据库负载均衡访问，配置项中的 priority 参数可指定数据库的使用优先级，配置项名称 master_slave 是示例名称，可自定义。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
/**
* 天然支持主从复制模式，当配置项中包含master和slave字段时，数据库操作自动切换为主从模式，不会读取该配置项内的其他配置.
* 程序在执行数据库操作时会判断优先级，优先级计算方式：配置项值/总配置项值.
*/
'master_slave' => array(
    'master'  => array(
        array(
            'host'     => '127.0.0.1',
            'user'     => 'root',
            'pass'     => '',
            'port'     => '3306',
            'type'     => 'mysql',
            'charset'  => 'utf8',
            'prefix'   => '',
            'database' => '',
            'priority' => 100,
            'linkinfo' => '',
        ),
        array(
            'host'     => '127.0.0.1',
            'user'     => 'root',
            'pass'     => '',
            'port'     => '3306',
            'type'     => 'mysql',
            'charset'  => 'utf8',
            'prefix'   => '',
            'database' => '',
            'priority' => 100,
            'linkinfo' => '',
        ),
    ),
    'slave'   => array(
        array(
            'host'     => '127.0.0.1',
            'user'     => 'root',
            'pass'     => '',
            'port'     => '3306',
            'type'     => 'mysql',
            'charset'  => 'utf8',
            'prefix'   => '',
            'database' => '',
            'priority' => 100,
            'linkinfo' => '',
        ),
        array(
            'host'     => '127.0.0.1',
            'user'     => 'root',
            'pass'     => '',
            'port'     => '3306',
            'type'     => 'mysql',
            'charset'  => 'utf8',
            'prefix'   => '',
            'database' => '',
            'priority' => 100,
            'linkinfo' => '',
        ),
    ),
),

二、数据库操作API

数据库的API有很多，包括配置API以及操作API，具体请查看底层数据库封装类和数据表封装类(Model)。

三、数据表封装类操作(Model)

使用以下方法即可获取一个表的model类对象：

1
2
3
4
5
6
7
8
9
/**
* 获得对象的方法，请使用该方法获得对象.
*
* @param string $table        表名称.
* @param string $dbConfigName 数据库配置名称.
*
* @return BaseModelTable
*/
Instance::table($table, $dbConfigName = 'default')

数据库封装类对象的常用操作方法如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
/**
* 根据条件查询记录数。
* @param  mixed $condition 条件.
* @param  mixed $groupBy   分组.
* @param  mixed $fields    用于获得数量用到的字段.
*
* @return int
*/
public function getCount($condition = array(), $groupBy = array(), $fields = array())
 
/**
*
* 查询记录。
*
* @param mixed  $fields          查询字段.
* @param mixed  $conditions      查询条件.
* @param mixed  $groupBy         分组.
* @param mixed  $orderBy         排序.
* @param int    $first           分页起始.
* @param int    $limit           查询条数.
* @param string $arrayKey        作为返回数组的主键的字段名.
*
* @return array
*/
public function getAll($fields     = array('*'),
                       $conditions = array(),
                       $groupBy    = array(),
                       $orderBy    = array(),
                       $first      = 0,
                       $limit      = 0,
                       $arrayKey   = null)
 
/**
* 根据条件获得一条记录。
*
* @param mixed  $fields          查询字段.
* @param mixed  $conditions      查询条件.
* @param mixed  $groupBy         分组.
* @param mixed  $orderBy         排序.
*
* @return array
*/
public function getOne($fields     = array('*'),
                       $conditions = array(),
                       $groupBy    = array(),
                       $orderBy    = array())
 
/**
* 根据条件获得一条字段的值.
*
* @param string  $field          查询字段.
* @param mixed   $conditions     查询条件.
* @param mixed   $groupBy        分组.
* @param mixed   $orderBy        排序.
*
* @return string
*/
public function getValue($field,
                         $conditions = array(),
                         $groupBy    = array(),
                         $orderBy    = array())
 
/**
* 添加记录，并返回添加记录的ID，失败返回false.
* 注意：如果主键为非自增ID，那么成功会返回0，因此判断返回值是否为false来判断是否执行成功.
*
* @param  array   $data          写入的数据.
* @param  mixed   $option        选项(replace:同记录替换, update:同记录更新, ignore:同记录忽略, 默认直接写入)
* @param  boolean $getInsertedId 获取插入的主键ID(存在自动增加主键时有用).
*
* @return int|false
*/
public function insert(array $data, $option = '', $getInsertedId = true)
 
/**
* 批量添加记录，并返回添加记录的ID，失败返回false.
*
* @param  array   $list
* @param  integer $perCount
* @param  mixed   $option   选项(replace:同记录替换, update:同记录更新, ignore:同记录忽略, 默认直接写入)
*
* @return boolean
*/
public function batchInsert(array $list, $perCount = 10, $option = '')
 
/**
* 保存记录(如果数据中存在主键或者唯一索引，那么执行更新，否则执行插入).
*
* @param  array   $data          写入的数据.
* @param  boolean $getInsertedId 获取插入的主键ID(存在自动增加主键时有用).
*
* @return int|false
*/
public function save(array $data, $getInsertedId = false)
 
/**
* 批量保存记录(如果数据中存在主键或者唯一索引，那么执行更新，否则执行插入).
*
* @param  array   $list     数据列表.
* @param  integer $perCount 批量写入的每批大小.
*
* @return boolean
*/
public function batchSave(array $list, $perCount = 10)
 
/**
* 更新记录.
*
* @param  mixed  $data       更新数据.
* @param  mixed  $conditions 更新条件.
*
* @return boolean
*/
public function update($data, $conditions)
 
/**
* 删除记录.
*
* @param  mixed  $conditions 更新条件.
*
* @return boolean
*/
public function delete($conditions = array())

三、数据表封装类操作示例

1、添加数据

1
2
3
4
5
6
7
$data = array(
    'uid'     => 2,
    'name'    => 'john',
    'age'     => 29,
    'gender'  => 1,
);
Instance::table('lge_user')->insert($data);

insert方法有多个参数，例如可以决定是否返回自增ID，是否执行检查更新等。

默认返回值将会返回写入的自增ID值，如果没有自增ID那么将会返回空字符串，写入失败返回false。

批量添加数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
$list = array(
    array(
        'uid'     => 1,
        'name'    => 'john',
        'salary'  => 5000,
        'number'  => '1001',
    ),
    array(
        'uid'     => 2,
        'name'    => 'smith',
        'salary'  => 6000,
        'number'  => '1002',
    ),
);
Instance::table('company')->batchInsert($list);

2、修改数据

1
$result = Instance::table('company')->update(array('salary' => 8000), array('uid' => 1));

或者

1
$result = Instance::table('company')->update(array('salary' => 8000), array('salary > ? and salary < ?', 5000, 7000));

或者

1
$result = Instance::table('company')->update(array('salary' => 8000), "number=1001");

3、删除数据

1
Instance::table('company')->delete(array('uid' => 1));

或者

1
Instance::table('company')->delete("uid=1");

或者

1
Instance::table('company')->delete(array('uid=?', $_GET['uid']));

 4、保存数据

保存操作是insert操作的一个封装，主要的逻辑是当需要写入的数据中存在主键或者唯一索引的时候则更新相对应的记录，否则写入一条新纪录。

1
2
3
4
5
6
7
$data = array(
    'uid'     => 2,
    'name'    => 'john guo',
    'age'     => 30,
    'gender'  => 1,
);
Instance::table('lge_user')->save($data);

保存数据也支持批量操作：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
$list = array(
    array(
        'uid'     => 1,
        'name'    => 'john',
        'salary'  => 5000,
        'number'  => '1001',
    ),
    array(
        'uid'     => 2,
        'name'    => 'smith',
        'salary'  => 6000,
        'number'  => '1002',
    ),
);
Instance::table('company')->batchSave($list);

 5、查询数据

查询操作是项目中使用得比较频繁的操作，查询数据总共有四个封装方法：

1)、getAll 用于查询记录列表(数组)，默认索引值从0开始，列表中的每一项是数据表中的一条记录，并且可以在最后一个参数中指定通过某个字段构造成关联数组返回；
2)、getOne 用于查询单条记录，返回记录的关联数组；
3)、getValue 用于查询一个字段，返回的是对应的字段值；
4)、getCount 用于查询符合条件的记录总数，一般用于分页；

以下以 getAll 方法为例展示查询数据的方式：

1
2
3
4
5
// 获取年龄为18岁的所有妙龄女性
Instance::table('lge_user')->getAll('*', array(
    'age'    => 18,
    'gender' => 0,
));

 更完整的查询示例如下：

1
2
3
4
5
6
7
8
$fields    = 'uid,name';
$condition = array("gender=? and age>? and uid in(1,2,3) and name like '%john%'", $_GET['gender'], $_GET['age']);
$groupby   = null;
$orderby   = 'uid desc';
$start     = 0;
$limit     = 100;
$arrayKey  = 'uid';
Instance::table('lge_user')->getAll($fields, $condition, $groupby, $orderby, $start, $limit, $arrayKey);

这个查询基本演示了所有的查询参数：

(1)、查询可指定返回的结果集字段；

(2)、使用了预处理语句(由于查询条件是通过客户端传递的，所以通过预处理能提高SQL执行的安全性)；

(3)、展示了无法使用预处理的 in 和 like 操作，因此只能使用字符串作为输入；

(4)、分页操作使用$start和$limit进行控制，如果$limit不传递或者传递为0(默认为0)，那么获取全部结果集返回；

(5)、通过最后一个字段指定关联数组的键名字段，这里返回的结果中，索引将不在从0开始，而是以用户uid作为索引的关联数组；

6、联表查询

数据表封装类操作也支持联表操作，示例如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
// 查询指定某一时间段高于某个价格的用户信息及用户地址
$tables    = array(
    'user u',
    'left join order o on(o.uid=u.uid)',
    'left join order_address oa on(oa.order_id=o.id)',
);
$fields    = 'u.uid,u.nickname,oa.order_id,oa.address';
$condition = array("o.order_price>? and o.create_time between ? and ?", $_GET['price'], $_GET['start_time'], $_GET['end_time']);
$groupby   = null;
$orderby   = 'oa.order_id desc';
$start     = 0;
$limit     = 100;
$arrayKey  = 'oa.order_id';
Instance::table($tables)->getAll($fields, $condition, $groupby, $orderby, $start, $limit, $arrayKey);

1
2
3
4
5
// 查询某个区域所有小于某个年龄的用户
$tables    = "user u, user_address ua";
$fields    = 'u.uid,u.nickname,ua.address';
$condition = array("u.uid=ua.uid and u.age<:max_age and ua.address like '四川省成都市%'", array(':max_age' => $_GET['age']));
Instance::table($tables)->getAll($fields, $condition);

 7、预处理查询

上面我们演示了基本的预处理操作，在查询条件中使用占位符?表示条件参数，并在数组的第二个参数开始按照顺序写入传递的参数值。这种预处理查询条件是比较常见的方式，当然Lge也支持带名称的预处理，例如：

1
2
3
4
5
6
Instance::table('lge_user')->getValue('nickname', array(
    "age=:age and gender=:gender", array(
        ':gender' => $_GET['gender'],
        ':age'    => $_GET['age'],
    )
));

这种带名称的预处理参数传递和预处理参数的传入顺序便没有任何关系，只需要预处理参数的名称对应即可。

8、关于查询条件

查询条件($condition参数)支持多种方式，在上面的示例代码中也展示了，这里总结一下：

1)、(不推荐) 查询支持直接使用字符串的形式，这种情况下字符串条件容易产生SQL拼接的情况，如果查询条件包含客户端提交的参数且对提交参数处理不严谨的时候，存在一定的安全隐患，但从灵活性折衷考虑，保留了这一查询形式；

2)、(推荐) 查询支持两种预处理传递查询条件的形式，一种是使用占位符?，一种是使用预处理参数名称占位，但无论使用哪种形式，预处理查询都将比直接使用字符串更加安全；

3)、in 和 like 查询不支持预处理，因此这两种查询只能使用字符串参数形式，这个时候请严格处理用于查询的参数值(转义处理，addslashes)；

四、底层数据库封装类操作

由于Model类的存在，底层数据库封装类很少会直接使用到，除非是一些非常复杂的SQL操作，或者事务处理，这个时候我们可以直接通过db对象来进行操作。

因此，我们首先需要获取一个db对象：

1
$db = Instance::database();

参数是我们的数据库配置项名称，默认为default。

获取到db对象之后，我们便可以执行原始的SQL语句，如下：

1
$db->query("SELECT * FROM user WHERE name='John'");

或者也可以使用db对象的数据查询封装方法：

1
$db->getOne('SELECT * FROM user WHERE name=?', array('john'));

另外很重要的一点是，有了db对象我们可以在db连接的数据库上使用事务操作：

1
2
3
4
5
6
7
8
// 开启事务
$db->beginTransaction();
 
// 事务回滚
$db->rollBack();
 
// 事务提交
$db->commit();

五、其他

1、全局表前缀

通过Model类操作数据库的话支持全局表前缀处理，在配置文件中的 prefix_ 字段处配置好表前缀，在访问的时候可以直接忽略表前缀使用下划线开头的表名来进行操作。

1
2
3
4
5
6
7
8
9
10
11
'default' => array(
    'host'     => '127.0.0.1', // 主机地址(使用IP防止DNS解析)
    'user'     => 'root',      // 账号
    'pass'     => '',          // 密码
    'port'     => '3306',      // 数据库端口
    'type'     => 'mysql',     // 数据库类型(mysql|pgsql|sqlite|oracle|mssql)
    'charset'  => 'utf8',      // 数据库编码
    'prefix'   => '',          // 表名前缀，这个时候缩略表名应当以'_'符号开头
    'database' => '',          // 数据库名称
    'linkinfo' => '',          // 可自定义PDO数据库连接信息，主要用于操作type项不支持，但PDO支持的数据库类型
),

例如：

lge_user 表可以直接通过lge_user完整表名进行访问，也可以通过 _user 缩略表名进行访问；

lge_user_address 表可以直接通过lge_user_address完整表名访问，也可以通过 _user_address 缩略表名进行访问；

1
2
3
4
5
6
7
8
9
10
11
12
13
14
/**
* 表前缀示例
*/
// 1、单表查询
Instance::table('_user')->getOne('*', 'uid=1');
 
// 2、联表查询
$tables    = array(
    '_user u',
    'left join _user_address ua on(ua.uid=u.uid)',
);
$fields    = 'u.uid,u.nickname,ua.address';
$condition = array('uid' => $_GET['uid']);
Instance::table($tables)->getAll($fields, $condition);

 

2、查看已执行的SQL语句

在请求流程末尾查看当前请求流程中执行的所有SQL语句以及执行时间，可以这样来查看：

1
2
3
$db   = Instance::database();
$sqls = $db->getQueriedSqls();
print_r($sqls);

打印出的结果示例如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
Array
(
    [0] => Array
        (
            [sql] => SELECT id,content,exten_port,extra,create_time,phone,status,result,channel,type FROM lge_sms ORDER BY id desc LIMIT 0, 10
            [mode] => slave
            [cost] => 0.000855
            [link] => mysql:host=127.0.0.1;port=3306;dbname=lge_playard;charset=utf8
            [time] => 1492744523.9501
            [method] => query
            [params] => Array
                (
                )
 
        )
 
    [1] => Array
        (
            [sql] => SELECT COUNT(1) FROM lge_sms  
            [mode] => slave
            [cost] => 0.000062
            [link] => mysql:host=127.0.0.1;port=3306;dbname=lge_playard;charset=utf8
            [time] => 1492744523.9503
            [method] => query
            [params] => Array
                (
                )
 
        )
 
    [2] => Array
        (
            [sql] => SELECT group_key FROM lge_user_group WHERE id=? LIMIT 0, 1
            [mode] => slave
            [cost] => 0.000198
            [link] => mysql:host=127.0.0.1;port=3306;dbname=lge_playard;charset=utf8
            [time] => 1492744523.9514
            [method] => _doPrepareExecute
            [params] => Array
                (
                    [0] => 1
                )
 
        )
 
)

 其中打印出的SQL中带有?符号的表示是预处理语句，其参数在params字段中。 

 

 

 

 

 

Leave a Reply

Your email address will not be published.