本文部分內(nèi)容來自 【PHP秒殺系統(tǒng) 高并發(fā)高性能的極致挑戰(zhàn)】
目標
- 如何設計和實現(xiàn)秒殺系統(tǒng)颠猴？
- 實現(xiàn)系統(tǒng)的高并發(fā)甲献、高性能
- 實現(xiàn)系統(tǒng)的安全可靠
特點
- 人多商品少
- 時間短流量高
- 外掛機器
技術(shù)分析
- 瞬時高并發(fā)的處理能力
- 多層次的分布式處理能力
- 人機交互與對抗
系統(tǒng)設計
基本功能
- 后臺
- 活動管理
- 商品管理
- 訂單管理
- 日志管理
- 前臺
- 商品展示
- 秒殺
- 購物車
- 我的訂單
- 安全
- 驗證碼
- 問答
流程
- 秒殺流程
- 驗證用戶登錄狀態(tài)
- 問答驗證步脓、問答信息
- 庫存驗證
- 生成訂單
系統(tǒng)環(huán)境搭建
技術(shù)選型
- 基礎服務： Linux + Nginx + PHP + Mysql + redis
- CDN
- 負載均衡LVS
開發(fā)環(huán)境準備
- 開發(fā)工具：PHPStorm
- 部署開發(fā)環(huán)境： Linux + Nginx + PHP + Mysql + redis
- 代碼管理器：Git
創(chuàng)建項目
- 創(chuàng)建miaosha項目
image.png
- 創(chuàng)建app寄猩，app/Mysql姐刁，config, Common目錄
image.png
調(diào)試封裝類
封裝資源類的調(diào)用铃诬，比如MySQL，Redis座哩，curl等
-
功能及其作用如下：
1 time 性能探針徒扶，計算運行的步驟以及每一步的執(zhí)行效率 2 log 日志記錄，把每一個日志信息記錄下來 3 http 接口調(diào)用的記錄以及耗時的匯總統(tǒng)計 4 redis redis調(diào)用的記錄以及耗時的匯總統(tǒng)計 5 mysql mysql調(diào)用的記錄以及耗時的匯總統(tǒng)計 6 cache memcache調(diào)用的記錄以及耗時的匯總統(tǒng)計 -
在Common目錄下創(chuàng)建DebugLog.php
<?php /** * 調(diào)試日志操作類 * DEBUG_LEVEL=0的時候不會在后端運行根穷， * DEBUG_LEVEL=1的時候會記錄錯誤姜骡、警告信息以及資源調(diào)用的耗時匯總統(tǒng)計， * DEBUG_LEVEL=2的時候屿良，會記錄全部的數(shù)據(jù) * 如果在參數(shù)列表中出現(xiàn) __DEBUG_LEVEL 圈澈，則會強制覆蓋 DEBUG_LEVEL 的值 * 功能列表如下： * 1 time 性能探針，計算運行的步驟以及每一步的執(zhí)行效率 * 2 log 日志記錄尘惧，把每一個日志信息記錄下來 * 3 http 接口調(diào)用的記錄以及耗時的匯總統(tǒng)計 * 4 redis redis調(diào)用的記錄以及耗時的匯總統(tǒng)計 * 5 mysql mysql調(diào)用的記錄以及耗時的匯總統(tǒng)計 * 6 cache memcache調(diào)用的記錄以及耗時的匯總統(tǒng)計 * * **/ namespace App\Common; define('DEBUG_LOG_ERROR', 'ERROR'); define('DEBUG_LOG_WARNING', 'WARNING'); define('DEBUG_LOG_INFO', 'INFO'); if (!defined('DEBUG_LEVEL')) { define('DEBUG_LEVEL', 0); } /** * Created by PhpStorm. * User: wangxinhuang * Date: 2019-07-26 * Time: 20:07 * * @package App\Common */ class DebugLog { private $logId; private $timeList; private $logList; private $httpList; private $redisList; private $mysqlList; private $cacheList; /** * @var bool|DebugLog */ private static $instance = false; private function __construct() { } /** * 初始化調(diào)試日志操作類改抡，沒有經(jīng)過初始化的后續(xù)調(diào)試代碼都不會生效 */ public static function _init() { if (!self::$instance) { self::$instance = new DebugLog(); self::$instance->logId = microtime(); } } /** * 記錄時間碗淌，方便調(diào)試程序執(zhí)行邏輯和每一步的執(zhí)行效率 * @param $label * @param bool $handler */ public static function _time($label, $handler=false) { if (self::$instance === false) return; self::$instance->timeList[] = array($label, microtime(), $handler); } /** * 記錄運行時的調(diào)試信息钟病，分為 DEBUG_LOG_INFO 和 DEBUG_LOG_ERROR，DEBUG_LOG_INFO 只有在全量輸出調(diào)試信息的時候才會輸出 * @param $label * @param $info * @param string $level * @param bool $handler */ public static function _log($label, $info, $level=DEBUG_LOG_INFO, $handler=false) { if (self::$instance === false || (DEBUG_LEVEL < 2 && $level == DEBUG_LOG_INFO)) return; self::$instance->logList[] = array($label, $info
, handler);
}
/**
* 記錄運行時的http請求
* @param $label
* @param $params
* @param $config
* @param $mtime1
* @param $mtime2
* @param null $data
* @param bool $handler
*/
public static function _http($label, $params, $config, $mtime1, $mtime2, $data=null, $handler=false)
{
if (self::$instance === false) return;
if (DEBUG_LEVEL === 1) {
self::$instance->httpList[] = array($label, json_encode($params), json_encode($config), $mtime1, $mtime2, null, $handler);
} else {
self::$instance->httpList[] = array($label, json_encode($params), json_encode($config), $mtime1, $mtime2, $data, $handler);
}
}
/**
* 記錄運行時的redis請求
* @param $label
* @param $params
* @param $config
* @param $mtime1
* @param $mtime2
* @param null $data
* @param bool $handler
*/
public static function _redis($label, $params, $config, $mtime1, $mtime2, $data = null, $handler = false)
{
if (self::$instance === false) return;
if (DEBUG_LEVEL === 1) {
if ('setex' == $label) {
// 過濾掉內(nèi)容塊漾狼，避免日志太多
$params[2] = null;
}
self::$instance->redisList[] = array($label, json_encode($params), json_encode($config), $mtime1, $mtime2, null, $handler);
} else {
self::$instance->redisList[] = array($label, json_encode($params), json_encode($config), $mtime1, $mtime2, $data, $handler);
}
}
/**
* 記錄運行時的mysql請求
* @param $label
* @param $params
* @param $config
* @param $mtime1
* @param $mtime2
* @param null $data
* @param bool $handler
*/
public static function _mysql($label, $params, $config, $mtime1, $mtime2, $data = null, $handler = false)
{
if (self::$instance === false) return;
if (DEBUG_LEVEL === 1) {
self::$instance->mysqlList[] = array($label, json_encode($params), json_encode($config), $mtime1, $mtime2, null, $handler);
} else {
self::$instance->mysqlList[] = array($label, json_encode($params), json_encode($config), $mtime1, $mtime2, $data, $handler);
}
}
/**
* 記錄運行時的memcache請求
* @param $label
* @param $params
* @param $config
* @param $mtime1
* @param $mtime2
* @param null $data
* @param bool $handler
*/
public static function _cache($label, $params, $config, $mtime1, $mtime2, $data = null, $handler = false)
{
if (self::$instance === false) return;
if (DEBUG_LEVEL === 1) {
self::$instance->cacheList[] = array($label, json_encode($params), json_encode($config), $mtime1, $mtime2, null, $handler);
} else {
self::$instance->cacheList[] = array($label, json_encode($params), json_encode($config), $mtime1, $mtime2, $data, $handler);
}
}
/**
* 輸出日志
*/
public static function _show()
{
if (self::$instance === false) return;
if (isset($_SERVER['HTTP_USER_AGENT'])) {
// 界面上可視化模式輸出內(nèi)容
self::$instance->showViews();
} else {
self::$instance->writeLogs();
}
}
/**
* 是否有可視化界面輸出重慢，HTML代碼直接返回到瀏覽器
* @return bool
*/
public static function _is_show_view()
{
if (self::$instance && isset($_SERVER['HTTP_USER_AGENT'])) return true;
return false;
}
/**
* 將microtime的時間字符串轉(zhuǎn)換為float型的毫秒時間
* @param $mt mixed
* @return float|int
*/
private function _floatMicrotime($mt)
{
if (strpos($mt, ' ')) {
list($ms, $m) = explode(' ', $mt);
return ($m + $ms) * 1000;
}
return floatval($mt) * 1000;
}
/**
* 計算兩個microtime時間的間隔時間
* @param $m1 mixed 開始時間
* @param $m2 mixed 結(jié)束時間
* @param int $round 保留小數(shù)位
* @return float
*/
private function _intervalTime($m1, $m2, $round = 3) {
return round(($this->_floatMicrotime($m2) - $this->_floatMicrotime($m1)), $round);
}
/**
* 將調(diào)試信息生成可視化的HTML代碼
*/
private function showViews()
{
$showTime = microtime();
$output = array();
$output[] = "\n";
$output[] = '<ul>';
$output[] = '<li><strong style="font-size:18px;">DebugLog showViews.total process time is ' . $this->_intervalTime($this->logId, $showTime) . 'ms</strong></li>';
if ($this->timeList) {
$total_num = count($this->timeList);
$output[] = '<li><strong style="font-size:18px;">TimeList total count is ' . count($this->timeList) . ', log time is ' . $this->_intervalTime($this->logId, $this->timeList[$total_num - 1][1]) . '</strong></li>';
$lasttime = $this->logId;
$output[] = '<li>0.000 : start debug log ' . $lasttime . '</li>';
foreach ($this->timeList as $info) {
$lasttime2 = $info[1];
$output[] = '<li>'. $this->_intervalTime($lasttime, $lasttime2) . ' : ' . implode("\t", $info) . '</li>';
$lasttime = $lasttime2;
}
}
if ($this->logList) {
$output[] = '<li><strong style="font-size:18px;">LogList total count is ' . count($this->logList) . '</strong></li>';
foreach ($this->logList as $info) {
$output[] = '<li>' . implode("\t", $info) . '</li>';
}
}
if ($this->httpList) {
$current = count($output);
$total_time = 0;
$output[] = null;
$max_num = array();
$multi_num = array();
foreach ($this->httpList as $info) {
$intval = $this->_intervalTime($info[3], $info[4]);
$multi_flag = @json_decode($info[2],true);
if(isset($multi_flag) && isset($multi_flag['is_multi']) && $multi_flag['is_multi']==1)
{
$multi_str = strval($multi_flag['multi_num']);
if($intval > $max_num[$multi_str])
{
$max_num[$multi_str] = $intval;
if(!in_array($multi_str, $multi_num))
{
$multi_num[] = $multi_str;
}
}
}
else
{
$total_time += $intval;
}
if ($info[5] && is_array($info[5])) {
$info[5] = json_encode($info[5]);
}
$output[] = '<li>'. $intval .' : ' . implode("\t", $info) . '</li>';
}
if(!empty($multi_num ))
{
foreach($multi_num as $val)
{
$total_time += $max_num[$val];
}
}
$output[$current] = '<li><strong style="font-size:18px;">HttpList total count is ' . count($this->httpList) . ', total time is ' . $total_time . '</strong></li>';
}
if ($this->redisList) {
$current = count($output);
$total_time = 0;
$output[] = null;
foreach ($this->redisList as $info) {
$intval = $this->_intervalTime($info[3], $info[4]);
$total_time += $intval;
if ($info[5] && is_array($info[5])) {
$info[5] = json_encode($info[5]);
}
$output[] = '<li>'. $intval .' : ' . implode("\t", $info) . '</li>';
}
$output[$current] = '<li><strong style="font-size:18px;">RedisList total count is ' . count($this->redisList) . ', total time is ' . $total_time . '</strong></li>';
}
if ($this->mysqlList) {
$current = count($output);
$total_time = 0;
$output[] = null;
foreach ($this->mysqlList as $info) {
$intval = $this->_intervalTime($info[3], $info[4]);
$total_time += $intval;
if ($info[5] && is_array($info[5])) {
$info[5] = json_encode($info[5]);
} elseif (!$info[5]) {
$info[5] = '';
}
$output[] = '<li>'. $intval .' : ' . implode("\t", $info) . '</li>';
}
$output[$current] = '<li><strong style="font-size:18px;">MysqlList total count is ' . count($this->mysqlList) . ', total time is ' . $total_time . '</strong></li>';
}
if ($this->cacheList) {
$current = count($output);
$total_time = 0;
$output[] = null;
foreach ($this->cacheList as $info) {
$intval = $this->_intervalTime($info[3], $info[4]);
$total_time += $intval;
if ($info[5] && is_array($info[5])) {
$info[5] = json_encode($info[5]);
}
$output[] = '<li>'. $intval .' : ' . implode("\t", $info) . '</li>';
}
$output[$current] = '<li><strong style="font-size:18px;">CacheList total count is ' . count($this->cacheList) . ', total time is ' . $total_time . '</strong></li>';
}
$output[] = '</ul>';
echo implode("\n", $output);
}
/**
* 將調(diào)試日志寫入到本地文件中饥臂，使用JSON格式保存為一行
*/
public function writeLogs()
{
$showTime = microtime();
if (!defined('DEBUG_LOG_PATH')) {
define('DEBUG_LOG_PATH', '/var/log/');
}
$serverList = array(
'SCRIPT_NAME' => $_SERVER['SCRIPT_NAME'],
'REQUEST_URI' => $_SERVER['REQUEST_URI'],
'REMOTE_ADDR:PORT' => $_SERVER['REMOTE_ADDR'] . ':' . $_SERVER['REMOTE_PORT'],
);
$datalist = array(
'logId'=>$this->logId,
'logTime'=>$showTime,
'timeList'=>$this->timeList,
'logList'=>$this->logList,
'httpList'=>$this->httpList,
'redisList'=>$this->redisList,
'mysqlList'=>$this->mysqlList,
'server'=>$serverList,
);
$str = json_encode($datalist);
$str = str_replace("\n", ' ', $str);
$str .= "\n";
$file_path = DEBUG_LOG_PATH . 'discuz_debug.log';
if($fd = @fopen($file_path, 'a')) {
fputs($fd, $str);
fclose($fd);
}
}
/**
* 將消息輸出到指定的文件
* 默認 define('DEBUG_LOG_PATH', '~/log/php/today/')
* @param $msg
* @param string $file
*/
public static function writeDebugLog($msg, $file='discuz_php.log')
{
$dtime = date('Y-m-d H:i:s');
if (!defined('DEBUG_LOG_PATH')) {
$default_path = '/var/log/';
if (file_exists($default_path)) {
define('DEBUG_LOG_PATH', $default_path);
} else {
define('DEBUG_LOG_PATH', '');
}
}
$str_cookie = 'no cookie';
$str_server = json_encode(array($_SERVER['HTTP_X_FORWARDED_FOR'], $_SERVER['REMOTE_ADDR'], $_SERVER['HTTP_HOST'], $_SERVER['REQUEST_URI']));
$str = "[$dtime]||$msg||$str_cookie||$str_server\n";
$file_path = DEBUG_LOG_PATH . $file;
if($fd = @fopen($file_path, 'a')) {
fputs($fd, $str);
fclose($fd);
}
}
/**
* 通過PHP的 debug_backtrace 可以詳細的查看到方法調(diào)用的細節(jié)情況
* @param int $deep
* @param bool $all
*/
public static function writeBacktrace($deep=3, $all=false)
{
$result = array();
$trace = debug_backtrace();
unset($trace[0]);
if ($deep < count($trace)) {
for ($i = 1; $i <= $deep; $i++) {
$info = $trace[$i];
if (isset($info['object']) && $all === false) {
unset($info['object']);
}
$result[] = $info;
}
} elseif ($all === false) {
foreach ($trace as $info) {
if (isset($info['object'])) {
unset($info['object']);
}
$result[] = $info;
}
} else {
$result = $trace;
}
self::writeDebugLog(json_encode($result), 'backtrace.log');
}
}
```
Rdis封裝類
-
創(chuàng)建配置文件config/redis.ini.php
<?php /** * Created by PhpStorm. * User: wangxinhuang * Date: 2019-07-27 * Time: 10:19 */ $config['redis']['instance1'] = array( 'default' => array( 'host' => '127.0.0.1', 'port' => '6379', 'timeout' => 5, 'pconnect' => 1, 'password' => '' ) ); $config['redis']['instance2'] = array( 'default' => array( 'host' => '127.0.0.1', 'port' => '6379', 'timeout' => 5, 'pconnect' => 1, 'password' => '' ) ); -
創(chuàng)建Helpers目錄和Helpers/RedisHelper.php
<?php namespace App\Helpers; use App\Common\DebugLog; /** * Created by PhpStorm. * User: wangxinhuang * Date: 2019-07-27 * Time: 10:24 * * @package App\Helpers */ class RedisHelper { /** * @var string */ private $_config_name = ''; /** * redis配置信息 * @var null */ private $_redis_config = null; /** * redis作用域 * @var null */ private $_server_region = null; /** * 超時 * @var int */ public $timeout = 1; /** * @var \Redis */ private $_redis = null; /** * @var array */ private static $instances = array(); /** * 計數(shù)器，連接重試 * @var int */ private static $connect_error = 0; /** * @var int */ private $call_error = 0; /** * RedisHelper constructor. * @param string $_config_name * @param null $_redis_config * @param null $_server_region */ public function __construct($config_name, $redis_config, $server_region) { if ($config_name && $redis_config && $server_region) { $this->_config_name = $config_name; $this->_redis_config = $redis_config; $this->_server_region = $server_region; $this->timeout = isset($this->_redis_config[$server_region]['timeout']) ? $this->_redis_config[$server_region]['timeout'] : $this->timeout; try { $this->_redis = new \Redis(); $this->_redis->connect($this->_redis_config[$server_region]['host'], $this->_redis_config[$server_region]['port'], $this->timeout); $password = $this->_redis_config[$server_region]['password']; if ($password && !$this->_redis->auth($password)) { $this->_redis = null; } } catch (\Exception $exception) { $this->_redis = null; } } else { $this->_redis = null; } } /** * Redis實例公開方法 * @param $config_name string 配置名稱 * @param $redis_config array 配置信息 * @param $server_region string 作用域 * @return bool|mixed */ public static function instance($config_name, $redis_config, $server_region) { if (!$config_name || !$redis_config) { return false; } // 判斷實例是否存在似踱， // $starttime = microtime(); $only_key = $config_name . ':' . $server_region; if (!isset(self::$instances[$only_key])) { try { self::$instances[$only_key] = new RedisHelper($config_name, $redis_config, $server_region); self::$connect_error = 0; } catch (\Exception $exception) { if (self::$connect_error < 2) { self::$connect_error += 1; return self::instance($config_name, $redis_config, $server_region); } else { self::$connect_error = 0; self::$instances[$only_key] = new RedisHelper(false, false, false); } } } $redis_config_info = array(); if ($redis_config && isset($redis_config[$server_region]) && isset($redis_config[$server_region]['password'])) { $redis_config_info = $redis_config[$server_region]; unset($redis_config_info['password']); } DebugLog::_redis('redis_instance', $config_name, $redis_config_info, $starttime, microtime(), null); self::$connect_error = 0; return self::$instances[$only_key]; } /** * 魔術(shù)方法隅熙， 沒有定義的方法都會走這邊 * @param $name * @param $arguments * @return array|bool|mixed */ public function __call($name, $arguments) { if (!$this->_redis) { return false; } $starttime = microtime(); try { if ('scan' == $name) { $data = $this->_redis->scan($arguments[0]); } else { $data = call_user_func_array(array($this->_redis, $name), $arguments); } } catch (\Exception $exception) { if ($this->call_error < 2) { $this->call_error++; return call_user_func_array(array($this->_redis, $name), $arguments); } else { $this->call_error = 0; } $data = false; } $this->call_error = 0; $redis_config = $this->_redis_config[$this->_server_region]; if ($redis_config && isset($redis_config['password'])) { unset($redis_config['password']); } DebugLog::_redis($name, $arguments, $redis_config, $starttime, microtime(), (is_string($data) || is_array($data)) ? $data : null); return $data; } /** * 銷毀 */ public function __destruct() { if ($this->_redis != NULL) { $this->_redis->close(); } } } -
封裝RedisHelper，創(chuàng)建Common/Datasource.php
<?php namespace App\Common; use App\Helpers\RedisHelper; /** * Created by PhpStorm. * User: wangxinhuang * Date: 2019-07-27 * Time: 10:56 * * @package App\Common */ class Datasource { /** * redis實例 * @var array */ public static $redises = array(); /** * Datasource constructor. */ public function __construct() { } /** * 獲取redis實例 * @param null $config_name * @param string $server_region * @return mixed|null */ public static function getRedis($config_name = null, $server_region = 'default') { if ($config_name === null) { return null; } // 判斷是否存在核芽，防止重復創(chuàng)建囚戚，浪費資源 if (isset(self::$redises[$config_name]) && self::$redises[$config_name]) { return self::$redises[$config_name]; } // 獲取全局配置信息 global $config; $redis_config = $config['redis'][$config_name]; // 創(chuàng)建Redis實例 try { self::$redises[$config_name] = RedisHelper::instance($config_name, $redis_config, $server_region); } catch (\Exception $exception) { self::$redises[$config_name] = null; } return self::$redises[$config_name]; } }
MySQL封裝類
-
創(chuàng)建配置文件config/db.ini.php
<?php /** * Created by PhpStorm. * User: wangxinhuang * Date: 2019-07-26 * Time: 19:07 */ // 數(shù)據(jù)庫配置文件 // 主庫 $config['db']['master'] = array( 'host' => '127.0.0.1:3306', 'dbname' => 'miaosha', 'user' => 'root', 'password' => '', ); // 從庫 $config['db']['slave'] = array( 'host' => '127.0.0.1:3306', 'dbname' => 'miaosha', 'user' => 'root', 'password' => '', ); 在app/Mysql目錄下創(chuàng)建Db.php
```
<?php
namespace App\Mysql;
use App\Common\DebugLog;
use PDO;
use PDOException;
/**
* DB - A simple database class
* PDO寫入數(shù)據(jù)庫
* @modify 黃旺鑫
* @author Author: Vivek Wicky Aswal. (https://twitter.com/#!/VivekWickyAswal)
* @git https://github.com/wickyaswal/php-my-sql-pdo-database-class
* @version 0.2ab
*
*/
class DB
{
# @object, The PDO object
/**
* @var PDO
*/
private $pdo;
# @object, PDO statement object
/**
* @var \PDOStatement
*/
private $sQuery;
# @array, The database settings
private $settings;
# @bool , Connected to the database
private $bConnected = false;
# @object, Object for logging exceptions
private $log;
# @array, The parameters of the SQL query
private $parameters;
private static $instances = array();
/**
* 獲取單例
* @param string $name
* @return mixed
*/
public static function getInstance($name = 'master') {
// 判斷是否已存在實例， 使用單例
if (isset(self::$instances[$name])) {
return self::$instances[$name];
}
self::$instances[$name] = new DB($name);
return self::$instances[$name];
}
/**
* Default Constructor
*
* 1. Instantiate Log class.
* 2. Connect to database.
* 3. Creates the parameter array.
*/
private function __construct($name = 'master')
{
$this->Connect($name);
$this->parameters = array();
}
/**
* This method makes connection to the database.
*
* 1. Reads the database settings from a ini file.
* 2. Puts the ini content into the settings array.
* 3. Tries to connect to the database.
* 4. If connection failed, exception is displayed and a log file gets created.
*/
private function Connect($name = 'master')
{
// 全局配置信息
global $config;
$mtime1 = microtime();
$this->settings = $config['db'][$name];
$dsn = 'mysql:dbname=' . $this->settings["dbname"] . ';host=' . $this->settings["host"] . '';
try {
# Read settings from INI file, set UTF8
$this->pdo = new PDO($dsn, $this->settings["user"], $this->settings["password"], array(
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"
));
# We can now log any exceptions on Fatal error.
$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
# Disable emulation of prepared statements, use REAL prepared statements instead.
$this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
# Connection succeeded, set the boolean to true.
$this->bConnected = true;
}
catch (PDOException $e) {
# Write into log
print_r($e);
echo $this->ExceptionLog($e->getMessage());
die();
}
$mtime2 = microtime();
// 輸出日志
DebugLog::_mysql('connect', null, array('host' => $this->settings['host'],
'dbname' => $this->settings['dbname']), $mtime1, $mtime2, null);
}
/*
* You can use this little method if you want to close the PDO connection
*
*/
public function CloseConnection()
{
# Set the PDO object to null to close the connection
# http://www.php.net/manual/en/pdo.connections.php
$this->pdo = null;
}
/**
* Every method which needs to execute a SQL query uses this method.
*
* 1. If not connected, connect to the database.
* 2. Prepare Query.
* 3. Parameterize Query.
* 4. Execute Query.
* 5. On exception : Write Exception into the log + SQL query.
* 6. Reset the Parameters.
*/
private function Init($query, $parameters = "")
{
# Connect to database
if (!$this->bConnected) {
$this->Connect();
}
try {
# Prepare query
$this->sQuery = $this->pdo->prepare($query);
# Add parameters to the parameter array
if ($parameters && isset($parameters[0])) {
// ? 占位符形式
# Execute SQL
$this->sQuery->execute($this->parameters);
} else {
// : fieldname 字段名形式
$this->bindMore($parameters);
# Bind parameters
if (!empty($this->parameters)) {
foreach ($this->parameters as $param) {
$parameters = explode("\x7F", $param);
$this->sQuery->bindParam($parameters[0], $parameters[1]);
// if(is_int($value[1])) {
// $type = PDO::PARAM_INT;
// } else if(is_bool($value[1])) {
// $type = PDO::PARAM_BOOL;
// } else if(is_null($value[1])) {
// $type = PDO::PARAM_NULL;
// } else {
// $type = PDO::PARAM_STR;
// }
// // Add type when binding the values to the column
// $this->sQuery->bindValue($value[0], $value[1], $type);
}
}
# Execute SQL
$this->sQuery->execute();
}
}
catch (PDOException $e) {
# Write into log and display Exception
echo $this->ExceptionLog($e->getMessage(), $query);
die();
}
# Reset the parameters
$this->parameters = array();
}
/**
* @void
*
* Add the parameter to the parameter array
* @param string $para
* @param string $value
*/
public function bind($para, $value)
{
if (is_array($para)) {
$para = json_encode($para);
}
if (is_array($value)) {
$value = json_encode($value);
}
$this->parameters[sizeof($this->parameters)] = ":" . $para . "\x7F" . $value;
// $this->parameters[sizeof($this->parameters)] = [":" . $para , $value];
}
/**
* @void
*
* Add more parameters to the parameter array
* @param array $parray
*/
public function bindMore($parray)
{
if (empty($this->parameters) && is_array($parray)) {
$columns = array_keys($parray);
foreach ($columns as $i => &$column) {
$this->bind($column, $parray[$column]);
}
}
}
/**
* If the SQL query contains a SELECT or SHOW statement it returns an array containing all of the result set row
* If the SQL statement is a DELETE, INSERT, or UPDATE statement it returns the number of affected rows
*
* @param string $query
* @param array $params
* @param int $fetchmode
* @return mixed
*/
public function query($query, $params = null, $fetchmode = PDO::FETCH_ASSOC)
{
$mtime1 = microtime();
$query = trim($query);
// $query = trim(str_replace("\r", " ", $query));
$this->Init($query, $params);
$rawStatement = explode(" ", $query);
// $rawStatement = explode(" ", preg_replace("/\s+|\t+|\n+/", " ", $query));
# Which SQL statement is used
$statement = strtolower($rawStatement[0]);
$result = NULL;
if ($statement === 'select' || $statement === 'show') {
$result = $this->sQuery->fetchAll($fetchmode);
} elseif ($statement === 'insert' || $statement === 'update' || $statement === 'delete') {
$result = $this->sQuery->rowCount();
}
$mtime2 = microtime();
DebugLog::_mysql('query: ' . $query, $params, array('host' => $this->settings['host'],
'dbname' => $this->settings['dbname']), $mtime1, $mtime2, $result);
return $result;
}
/**
* Returns the last inserted id.
* @return string
*/
public function lastInsertId()
{
return $this->pdo->lastInsertId();
}
/**
* Starts the transaction
* @return boolean, true on success or false on failure
*/
public function beginTransaction()
{
return $this->pdo->beginTransaction();
}
/**
* Execute Transaction
* @return boolean, true on success or false on failure
*/
public function executeTransaction()
{
return $this->pdo->commit();
}
/**
* Rollback of Transaction
* @return boolean, true on success or false on failure
*/
public function rollBack()
{
return $this->pdo->rollBack();
}
/**
* Returns an array which represents a column from the result set
*
* @param string $query
* @param array $params
* @return array
*/
public function column($query, $params = null)
{
$mtime1 = microtime();
$this->Init($query, $params);
$Columns = $this->sQuery->fetchAll(PDO::FETCH_NUM);
$column = null;
foreach ($Columns as $cells) {
$column[] = $cells[0];
}
$mtime2 = microtime();
DebugLog::_mysql('column: ' . $query, $params, array('host' => $this->settings['host'],
'dbname' => $this->settings['dbname']), $mtime1, $mtime2, $column);
return $column;
}
/**
* Returns an array which represents a row from the result set
*
* @param string $query
* @param array $params
* @param int $fetchmode
* @return array
*/
public function row($query, $params = null, $fetchmode = PDO::FETCH_ASSOC)
{
$mtime1 = microtime();
$this->Init($query, $params);
$result = $this->sQuery->fetch($fetchmode);
// $this->sQuery->closeCursor(); // Frees up the connection to the server so that other SQL statements may be issued,
$mtime2 = microtime();
DebugLog::_mysql('row: ' . $query, $params, array('host' => $this->settings['host'],
'dbname' => $this->settings['dbname']), $mtime1, $mtime2, $result);
return $result;
}
/**
* Returns the value of one single field/column
*
* @param string $query
* @param array $params
* @return string
*/
public function single($query, $params = null)
{
$mtime1 = microtime();
$this->Init($query, $params);
$result = $this->sQuery->fetchColumn();
$mtime2 = microtime();
DebugLog::_mysql('single: ' . $query, $params, array('host' => $this->settings['host'],
'dbname' => $this->settings['dbname']), $mtime1, $mtime2, $result);
// $this->sQuery->closeCursor(); // Frees up the connection to the server so that other SQL statements may be issued
return $result;
}
/**
* Writes the log and returns the exception
*
* @param string $message
* @param string $sql
* @return string
*/
private function ExceptionLog($message, $sql = "")
{
$exception = 'Unhandled Exception. <br />';
if (!empty($sql)) {
# Add the Raw SQL to the Log
$message .= "\r\nRaw SQL : " . $sql;
$exception .= $message;
$exception .= "<br /> You can find the error back in the log.";
return $exception;
}
# Write into log
// $this->log->write($message);
return '';
}
}
?>
```
-
在app/Mysql目錄下創(chuàng)建Crud.php
<?php namespace App\Mysql; /** * Easy Crud - This class kinda works like ORM. Just created for fun :) * @modify 王毅 * @author Author: Vivek Wicky Aswal. (https://twitter.com/#!/VivekWickyAswal) * @version 0.1a */ class Crud { private $db; protected $fields; public $variables; public function __construct($data = array()) { if ($this->fields && $data) { foreach ($data as $k => $d) { if (!in_array($k, $this->fields)) { unset($data[$k]); } } } $this->variables = $data; } public function setDb($db) { $this->db = $db; } public function getDb() { if (!$this->db) { $this->db = DB::getInstance('master'); } return $this->db; } public function __set($name,$value){ if(strtolower($name) === $this->pk) { $this->variables[$this->pk] = $value; } else { if (!$this->fields || in_array($name, $this->fields)) { $this->variables[$name] = $value; } } } public function __get($name) { if(is_array($this->variables)) { if(array_key_exists($name,$this->variables)) { return $this->variables[$name]; } } $trace = debug_backtrace(); trigger_error( 'Undefined property via __get(): ' . $name . ' in ' . $trace[0]['file'] . ' on line ' . $trace[0]['line'], E_USER_NOTICE); return null; } public function save($id = "0") { $this->variables[$this->pk] = $id ? $id : $this->variables[$this->pk]; $fieldsvals = ''; $columns = array_keys($this->variables); foreach($columns as $column) { if($column !== $this->pk) $fieldsvals .= "`{$column}` = :". $column . ","; } $fieldsvals = substr_replace($fieldsvals , '', -1); if(count($columns) > 1 ) { $sql = "UPDATE `" . $this->table . "` SET " . $fieldsvals . " WHERE `" . $this->pk . "`= :" . $this->pk; return $this->getDb()->query($sql,$this->variables); } } public function create() { $bindings = $this->variables; if(!empty($bindings)) { $fields = array_keys($bindings); $fieldsvals = array('`' . implode("`,`",$fields) . '`', ":" . implode(",:",$fields)); $sql = "INSERT INTO `".$this->table."` (".$fieldsvals[0].") VALUES (".$fieldsvals[1].")"; } else { $sql = "INSERT INTO `".$this->table."` () VALUES ()"; } $ok = $this->getDb()->query($sql,$bindings); if ($ok) { return $this->getDB()->lastInsertId(); } else { return $ok; } } public function delete($id = "") { $id = (empty($this->variables[$this->pk])) ? $id : $this->variables[$this->pk]; if(!empty($id)) { $sql = "DELETE FROM `" . $this->table . "` WHERE `" . $this->pk . "`= :" . $this->pk. " LIMIT 1" ; return $this->getDb()->query($sql,array($this->pk=>$id)); } } public function get($id = "") { $id = $id ? $id : $this->variables[$this->pk]; if(!empty($id)) { $sql = "SELECT * FROM `" . $this->table ."` WHERE `" . $this->pk . "`= :" . $this->pk . " LIMIT 1"; $this->variables = $this->getDb()->row($sql,array($this->pk=>$id)); } return $this->variables; } public function all(){ return $this->getDb()->query("SELECT * FROM `" . $this->table . '`'); } public function count(){ return $this->getDb()->query("SELECT COUNT(1) FROM `" . $this->table . '`'); } // public function min($field) { // if($field) // return $this->getDb()->single("SELECT min(" . $field . ")" . " FROM " . $this->table); // } // // public function max($field) { // if($field) // return $this->getDb()->single("SELECT max(" . $field . ")" . " FROM " . $this->table); // } // // public function avg($field) { // if($field) // return $this->getDb()->single("SELECT avg(" . $field . ")" . " FROM " . $this->table); // } // // public function sum($field) { // if($field) // return $this->getDb()->single("SELECT sum(" . $field . ")" . " FROM " . $this->table); // } }
