前言
懒得写sql,把大部分查询,执行条件封装为数组。
实例
use core\tools\pdoMysql;
$pd = pdoMysql::getInstance();
$fieldArr = ['id','file_name','save_path','save_name','size'];
$whereArr = [];
$whereArr[] = ['id',111427,'>='];
$whereArr[] = ['state',1];
$orderStr = 'order by id desc';
$table = 'talbe';
$data = $pd->getList($table,$fieldArr ,$whereArr , $orderStr);
函数
getList 查询多条数据
getOneFree 查询单条数据
insertOne 插入单条
insertMulti 批量插入
selectNum 查询数量
updateArr 更新
CODE
<?php
/**
* Created by PhpStorm.
* User: Chan
* Date: 2020年5月11日15:26:06
* Job: pdo mysql数据库操作
* Tables:
*/
namespace core\tools;
use PDO;
class pdoMysql
{
protected static $_instance = null;
private $db;
private function __construct($dsn = '', $user = '', $pass = '', $charset = '', $klive = false)
{
$user == '' && $user = C('DB_USER');
$pass == '' && $pass = C('DB_PWD');
$charset == '' && $charset = C('DB_CHARSET');
try {
if (empty($dsn)) {
$dsn = "mysql:host=" . C('DB_HOST') . ";port=" . C('DB_PORT') . ";dbname=" . C('DB_NAME') . ";charset=" . $charset;
}
$this->db = new PDO($dsn, $user, $pass, array(PDO::ATTR_PERSISTENT => $klive));
} catch (PDOException $e) {
die("Connect not open pdo_db" . $e->getMessage());
}
$this->db->exec("SET NAMES " . $charset);
}
/**
* 防止克隆
*/
private function __clone()
{
}
/**
*
* 事物开启
*
*/
public function beginTransaction(){
$this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);//开启异常处理
$this->db->beginTransaction();
}
/**
*
* 事物提交
*
*/
public function commit(){
$this->db->commit();
}
/**
*
* 事物回滚
*
*/
public function rollback(){
$this->db->rollback();
}
/**
* Singleton instance
*
* @return Object
*/
public static function getInstance()
{
if (self::$_instance === null) {
self::$_instance = new self();
}
return self::$_instance;
}
/* 字符串转义 */
public function quote($str)
{
return $this->db->quote($str);
}
/* 执行sql */
public function exec($sql)
{
try{
return $this->db->exec($sql);
}catch(\PDOException $e){
throw new \PDOException($e->getMessage());
}
}
/* 最后insert的主键 */
public function lastInsertId()
{
return $this->db->lastInsertId();
}
/* 预处理 */
public function prepare($sql)
{
return $this->db->prepare($sql);
}
/* 查询语句用query执行 */
public function query($sql)
{
//runtime();
$start = microtime(true);
$rs = $this->db->query($sql);
$time = microtime(true)-$start;
$time > 0.1 && log::writeSlowSql($time,$sql);
return $rs;
}
/* 关闭 */
public function __destruct()
{
$this->close();
}
public function close()
{
if (isset($this->db)) {
unset($this->db);
}
}
public function error()
{
return $this->db->errorInfo();
}
/*
* @purpose 多列查询
* @return array 二位数组
* */
public function getList($tableName = '', $fieldArr = [], $whereArr = [], $orderStr = '', $orWhereArr = [], $orWhereAnd = [])
{
$where = $this->handleWhere($whereArr, $orWhereArr, $orWhereAnd);
$fieldStr = !empty($fieldArr) ? "`".join("`,`",$fieldArr)."`" : '*';
$sql = sprintf("SELECT %s FROM `%s` %s %s", $fieldStr, $tableName, $where, $orderStr);
$result = $this->db->query($sql);
if (false === $result) {
return [];
}
return $result->fetchAll(PDO::FETCH_ASSOC);
}
/*
* @purpose 单列查询
* @return array 一位数组
* */
public function getOneFree($tableName = '', $fieldArr = [], $whereArr, $orWhereArr = [], $orWhereAnd = [])
{
$where = $this->handleWhere($whereArr, $orWhereArr, $orWhereAnd);
$fieldStr = !empty($fieldArr) ? "`".join("`,`",$fieldArr)."`" : '*';
$sql = sprintf("SELECT %s FROM `%s` %s", $fieldStr, $tableName, $where);
$result = $this->db->query($sql);
if (false === $result) {
return [];
}
return $result->fetch(PDO::FETCH_ASSOC);
}
/*
* @purpose 查询数量
* @return int
* */
public function selectNum($tableName = '', $whereArr = [], $orWhereArr = [], $orWhereAnd = [], $orderStr = '')
{
$where = $this->handleWhere($whereArr, $orWhereArr, $orWhereAnd);
$sqlTotal = sprintf("SELECT COUNT(1) AS `totalNum` FROM `%s` %s %s", $tableName, $where, $orderStr);
$resultTotal = $this->db->query($sqlTotal);
if (false === $resultTotal) {
return 0;
} elseif (0 == ($totalNum = $resultTotal->fetchColumn(0))) {
return 0;
}
return $totalNum;
}
/*
* @purpose 单条插入
* @return int|bool
* */
public function insertOne($tableName = '', $data = [], $lastIdTag = false)
{
$sql = sprintf("INSERT INTO `%s` (`%s`) VALUES (%s)", $tableName, join('`,`', array_keys($data)), "'" . join("','", $data) . "'");
$result = $this->db->exec($sql);
if (false === $result) {
return false;
}
if ($lastIdTag) {
return $this->db->lastInsertId();
}
return true;
}
/*
* @purpose 多条插入
* @return bool
* */
public function insertMulti($tableName = '', $data = [])
{
if (is_array($data) && count($data)) {
$fieldArr = [];
$insertStrArr = [];
foreach ($data as $k => $v) {
if (is_array($v) && count($v)) {
$fieldArr = array_keys($v);
$insertStrArr[] = sprintf("(%s)", "'" . join("','", array_values($v)) . "'");
} else {
return false;
}
}
$sql = sprintf("INSERT INTO `%s` (`%s`) VALUES %s", $tableName, join('`,`', $fieldArr), join(',', $insertStrArr));
$result = $this->db->exec($sql);
if (false === $result) {
return false;
}
return true;
} else {
return false;
}
}
public function updateArr($tableName = '', $data = [], $whereArr = [], $orWhereArr = [], $orWhereAnd = [])
{
$where = $this->handleWhere($whereArr, $orWhereArr, $orWhereAnd);
$sql = sprintf("UPDATE `%s` %s %s", $tableName, $this->getUpdateStr($data), $where);
$result = $this->db->exec($sql);
if (false === $result) {
return false;
}
return true;
}
public function getUpdateStr($updateData = [])
{
$str = ' SET ';
if (is_array($updateData) && count($updateData) > 0) {
foreach ($updateData as $key => $item) {
$str .= sprintf(" `%s` = '%s' ,", $key, $item);
}
$str = rtrim($str, ',');
}
return $str;
}
public function handleWhere($whereArr = [], $orWhereArr = [], $orWhereAnd = [])
{
if (is_array($whereArr) && count($whereArr) > 0) {
$where = 'WHERE' . $this->whereStr($whereArr);
} else {
$where = 'WHERE';
}
strlen(trim($where)) > 5 && $where .= 'AND';
if (is_array($orWhereArr) && count($orWhereArr) > 0) {
$where .= $this->orWhereStr($orWhereArr);
} else {
$where .= '';
$where = rtrim($where, 'AND');
}
strlen(trim($where)) > 5 && $where .= 'AND';
if (is_array($orWhereAnd) && count($orWhereAnd) > 0) {
$where .= $this->orWhereAnd($orWhereAnd);
} else {
$where .= '';
$where = rtrim($where, 'AND');
}
strlen(trim($where)) == 5 && $where = '';
return $where;
}
public function whereStr($whereArr = [])
{
$where = '';
if (is_array($whereArr) && count($whereArr) > 0) {
//$where = ' WHERE ';
foreach ($whereArr as $key => $item) {
if (is_array($item) && count($item) > 0) {
if (isset($item['tag']) || isset($item[2])) {
$tag = isset($item['tag']) ? $item['tag'] : $item[2];
switch (strtolower($tag)) {
case "like":
$where .= sprintf(" `%s` %s '%s' ESCAPE '/' %s", isset($item['key']) ? $item['key'] : $item[0], 'like', '%' . str_replace('_', '/_', str_replace('%', '/%', isset($item['value']) ? $item['value'] : $item[1])) . '%', 'AND');
break;
case "llike":
$where .= sprintf(" `%s` %s '%s' ESCAPE '/' %s", isset($item['key']) ? $item['key'] : $item[0], 'like', '%' . str_replace('_', '/_', str_replace('%', '/%', isset($item['value']) ? $item['value'] : $item[1])), 'AND');
break;
case "rlike":
$where .= sprintf(" `%s` %s '%s' ESCAPE '/' %s", isset($item['key']) ? $item['key'] : $item[0], 'like', str_replace('_', '/_', str_replace('%', '/%', isset($item['value']) ? $item['value'] : $item[1])) . '%', 'AND');
break;
case "in":
$where .= sprintf(" `%s` %s (%s) %s", isset($item['key']) ? $item['key'] : $item[0], isset($item['tag']) ? $item['tag'] : $item[2], isset($item['value']) ? $item['value'] : $item[1], 'AND');
break;
case "not in":
$where .= sprintf(" `%s` %s (%s) %s", isset($item['key']) ? $item['key'] : $item[0], isset($item['tag']) ? $item['tag'] : $item[2], isset($item['value']) ? $item['value'] : $item[1], 'AND');
break;
case "locate":
$where .= sprintf(" locate('%s', `%s`) %s %d %s", isset($item['value']) ? $item['value'] : $item[1], isset($item['key']) ? $item['key'] : $item[0], '>', '0', 'AND');
break;
case "find_in_set":
$where .= sprintf(" find_in_set('%s', `%s`) %s %d %s", isset($item['value']) ? $item['value'] : $item[1], isset($item['key']) ? $item['key'] : $item[0], '>', 0, 'AND');
break;
case "orinand":
if (is_array($item['value']) && count($item['value']) > 0) {
$tmpStr = ' ( ';
foreach ($item['value'] as $v) {
$tmpStr .= sprintf("`%s` %s %s %s", isset($v['key']) ? $v['key'] : $v[0], isset($v['tag']) ? $v['tag'] : $v['2'], isset($v['value']) ? $v['value'] : $v['1'], 'OR');
}
$where .= rtrim($tmpStr, 'OR') . ' ) AND';
}
break;
case "like_search":
$where .= sprintf(" %s %s '%s' %s", isset($item['key']) ? $item['key'] : $item[0], 'like', isset($item['value']) ? '%' . $item['value'] . '%' : '%' . $item[1] . '%', 'AND');
break;
case "free":
$where .= sprintf(" %s %s", isset($item['value']) ? $item['value'] : $item[1], 'AND');
break;
default:
$where .= sprintf(" `%s` %s '%s' %s", isset($item['key']) ? $item['key'] : $item[0], isset($item['tag']) ? $item['tag'] : $item[2], isset($item['value']) ? $item['value'] : $item[1], 'AND');
break;
}
} else {
$where .= sprintf(" `%s` %s '%s' %s", isset($item['key']) ? $item['key'] : $item[0], '=', isset($item['value']) ? $item['value'] : $item[1], 'AND');
}
} else {
return '';
}
}
$where = rtrim($where, 'AND');
}
return $where;
}
public function orWhereStr($orWhereArr = [])
{
$where = '';
if (is_array($orWhereArr) && count($orWhereArr) > 0) {
foreach ($orWhereArr as $key => $value) {
$where .= ' (';
foreach ($value as $k => $item) {
if (isset($item['tag']) || isset($item[2])) {
$tag = isset($item['tag']) ? $item['tag'] : $item[2];
switch (strtolower($tag)) {
case "like":
$where .= sprintf(" `%s` %s '%s' ESCAPE '/' %s", isset($item['key']) ? $item['key'] : $item[0], 'like', '%' . str_replace('_', '/_', str_replace('%', '/%', isset($item['value']) ? $item['value'] : $item[1])) . '%', 'OR');
break;
case "llike":
$where .= sprintf(" `%s` %s '%s' ESCAPE '/' %s", isset($item['key']) ? $item['key'] : $item[0], 'like', '%' . str_replace('_', '/_', str_replace('%', '/%', isset($item['value']) ? $item['value'] : $item[1])), 'OR');
break;
case "rlike":
$where .= sprintf(" `%s` %s '%s' ESCAPE '/' %s", isset($item['key']) ? $item['key'] : $item[0], 'like', str_replace('_', '/_', str_replace('%', '/%', isset($item['value']) ? $item['value'] : $item[1])) . '%', 'OR');
break;
case "in":
$where .= sprintf(" `%s` %s (%s) %s", isset($item['key']) ? $item['key'] : $item[0], isset($item['tag']) ? $item['tag'] : $item[2], isset($item['value']) ? $item['value'] : $item[1], 'OR');
break;
case "not in":
$where .= sprintf(" `%s` %s (%s) %s", isset($item['key']) ? $item['key'] : $item[0], isset($item['tag']) ? $item['tag'] : $item[2], isset($item['value']) ? $item['value'] : $item[1], 'OR');
break;
case "locate":
$where .= sprintf(" locate('%s', `%s`) %s %d %s", isset($item['value']) ? $item['value'] : $item[1], isset($item['key']) ? $item['key'] : $item[0], '>', 0, 'OR');
break;
case "find_in_set":
$where .= sprintf(" find_in_set('%s', `%s`) %s %d %s", isset($item['value']) ? $item['value'] : $item[1], isset($item['key']) ? $item['key'] : $item[0], '>', 0, 'OR');
break;
case "free":
$where .= sprintf(" %s %s", isset($item['value']) ? $item['value'] : $item[1], 'AND');
break;
default:
$where .= sprintf(" `%s` %s '%s' %s", isset($item['key']) ? $item['key'] : $item[0], isset($item['tag']) ? $item['tag'] : $item[2], isset($item['value']) ? $item['value'] : $item[1], 'OR');
break;
}
} else {
$where .= sprintf(" `%s` %s '%s' %s", isset($item['key']) ? $item['key'] : $item[0], '=', isset($item['value']) ? $item['value'] : $item[1], 'OR');
}
//$where .= sprintf(" `%s` %s '%s' %s", $item['key'], $item['tag'], $item['value'], 'OR');
}
$where = rtrim($where, 'OR');
$where .= ') AND';
}
$where = rtrim($where, 'AND');
}
return $where;
}
public function orWhereAnd($orWhereArr = [])
{
$where = '(';
if (is_array($orWhereArr) && count($orWhereArr) > 0) {
foreach ($orWhereArr as $key => $value) {
$where .= ' (';
foreach ($value as $k => $item) {
if (isset($item['tag']) || isset($item[2])) {
$tag = isset($item['tag']) ? $item['tag'] : $item[2];
switch (strtolower($tag)) {
case "like":
$where .= sprintf(" `%s` %s '%s' ESCAPE '/' %s", isset($item['key']) ? $item['key'] : $item[0], 'like', '%' . str_replace('_', '/_', str_replace('%', '/%', isset($item['value']) ? $item['value'] : $item[1])) . '%', 'AND');
break;
case "llike":
$where .= sprintf(" `%s` %s '%s' ESCAPE '/' %s", isset($item['key']) ? $item['key'] : $item[0], 'like', '%' . str_replace('_', '/_', str_replace('%', '/%', isset($item['value']) ? $item['value'] : $item[1])), 'AND');
break;
case "rlike":
$where .= sprintf(" `%s` %s '%s' ESCAPE '/' %s", isset($item['key']) ? $item['key'] : $item[0], 'like', str_replace('_', '/_', str_replace('%', '/%', isset($item['value']) ? $item['value'] : $item[1])) . '%', 'AND');
break;
case "in":
$where .= sprintf(" `%s` %s (%s) %s", isset($item['key']) ? $item['key'] : $item[0], isset($item['tag']) ? $item['tag'] : $item[2], isset($item['value']) ? $item['value'] : $item[1], 'AND');
break;
case "not in":
$where .= sprintf(" `%s` %s (%s) %s", isset($item['key']) ? $item['key'] : $item[0], isset($item['tag']) ? $item['tag'] : $item[2], isset($item['value']) ? $item['value'] : $item[1], 'AND');
break;
case "locate":
$where .= sprintf(" locate('%s', `%s`) %s %d %s", isset($item['value']) ? $item['value'] : $item[1], isset($item['key']) ? $item['key'] : $item[0], '>', 0, 'AND');
break;
case "find_in_set":
$where .= sprintf(" find_in_set('%s', `%s`) %s %d %s", isset($item['value']) ? $item['value'] : $item[1], isset($item['key']) ? $item['key'] : $item[0], '>', 0, 'AND');
break;
case "free":
$where .= sprintf(" %s %s", isset($item['value']) ? $item['value'] : $item[1], 'AND');
break;
default:
$where .= sprintf(" `%s` %s '%s' %s", isset($item['key']) ? $item['key'] : $item[0], isset($item['tag']) ? $item['tag'] : $item[2], isset($item['value']) ? $item['value'] : $item[1], 'AND');
break;
}
} else {
$where .= sprintf(" `%s` %s '%s' %s", isset($item['key']) ? $item['key'] : $item[0], '=', isset($item['value']) ? $item['value'] : $item[1], 'AND');
}
}
$where = rtrim($where, 'AND');
$where .= ') OR';
}
$where = rtrim($where, 'OR');
$where .= ') ';
}
return $where;
}
}