kly_api/base/mysql.php

<?php
defined('ONLY_ONLY_ONLY') or exit('Access Denied');
define('SQL_THROW_EXCEPTION', 1);
function mysqldb()
{
    static $db;
    global $DB_CONFIG;
    if (empty($db)) {
        $DB_CONFIG['charset'] = empty($DB_CONFIG['charset']) ? 'utf8' : $DB_CONFIG['charset'];
        $db = new PdoUtil($DB_CONFIG);
    }
    return $db;
}
function pdo_query($sql, $params = array())
{
    return mysqldb()->query($sql, $params);
}
function pdo_query2($sql, $params = array())
{
    return mysqldb()->query2($sql, $params);
}
function pdo_query3($sql, $params = array())
{
    return mysqldb()->query2_withtrans($sql, $params);
}
function pdo_fetch($sql, $params = array())
{
    return mysqldb()->fetch($sql, $params);
}
function pdo_fetchcolumn($sql, $params = array(), $column = 0)
{
    return mysqldb()->fetchcolumn($sql, $params, $column);
}
function pdo_fetchall($sql, $params = array(), $keyfield = '')
{
    return mysqldb()->fetchall($sql, $params, $keyfield);
}
function pdo_update($table, $data = array(), $params = array(), $orwith = 'AND')
{
    if ($params == 'empty') {
        $params = array();
    }
    return mysqldb()->update($table, $data, $params, $orwith);
}
function pdo_delete($table, $params = array(), $orwith = 'AND')
{
    if ($params == 'empty') {
        $params = array();
    }
    return mysqldb()->delete($table, $params, $orwith);
}
function pdo_insert($table, $data = array(), $es = false)
{
    return mysqldb()->insert($table, $data, $es);
}
function pdo_insertid()
{
    return mysqldb()->insertid();
}

function pdo_fieldexists($tablename, $fieldname)
{
    return mysqldb()->fieldexists($tablename, $fieldname);
}
function pdo_indexexists($tablename, $indexname)
{
    return mysqldb()->indexexists($tablename, $indexname);
}
function pdo_fetchallfields($tablename)
{
    $fields = pdo_fetchall("DESCRIBE {$tablename}", array(), 'Field');
    $fields = array_keys($fields);
    return $fields;
}
function pdo_buildSetSQL($suffix, $params)
{
    return mysqldb()->splitForSQL($params, ',', $suffix);
}

function mysql_strvalue($str)
{
    if (empty($str)) {
        $str = '';
    }

    $str = mb_ereg_replace("'", "", $str);
    $str = "'" . $str . "'";
    return $str;
}
function mysql_param(&$sqlParam, $fdname, $fdvalue)
{
    if (empty($fdname)) {
        $fdname = 'sp';
    }

    $paraName = ":" . $fdname . "_" . count($sqlParam);
    $sqlParam[$paraName] = $fdvalue;
    return $paraName;
}

class PdoUtil
{
    private $dbo;
    private $cfg;
    public function __construct($cfg)
    {
        if (empty($cfg)) {
            throw new Exception("PdoUtil发生异常，没有设置数据库配置信息！！");
        }
        $mysqlurl = "mysql:dbname={$cfg['database']};host={$cfg['host']};port={$cfg['port']};allowMultiQueries=true";
        try {
            $this->dbo = new PDO($mysqlurl, $cfg['username'], $cfg['password']);
        } catch (PDOException $e) {
            throw new Exception("PdoUtil发生异常，数据库连接失败，请检查数据库配置");
        }
        $sql = "SET NAMES '{$cfg['charset']}';";
        $this->dbo->exec($sql);
        $this->dbo->exec("SET sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';");
        $this->cfg = $cfg;
        if (SQL_THROW_EXCEPTION) {
            $this->errhandle($this->dbo->errorInfo(), $sql);
        }
    }
    public function table($table)
    {
        return $table;
    }
    public function errhandle($errors, $sql = "")
    {
        if (!empty($errors[0]) && !empty($errors[0]) && $errors[0] != '00000') {
            throw new Exception("数据库执行异常:" . $errors[2] . "\n sql语句：" . $sql . "\n" . @json_encode($errors));
        }
        return $errors;
    }

    public function query($sql, $params = array())
    {
        if (empty($params)) {
            $result = $this->dbo->exec($sql);
            if (SQL_THROW_EXCEPTION) {
                $this->errhandle($this->dbo->errorInfo(), $sql);
            }
            return $result;
        }
        $statement = $this->dbo->prepare($sql);
        $result = $statement->execute($params);
        if (SQL_THROW_EXCEPTION) {
            $this->errhandle($statement->errorInfo(), $sql);
        }
        if (!$result) {
            return false;
        } else {
            return $statement->rowCount();
        }
    }
    public function query2($sql, $params = array())
    {
        $statement = $this->dbo->prepare($sql);
        if ($statement) {
            $statement->closeCursor();
        }

        if (empty($params)) {
            $params = array();
        }

        $result = $statement->execute($params);
        if (SQL_THROW_EXCEPTION) {
            $this->errhandle($statement->errorInfo(), $sql);
        }
        $rowSet = array();
        do {
            try {
                if ($statement->columnCount() > 0) {
                    $rs = $statement->fetchAll(pdo::FETCH_ASSOC);
                    $rowSet[] = $rs;
                } else {
                    $rowSet[] = $statement->rowCount();
                }
            } catch (PDOException $pdoex) {
                if ($statement) {
                    $statement->closeCursor();
                }

                throw $pdoex;
            } catch (Throwable $ex) {
                if ($statement) {
                    $statement->closeCursor();
                }

                throw $ex;
            }
        } while ($statement->nextRowset());
        $lastError = $statement->errorInfo();
        if ($statement) {
            $statement->closeCursor();
        }

        if (SQL_THROW_EXCEPTION) {
            $this->errhandle($lastError, $sql);
        }
        return $rowSet;
    }
    public function query2_withtrans($sql, $params = array())
    {
        $sql = trim($sql);
        if (mb_strlen($sql) > 0) {
            $last = mb_substr($sql, mb_strlen($sql) - 1);
            if (!($last === ";")) {
                $sql .= ";";
            }
            $sql = "SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;start transaction;" . $sql . "commit;";
        } else {
            throw new Exception("sql语句不能为空！");
        }
        $rowSet = $this->query2($sql, $params);
        if (count($rowSet) <= 3) {
            throw new Exception("怎么可能结果集小于等于3呢！");
        }
        $rowSetNew = array_slice($rowSet, 2, count($rowSet) - 3);
        return $rowSetNew;
    }

    public function fetchcolumn($sql, $params = array(), $column = 0)
    {
        $statement = $this->dbo->prepare($sql);
        $result = $statement->execute($params);
        if (SQL_THROW_EXCEPTION) {
            $this->errhandle($statement->errorInfo(), $sql);
        }
        if (!$result) {
            return false;
        } else {
            return $statement->fetchColumn($column);
        }
    }
    public function fetch($sql, $params = array())
    {
        $statement = $this->dbo->prepare($sql);
        $result = $statement->execute($params);
        if (SQL_THROW_EXCEPTION) {
            $this->errhandle($statement->errorInfo(), $sql);
        }
        if (!$result) {
            return false;
        } else {
            return $statement->fetch(pdo::FETCH_ASSOC);
        }
    }
    public function fetchall($sql, $params = array(), $keyfield = '')
    {
        $statement = $this->dbo->prepare($sql);
        $result = $statement->execute($params);
        if (SQL_THROW_EXCEPTION) {
            $this->errhandle($statement->errorInfo(), $sql);
        }
        if (!$result) {
            return false;
        } else {
            if (empty($keyfield)) {
                return $statement->fetchAll(pdo::FETCH_ASSOC);
            } else {
                $temp = $statement->fetchAll(pdo::FETCH_ASSOC);
                $rs = array();
                if (!empty($temp)) {
                    foreach ($temp as $key => &$row) {
                        if (isset($row[$keyfield])) {
                            $rs[$row[$keyfield]] = $row;
                        } else {
                            $rs[] = $row;
                        }
                    }
                }
                return $rs;
            }
        }
    }
    public function update($table, $data = array(), $params = array(), $orwith = 'AND')
    {
        $fields = $this->splitForSQL($data, ',');
        $condition = $this->splitForSQL($params, $orwith);
        $params = array_merge($fields['params'], $condition['params']);
        $sql = "UPDATE " . $this->table($table) . " SET {$fields['fields']}";
        $sql .= $condition['fields'] ? ' WHERE ' . $condition['fields'] : '';
        return $this->query($sql, $params);
    }
    public function insert($table, $data = array(), $es = false)
    {
        $condition = $this->splitForSQL($data, ',');
        $backData = $this->query2("INSERT INTO " . $this->table($table) . " SET {$condition['fields']};select LAST_INSERT_ID() as lastInsertId;", $condition['params']);
        return intval($backData[1][0]["lastInsertId"]);
    }
    public function insertid()
    {
        return $this->dbo->lastInsertId();
    }
    public function delete($table, $params = array(), $orwith = 'AND')
    {
        $condition = $this->splitForSQL($params, $orwith);
        $sql = "DELETE FROM " . $this->table($table);
        $sql .= $condition['fields'] ? ' WHERE ' . $condition['fields'] : '';
        return $this->query($sql, $condition['params']);
    }
    public function splitForSQL($params, $orwith = ',', $suffix = '')
    {
        $result = array(
            'fields' => ' 1 ', 'fieldnames' => ' 1 ', 'fieldvals' => ' 1 ',
            'params' => array(),
        );
        $split = '';
        if (in_array(strtolower($orwith), array('and', 'or'))) {
            $suffix = '__';
        }
        if (!is_array($params)) {
            $result['fields'] = $params;
            return $result;
        }
        if (is_array($params)) {
            $result['fields'] = '';
            $result['fieldnames'] = '';
            $result['fieldvals'] = '';
            foreach ($params as $fields => $value) {
                $result['fieldnames'] .= $split . "`$fields`";
                $result['fieldvals'] .= $split . ":{$suffix}$fields";
                $result['fields'] .= $split . "`$fields` =  :{$suffix}$fields";
                $split = ' ' . $orwith . ' ';
                $result['params'][":{$suffix}$fields"] = is_null($value) ? '' : $value;
            }
        }
        return $result;
    }
    public function fieldexists($tablename, $fieldname)
    {
        $isexists = $this->fetch("DESCRIBE " . $this->table($tablename) . " `{$fieldname}`");
        return !empty($isexists) ? true : false;
    }
    public function indexexists($tablename, $indexname)
    {
        if (!empty($indexname)) {
            $indexs = $this->fetchall("SHOW INDEX FROM " . $this->table($tablename));
            if (!empty($indexs) && is_array($indexs)) {
                foreach ($indexs as $row) {
                    if ($row['Key_name'] == $indexname) {
                        return true;
                    }
                }
            }
        }
        return false;
    }
}