easyswoole 数据库相关操作集合大复盘

初始化ORM连接池

1.在dev.php 配置文件中配置其MYSQL数据库连接配置

return [
   'MYSQL' =>[
        'host'          => '',
        'port'          => 3300,
        'user'          => '',
        'password'      => '',
        'database'      => '',
        'timeout'       => 5,
        'charset'       => 'utf8mb4',
   ],
];

 2.在其EasyswooleEvent.php 进行注册

namespace EasySwoole\EasySwoole;
use EasySwoole\EasySwoole\Config;
use EasySwoole\ORM\Db\Connection;
use EasySwoole\ORM\DbManager;
 
class EasyswooleEvent implements Event {
   public function initialize() {
       //MYSQL ORM连接池
         $mysql_config = Config::getInstance()->getConf('MYSQL');
         $config = new \EasySwoole\ORM\DB\Config($mysql_config);
         try {
             //连接池配置
            $config->setGetObjectTimeout(3.0); //设置获取连接池对象超时时间
            $config->setIntervalCheckTime(30*1000); //设置检测连接存活执行回收和创建的周期
            $config->setMaxIdleTime(15); //连接池对象最大闲置时间(秒)
            $config->setMinObjectNum(5); //设置最小连接池存在连接对象数量
            $config->setMaxObjectNum(20); //设置最大连接池存在连接对象数量
            $config->setAutoPing(5); //设置自动ping客户端链接的间隔
            DbManager::getInstance()->addConnection(new Connection($config));
         } catch (\Throwable $e) {
             var_dump('注册mysql连接池异常：'.$e->getMessage());
         }
   } 
 
}

自定义SQL执行

有时候你可能需要在查询中使用原生表达式。你可以使用 QueryBuilder 构造一个原生 SQL 表达式

ORM 内部依赖的是 mysqli 组件的QueryBuilder

namespace App\Model;
use EasySwoole\Mysqli\QueryBuilder;
 
//直接使用
public function getData(){
     $queryBuild = new QueryBuilder();
     // 支持参数绑定 第二个参数非必传
     $queryBuild->raw("select * from test where name = ?", ['siam']);
     // $queryBuild->raw("select * from test where name = 'siam'");
 
     // 第二个参数 raw  指定true，表示执行原生sql
     // 第三个参数 connectionName 指定使用的连接名，默认 default
     $data = DbManager::getInstance()->query($queryBuild, true, 'default');
     return $data;
}
 
//采用invoke方式  
public function runSqlGetData($sql,$param){
     return DbManager::getInstance()->invoke(function ($client) use ($sql,$param) {
        $queryBuilder = new QueryBuilder();
        $query_raw_obj = $queryBuilder->raw($sql,$param);
        $res_obj = $client->query($query_raw_obj);
        //获取结果
        $result = $res_obj->getResult();
        return $result;
     });
}
 
 
 
 
 

在模型中使用 

 代码如下：

namespace App\Model;
 
use EasySwoole\Mysqli\QueryBuilder;
use EasySwoole\ORM\DbManager;
use EasySwoole\ORM\AbstractModel;
 
class DepartModel extends AbstractModel
{
    protected $tableName = 'region';
    
    //$field
    public function get_depart_region($where,$field="*")
    {
        $region = $this->where($where)
            ->field('*')
            ->withTotalCount()
            ->all();
        $total = $this->lastQueryResult()->getTotalCount(); //获取总条数
        $sql = $this->lastQuery()->getlastQuery(); //获取执行的sql
        return $this->lastQueryResult()->toArray();
     }
}

 使用  $this->lastQueryResult()->toArray(); 获取结果

使用 withTotalCount 与 $this->lastQueryResult()->getTotalCount() 获取其数据总条数

获取执行的SQL 

使用 $this->lastQuery()->getlastQuery(); //获取执行的sql

发现 使用

$this->lastQuery()->getlastQuery()  

$this->lastQuery()->getLastQuery()

$this->lastQuery()->getLastquery()  

效果一样
效果一样

效果一样

获取结果 

1. 使用  $this->lastQueryResult()->toArray(); 获取结果

2. 使用 $this->lastQueryResult()->getResult();  //获取结果  二维数组  多个结果

3.使用 $this->lastQueryResult()->getResultOne();  //获取结果  一维数组  单个结果

模型使用invoke 

namespace App\Model;
use EasySwoole\ORM\AbstractModel;
use EasySwoole\ORM\DbManager;
 
 
class ReginModel extends AbstractModel {
 
    protected $tableName = 'region';
    
    
    //采用invoke方式  
    public function runSqlGetData($where,$field,$order,$pn,$page_size,$where2=""){
    
         return DbManager::getInstance()-invoke(function ($client) use 
           ($where,$field,$order,$pn,$page_size,$where2) {
                $model = self::invoke($client);
                $model = alias('a');
                $model->where($where);
                if($where2!='') {
                     $model->where($where2);
                }
                $model->join("user b",'b.id=a.user_id','left');
                      ->limit(($pn-1)*$page_size , $page_size)
                      ->field($field)
                      ->order($order[0],$order[1])
                      ->all();
                //获取结果
                $result = $model->lastQueryResult()->getResult();
                //var_dump($model->lastQuery()->getlastQuery());  //执行的SQL语句
                return $result;
     });
 
 
    
}
 

新增 、修改、删除操作

代码如下：

//新增
public function addInfo($data) {
    return DbManager::getInstance()->invoke(function ($client) use ($data) {
         $m = self::invoke($client);
         return $m->data($data,false)->save();  //返回insert_id 
    });
}
 
 
//批量新增
//@param $data 二维数组
public function addMutiInfo($data,$table_name){
    return DbManager::getInstance()->invoke(function ($client) use ($data,$table_name) {
         $query_builder = new QueryBuilder();
         $res_obj = $query_builder->insertAll($table_name,$data);
         $res_obj1 = $client->query($res_obj);
         return $res_obj1->getResult();
    });
}
 
//删除数据
public function delInfo($where) {
    return DbManager::getInstance()->invoke(function ($client) use ($where)  {
         $m = self::invoke($client);
         $result = $m->destroy($where);
         //var_dump($m->lastQuery()->getLastQuery());
         return $result;  
    });
}
 
 
//修改数据
public function updateInfo($data,$where) {
     return DbManager::getInstance()->invoke(function ($client) use ($data,$where) {
         $m = self::invoke($client);
         $m->update($data,$where);
         return $m->lastQueryResult()->getAffectedRows();  
    });
}

使用ORM时，需要每个模型定义一个操作表，这样操作不方便，进而我们再次使用QueryBuilder进行封装  代码如下

namespace App\Model;
 
use EasySwoole\Mysqli\QueryBuilder;
use EasySwoole\ORM\DbManager;
 
class CommonModel extends QueryBulider{
 
    private static ?self $_instance = null;
 
    public function execute(){
        return DbManage::getInstance()->query($this,true)->getResult();    
    }
 
    public function showData($where=[],$table_name,$field="*") {
        return $this->whereArray($where)->get($table_name,null,$field)->execute();
    }
 
    public function whereArray($where){
        foreach ($where as $key=>$value)  {
            if(!is_array($value)) {
                $this->where($key,$value,'=');
                continue;
            } 
            foreach( $value as $value2) {
                 if(is_array($value2)) {
                     $this->where($key,...$value);
                     goto BREAK_EXECUTE;  
                 }              
            }
            foreach( $value as $item) {
                $this->where($key,...$item);
            }
            goto BREAK_EXECUTE;  
            continue
             
        }
        return $this;
    }
 
}

 在使用是 直接调用模型 

$model = new CommonModel();
 
$where['id'] = 1;
 
$table_name = "test";
 
$field = "name,age";
 
$data = $model->showData($where,$table_name,$field);

---

分割线

Mysqli

安装

composer require easyswoole/mysqli

 1.在dev.php 配置文件中配置其MYSQL数据库连接配置

return [
   'MYSQL' =>[
        'host'          => '',
        'port'          => 3300,
        'user'          => '',
        'password'      => '',
        'database'      => '',
        'timeout'       => 5,
        'charset'       => 'utf8mb4',
   ],
];

 2.在其EasyswooleEvent.php 进行注册

namespace EasySwoole\EasySwoole;
use EasySwoole\EasySwoole\Config;
class EasySwooleEvent implements Event
{
    public static function mainServerCreate(EventRegister $register)
    {
    
         $mysqlConfig = new \EasySwoole\Mysqli\Config(Config::getInstance()->getConf('MYSQL'));
         try {
            \EasySwoole\MysqliPool\Mysql::getInstance()->register('mysql', $mysqlConfig);
           
        } catch (MysqlPoolException $e) {
            echo "[Warn] --> mysql池注册失败\n";
        }
          
    }
 
}

使用BaseModel

namespace App\Model;
 
use EasySwoole\MysqliPool\Mysql;
 
class BaseModel
{
    protected $db;
 
    public function __construct(\EasySwoole\Mysqli\Mysqli $dbObject = null)
    {
        if (!$dbObject)
            $dbObject = Mysql::defer("mysql");
  
        $this->db = $dbObject;
    }
 
    public function getDb(): \EasySwoole\Mysqli\Mysqli
    {
        return $this->db;
    }

使用模型  继承BaseModel 模型

namespace App\Model;
class UserModel extends BaseModel{
    protected $table = 'user';
 
 
    public function getUseableMonitorNum(){
        $num = $this->db->where("status",1)->count($this->table);
        return $num;
    }
 
    /**
     * 根据条件查询布控数据
     * @param $where str 查询条件 
     */
    public function searchData($where,$field = '*'){
        if ($where) {
            $data = $this->db->where($where)->orderBy("cap_id",'desc')->get($this->table,null,$field);
        } else {
            $data = $this->db->orderBy("cap_id",'desc')->get($this->table,null,$field);
        }
        // var_dump($this->db->getLastQuery());  
        if ($data) {
            return $data;
        }
        return [];
    }

 // var_dump($this->db->getLastQuery());   获取执行的sql

2022年11月22日18:41:11  在使用Mysqli 是  其实也可以使用invoke 方式  代码如下：

namespace App\Model;
 
use EasySwoole\MysqliPool\Mysql;
 
class TestModel {
      
      //获取数据
      public function getData(){
           //此处的mysql 由上面的注册名称 保持一致
           $data = Mysql::invoker('mysql',function ($conn) {
                return $conn->rawQuery('select version()');
           });
           var_dump($data);
      }
}