php鏈式操作mysql數(shù)據(jù)庫(封裝類帶使用示例)
更新時間：2023年02月25日 10:11:31 投稿：yin
本文代碼將一些簡單常用的SQL語句，拆分、封裝成鏈式函數(shù)與終結(jié)函數(shù)，鏈式操作沒有先后之分，實現(xiàn)傻瓜式mysql數(shù)據(jù)庫操作。?同時學習下靜態(tài)成員函數(shù),實現(xiàn)鏈式操作的具體語法。
本文代碼將一些簡單常用的SQL語句，拆分、封裝成鏈式函數(shù)與終結(jié)函數(shù)，鏈式操作沒有先后之分，實現(xiàn)傻瓜式mysql數(shù)據(jù)庫操作。 同時學習下靜態(tài)成員函數(shù),實現(xiàn)鏈式操作的具體語法。
鏈式操作是利用運算符進行連續(xù)操作。它的特點是一條語句中出現(xiàn)兩個或兩個以上相同的操作符。鏈式操作，說白了其實就是鏈式的調(diào)用對象的方法。既然要實現(xiàn)字符串的鏈式操作，那么就要實現(xiàn)一個字符串類，然后對這個類的對象進行調(diào)用操作。時代在變遷，技術(shù)不斷進度，代碼既要好用，還得優(yōu)雅。相比傳統(tǒng)調(diào)用方法，采用鏈式操作后，一步到位。
封裝類常用操作使用示例
<?php
// 初始化db連接
$db = new \Workerman\MySQL\Connection('host', 'port', 'user', 'password', 'db_name');
// 獲取所有數(shù)據(jù)
$db->select('ID,Sex')->from('Persons')->where('sex= :sex AND ID = :id')->bindValues(array('sex'=>'M', 'id' => 1))->query();
//等價于
$db->select('ID,Sex')->from('Persons')->where("sex= 'M' AND ID = 1")->query();
//等價于
$db->query("SELECT ID,Sex FROM `Persons` WHERE sex='M' AND ID = 1");
// 獲取一行數(shù)據(jù)
$db->select('ID,Sex')->from('Persons')->where('sex= :sex')->bindValues(array('sex'=>'M'))->row();
//等價于
$db->select('ID,Sex')->from('Persons')->where("sex= 'M' ")->row();
//等價于
$db->row("SELECT ID,Sex FROM `Persons` WHERE sex='M'");
// 獲取一列數(shù)據(jù)
$db->select('ID')->from('Persons')->where('sex= :sex')->bindValues(array('sex'=>'M'))->column();
//等價于
$db->select('ID')->from('Persons')->where("sex= 'F' ")->column();
//等價于
$db->column("SELECT `ID` FROM `Persons` WHERE sex='M'");
// 獲取單個值
$db->select('ID')->from('Persons')->where('sex= :sex')->bindValues(array('sex'=>'M'))->single();
// 等價于
$db->select('ID')->from('Persons')->where("sex= 'F' ")->single();
//等價于
$db->single("SELECT ID FROM `Persons` WHERE sex='M'");
// 復(fù)雜查詢
$db->select('*')->from('table1')->innerJoin('table2','table1.uid = table2.uid')->where('age > :age')->groupBy(array('aid'))->having('foo="foo"')->orderByASC/*orderByDESC*/(array('did'))
->limit(10)->offset(20)->bindValues(array('age' => 13));
// 等價于
$db->query('SELECT * FROM `table1` INNER JOIN `table2` ON `table1`.`uid` = `table2`.`uid`
WHERE age > 13 GROUP BY aid HAVING foo="foo" ORDER BY did LIMIT 10 OFFSET 20');
// 插入
$insert_id = $db->insert('Persons')->cols(array(
'Firstname'=>'abc',
'Lastname'=>'efg',
'Sex'=>'M',
'Age'=>13))->query();
// 等價于
$insert_id = $db->query("INSERT INTO `Persons` ( `Firstname`,`Lastname`,`Sex`,`Age`)
VALUES ( 'abc', 'efg', 'M', 13)");
// 更新
$row_count = $db->update('Persons')->cols(array('sex'))->where('ID=1')
->bindValue('sex', 'F')->query();
// 等價于
$row_count = $db->update('Persons')->cols(array('sex'=>'F'))->where('ID=1')->query();
// 等價于
$row_count = $db->query("UPDATE `Persons` SET `sex` = 'F' WHERE ID=1");
// 刪除
$row_count = $db->delete('Persons')->where('ID=9')->query();
// 等價于
$row_count = $db->query("DELETE FROM `Persons` WHERE ID=9");
// 事務(wù)
$db->beginTrans();
// ....
$db->commitTrans(); // or $db->rollBackTrans();封裝源碼（保存文件引用）
<?php
/**
* 數(shù)據(jù)庫連接類，依賴 PDO_MYSQL 擴展
* 在 https://github.com/auraphp/Aura.SqlQuery 的基礎(chǔ)上修改而成
*/
class Connection
{
/**
* SELECT
*
* @var array
*/
protected $union = array();
/**
* 是否是更新
*
* @var bool
*/
protected $for_update = false;
/**
* 選擇的列
*
* @var array
*/
protected $cols = array();
/**
* 從哪些表里面 SELECT
*
* @var array
*/
protected $from = array();
/**
* $from 當前的 key
*
* @var int
*/
protected $from_key = -1;
/**
* GROUP BY 的列
*
* @var array
*/
protected $group_by = array();
/**
* HAVING 條件數(shù)組.
*
* @var array
*/
protected $having = array();
/**
* HAVING 語句中綁定的值.
*
* @var array
*/
protected $bind_having = array();
/**
* 每頁多少條記錄
*
* @var int
*/
protected $paging = 10;
/**
* sql 中綁定的值
*
* @var array
*/
protected $bind_values = array();
/**
* WHERE 條件.
*
* @var array
*/
protected $where = array();
/**
* WHERE 語句綁定的值
*
* @var array
*/
protected $bind_where = array();
/**
* ORDER BY 的列
*
* @var array
*/
protected $order_by = array();
/**
* ORDER BY 的排序方式,默認為升序
*
* @var bool
*/
protected $order_asc = true;
/**
* SELECT 多少記錄
*
* @var int
*/
protected $limit = 0;
/**
* 返回記錄的游標
*
* @var int
*/
protected $offset = 0;
/**
* flags 列表
*
* @var array
*/
protected $flags = array();
/**
* 操作哪個表
*
* @var string
*/
protected $table;
/**
* 表.列 和 last-insert-id 映射
*
* @var array
*/
protected $last_insert_id_names = array();
/**
* INSERT 或者 UPDATE 的列
*
* @param array
*/
protected $col_values;
/**
* 返回的列
*
* @var array
*/
protected $returning = array();
/**
* sql 的類型 SELECT INSERT DELETE UPDATE
*
* @var string
*/
protected $type = '';
/**
* pdo 實例
*
* @var PDO
*/
protected $pdo;
/**
* PDOStatement 實例
*
* @var \PDOStatement
*/
protected $sQuery;
/**
* 數(shù)據(jù)庫用戶名密碼等配置
*
* @var array
*/
protected $settings = array();
/**
* sql 的參數(shù)
*
* @var array
*/
protected $parameters = array();
/**
* 最后一條直行的 sql
*
* @var string
*/
protected $lastSql = '';
/**
* 是否執(zhí)行成功
*
* @var bool
*/
protected $success = false;
/**
* 選擇哪些列
*
* @param string|array $cols
* @return self
*/
public function select($cols = '*')
{
$this->type = 'SELECT';
if (!is_array($cols)) {
$cols = explode(',', $cols);
}
$this->cols($cols);
return $this;
}
/**
* 從哪個表刪除
*
* @param string $table
* @return self
*/
public function delete($table)
{
$this->type = 'DELETE';
$this->table = $this->quoteName($table);
$this->fromRaw($this->quoteName($table));
return $this;
}
/**
* 更新哪個表
*
* @param string $table
* @return self
*/
public function update($table)
{
$this->type = 'UPDATE';
$this->table = $this->quoteName($table);
return $this;
}
/**
* 向哪個表插入
*
* @param string $table
* @return self
*/
public function insert($table)
{
$this->type = 'INSERT';
$this->table = $this->quoteName($table);
return $this;
}
/**
*
* 設(shè)置 SQL_CALC_FOUND_ROWS 標記.
*
* @param bool $enable
* @return self
*/
public function calcFoundRows($enable = true)
{
$this->setFlag('SQL_CALC_FOUND_ROWS', $enable);
return $this;
}
/**
* 設(shè)置 SQL_CACHE 標記
*
* @param bool $enable
* @return self
*/
public function cache($enable = true)
{
$this->setFlag('SQL_CACHE', $enable);
return $this;
}
/**
* 設(shè)置 SQL_NO_CACHE 標記
*
* @param bool $enable
* @return self
*/
public function noCache($enable = true)
{
$this->setFlag('SQL_NO_CACHE', $enable);
return $this;
}
/**
* 設(shè)置 STRAIGHT_JOIN 標記.
*
* @param bool $enable
* @return self
*/
public function straightJoin($enable = true)
{
$this->setFlag('STRAIGHT_JOIN', $enable);
return $this;
}
/**
* 設(shè)置 HIGH_PRIORITY 標記
*
* @param bool $enable
* @return self
*/
public function highPriority($enable = true)
{
$this->setFlag('HIGH_PRIORITY', $enable);
return $this;
}
/**
* 設(shè)置 SQL_SMALL_RESULT 標記
*
* @param bool $enable
* @return self
*/
public function smallResult($enable = true)
{
$this->setFlag('SQL_SMALL_RESULT', $enable);
return $this;
}
/**
* 設(shè)置 SQL_BIG_RESULT 標記
*
* @param bool $enable
* @return self
*/
public function bigResult($enable = true)
{
$this->setFlag('SQL_BIG_RESULT', $enable);
return $this;
}
/**
* 設(shè)置 SQL_BUFFER_RESULT 標記
*
* @param bool $enable
* @return self
*/
public function bufferResult($enable = true)
{
$this->setFlag('SQL_BUFFER_RESULT', $enable);
return $this;
}
/**
* 設(shè)置 FOR UPDATE 標記
*
* @param bool $enable
* @return self
*/
public function forUpdate($enable = true)
{
$this->for_update = (bool)$enable;
return $this;
}
/**
* 設(shè)置 DISTINCT 標記
*
* @param bool $enable
* @return self
*/
public function distinct($enable = true)
{
$this->setFlag('DISTINCT', $enable);
return $this;
}
/**
* 設(shè)置 LOW_PRIORITY 標記
*
* @param bool $enable
* @return self
*/
public function lowPriority($enable = true)
{
$this->setFlag('LOW_PRIORITY', $enable);
return $this;
}
/**
* 設(shè)置 IGNORE 標記
*
* @param bool $enable
* @return self
*/
public function ignore($enable = true)
{
$this->setFlag('IGNORE', $enable);
return $this;
}
/**
* 設(shè)置 QUICK 標記
*
* @param bool $enable
* @return self
*/
public function quick($enable = true)
{
$this->setFlag('QUICK', $enable);
return $this;
}
/**
* 設(shè)置 DELAYED 標記
*
* @param bool $enable
* @return self
*/
public function delayed($enable = true)
{
$this->setFlag('DELAYED', $enable);
return $this;
}
/**
* 序列化
*
* @return string
*/
public function __toString()
{
$union = '';
if ($this->union) {
$union = implode(' ', $this->union) . ' ';
}
return $union . $this->build();
}
/**
* 設(shè)置每頁多少條記錄
*
* @param int $paging
* @return self
*/
public function setPaging($paging)
{
$this->paging = (int)$paging;
return $this;
}
/**
* 獲取每頁多少條記錄
*
* @return int
*/
public function getPaging()
{
return $this->paging;
}
/**
* 獲取綁定在占位符上的值
*/
public function getBindValues()
{
switch ($this->type) {
case 'SELECT':
return $this->getBindValuesSELECT();
case 'DELETE':
case 'UPDATE':
case 'INSERT':
return $this->getBindValuesCOMMON();
default :
throw new Exception("type err");
}
}
/**
* 獲取綁定在占位符上的值
*
* @return array
*/
public function getBindValuesSELECT()
{
$bind_values = $this->bind_values;
$i = 1;
foreach ($this->bind_where as $val) {
$bind_values[$i] = $val;
$i++;
}
foreach ($this->bind_having as $val) {
$bind_values[$i] = $val;
$i++;
}
return $bind_values;
}
/**
*
* SELECT選擇哪些列
*
* @param mixed $key
* @param string $val
* @return void
*/
protected function addColSELECT($key, $val)
{
if (is_string($key)) {
$this->cols[$val] = $key;
} else {
$this->addColWithAlias($val);
}
}
/**
* SELECT 增加選擇的列
*
* @param string $spec
*/
protected function addColWithAlias($spec)
{
$parts = explode(' ', $spec);
$count = count($parts);
if ($count == 2 && trim($parts[0]) != '' && trim($parts[1]) != '') {
$this->cols[$parts[1]] = $parts[0];
} elseif ($count == 3 && strtoupper($parts[1]) == 'AS') {
$this->cols[$parts[2]] = $parts[0];
} else {
$this->cols[] = trim($spec);
}
}
/**
* from 哪個表
*
* @param string $table
* @return self
*/
public function from($table)
{
return $this->fromRaw($this->quoteName($table));
}
/**
* from的表
*
* @param string $table
* @return self
*/
public function fromRaw($table)
{
$this->from[] = array($table);
$this->from_key++;
return $this;
}
/**
*
* 子查詢
*
* @param string $table
* @param string $name The alias name for the sub-select.
* @return self
*/
public function fromSubSelect($table, $name)
{
$this->from[] = array("($table) AS " . $this->quoteName($name));
$this->from_key++;
return $this;
}
/**
* 增加 join 語句
*
* @param string $table
* @param string $cond
* @param string $type
* @return self
* @throws Exception
*/
public function join($table, $cond = null, $type = '')
{
return $this->joinInternal($type, $table, $cond);
}
/**
* 增加 join 語句
*
* @param string $join inner, left, natural
* @param string $table
* @param string $cond
* @return self
* @throws Exception
*/
protected function joinInternal($join, $table, $cond = null)
{
if (!$this->from) {
throw new Exception('Cannot join() without from()');
}
$join = strtoupper(ltrim("$join JOIN"));
$table = $this->quoteName($table);
$cond = $this->fixJoinCondition($cond);
$this->from[$this->from_key][] = rtrim("$join $table $cond");
return $this;
}
/**
* quote
*
* @param string $cond
* @return string
*
*/
protected function fixJoinCondition($cond)
{
if (!$cond) {
return '';
}
$cond = $this->quoteNamesIn($cond);
if (strtoupper(substr(ltrim($cond), 0, 3)) == 'ON ') {
return $cond;
}
if (strtoupper(substr(ltrim($cond), 0, 6)) == 'USING ') {
return $cond;
}
return 'ON ' . $cond;
}
/**
* inner join
*
* @param string $table
* @param string $cond
* @return self
* @throws Exception
*/
public function innerJoin($table, $cond = null)
{
return $this->joinInternal('INNER', $table, $cond);
}
/**
* left join
*
* @param string $table
* @param string $cond
* @return self
* @throws Exception
*/
public function leftJoin($table, $cond = null)
{
return $this->joinInternal('LEFT', $table, $cond);
}
/**
* right join
*
* @param string $table
* @param string $cond
* @return self
* @throws Exception
*/
public function rightJoin($table, $cond = null)
{
return $this->joinInternal('RIGHT', $table, $cond);
}
/**
* joinSubSelect
*
* @param string $join inner, left, natural
* @param string $spec
* @param string $name sub-select 的別名
* @param string $cond
* @return self
* @throws Exception
*/
public function joinSubSelect($join, $spec, $name, $cond = null)
{
if (!$this->from) {
throw new \Exception('Cannot join() without from() first.');
}
$join = strtoupper(ltrim("$join JOIN"));
$name = $this->quoteName($name);
$cond = $this->fixJoinCondition($cond);
$this->from[$this->from_key][] = rtrim("$join ($spec) AS $name $cond");
return $this;
}
/**
* group by 語句
*
* @param array $cols
* @return self
*/
public function groupBy(array $cols)
{
foreach ($cols as $col) {
$this->group_by[] = $this->quoteNamesIn($col);
}
return $this;
}
/**
* having 語句
*
* @param string $cond
* @return self
*/
public function having($cond)
{
$this->addClauseCondWithBind('having', 'AND', func_get_args());
return $this;
}
/**
* or having 語句
*
* @param string $cond The HAVING condition.
* @return self
*/
public function orHaving($cond)
{
$this->addClauseCondWithBind('having', 'OR', func_get_args());
return $this;
}
/**
* 設(shè)置每頁的記錄數(shù)量
*
* @param int $page
* @return self
*/
public function page($page)
{
$this->limit = 0;
$this->offset = 0;
$page = (int)$page;
if ($page > 0) {
$this->limit = $this->paging;
$this->offset = $this->paging * ($page - 1);
}
return $this;
}
/**
* union
*
* @return self
*/
public function union()
{
$this->union[] = $this->build() . ' UNION';
$this->reset();
return $this;
}
/**
* unionAll
*
* @return self
*/
public function unionAll()
{
$this->union[] = $this->build() . ' UNION ALL';
$this->reset();
return $this;
}
/**
* 重置
*/
protected function reset()
{
$this->resetFlags();
$this->cols = array();
$this->from = array();
$this->from_key = -1;
$this->where = array();
$this->group_by = array();
$this->having = array();
$this->order_by = array();
$this->limit = 0;
$this->offset = 0;
$this->for_update = false;
}
/**
* 清除所有數(shù)據(jù)
*/
protected function resetAll()
{
$this->union = array();
$this->for_update = false;
$this->cols = array();
$this->from = array();
$this->from_key = -1;
$this->group_by = array();
$this->having = array();
$this->bind_having = array();
$this->paging = 10;
$this->bind_values = array();
$this->where = array();
$this->bind_where = array();
$this->order_by = array();
$this->limit = 0;
$this->offset = 0;
$this->flags = array();
$this->table = '';
$this->last_insert_id_names = array();
$this->col_values = array();
$this->returning = array();
$this->parameters = array();
}
/**
* 創(chuàng)建 SELECT SQL
*
* @return string
*/
protected function buildSELECT()
{
return 'SELECT'
. $this->buildFlags()
. $this->buildCols()
. $this->buildFrom()
. $this->buildWhere()
. $this->buildGroupBy()
. $this->buildHaving()
. $this->buildOrderBy()
. $this->buildLimit()
. $this->buildForUpdate();
}
/**
* 創(chuàng)建 DELETE SQL
*/
protected function buildDELETE()
{
return 'DELETE'
. $this->buildFlags()
. $this->buildFrom()
. $this->buildWhere()
. $this->buildOrderBy()
. $this->buildLimit()
. $this->buildReturning();
}
/**
* 生成 SELECT 列語句
*
* @return string
* @throws Exception
*/
protected function buildCols()
{
if (!$this->cols) {
throw new Exception('No columns in the SELECT.');
}
$cols = array();
foreach ($this->cols as $key => $val) {
if (is_int($key)) {
$cols[] = $this->quoteNamesIn($val);
} else {
$cols[] = $this->quoteNamesIn("$val AS $key");
}
}
return $this->indentCsv($cols);
}
/**
* 生成 FROM 語句.
*
* @return string
*/
protected function buildFrom()
{
if (!$this->from) {
return '';
}
$refs = array();
foreach ($this->from as $from) {
$refs[] = implode(' ', $from);
}
return ' FROM' . $this->indentCsv($refs);
}
/**
* 生成 GROUP BY 語句.
*
* @return string
*/
protected function buildGroupBy()
{
if (!$this->group_by) {
return '';
}
return ' GROUP BY' . $this->indentCsv($this->group_by);
}
/**
* 生成 HAVING 語句.
*
* @return string
*/
protected function buildHaving()
{
if (!$this->having) {
return '';
}
return ' HAVING' . $this->indent($this->having);
}
/**
* 生成 FOR UPDATE 語句
*
* @return string
*/
protected function buildForUpdate()
{
if (!$this->for_update) {
return '';
}
return ' FOR UPDATE';
}
/**
* where
*
* @param string|array $cond
* @return self
*/
public function where($cond)
{
if (is_array($cond)) {
foreach ($cond as $key => $val) {
if (is_string($key)) {
$this->addWhere('AND', array($key, $val));
} else {
$this->addWhere('AND', array($val));
}
}
} else {
$this->addWhere('AND', func_get_args());
}
return $this;
}
/**
* or where
*
* @param string|array $cond
* @return self
*/
public function orWhere($cond)
{
if (is_array($cond)) {
foreach ($cond as $key => $val) {
if (is_string($key)) {
$this->addWhere('OR', array($key, $val));
} else {
$this->addWhere('OR', array($val));
}
}
} else {
$this->addWhere('OR', func_get_args());
}
return $this;
}
/**
* limit
*
* @param int $limit
* @return self
*/
public function limit($limit)
{
$this->limit = (int)$limit;
return $this;
}
/**
* limit offset
*
* @param int $offset
* @return self
*/
public function offset($offset)
{
$this->offset = (int)$offset;
return $this;
}
/**
* orderby.
*
* @param array $cols
* @return self
*/
public function orderBy(array $cols)
{
return $this->addOrderBy($cols);
}
/**
* order by ASC OR DESC
*
* @param array $cols
* @param bool $order_asc
* @return self
*/
public function orderByASC(array $cols, $order_asc = true)
{
$this->order_asc = $order_asc;
return $this->addOrderBy($cols);
}
/**
* order by DESC
*
* @param array $cols
* @return self
*/
public function orderByDESC(array $cols)
{
$this->order_asc = false;
return $this->addOrderBy($cols);
}
// -------------abstractquery----------
/**
* 返回逗號分隔的字符串
*
* @param array $list
* @return string
*/
protected function indentCsv(array $list)
{
return ' ' . implode(',', $list);
}
/**
* 返回空格分隔的字符串
*
* @param array $list
* @return string
*/
protected function indent(array $list)
{
return ' ' . implode(' ', $list);
}
/**
* 批量為占位符綁定值
*
* @param array $bind_values
* @return self
*
*/
public function bindValues(array $bind_values)
{
foreach ($bind_values as $key => $val) {
$this->bindValue($key, $val);
}
return $this;
}
/**
* 單個為占位符綁定值
*
* @param string $name
* @param mixed $value
* @return self
*/
public function bindValue($name, $value)
{
$this->bind_values[$name] = $value;
return $this;
}
/**
* 生成 flag
*
* @return string
*/
protected function buildFlags()
{
if (!$this->flags) {
return '';
}
return ' ' . implode(' ', array_keys($this->flags));
}
/**
* 設(shè)置 flag.
*
* @param string $flag
* @param bool $enable
*/
protected function setFlag($flag, $enable = true)
{
if ($enable) {
$this->flags[$flag] = true;
} else {
unset($this->flags[$flag]);
}
}
/**
* 重置 flag
*/
protected function resetFlags()
{
$this->flags = array();
}
/**
*
* 添加 where 語句
*
* @param string $andor 'AND' or 'OR
* @param array $conditions
* @return self
*
*/
protected function addWhere($andor, $conditions)
{
$this->addClauseCondWithBind('where', $andor, $conditions);
return $this;
}
/**
* 添加條件和綁定值
*
* @param string $clause where 、having等
* @param string $andor AND、OR等
* @param array $conditions
*/
protected function addClauseCondWithBind($clause, $andor, $conditions)
{
$cond = array_shift($conditions);
$cond = $this->quoteNamesIn($cond);
$bind =& $this->{"bind_{$clause}"};
foreach ($conditions as $value) {
$bind[] = $value;
}
$clause =& $this->$clause;
if ($clause) {
$clause[] = "$andor $cond";
} else {
$clause[] = $cond;
}
}
/**
* 生成 where 語句
*
* @return string
*/
protected function buildWhere()
{
if (!$this->where) {
return '';
}
return ' WHERE' . $this->indent($this->where);
}
/**
* 增加 order by
*
* @param array $spec The columns and direction to order by.
* @return self
*/
protected function addOrderBy(array $spec)
{
foreach ($spec as $col) {
$this->order_by[] = $this->quoteNamesIn($col);
}
return $this;
}
/**
* 生成 order by 語句
*
* @return string
*/
protected function buildOrderBy()
{
if (!$this->order_by) {
return '';
}
if ($this->order_asc) {
return ' ORDER BY' . $this->indentCsv($this->order_by) . ' ASC';
} else {
return ' ORDER BY' . $this->indentCsv($this->order_by) . ' DESC';
}
}
/**
* 生成 limit 語句
*
* @return string
*/
protected function buildLimit()
{
$has_limit = $this->type == 'DELETE' || $this->type == 'UPDATE';
$has_offset = $this->type == 'SELECT';
if ($has_offset && $this->limit) {
$clause = " LIMIT {$this->limit}";
if ($this->offset) {
$clause .= " OFFSET {$this->offset}";
}
return $clause;
} elseif ($has_limit && $this->limit) {
return " LIMIT {$this->limit}";
}
return '';
}
/**
* Quotes
*
* @param string $spec
* @return string|array
*/
public function quoteName($spec)
{
$spec = trim($spec);
$seps = array(' AS ', ' ', '.');
foreach ($seps as $sep) {
$pos = strripos($spec, $sep);
if ($pos) {
return $this->quoteNameWithSeparator($spec, $sep, $pos);
}
}
return $this->replaceName($spec);
}
/**
* 指定分隔符的 Quotes
*
* @param string $spec
* @param string $sep
* @param int $pos
* @return string
*/
protected function quoteNameWithSeparator($spec, $sep, $pos)
{
$len = strlen($sep);
$part1 = $this->quoteName(substr($spec, 0, $pos));
$part2 = $this->replaceName(substr($spec, $pos + $len));
return "{$part1}{$sep}{$part2}";
}
/**
* Quotes "table.col" 格式的字符串
*
* @param string $text
* @return string|array
*/
public function quoteNamesIn($text)
{
$list = $this->getListForQuoteNamesIn($text);
$last = count($list) - 1;
$text = null;
foreach ($list as $key => $val) {
if (($key + 1) % 3) {
$text .= $this->quoteNamesInLoop($val, $key == $last);
}
}
return $text;
}
/**
* 返回 quote 元素列表
*
* @param string $text
* @return array
*/
protected function getListForQuoteNamesIn($text)
{
$apos = "'";
$quot = '"';
return preg_split(
"/(($apos+|$quot+|\\$apos+|\\$quot+).*?\\2)/",
$text,
-1,
PREG_SPLIT_DELIM_CAPTURE
);
}
/**
* 循環(huán) quote
*
* @param string $val
* @param bool $is_last
* @return string
*/
protected function quoteNamesInLoop($val, $is_last)
{
if ($is_last) {
return $this->replaceNamesAndAliasIn($val);
}
return $this->replaceNamesIn($val);
}
/**
* 替換成別名
*
* @param string $val
* @return string
*/
protected function replaceNamesAndAliasIn($val)
{
$quoted = $this->replaceNamesIn($val);
$pos = strripos($quoted, ' AS ');
if ($pos !== false) {
$bracket = strripos($quoted, ')');
if ($bracket === false) {
$alias = $this->replaceName(substr($quoted, $pos + 4));
$quoted = substr($quoted, 0, $pos) . " AS $alias";
}
}
return $quoted;
}
/**
* Quotes name
*
* @param string $name
* @return string
*/
protected function replaceName($name)
{
$name = trim($name);
if ($name == '*') {
return $name;
}
return '`' . $name . '`';
}
/**
* Quotes
*
* @param string $text
* @return string|array
*/
protected function replaceNamesIn($text)
{
$is_string_literal = strpos($text, "'") !== false
|| strpos($text, '"') !== false;
if ($is_string_literal) {
return $text;
}
$word = '[a-z_][a-z0-9_]*';
$find = "/(\\b)($word)\\.($word)(\\b)/i";
$repl = '$1`$2`.`$3`$4';
$text = preg_replace($find, $repl, $text);
return $text;
}
// ---------- insert --------------
/**
* 設(shè)置 `table.column` 與 last-insert-id 的映射
*
* @param array $last_insert_id_names
*/
public function setLastInsertIdNames(array $last_insert_id_names)
{
$this->last_insert_id_names = $last_insert_id_names;
}
/**
* insert into.
*
* @param string $table
* @return self
*/
public function into($table)
{
$this->table = $this->quoteName($table);
return $this;
}
/**
* 生成 INSERT 語句
*
* @return string
*/
protected function buildINSERT()
{
return 'INSERT'
. $this->buildFlags()
. $this->buildInto()
. $this->buildValuesForInsert()
. $this->buildReturning();
}
/**
* 生成 INTO 語句
*
* @return string
*/
protected function buildInto()
{
return " INTO " . $this->table;
}
/**
* PDO::lastInsertId()
*
* @param string $col
* @return mixed
*/
public function getLastInsertIdName($col)
{
$key = str_replace('`', '', $this->table) . '.' . $col;
if (isset($this->last_insert_id_names[$key])) {
return $this->last_insert_id_names[$key];
}
return null;
}
/**
* 設(shè)置一列，如果有第二各參數(shù)，則把第二個參數(shù)綁定在占位符上
*
* @param string $col
* @return self
*/
public function col($col)
{
return call_user_func_array(array($this, 'addCol'), func_get_args());
}
/**
* 設(shè)置多列
*
* @param array $cols
* @return self
*/
public function cols(array $cols)
{
if ($this->type == 'SELECT') {
foreach ($cols as $key => $val) {
$this->addColSELECT($key, $val);
}
return $this;
}
return $this->addCols($cols);
}
/**
* 直接設(shè)置列的值
*
* @param string $col
* @param string $value
* @return self
*/
public function set($col, $value)
{
return $this->setCol($col, $value);
}
/**
* 為 INSERT 語句綁定值
*
* @return string
*/
protected function buildValuesForInsert()
{
return ' (' . $this->indentCsv(array_keys($this->col_values)) . ') VALUES (' .
$this->indentCsv(array_values($this->col_values)) . ')';
}
// ------update-------
/**
* 更新哪個表
*
* @param string $table
* @return self
*/
public function table($table)
{
$this->table = $this->quoteName($table);
return $this;
}
/**
* 生成完整 SQL 語句
*
* @return string
* @throws Exception
*/
protected function build()
{
switch ($this->type) {
case 'DELETE':
return $this->buildDELETE();
case 'INSERT':
return $this->buildINSERT();
case 'UPDATE':
return $this->buildUPDATE();
case 'SELECT':
return $this->buildSELECT();
}
throw new Exception("type empty");
}
/**
* 生成更新的 SQL 語句
*/
protected function buildUPDATE()
{
return 'UPDATE'
. $this->buildFlags()
. $this->buildTable()
. $this->buildValuesForUpdate()
. $this->buildWhere()
. $this->buildOrderBy()
. $this->buildLimit()
. $this->buildReturning();
}
/**
* 哪個表
*
* @return string
*/
protected function buildTable()
{
return " {$this->table}";
}
/**
* 為更新語句綁定值
*
* @return string
*/
protected function buildValuesForUpdate()
{
$values = array();
foreach ($this->col_values as $col => $value) {
$values[] = "{$col} = {$value}";
}
return ' SET' . $this->indentCsv($values);
}
// ----------Dml---------------
/**
* 獲取綁定的值
*
* @return array
*/
public function getBindValuesCOMMON()
{
$bind_values = $this->bind_values;
$i = 1;
foreach ($this->bind_where as $val) {
$bind_values[$i] = $val;
$i++;
}
return $bind_values;
}
/**
* 設(shè)置列
*
* @param string $col
* @return self
*/
protected function addCol($col)
{
$key = $this->quoteName($col);
$this->col_values[$key] = ":$col";
$args = func_get_args();
if (count($args) > 1) {
$this->bindValue($col, $args[1]);
}
return $this;
}
/**
* 設(shè)置多個列
*
* @param array $cols
* @return self
*/
protected function addCols(array $cols)
{
foreach ($cols as $key => $val) {
if (is_int($key)) {
$this->addCol($val);
} else {
$this->addCol($key, $val);
}
}
return $this;
}
/**
* 設(shè)置單列的值
*
* @param string $col .
* @param string $value
* @return self
*/
protected function setCol($col, $value)
{
if ($value === null) {
$value = 'NULL';
}
$key = $this->quoteName($col);
$value = $this->quoteNamesIn($value);
$this->col_values[$key] = $value;
return $this;
}
/**
* 增加返回的列
*
* @param array $cols
* @return self
*
*/
protected function addReturning(array $cols)
{
foreach ($cols as $col) {
$this->returning[] = $this->quoteNamesIn($col);
}
return $this;
}
/**
* 生成 RETURNING 語句
*
* @return string
*/
protected function buildReturning()
{
if (!$this->returning) {
return '';
}
return ' RETURNING' . $this->indentCsv($this->returning);
}
/**
* 構(gòu)造函數(shù)
*
* @param string $host
* @param int $port
* @param string $user
* @param string $password
* @param string $db_name
* @param string $charset
*/
public function __construct($host, $port, $user, $password, $db_name, $charset = 'utf8')
{
$this->settings = array(
'host' => $host,
'port' => $port,
'user' => $user,
'password' => $password,
'dbname' => $db_name,
'charset' => $charset,
);
$this->connect();
}
/**
* 創(chuàng)建 PDO 實例
*/
protected function connect()
{
$dsn = 'mysql:dbname=' . $this->settings["dbname"] . ';host=' .
$this->settings["host"] . ';port=' . $this->settings['port'];
$this->pdo = new PDO($dsn, $this->settings["user"], $this->settings["password"],
array(
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES ' . (!empty($this->settings['charset']) ?
$this->settings['charset'] : 'utf8')
));
$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);
$this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
}
/**
* 關(guān)閉連接
*/
public function closeConnection()
{
$this->pdo = null;
}
/**
* 執(zhí)行
*
* @param string $query
* @param string $parameters
* @throws PDOException
*/
protected function execute($query, $parameters = "")
{
try {
if (is_null($this->pdo)) {
$this->connect();
}
$this->sQuery = @$this->pdo->prepare($query);
$this->bindMore($parameters);
if (!empty($this->parameters)) {
foreach ($this->parameters as $param) {
$this->sQuery->bindParam($param[0], $param[1]);
}
}
$this->success = $this->sQuery->execute();
} catch (PDOException $e) {
// 服務(wù)端斷開時重連一次
if ($e->errorInfo[1] == 2006 || $e->errorInfo[1] == 2013) {
$this->closeConnection();
$this->connect();
try {
$this->sQuery = $this->pdo->prepare($query);
$this->bindMore($parameters);
if (!empty($this->parameters)) {
foreach ($this->parameters as $param) {
$this->sQuery->bindParam($param[0], $param[1]);
}
}
$this->success = $this->sQuery->execute();
} catch (PDOException $ex) {
$this->rollBackTrans();
throw $ex;
}
} else {
$this->rollBackTrans();
$msg = $e->getMessage();
$err_msg = "SQL:".$this->lastSQL()." ".$msg;
$exception = new \PDOException($err_msg, (int)$e->getCode());
throw $exception;
}
}
$this->parameters = array();
}
/**
* 綁定
*
* @param string $para
* @param string $value
*/
public function bind($para, $value)
{
if (is_string($para)) {
$this->parameters[sizeof($this->parameters)] = array(":" . $para, $value);
} else {
$this->parameters[sizeof($this->parameters)] = array($para, $value);
}
}
/**
* 綁定多個
*
* @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]);
}
}
}
/**
* 執(zhí)行 SQL
*
* @param string $query
* @param array $params
* @param int $fetchmode
* @return mixed
*/
public function query($query = '', $params = null, $fetchmode = PDO::FETCH_ASSOC)
{
$query = trim($query);
if (empty($query)) {
$query = $this->build();
if (!$params) {
$params = $this->getBindValues();
}
}
$this->resetAll();
$this->lastSql = $query;
$this->execute($query, $params);
$rawStatement = explode(" ", $query);
$statement = strtolower(trim($rawStatement[0]));
if ($statement === 'select' || $statement === 'show') {
return $this->sQuery->fetchAll($fetchmode);
} elseif ($statement === 'update' || $statement === 'delete' || $statement === 'replace') {
return $this->sQuery->rowCount();
} elseif ($statement === 'insert') {
if ($this->sQuery->rowCount() > 0) {
return $this->lastInsertId();
}
} else {
return null;
}
return null;
}
/**
* 返回一列
*
* @param string $query
* @param array $params
* @return array
*/
public function column($query = '', $params = null)
{
$query = trim($query);
if (empty($query)) {
$query = $this->build();
if (!$params) {
$params = $this->getBindValues();
}
}
$this->resetAll();
$this->lastSql = $query;
$this->execute($query, $params);
$columns = $this->sQuery->fetchAll(PDO::FETCH_NUM);
$column = null;
foreach ($columns as $cells) {
$column[] = $cells[0];
}
return $column;
}
/**
* 返回一行
*
* @param string $query
* @param array $params
* @param int $fetchmode
* @return array
*/
public function row($query = '', $params = null, $fetchmode = PDO::FETCH_ASSOC)
{
$query = trim($query);
if (empty($query)) {
$query = $this->build();
if (!$params) {
$params = $this->getBindValues();
}
}
$this->resetAll();
$this->lastSql = $query;
$this->execute($query, $params);
return $this->sQuery->fetch($fetchmode);
}
/**
* 返回單個值
*
* @param string $query
* @param array $params
* @return string
*/
public function single($query = '', $params = null)
{
$query = trim($query);
if (empty($query)) {
$query = $this->build();
if (!$params) {
$params = $this->getBindValues();
}
}
$this->resetAll();
$this->lastSql = $query;
$this->execute($query, $params);
return $this->sQuery->fetchColumn();
}
/**
* 返回 lastInsertId
*
* @return string
*/
public function lastInsertId()
{
return $this->pdo->lastInsertId();
}
/**
* 返回最后一條執(zhí)行的 sql
*
* @return string
*/
public function lastSQL()
{
return $this->lastSql;
}
/**
* 開始事務(wù)
*/
public function beginTrans()
{
try {
if (is_null($this->pdo)) {
$this->connect();
}
return $this->pdo->beginTransaction();
} catch (PDOException $e) {
// 服務(wù)端斷開時重連一次
if ($e->errorInfo[1] == 2006 || $e->errorInfo[1] == 2013) {
$this->closeConnection();
$this->connect();
return $this->pdo->beginTransaction();
} else {
throw $e;
}
}
}
/**
* 提交事務(wù)
*/
public function commitTrans()
{
return $this->pdo->commit();
}
/**
* 事務(wù)回滾
*/
public function rollBackTrans()
{
if ($this->pdo->inTransaction()) {
return $this->pdo->rollBack();
}
return true;
}
}鏈式操作的關(guān)鍵是在做完操作后要return $this。return $this表示方法結(jié)束后，返回的是當前對象，它可以實現(xiàn)鏈式操作。
到此這篇關(guān)于php鏈式操作mysql數(shù)據(jù)庫(封裝類帶使用示例)的文章就介紹到這了,更多相關(guān)php操作mysql數(shù)據(jù)庫封裝類內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了php實現(xiàn)的操作excel類,較為詳細的分析說明了PHP操作excel的具體技巧,包括PHP針對excel的創(chuàng)建、打開、讀取、修改等,需要的朋友可以參考下2016-01-01
本篇文章是對php處理整數(shù)函數(shù)進行了詳細的分析介紹，需要的朋友參考下2013-06-06
在這篇文章中主要講解php_curl庫的知識，并教你如何更好的使用php_curl。2010-11-11
這篇文章主要介紹了PHP使用Redis替代文件存儲Session的方法,結(jié)合實例形式較為詳細的分析了Session的基本操作方法及使用Redis存儲session的相關(guān)技巧,需要的朋友可以參考下2017-02-02
DOMElement操作xml想必大家有所耳聞，接下來為大家以實例介紹下，感興趣的朋友可以參考下哈，2013-03-03
這篇文章主要介紹了php表單請求獲得數(shù)據(jù)求和實現(xiàn)代碼,需要的朋友可以參考下2014-05-05
關(guān)于Anemometer圖形化顯示MySQL慢日志的工具搭建及使用的詳細介紹
這篇文章主要介紹了關(guān)于Anemometer圖形化顯示MySQL慢日志的工具搭建及使用的詳細介紹，Anemometer 是一個圖形化顯示MySQL慢日志的工具，Anemometer可以很輕松的幫你去分析慢查詢?nèi)罩荆屇愫苋菀拙湍苷业侥男㏒QL需要優(yōu)化。感興趣可以來了解一下2020-07-07
這篇文章主要介紹了php遞歸遍歷刪除文件的方法,涉及php遍歷文件操作的相關(guān)技巧,非常具有實用價值,需要的朋友可以參考下2015-04-04
這篇文章主要介紹了PHP實現(xiàn)惡意DDOS攻擊避免帶寬占用問題方法,本文介紹的是一個簡單方法,需要的朋友可以參考下2015-05-05
php使用fgetcsv讀取csv文件出現(xiàn)亂碼的解決方法
這篇文章主要介紹了php使用fgetcsv讀取csv文件出現(xiàn)亂碼的解決方法,實例分析了造成亂碼的原因與對應(yīng)的解決方法,并給出了Linux平臺下的亂碼解決方法,需要的朋友可以參考下2014-11-11