title: Mysqli组件 meta:

  • name: description content: Easyswoole Mysqli库，旨在方便用户以面向对象的形式进行数据库调用的一个库。并且为Orm组件等高级用法提供了基础支持
  • name: keywords content: swoole|swoole extension|swoole framework|EasySwoole mysqli|EasySwoole ORM|Swoole mysqli协程客户端|swoole ORM

GET

  1. use EasySwoole\Mysqli\QueryBuilder;
  3. $builder = new QueryBuilder();
  5. // 获取全表
  6. $builder->get('getTable');
  8. // 表前缀
  9. $builder->setPrefix('easyswoole_')->get('getTable');
  11. // 获取总数。下面两个结果相同
  12. $builder->withTotalCount()->where('col1', 1, '>')->get('getTable');
  13. $builder->setQueryOption('SQL_CALC_FOUND_ROWS')->where('col1', 1, '>')->get('getTable');
  15. // fields。支持一维数组或字符串
  16. $builder->fields('col1, col2')->get('getTable');
  17. $builder->get('getTable', null, ['col1','col2']);
  19. // limit 1。下面两个结果相同
  20. $builder->get('getTable', 1)
  21. $builder->getOne('getTable')
  23. // offset 1, limit 10
  24. $builder->get('getTable',[1, 10])
  26. // 去重查询。
  27. $builder->get('getTable', [2,10], ['distinct col1','col2']);
  29. // where查询
  30. $builder->where('col1', 2)->get('getTable');
  32. // where查询2
  33. $builder->where('col1', 2, '>')->get('getTable');
  35. // 多条件where
  36. $builder->where('col1', 2)->where('col2', 'str')->get('getTable');
  38. // whereIn, whereNotIn, whereLike，修改相应的operator(IN, NOT IN, LIKE)
  39. $builder->where('col3', [1,2,3], 'IN')->get('getTable');
  41. // orWhere
  42. $builder->where('col1', 2)->orWhere('col2', 'str')->get('getTable');
  44. // 复杂where
  45. // 生成大概语句：where status = 1 AND (id > 10 or id < 2)
  46. $builder->where('status', 1)->where(' (id > 10 or id <2) ')->get('getTable');

Join

  1. use EasySwoole\Mysqli\QueryBuilder;
  3. $builder = new QueryBuilder();
  5. // join。默认INNER JOIN
  6. $builder->join('table2', 'table2.col1 = getTable.col2')->get('getTable');
  7. $builder->join('table2', 'table2.col1 = getTable.col2', 'LEFT')->get('getTable');
  9. // join Where
  10. $builder->join('table2','table2.col1 = getTable.col2')->where('table2.col1', 2)->get('getTable');

GroupBy Having

  1. use EasySwoole\Mysqli\QueryBuilder;
  3. $builder = new QueryBuilder();
  5. // groupBy. 
  6. $builder->groupBy('col1')->get('getTable');
  7. $builder->where('col1',2)->groupBy('col1')->get('getTable');
  9. // having
  10. $builder->groupBy('col1')->having('col1')->get('getTable');
  11. $builder->groupBy('col1')->having('col1', 1, '>')->get('whereGet');
  13. // and having. having第4个参数默认是 `AND`，默认多having是`且`关系
  14. $builder->groupBy('col1')->having('col1', 1, '>')->having('col2', 1, '>')->get('whereGet');
  16. // or having. 下面两种方法效果相等
  17. $builder->groupBy('col1')->having('col1', 1, '>')->orHaving('col2', 1, '>')->get('whereGet');
  18. $builder->groupBy('col1')->having('col1', 1, '>')->having('col2', 1, '>', 'OR')->get('whereGet');

OrderBy

  1. use EasySwoole\Mysqli\QueryBuilder;
  3. $builder = new QueryBuilder();
  5. // orderBy. 默认DESC
  6. $builder->orderBy('col1', 'ASC')->get('getTable');
  7. $builder->where('col1',2)->orderBy('col1', 'ASC')->get('getTable');

Union

  1. use EasySwoole\Mysqli\QueryBuilder;
  3. $builder = new QueryBuilder();
  5. // union. 相当于 adminTable UNION userTable
  6. $builder->union((new QueryBuilder)->where('userName', 'user')->get('userTable'))
  7.     ->where('adminUserName', 'admin')->get('adminTable');

UPDATE

  1. use EasySwoole\Mysqli\QueryBuilder;
  3. $builder = new QueryBuilder();
  5. // update 
  6. $builder->update('updateTable', ['a' => 1]);
  8. // limit update
  9. $builder->update('updateTable', ['a' => 1], 5);
  11. // where update
  12. $builder->where('whereUpdate', 'whereValue')->update('updateTable', ['a' => 1]);

DELETE

  1. use EasySwoole\Mysqli\QueryBuilder;
  3. $builder = new QueryBuilder();
  5. // delete all
  6. $builder->delete('deleteTable');
  8. // limit delete
  9. $builder->delete('deleteTable', 1);
  11. // where delete
  12. $builder->where('whereDelete', 'whereValue')->delete('deleteTable');

INSERT

  1. use EasySwoole\Mysqli\QueryBuilder;
  3. $builder = new QueryBuilder();
  5. // insert into
  6. $builder->insert('insertTable', ['a' => 1, 'b' => "b"]);
  8. // replace into
  9. $builder->replace('replaceTable', ['a' => 1]);

SUBQUERY

  1. use EasySwoole\Mysqli\QueryBuilder;
  3. $builder = new QueryBuilder();
  5. // 单一where条件子查询
  6. // 等同于 SELECT * FROM users WHERE id in ((SELECT userId FROM products WHERE  qty > 2))
  7. $sub = $this->builder::subQuery();
  8. $sub->where("qty", 2, ">");
  9. $sub->get("products", null, "userId");
  10. $builder->where("id", $sub, 'in')->get('users');
  12. // 多where条件子查询
  13. // 等同于 SELECT * FROM users WHERE col2 = 1 AND id in ((SELECT userId FROM products WHERE  qty > 2))
  14. $sub = $this->builder::subQuery();
  15. $sub->where ("qty", 2, ">");
  16. $sub->get ("products", null, "userId");
  17. $this->builder->where('col2',1)->where ("id", $sub, 'in')->get('users');
  19. // INSERT 包含子结果集
  20. // 等同于 INSERT INTO products (`productName`, `userId`, `lastUpdated`) VALUES ('test product', (SELECT name FROM users WHERE  id = 6  LIMIT 1), NOW())
  21. $userIdQ = $this->builder::subQuery();
  22. $userIdQ->where ("id", 6);
  23. $userIdQ->getOne ("users", "name");
  24. $data = Array (
  25.     "productName" => "test product",
  26.     "userId" => $userIdQ,
  27.     "lastUpdated" => $this->builder->now()
  28. );
  29. $this->builder->insert ("products", $data);

LOCK

  1. use EasySwoole\Mysqli\QueryBuilder;
  3. $builder = new QueryBuilder();
  5. // FOR UPDATE 排它锁。下面两个方法效果相同
  6. $builder->setQueryOption("FOR UPDATE")->get('getTable');
  7. $builder->selectForUpdate(true)->get('getTable');
  9. //  LOCK IN SHARE MODE。共享锁
  10. $builder->lockInShareMode()->get('getTable');
  11. $builder->setQueryOption(['LOCK IN SHARE MODE'])->get('getTable');
  13. // LOCK TABLES 获取表锁
  14. $builder->lockTable('table');
  16. // UNLOCK TABLES 释放表锁
  17. $builder->unlockTable('table');