作者简介:

       姜海强:闷骚码农,互联网行业摸爬滚打数余载,先后担任中国体育直播TV主程、网信集团先锋支付架构师、奇虎360服务器端资深开发。热爱技术,喜欢分享,热衷领域:PHP/Golang语言、面向对象设计模式、Redis、Yaf、Yii2、微服务等。

视频课程

yaf+yar微服务-腾讯课堂
yaf+yar微服务-51CTO学院
CSDN学院

Github

个人主页
swoole-boot
roach
roach-orm

QQ群:

姜海强的QQ群

公众号:

360tryst公众号

db封装

以下Connection类封装支持以下几个特性

  • 1.参数绑定防止sql注入
  • 2.读写分离
  • 3.多主多从,多节点负载均衡
  • 4.故障自动摘除及自动恢复

代码实现

  1. <?php
  2. class Connection
  3. {
  4. /**
  5. * @var array
  6. * @datetime 2020/7/5 10:20 AM
  7. * @author roach
  8. * @email jhq0113@163.com
  9. */
  10. public $masters = [];
  11. /**
  12. * @var array
  13. * @datetime 2020/7/5 10:20 AM
  14. * @author roach
  15. * @email jhq0113@163.com
  16. */
  17. public $slaves = [];
  18. /**
  19. * @var \PDO
  20. * @datetime 2020/7/5 10:21 AM
  21. * @author roach
  22. * @email jhq0113@163.com
  23. */
  24. protected $_master;
  25. /**
  26. * @var \PDO
  27. * @datetime 2020/7/5 10:21 AM
  28. * @author roach
  29. * @email jhq0113@163.com
  30. */
  31. protected $_slave;
  32. /**
  33. * @var string
  34. * @datetime 2020/7/6 1:32 下午
  35. * @author roach
  36. * @email jhq0113@163.com
  37. */
  38. protected $_driver;
  39. /**
  40. * @return string
  41. * @datetime 2020/7/6 1:49 下午
  42. * @author roach
  43. * @email jhq0113@163.com
  44. */
  45. public function getDriver()
  46. {
  47. if(is_null($this->_driver)) {
  48. $this->_driver = ucfirst(substr($this->masters[0]['dsn'], 0, strpos($this->masters[0]['dsn'], ':')));
  49. }
  50. return $this->_driver;
  51. }
  52. /**
  53. * @param array $configs
  54. * @return \PDO
  55. * @datetime 2020/7/5 12:19 PM
  56. * @author roach
  57. * @email jhq0113@163.com
  58. */
  59. protected function _select($configs = [])
  60. {
  61. shuffle($configs);
  62. foreach ($configs as $config) {
  63. try {
  64. if(!isset($config['options'])) {
  65. $config['options'] = [];
  66. }
  67. $config['options'][ \PDO::ATTR_ERRMODE ] = \PDO::ERRMODE_EXCEPTION;
  68. $pdo = new \PDO($config['dsn'], $config['username'], $config['password'], $config['options']);
  69. return $pdo;
  70. }catch (\Throwable $throwable) {
  71. continue;
  72. }
  73. }
  74. }
  75. /**
  76. * @return \PDO
  77. * @throws Exception
  78. * @datetime 2020/7/5 10:30 AM
  79. * @author roach
  80. * @email jhq0113@163.com
  81. */
  82. protected function _master()
  83. {
  84. if(is_null($this->_master)) {
  85. $this->_master = $this->_select($this->masters);
  86. if(is_null($this->_master)) {
  87. throw new Exception('没有可用的master数据库了');
  88. }
  89. }
  90. return $this->_master;
  91. }
  92. /**
  93. * @return \PDO
  94. * @throws Exception
  95. * @datetime 2020/7/5 10:31 AM
  96. * @author roach
  97. * @email jhq0113@163.com
  98. */
  99. protected function _slave()
  100. {
  101. if(is_null($this->_slave)) {
  102. if(empty($this->slaves)) {
  103. $this->slaves = $this->masters;
  104. }
  105. $this->_slave = $this->_select($this->slaves);
  106. if(is_null($this->_slave)) {
  107. throw new Exception('没有可用的slave数据库了');
  108. }
  109. }
  110. return $this->_slave;
  111. }
  112. /**
  113. * @param bool $useMaster
  114. * @param string $sql
  115. * @return bool|\PDOStatement
  116. * @throws Exception
  117. * @datetime 2020/7/5 12:12 PM
  118. * @author roach
  119. * @email jhq0113@163.com
  120. */
  121. protected function _createCommand($useMaster, $sql)
  122. {
  123. try {
  124. if($useMaster) {
  125. $pdo = $this->_master();
  126. return $pdo->prepare($sql);
  127. } else {
  128. $pdo = $this->_slave();
  129. return $pdo->prepare($sql);
  130. }
  131. }catch (\Throwable $throwable) {
  132. //连接断了,原因可能是超时、mysql宕机等,会重新选择一个数据库,仅重新选择一次
  133. if($useMaster) {
  134. $this->_master = null;
  135. $pdo = $this->_master();
  136. return $pdo->prepare($sql);
  137. } else {
  138. $this->_slave = null;
  139. $pdo = $this->_slave();
  140. return $pdo->prepare($sql);
  141. }
  142. }
  143. }
  144. /**
  145. * @return string
  146. * @datetime 2020/7/5 10:42 AM
  147. * @author roach
  148. * @email jhq0113@163.com
  149. */
  150. public function lastInsertId()
  151. {
  152. return $this->_master->lastInsertId();
  153. }
  154. /**
  155. * @param string $sql
  156. * @param array $params
  157. * @param bool $useMaster
  158. * @return array
  159. * @throws Exception
  160. * @datetime 2020/7/5 12:13 PM
  161. * @author roach
  162. * @email jhq0113@163.com
  163. */
  164. public function queryAll($sql, $params = [], $useMaster = false)
  165. {
  166. $stmt = $this->_createCommand($useMaster, $sql);
  167. $stmt->execute($params);
  168. $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);
  169. $stmt->closeCursor();
  170. return $rows;
  171. }
  172. /**
  173. * @param $sql
  174. * @param array $params
  175. * @return int
  176. * @throws Exception
  177. * @throws \ReflectionException
  178. * @datetime 2020/7/5 12:13 PM
  179. * @author roach
  180. * @email jhq0113@163.com
  181. */
  182. public function execute($sql, $params = [])
  183. {
  184. $stmt = $this->_createCommand(true, $sql);
  185. $stmt->execute($params);
  186. return $stmt->rowCount();
  187. }
  188. /**
  189. * @return bool
  190. * @throws Exception
  191. * @datetime 2020/7/5 10:44 AM
  192. * @author roach
  193. * @email jhq0113@163.com
  194. */
  195. public function begin()
  196. {
  197. return $this->_master()->beginTransaction();
  198. }
  199. /**
  200. * @return bool
  201. * @throws Exception
  202. * @datetime 2020/7/5 10:45 AM
  203. * @author roach
  204. * @email jhq0113@163.com
  205. */
  206. public function rollback()
  207. {
  208. return $this->_master()->rollBack();
  209. }
  210. /**
  211. * @return bool
  212. * @throws Exception
  213. * @datetime 2020/7/5 10:48 AM
  214. * @author roach
  215. * @email jhq0113@163.com
  216. */
  217. public function commit()
  218. {
  219. return $this->_master()->commit();
  220. }
  221. /**
  222. * @param callable $handler
  223. * @return bool
  224. * @throws Exception
  225. * @datetime 2020/7/5 10:50 AM
  226. * @author roach
  227. * @email jhq0113@163.com
  228. */
  229. public function transaction(callable $handler)
  230. {
  231. $result = $this->begin();
  232. if(!$result) {
  233. return false;
  234. }
  235. $result = call_user_func($handler, $this);
  236. if($result) {
  237. return $this->commit();
  238. }
  239. $this->rollback();
  240. return false;
  241. }
  242. }

使用

  1. <?php
  2. $connection = new Connection([
  3. 'masters' => [
  4. [
  5. 'dsn' => 'mysql:host=10.16.49.113;port=3306;dbname=doctor_v6;charset=utf8',
  6. 'username' => 'browser',
  7. 'password' => 'browser.360'
  8. ]
  9. ],
  10. 'slaves' => [
  11. [
  12. 'dsn' => 'mysql:host=10.16.49.113;port=3306;dbname=doctor_v6;charset=utf8',
  13. 'username' => 'browser',
  14. 'password' => 'browser.360'
  15. ],
  16. [
  17. 'dsn' => 'mysql:host=10.16.49.113;port=3306;dbname=doctor_v6;charset=utf8',
  18. 'username' => 'browser',
  19. 'password' => 'browser.360'
  20. ],
  21. ]
  22. ]);
  23. $rows = $connection->execute('INSERT INTO `t_user`(`user_name`,`true_name`,`add_time`) VALUES(?, ?, ?)', [
  24. uniqid('u'), uniqid('t'), time()
  25. ]);
  26. $userId = $connection->lastInsertId();
  27. echo '插入的用户id为:'.$userId.';受影响行数为:'.$rows.PHP_EOL;
  28. $rows = $connection->execute('UPDATE `t_user` SET add_time=? WHERE id=?', [
  29. time()+1, $userId
  30. ]);
  31. echo '修改的受影响行数为:'.$rows.PHP_EOL;
  32. $users = $connection->queryAll('SELECT * FROM `t_user` WHERE id=?', [
  33. $userId
  34. ]);
  35. echo '插入和修改的用户信息为:'.json_encode($users[0], JSON_UNESCAPED_UNICODE).PHP_EOL;
  36. //强制读主库
  37. $users = $connection->queryAll('SELECT * FROM `t_user` LIMIT 5', [], true);
  38. echo '主库读取到的数据:'.json_encode($users, JSON_UNESCAPED_UNICODE).PHP_EOL;
  39. //使用事务
  40. try {
  41. $connection->begin();
  42. $rows = $connection->execute('INSERT INTO `t_user`(`user_name`,`true_name`,`add_time`) VALUES(?, ?, ?)', [
  43. uniqid('u'), uniqid('t'), time()
  44. ]);
  45. if($rows < 1) {
  46. throw new \Exception('插入失败');
  47. }
  48. $userId = $connection->lastInsertId();
  49. if($userId % 2 !== 0) {
  50. throw new \Exception('用户id只能为偶数');
  51. }
  52. $connection->commit();
  53. echo '提交事务成功'.PHP_EOL;
  54. }catch (\Exception $exception) {
  55. $connection->rollback();
  56. echo $exception->getMessage().PHP_EOL;
  57. }

如果您需要一个orm封装,可以通过以下指令安装

  1. composer require jhq0113/roach-orm

jhq0113/roach-orm源码及文档地址

QQ群:

姜海强的QQ群

公众号:

360tryst公众号