README

项目介绍

fairy 支持在项目中提供多个数据库事务操作

软件架构

基于workerman开发，server为常驻服务，是真正与mysql通信服务，client在应用中调用server服务即可。

mysql操作由 walkor/mysql 改造而来

依赖的扩展

依赖pdo和pdo_mysql两个扩展

使用说明

安装

composer require wenken/fairy

服务端

<?php
use Wenken\Server;

require_once '../vendor/autoload.php';

$server = new Server('0.0.0.0', 12321);//生成环境
$server->count = 4;//服务启动进程数
$server->addIpWhiteList('192.168.0.103');// 仅此ip可访问服务

$server->addIpWhiteList([
    '192.168.0.104',
    '192.168.0.105'
]);// 数组设置可访问服务白名单
//$server->addMysql($server_name, $host, $port, $user, $pass, $dbname);
$server->addMysql('user', '192.168.0.100', 3306, 'root', 'root', 'dbname');
$server->addMysql('log', '192.168.0.101', 3306, 'root', 'root', 'dbname');
$server->run();

客户端

<?php
use Wenken\Mysql;
use Wenken\DB;

require_once '../vendor/autoload.php';
$db = new DB();
$db->setLogFile(__DIR__ . DIRECTORY_SEPARATOR . 'mysql.access.log');
// 添加user Mysql服务
$db->addDb('user', function () {
    return new Mysql('user', '192.168.0.100', 12321);
});
// 添加log Mysql服务
$db->addDb('log', function () {
    return new Mysql('log', '192.168.0.101', 12321);
});

$db->beginTrans();
$r1 = $db->user->update('user')->where('id=:id')->cols(['username' => 'wen3'])->bindValues(["id" => 1])->query();
$r2 = $db->log->update('log')->where('id=:id')->cols(['log' => 'update'])->bindValues(["id" => 2])->query();
if ($r1 && $r2) {
    $db->commitTrans();
} else {
    $db->rollBackTrans();
}
$result1 = $db->user->select('id,username')
    ->from('user')
    ->where('id = :id')
    ->bindValues([
        'id' => 1
    ])->row();
$result2 = $db->log->select('*')
    ->from('user')
    ->where('id= :id')
    ->groupBy(['id'])
    ->orderByASC(['id'])
    ->limit(0)
    ->offset(20)
    ->bindValues(
        ['id' => 2]
    )->query();
var_dump($result1, $result2);

语法参考

// 获取所有数据
$db->select('ID,Sex')->from('Persons')->where('sex= :sex AND ID = :id')->bindValues(array('sex'=>'M', 'id' => 1))->query();
//等价于
$db->select('ID,Sex')->from('Persons')->where("sex= 'M' AND ID = 1")->query();
//等价于
$db->query("SELECT ID,Sex FROM `Persons` WHERE sex='M' AND ID = 1");


// 获取一行数据
$db->select('ID,Sex')->from('Persons')->where('sex= :sex')->bindValues(array('sex'=>'M'))->row();
//等价于
$db->select('ID,Sex')->from('Persons')->where("sex= 'M' ")->row();
//等价于
$db->row("SELECT ID,Sex FROM `Persons` WHERE sex='M'");


// 获取一列数据
$db->select('ID')->from('Persons')->where('sex= :sex')->bindValues(array('sex'=>'M'))->column();
//等价于
$db->select('ID')->from('Persons')->where("sex= 'F' ")->column();
//等价于
$db->column("SELECT `ID` FROM `Persons` WHERE sex='M'");

// 获取单个值
$db->select('ID')->from('Persons')->where('sex= :sex')->bindValues(array('sex'=>'M'))->single();
//等价于
$db->select('ID')->from('Persons')->where("sex= 'F' ")->single();
//等价于
$db->single("SELECT ID FROM `Persons` WHERE sex='M'");

// 复杂查询
$db->select('*')->from('table1')->innerJoin('table2','table1.uid = table2.uid')->where('age > :age')->groupBy(array('aid'))->having('foo="foo"')->orderByASC/*orderByDESC*/(array('did'))
->limit(10)->offset(20)->bindValues(array('age' => 13));
// 等价于
$db->query('SELECT * FROM `table1` INNER JOIN `table2` ON `table1`.`uid` = `table2`.`uid`
WHERE age > 13 GROUP BY aid HAVING foo="foo" ORDER BY did LIMIT 10 OFFSET 20');

// 插入
$insert_id = $db->insert('Persons')->cols(array(
    'Firstname'=>'abc',
    'Lastname'=>'efg',
    'Sex'=>'M',
    'Age'=>13))->query();
等价于
$insert_id = $db->query("INSERT INTO `Persons` ( `Firstname`,`Lastname`,`Sex`,`Age`)
VALUES ( 'abc', 'efg', 'M', 13)");

// 更新
$row_count = $db->update('Persons')->cols(array('sex'))->where('ID=1')
->bindValue('sex', 'F')->query();
// 等价于
$row_count = $db->update('Persons')->cols(array('sex'=>'F'))->where('ID=1')->query();
// 等价于
$row_count = $db->query("UPDATE `Persons` SET `sex` = 'F' WHERE ID=1");

// 删除
$row_count = $db->delete('Persons')->where('ID=9')->query();
// 等价于
$row_count = $db->query("DELETE FROM `Persons` WHERE ID=9");

// 事务
$db->beginTrans();
....
$db->commitTrans(); // or $db->rollBackTrans();