溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
本篇內(nèi)容介紹了“php鏈?zhǔn)讲僮?a title="mysql" target="_blank" href="http://www.kemok4.com/mysql/">mysql數(shù)據(jù)庫的方法是什么”的有關(guān)知識(shí)，在實(shí)際案例的操作過程中，不少人都會(huì)遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！
// 初始化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();
//等價(jià)于
$db->select('ID,Sex')->from('Persons')->where("sex= 'M' AND ID = 1")->query();
//等價(jià)于
$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();
//等價(jià)于
$db->select('ID,Sex')->from('Persons')->where("sex= 'M' ")->row();
//等價(jià)于
$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();
//等價(jià)于
$db->select('ID')->from('Persons')->where("sex= 'F' ")->column();
//等價(jià)于
$db->column("SELECT `ID` FROM `Persons` WHERE sex='M'");
// 獲取單個(gè)值
$db->select('ID')->from('Persons')->where('sex= :sex')->bindValues(array('sex'=>'M'))->single();
//等價(jià)于
$db->select('ID')->from('Persons')->where("sex= 'F' ")->single();
//等價(jià)于
$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));
// 等價(jià)于
$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();
等價(jià)于
$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();
// 等價(jià)于
$row_count = $db->update('Persons')->cols(array('sex'=>'F'))->where('ID=1')->query();
// 等價(jià)于
$row_count = $db->query("UPDATE `Persons` SET `sex` = 'F' WHERE ID=1");
// 刪除
$row_count = $db->delete('Persons')->where('ID=9')->query();
// 等價(jià)于
$row_count = $db->query("DELETE FROM `Persons` WHERE ID=9");
// 事務(wù)
$db->beginTrans();
....
$db->commitTrans(); // or $db->rollBackTrans();<?php
/**
* 數(shù)據(jù)庫連接類，依賴 PDO_MYSQL 擴(kuò)展
* 在 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 當(dāng)前的 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 的排序方式,默認(rèn)為升序
*
* @var bool
*/
protected $order_asc = true;
/**
* SELECT 多少記錄
*
* @var int
*/
protected $limit = 0;
/**
* 返回記錄的游標(biāo)
*
* @var int
*/
protected $offset = 0;
/**
* flags 列表
*
* @var array
*/
protected $flags = array();
/**
* 操作哪個(gè)表
*
* @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 實(shí)例
*
* @var PDO
*/
protected $pdo;
/**
* PDOStatement 實(shí)例
*
* @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;
}
/**
* 從哪個(gè)表刪除
*
* @param string $table
* @return self
*/
public function delete($table)
{
$this->type = 'DELETE';
$this->table = $this->quoteName($table);
$this->fromRaw($this->quoteName($table));
return $this;
}
/**
* 更新哪個(gè)表
*
* @param string $table
* @return self
*/
public function update($table)
{
$this->type = 'UPDATE';
$this->table = $this->quoteName($table);
return $this;
}
/**
* 向哪個(gè)表插入
*
* @param string $table
* @return self
*/
public function insert($table)
{
$this->type = 'INSERT';
$this->table = $this->quoteName($table);
return $this;
}
/**
*
* 設(shè)置 SQL_CALC_FOUND_ROWS 標(biāo)記.
*
* @param bool $enable
* @return self
*/
public function calcFoundRows($enable = true)
{
$this->setFlag('SQL_CALC_FOUND_ROWS', $enable);
return $this;
}
/**
* 設(shè)置 SQL_CACHE 標(biāo)記
*
* @param bool $enable
* @return self
*/
public function cache($enable = true)
{
$this->setFlag('SQL_CACHE', $enable);
return $this;
}
/**
* 設(shè)置 SQL_NO_CACHE 標(biāo)記
*
* @param bool $enable
* @return self
*/
public function noCache($enable = true)
{
$this->setFlag('SQL_NO_CACHE', $enable);
return $this;
}
/**
* 設(shè)置 STRAIGHT_JOIN 標(biāo)記.
*
* @param bool $enable
* @return self
*/
public function straightJoin($enable = true)
{
$this->setFlag('STRAIGHT_JOIN', $enable);
return $this;
}
/**
* 設(shè)置 HIGH_PRIORITY 標(biāo)記
*
* @param bool $enable
* @return self
*/
public function highPriority($enable = true)
{
$this->setFlag('HIGH_PRIORITY', $enable);
return $this;
}
/**
* 設(shè)置 SQL_SMALL_RESULT 標(biāo)記
*
* @param bool $enable
* @return self
*/
public function smallResult($enable = true)
{
$this->setFlag('SQL_SMALL_RESULT', $enable);
return $this;
}
/**
* 設(shè)置 SQL_BIG_RESULT 標(biāo)記
*
* @param bool $enable
* @return self
*/
public function bigResult($enable = true)
{
$this->setFlag('SQL_BIG_RESULT', $enable);
return $this;
}
/**
* 設(shè)置 SQL_BUFFER_RESULT 標(biāo)記
*
* @param bool $enable
* @return self
*/
public function bufferResult($enable = true)
{
$this->setFlag('SQL_BUFFER_RESULT', $enable);
return $this;
}
/**
* 設(shè)置 FOR UPDATE 標(biāo)記
*
* @param bool $enable
* @return self
*/
public function forUpdate($enable = true)
{
$this->for_update = (bool)$enable;
return $this;
}
/**
* 設(shè)置 DISTINCT 標(biāo)記
*
* @param bool $enable
* @return self
*/
public function distinct($enable = true)
{
$this->setFlag('DISTINCT', $enable);
return $this;
}
/**
* 設(shè)置 LOW_PRIORITY 標(biāo)記
*
* @param bool $enable
* @return self
*/
public function lowPriority($enable = true)
{
$this->setFlag('LOW_PRIORITY', $enable);
return $this;
}
/**
* 設(shè)置 IGNORE 標(biāo)記
*
* @param bool $enable
* @return self
*/
public function ignore($enable = true)
{
$this->setFlag('IGNORE', $enable);
return $this;
}
/**
* 設(shè)置 QUICK 標(biāo)記
*
* @param bool $enable
* @return self
*/
public function quick($enable = true)
{
$this->setFlag('QUICK', $enable);
return $this;
}
/**
* 設(shè)置 DELAYED 標(biāo)記
*
* @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 哪個(gè)表
*
* @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----------
/**
* 返回逗號(hào)分隔的字符串
*
* @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;
}
/**
* 單個(gè)為占位符綁定值
*
* @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ù)，則把第二個(gè)參數(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-------
/**
* 更新哪個(gè)表
*
* @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();
}
/**
* 哪個(gè)表
*
* @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è)置多個(gè)列
*
* @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 實(shí)例
*/
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ù)端斷開時(shí)重連一次
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);
}
}
/**
* 綁定多個(gè)
*
* @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);
}
/**
* 返回單個(gè)值
*
* @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ù)端斷開時(shí)重連一次
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;
}
}鏈?zhǔn)讲僮鞯年P(guān)鍵是在做完操作后要return $this。return $this表示方法結(jié)束后，返回的是當(dāng)前對象，它可以實(shí)現(xiàn)鏈?zhǔn)讲僮鳌?/p>
“php鏈?zhǔn)讲僮鱩ysql數(shù)據(jù)庫的方法是什么”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼