PHPackages                             zhangzhaowy/php-mysql - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. zhangzhaowy/php-mysql

ActiveLibrary[Database & ORM](/categories/database)

zhangzhaowy/php-mysql
=====================

php mysql

1.0.0(6y ago)019MITPHPPHP ^5.4|^7.0

Since Sep 29Pushed 6y ago1 watchersCompare

[ Source](https://github.com/zhangzhaowy/php-mysql)[ Packagist](https://packagist.org/packages/zhangzhaowy/php-mysql)[ RSS](/packages/zhangzhaowy-php-mysql/feed)WikiDiscussions master Synced yesterday

READMEChangelog (1)DependenciesVersions (3)Used By (0)

MySQLi 封装类

---

声明
==

[](#声明)

此软件是为了满足个人使用习惯而在[ThingEngineer/PHP-MySQLi-Database-Class](https://github.com/ThingEngineer/PHP-MySQLi-Database-Class)的基础上开发的. 如果你想学习或研究MYSQL，可以去[ThingEngineer/PHP-MySQLi-Database-Class](https://github.com/ThingEngineer/PHP-MySQLi-Database-Class).

环境要求
====

[](#环境要求)

PHP 5.4+ and PDO extension installed

安装
==

[](#安装)

使用之前需要先下载或安装到自己的项目

composer 安装

```
composer require zhangzhaowy/php-mysql:dev-master

```

加载
==

[](#加载)

引入类文件

```
require_once ('Db.php');
```

或者 命名空间引入类文件

```
use Zhangzhaowy\Phpmysql\Db;
```

初始化
===

[](#初始化)

默认 字符集utf8，端口3306:

```
$db = new Db('host', 'username', 'password', 'databaseName');
```

还可以用数组来初始化:

```
$db = new Db([
    'host' => 'host',
    'username' => 'username',
    'password' => 'password',
    'db'=> 'databaseName',
    'port' => 3306,
    'prefix' => 'my_',
    'charset' => 'utf8']);
```

表前缀、字符集、端口参数都是可选的。

也支持mysqli对象:

```
$mysqli = new mysqli('host', 'username', 'password', 'databaseName');
$db = new Db($mysqli);
```

如果表有前缀，我们可以定义表前缀:

```
$db->setPrefix ('my_');
```

如果MySQL链接断开，会自动重连一次。 禁用方法：

```
$db->autoReconnect = false;
```

如果想使用已经创建过的数据库链接：

```
// 创建过的Mysql链接
$db = new Db('host', 'username', 'password', 'databaseName');
...
...
// 要启用创建过的Mysql链接
$db = Db::getInstance();
...

```

基本操作
====

[](#基本操作)

增加
--

[](#增加)

```
$data = [
    "login" => "admin",
    "firstName" => "John",
    "lastName" => 'Doe'
];
$id = $db->table('users')->insert($data);
if($id) {
    echo 'user was created. Id=' . $id;
} else {
    echo 'insert failed: ' . $db->getLastError();
}
```

在Insert中使用on duplicate key update

```
$data = [
    "login" => "admin",
    "firstName" => "John",
    "lastName" => 'Doe',
    "createdAt" => $db->now(),
    "updatedAt" => $db->now(),
];
$updateColumns = ["updatedAt"];
$lastInsertId = "id";
$db->onDuplicate($updateColumns, $lastInsertId);
$id = $db->table('users')->insert($data);
```

替换
--

[](#替换)

[replace()](https://dev.mysql.com/doc/refman/5.0/en/replace.html) 同 insert() 方法一样;

更新
--

[](#更新)

可以使用where()、limit()等联合查询，详解见[查询](#%E6%9F%A5%E8%AF%A2)

```
$data = [
    'firstName' => 'Bobby',
    'lastName' => 'Tables',
];
$db->where('id', 1)->limit(1);
if ($db->table('users')->update($data)) {
    echo $db->count . ' records were updated';
} else {
    echo 'update failed: ' . $db->getLastError();
}
```

删除
--

[](#删除)

```
$db->where('id', 1);
if($db->table('users')->delete()) {
    echo 'successfully deleted';
}
```

查询
--

[](#查询)

### 获取数据

[](#获取数据)

getAll() 获取多条记录
getOne() 获取一条记录
getColumn() 获取某列数据

```
// 包含全部用户
$users = $db->from('users')->getAll();
// 包含一个用户
$users = $db->from('users')->getOne();
// 包含所有用户的id
$users = $db->from('users')->getColumn('id');
```

### From

[](#from)

定义操作表

```
$users = $db->from('users u')->getOne();
// select * from my_users u limit 1;
$users = $db->from(['users' => 'u'])->getOne();
// select * from my_users u limit 1;
```

### Select

[](#select)

定义获取列字段

```
$users = $db->from('users')->select(['id', 'name'])->getOne();
// ['id' => 1, 'name' => 'user1']
$users = $db->from('users')->select('id, name'])->getOne();
// ['id' => 1, 'name' => 'user1']
```

给列定义别名

```
$users = $db->from('users')->select('id, name AS username'])->getOne();
$users = $db->from('users')->select(['id', 'name AS username'])->getOne();
$users = $db->from('users')->select(['id', 'name' => 'username'])->getOne();
// ['id' => 1, 'username' => 'user1']
```

### Join

[](#join)

在join的条件中将表名用``括起来，会自动追加表前缀。

```
// 左联
$users = $db->from('users')->leftJoin('score', '`score`.`uid` = `users`.`id`')->getAll();
// 右联
$users = $db->from('score s')->rightJoin('users u', 'u.`id` = s.`uid`')->getAll();
// 自定义联表
$users = $db->from('users u')->join('INNER', 'score s', 'u.`id` = s.`uid`')->getAll();
// joinWhere() 第一个参数与join的表名要一致，第二个参数与where()用法一致
$users = $db->from('users u')->leftJoin('score s', 's.`uid` = u.`id`')->joinWhere('score s', ['s.active' => 1])->getAll();
```

### Where

[](#where)

```
// SELECT * FROM my_users WHERE 1=1 AND 2=2
$db->from('users')->where('1=1 AND 2=2')->getAll();
// SELECT * FROM my_users WHERE id = '1' OR id = '5'
$db->from('users')->where(['id = ? OR id = ?', [1, 5]])->getAll();
// SELECT * FROM my_users WHERE name IS NULL
$db->from('users')->where(['name'])->getAll();
$db->from('users')->where(['name', 'IS', NULL])->getAll();
// SELECT * FROM my_users WHERE name IS NOT NULL
$db->from('users')->where(['name', 'IS NOT', NULL])->getAll();
// SELECT * FROM my_users WHERE id = '1'
$db->from('users')->where(['id' => 1])->getAll();
$db->from('users')->where(['id', 1])->getAll();
// SELECT * FROM my_users WHERE id in ( '1', '2', '3' )
$db->from('users')->where(['id' => [1, 2, 3]])->getAll();
$db->from('users')->where(['id', [1, 2, 3]])->getAll();
SELECT * FROM my_users WHERE id BETWEEN '1' AND '5'
$db->from('users')->where(['id' => ['BETWEEN' => [1, 5]]])->getAll();
$db->from('users')->where(['id', ['BETWEEN' => [1, 5]]])->getAll();
$db->from('users')->where(['id', 'BETWEEN', [1, 5]])->getAll();
// SELECT * FROM my_users WHERE name like '%zhang%'
$db->from('users')->where(['name', 'like', '%zhang%'])->getAll();
// SELECT * FROM my_users WHERE id != '1'
$db->from('users')->where(['id', '!=', 1])->getAll();
// SELECT * FROM my_users WHERE id != '1' OR id != '2'
$db->from('users')->where(['id', '!=', 1])->where(['OR', 'id', '!=', 2])->getAll();
// SELECT * FROM my_users WHERE id != '1' OR ( id > 0 AND name = 'zhang' OR ( id = '1' OR name like 'zh%' ) AND age != '10' OR name in ( 'zhang', 'wang', 'li' ) )
$db->from('users')->where(['id', '!=', 1])->where(['OR', [
    'id > 0',
    ['name' => 'zhang'],
    ['OR', [
        ['id' => 1],
        ['OR', 'name', 'like', 'zh%']
    ]],
    ['age', '!=', 10],
    ['OR', 'name', 'in', ['zhang', 'wang', 'li']]
]])->getAll();
```

### Group By

[](#group-by)

```
// SELECT * FROM my_users GROUP BY id, age
$db->from('users')->groupBy('id, age')->getAll();
$db->from('users')->groupBy(['id', 'age'])->getAll();
```

### Having

[](#having)

Having 用法同 Where 用法一样

```
// SELECT * FROM my_users GROUP BY age HAVING 1=1 AND 2=2
$db->from('users')->groupBy('age')->having('1=1 AND 2=2')->getAll();
// SELECT * FROM my_users GROUP BY age HAVING age = '10'
$db->from('users')->groupBy('age')->having(['age' => '10'])->getAll();
```

### Order By

[](#order-by)

```
// SELECT * FROM my_users ORDER BY id DESC
$db->from('users')->orderBy('id DESC')->getAll();
$db->from('users')->orderBy(['id DESC'])->getAll();
$db->from('users')->orderBy(['id' => 'DESC'])->getAll();
// SELECT * FROM my_users ORDER BY id DESC, age ASC
$db->from('users')->orderBy('id DESC,age ASC')->getAll();
$db->from('users')->orderBy(['id' => 'DESC', 'age' => 'ASC'])->getAll();
// SELECT * FROM my_users ORDER BY FIELD (id, "1","3","2") ASC
$db->from('users')->orderBy('id', [1, 3, 2])->getAll();
$db->from('users')->orderBy(['id'], [1, 3, 2])->getAll();
// SELECT * FROM my_users ORDER BY id REGEXP '^[a-z]' ASC
$db->from('users')->orderBy('id', "^[a-z]")->getAll();
$db->from('users')->orderBy(['id'], "^[a-z]")->getAll();
```

### Limit

[](#limit)

```
// SELECT * FROM my_users LIMIT 1
$db->from('users')->limit(1)->getAll();
// SELECT * FROM my_users LIMIT 1, 10
$db->from('users')->limit('1, 10')->getAll();
$db->from('users')->limit(['1', '10'])->getAll();
$db->from('users')->limit(['1' => '10'])->getAll();
```

### map

[](#map)

将某列的值作为返回结果集的索引

```
$users = $db->from('users')->getAll();
// 输出 [['id' => 1, 'name' => 'user1'], ['id' => 2, 'name' => 'user2']]
$users = $db->map('name')->from('users')->getAll();
// 输出 ['user1' => ['id' => 1, 'name' => 'user1'], 'user2' => ['id' => 2, 'name' => 'user2']]
```

### 定义结果集类型

[](#定义结果集类型)

```
// 结果集返回数组（默认）
$users = $db->from('users')->asArray()->getAll();
// 结果集返回对象
$users = $db->from('users')->asObject()->getAll();
// 结果集返回Json
$users = $db->from('users')->asJson()->getAll();
```

### Total Count

[](#total-count)

```
$db->from('users')->limit('0,2')->withTotalCount()->getAll();
// 结果输出2条数据
// $db->totalCount 显示总记录数
```

### 分页

[](#分页)

paginate() 分页
第一个参数是页数
第二个参数是每页记录数量（默认20）。

```
// 每页显示5条，显示第一页数据
$users = $db->from('users')->paginate(1, 5);
echo $db->totalCount; // 总记录数
echo $db->currentPage; // 当前页数
echo $db->pageLimit; // 每页记录数
echo $db->totalPages; // 总页数
```

### 子查询

[](#子查询)

需要先定义子查询对象

```
$sub = $db->subQuery($db->getPrefix());
```

再通过子查询对象拼装子查询语句

```
// SELECT id FROM my_users WHERE age = '10'
$sub->from('users')->select('id')->where(['age' => 10])->getAll();
```

最后子查询作为SQL的查询条件

```
// SELECT * FROM my_users WHERE id in ( (SELECT id FROM my_users WHERE age = '10' ) )
$db->from('users')->where(['id', 'in', $sub])->getAll();
```

### Query

[](#query)

直接通过Query获取结果

```
$users = $db->query('select * from my_users limit 1');
```

### 事务

[](#事务)

```
try {
    // 开启事务
    $db->startTransaction();

    // 插入一条数据
    $id = $db->table('users')->insert(['name' => 'user', 'age' => 10]);
    if ($id getLastErrno().' '.$db->getLastError());
    }

    // 提交
    $db->commit();
} catch(\Exception $e) {
    // 获取错误消息
    // $e->getMessage();
    // 回滚
    $db->rollback();
}
```

### Trace

[](#trace)

跟踪SQL、执行时间、文件位置

```
$db->setTrace(true);
$db->from('users')->getAll();
$db->from('users')->select(['id', 'name'])->getOne();
var_dump($db->trace);
// 打印输出结果
// [
//     0 => [
//         0 => 'SELECT * FROM my_users',
//         1 => 0.020965814590454,
//         2 => 'Zhangzhaowy\Phpmysql\Db->getAll() >>  file "**\controller\Test.php" line #214'
//     ],
//     1 => [
//         0 => 'SELECT  id,name FROM my_users LIMIT 1',
//         1 => 0.0006251335144043,
//         2 => 'Zhangzhaowy\Phpmysql\Db->getOne() >>  file "**\controller\Test.php" line #215'
//     ],
// ]
```

### SQL 关键词

[](#sql-关键词)

支持的关键词包括：
LOW\_PRIORITY | DELAYED | HIGH\_PRIORITY | IGNORE ALL | DISTINCT | DISTINCTROW | STRAIGHT\_JOIN | SQL\_SMALL\_RESULT | SQL\_BIG\_RESULT | SQL\_BUFFER\_RESULT | SQL\_CACHE | SQL\_NO\_CACHE | SQL\_CALC\_FOUND\_ROWS | QUICK | MYSQLI\_NESTJOIN FOR UPDATE | LOCK IN SHARE MODE

```
$db->table($table)->setQueryOption('LOW_PRIORITY')->insert($param);
// INSERT LOW_PRIORITY INTO table ...
```

```
$db->table($table)->setQueryOption('FOR UPDATE')->get('users');
// SELECT * FROM my_users FOR UPDATE;
```

多个关键词一起用

```
$db->table($table)->setQueryOption(['LOW_PRIORITY', 'IGNORE'])->insert($param);
// INSERT LOW_PRIORITY IGNORE INTO table ...
```

### 错误

[](#错误)

SQL执行完成之后，需要执行下面的方法判断是否成功。

```
if ($db->getLastErrno() === 0) {
    echo 'Succesfull';
} else {
    echo 'Failed. Error: '. $db->getLastError();
}
```

### 帮助方法

[](#帮助方法)

关闭数据库连接

```
$db->disconnect();
```

数据库连接断开时重新连接

```
if (!$db->ping()) {
    $db->connect();
}
```

获取最后一次执行的SQL
注：函数返回SQL查询仅用于调试目的，因为它的执行很可能会由于字符变量周围缺少引号而失败。

```
$db->get('users');
echo "Last executed query was ". $db->getLastQuery();
```

转义字符串方法

```
$escaped = $db->escape("' and 1=1");
```

###  Health Score

24

—

LowBetter than 31% of packages

Maintenance20

Infrequent updates — may be unmaintained

Popularity6

Limited adoption so far

Community7

Small or concentrated contributor base

Maturity54

Maturing project, gaining track record

 Bus Factor1

Top contributor holds 100% of commits — single point of failure

How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window.

**Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores.

**Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement.

**Maturity (30%)** — Project age, version count, PHP version support, and release stability.

###  Release Activity

Cadence

Unknown

Total

1

Last Release

2465d ago

### Community

Maintainers

![](https://www.gravatar.com/avatar/a684d1d9ed280ec24f48d439f122ddaf514e8faa856ec39526264b84c2d2da49?d=identicon)[zhangzhao](/maintainers/zhangzhao)

---

Top Contributors

[![zhangzhaowy](https://avatars.githubusercontent.com/u/20203182?v=4)](https://github.com/zhangzhaowy "zhangzhaowy (24 commits)")

---

Tags

phpmysql

### Embed Badge

![Health badge](/badges/zhangzhaowy-php-mysql/health.svg)

```
[![Health](https://phpackages.com/badges/zhangzhaowy-php-mysql/health.svg)](https://phpackages.com/packages/zhangzhaowy-php-mysql)
```

###  Alternatives

[leantime/leantime

Open source project management system for non-project managers. Simple like Trello, powerful like Jira. Built with neurodiversity in mind.

10.1k3.4k](/packages/leantime-leantime)[clouddueling/mysqldump-php

PHP version of mysqldump cli that comes with MySQL

1.3k23.1k](/packages/clouddueling-mysqldump-php)[stefangabos/zebra_database

An advanced, compact and lightweight MySQL database wrapper library, built around PHP's MySQLi extension.

11712.4k](/packages/stefangabos-zebra-database)[eftec/pdoone

Minimaist procedural PDO wrapper library

1116.1k9](/packages/eftec-pdoone)[popphp/pop-db

Pop Db Component for Pop PHP Framework

1815.7k12](/packages/popphp-pop-db)[matthew-p/docker-server

Universal docker server, Nginx, PHP-FPM, MySql, Redis

114.2k](/packages/matthew-p-docker-server)

PHPackages © 2026

[Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)