MysqliDb

鉴于每个用户的使用习惯问题，EasySwoole本身并不提供封装好的数据库操作与Model层，但我们强力推荐在项目中使用第三方开源库https://github.com/joshcam/PHP-MySQLi-Database-Class 作为数据操作类库，并构建自己的Model。

使用指导

项目引入

方法一：

  • 克隆(下载)MySQLi项目，并解压提取其中的MysqliDb.php文件，放入项目 ‘/App/Vendor/Db/‘ 路径下。

  • Class引入。在composer中引入MysqliDb.php。

  1. {
  2.   "autoload":{
  3.     "psr-4":{
  4.       "MysqliDb" : "App/Vendor/Db/MysqliDb.php"
  5.     }
  6.   }
  7. }

方法二：

  1. composer require joshcam/mysqli-database-class:dev-master

引入成功后即可在项目任意位置创建MysqliDb对象。

利用IOC容器实现单例长连接

在框架的\EasySwoole\EasySwooleEvent::mainServerCreate中，引入了MysqliDb.php后，即可进行IOC注入。

  1. Di::getInstance()->set('MYSQL',\MysqliDb::class,Array (
  2.             'host' => 'host',
  3.             'username' => 'username',
  4.             'password' => 'password',
  5.             'db'=> 'dbName',
  6.             'port' => 3306,
  7.             'charset' => 'utf8')
  8. );

获取数据库示例

  1. $db = Di::getInstance()->get('MYSQL');

注意：为避免出现多个进程复用同一个数据库连接的情况，请勿在服务启动前的任一位置执行Di::getInstance()->get(‘MYSQL’)。 workerStart事件中使用数据库，请以手动new class()的方式来获取一个数据库对象，其次，在单例子模式下，请注意数据库断线重连问题， MysqliDb类库中有实现断线自动重连。

数据库基础用法

Insert

  1. $data = Array (
  2.     "productName" => "test product",
  3.     "userId" => $userIdQ,
  4.     "lastUpdated" => $db->now()
  5. );
  6. $id = $db->insert ("products", $data);
  7. // Gives INSERT INTO PRODUCTS (productName, userId, lastUpdated) values ("test product", (SELECT name FROM users WHERE id = 6), NOW());

Update Query

  1. $data = Array (
  2.     'firstName' => 'Bobby',
  3.     'lastName' => 'Tables',
  4.     'editCount' => $db->inc(2),
  5.     // editCount = editCount + 2;
  6.     'active' => $db->not()
  7.     // active = !active;
  8. );
  9. $db->where ('id', 1);
  10. if ($db->update ('users', $data))
  11.     echo $db->count . ' records were updated';
  12. else
  13.     echo 'update failed: ' . $db->getLastError();

update() also support limit parameter:

  1. $db->update ('users', $data, 10);

// Gives: UPDATE users SET … LIMIT 10

Select Query

After any select/get function calls amount or returned rows is stored in $count variable

  1. $users = $db->get('users'); //contains an Array of all users 
  2. $users = $db->get('users', 10); //contains an Array 10 users

or select with custom columns set. Functions also could be used

  1. $cols = Array ("id", "name", "email");
  2. $users = $db->get ("users", null, $cols);
  3. if ($db->count > 0)
  4.     foreach ($users as $user) { 
  5.         print_r ($user);
  6.     }
  8. or select just one row
  10. $db->where ("id", 1);
  11. $user = $db->getOne ("users");
  12. echo $user['id'];
  14. $stats = $db->getOne ("users", "sum(id), count(*) as cnt");
  15. echo "total ".$stats['cnt']. "users found";
  17. or select one column value or function result
  19. $count = $db->getValue ("users", "count(*)");
  20. echo "{$count} users found";

select one column value or function result from multiple rows:

  1. $logins = $db->getValue ("users", "login", null);
  2. // select login from users
  3. $logins = $db->getValue ("users", "login", 5);
  4. // select login from users limit 5
  5. foreach ($logins as $login)
  6.     echo $login;

JOIN method

  1. $db->join("users u", "p.tenantID=u.tenantID", "LEFT");
  2. $db->where("u.id", 6);
  3. $products = $db->get ("products p", null, "u.name, p.productName");
  4. print_r ($products);

Join Conditions

Add AND condition to join statement

  1. $db->join("users u", "p.tenantID=u.tenantID", "LEFT");
  2. $db->joinWhere("users u", "u.tenantID", 5);
  3. $products = $db->get ("products p", null, "u.name, p.productName");
  4. print_r ($products);
  5. // Gives: SELECT  u.login, p.productName FROM products p LEFT JOIN users u ON (p.tenantID=u.tenantID AND u.tenantID = 5)

Add OR condition to join statement

  1. $db->join("users u", "p.tenantID=u.tenantID", "LEFT");
  2. $db->joinOrWhere("users u", "u.tenantID", 5);
  3. $products = $db->get ("products p", null, "u.name, p.productName");
  4. print_r ($products);
  5. // Gives: SELECT  u.login, p.productName FROM products p LEFT JOIN users u ON (p.tenantID=u.tenantID OR u.tenantID = 5)

更多用法请见: https://github.com/joshcam/PHP-MySQLi-Database-Class

创建自己的Model

  1. namespace App\Model\Goods;
  2. use EasySwoole\Core\Component\Spl\SplBean;
  3. class Bean extends SplBean
  4. {
  5.     protected $goodsId;
  6.     protected $goodsName;
  7.     protected $addTime;
  8.     protected function initialize()
  9.     {
  10.         // TODO: Implement initialize() method.
  11.         $this->addTime = time();
  12.     }
  14. }
  16. namespace App\Model\Goods;
  19. use EasySwoole\Core\Component\Di;
  21. class Goods
  22. {
  23.     protected $db;
  24.     protected $tableName = 'goods_list';
  25.     function __construct()
  26.     {
  27.         $db = Di::getInstance()->get("MYSQL");
  28.         if($db instanceof \MysqliDb){
  29.             $this->db = $db;
  30.         }
  31.     }
  33.     function add(Bean $bean){
  34.         return $this->db->insert($this->tableName,$bean->toArray($bean::FILTER_TYPE_NOT_NULL));
  35.     }
  36. }

注意：数据库若使用单例模式保持长连接，一定要处理断线问题。本文中推荐的数据库类已经处理了。