zhangzhaowy/php-mysql

php mysql

Maintainers

Details

github.com/zhangzhaowy/php-mysql

Source

Issues

Installs: 16

Dependents: 0

Suggesters: 0

Security: 0

Stars: 0

Watchers: 1

Forks: 0

Open Issues: 0

1.0.0 2019-09-29 09:42 UTC

Requires

  • php: ^5.4|^7.0
  • ext-pdo: *

Suggests

  • ext-pdo_mysql: For MySQL or MariaDB databases

MIT 2ffbe4b03111d4735137e4ee600395eca87c9c77

  • zhangzhao <zhangzhaowy.woop@163.com>

phpmysql

This package is auto-updated.

Last update: 2024-04-29 04:06:47 UTC

README

MySQLi 封装类

声明

此软件是为了满足个人使用习惯而在ThingEngineer/PHP-MySQLi-Database-Class的基础上开发的. 如果你想学习或研究MYSQL，可以去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() 同 insert() 方法一样;

更新

可以使用where()、limit()等联合查询，详解见查询

$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

定义操作表

$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

定义获取列字段

$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的条件中将表名用``括起来，会自动追加表前缀。

// 左联
$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

// 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

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

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

// 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

// 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

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

$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

$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获取结果

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

事务

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

    // 插入一条数据
    $id = $db->table('users')->insert(['name' => 'user', 'age' => 10]);
    if ($id <= 0) {
        // 失败，报错
        throw new \Exception('ERROR:'.$db->getLastErrno().' '.$db->getLastError());
    }

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

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 关键词

支持的关键词包括：
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");