简介
PDO 扩展为 PHP 访问数据库定义了一个轻量级的、一致性的接口，PDO 解决了数据库连接不统一的问题
简单封装
配置文件 config.php
<?php
defined('DB_HOST') or define('DB_HOST', 'localhost');
defined('DB_USER') or define('DB_USER', 'root');
defined('DB_PWD') or define('DB_PWD', 'root');
defined('DB_NAME') or define('DB_NAME', 'test');
defined('DB_PORT') or define('DB_PORT', '3306');
defined('DB_TYPE') or define('DB_TYPE', 'mysql');
defined('DB_CHARSET') or define('DB_CHARSET', 'utf8');类文件 PdoMySQL.class.php
<?php
class PdoMySQL
{
private static $config = []; // 设置连接参数，配置信息
private static $link = []; // 保存连接标识符
private static $keepAlive = false; // 是否开启长连接
private static $version = null; // 保存数据库版本
private static $connected = false; // 是否连接成功
private static $statement = null; // 保存 PDOStatement 对象
private static $lastQuery = null; // 保存最后执行的 SQL 语句
private static $error = null; // 保存错误信息
private static $lastInsertId = null; // 保存上一步插入操作产生的 AUTO_INCREMENT
private static $numRows = 0; // 上一步操作产生受影响的记录的条数
public function __construct($config = '')
{
if (!class_exists('PDO')) {
self::throw_exception('不支持 PDO，请先开启！');
}
if (!is_array($config)) {
$config = [
'hostname' => DB_HOST,
'username' => DB_USER,
'password' => DB_PWD,
'database' => DB_NAME,
'hostport' => DB_PORT,
'dbms' => DB_TYPE,
'dsn' => DB_TYPE . ':host=' . DB_HOST . ';dbname=' . DB_NAME,
];
}
if (empty($config['hostname'])) {
self::throw_exception('没有定义数据库配置，请先定义！');
}
self::$config = $config;
if (empty(self::$config['params'])) {
self::$config['params'] = [];
}
if (!isset(self::$link)) {
$configs = self::$config;
if (self::$keepAlive) {
// 开启长连接，添加到配置数组中
$config['params'][constant('PDO::ATTR_PERSISTENT')] = true;
}
try {
self::$link = new PDO($configs['dsn'], $configs['username'], $configs['password'], $configs['params']);
} catch (PDOException $e) {
self::throw_exception($e->getMessage());
}
if (!self::$link) {
self::throw_exception('PDO 连接错误！');
}
self::$link->exec('set names ' . DB_CHARSET);
self::$version = self::$link->getAttribute(constant('PDO::ATTR_SERVER_VERSION'));
self::$connected = true;
unset($configs);
}
}
/**
* 查询所有记录
* @param string $sql
* @return mixed
*/
public static function get_all($sql = null)
{
if ($sql != null) {
self::query($sql);
}
$result = self::$statement->fetchAll(constant('PDO::FETCH_ASSOC'));
return $result;
}
/**
* 查询一条记录
* @param string $sql
* @return mixed
*/
public static function get_row($sql = null)
{
if ($sql != null) {
self::query($sql);
}
$result = self::$statement->fetch(constant('PDO::FETCH_ASSOC'));
return $result;
}
/**
* 普通查询
* @param string $tables 表名
* @param string $fields 字段
* @param null $where
* @param null $group
* @param null $having
* @param null $order
* @param null $limit
* @return mixed
*/
public static function find($tables, $fields = '*', $where = null, $group = null, $having = null, $order = null, $limit = null)
{
$sql = 'select ' . self::parse_field($fields) . ' from' . $tables
. self::parse_where($where)
. self::parse_group($group)
. self::parse_having($having)
. self::parse_order($order)
. self::parse_limit($limit);
$data = self::get_all($sql);
return count($data) == 1 ? $data[0] : $data;
}
/**
* 根据主键查询
* @param string $tabName 表名
* @param int $priId 主键
* @param string $fields 字段
* @return mixed
*/
public static function find_by_id($tabName, $priId, $fields = '*')
{
$sql = 'select %s from %s where id=%d';
return self::get_row(sprintf($sql, self::parse_field($fields), $tabName, $priId));
}
/**
* 添加记录
* @param array $data
* $data = [
* 'username' => 'test',
* 'password' => 'test',
* ]
* @param string $table 表名
* @return bool|int
*/
public static function add($data, $table)
{
$keys = array_keys($data);
array_walk($keys, [
'PdoMySQL',
'add_special_char',
]);
$fieldStr = join(',', $keys);
$values = '\'' . join('\',\'', array_values($data)) . '\'';
$sql = 'insert ' . $table . '(' . $fieldStr . ') values(' . $values . ')';
return self::execute($sql);
}
/**
* 更新记录
* @param array $data
* $data = [
* 'username' => 'test',
* 'password' => 'test',
* ]
* @param string $table 表名
* @param null $where
* @param null $order
* @param null $limit
* @return bool|int
*/
public static function update($data, $table, $where = null, $order = null, $limit = null)
{
$sets = '';
foreach ($data as $key => $val) {
$sets .= $key . '=\'' . $val . '\',';
}
if (empty($sets)) {
return false;
}
$sets = rtrim($sets, ',');
$sql = 'update ' . $table . ' set ' . $sets . self::parse_where($where) . self::parse_order($order) . self::parse_limit($limit);
return self::execute($sql);
}
/**
* 删除记录
* @param string $table 表名
* @param null $where
* @param null $order
* @param null $limit
* @return bool|int
*/
public static function delete($table, $where = null, $order = null, $limit = null)
{
$sql = 'delete from ' . $table . self::parse_where($where) . self::parse_order($order) . self::parse_limit($limit);
return self::execute($sql);
}
/**
* 获取最后执行的 SQL 语句
* @return bool|null
*/
public static function get_last_query()
{
$link = self::$link;
if (!$link) {
return false;
}
return self::$lastQuery;
}
/**
* 获取上一步插入操作产生的 AUTO_INCREMENT
* @return bool|null
*/
public static function get_last_insert_id()
{
$link = self::$link;
if (!$link) {
return false;
}
return self::$lastInsertId;
}
/**
* 获取数据库版本
* @return bool|mixed|null
*/
public static function get_version()
{
$link = self::$link;
if (!$link) {
return false;
}
return self::$version;
}
/**
* 获取数据中的数据表
* @return array
*/
public static function show_tables()
{
$tables = [];
if (self::query('show tables')) {
$result = self::get_all();
foreach ($result as $key => $val) {
$tables[$key] = current($val);
}
}
return $tables;
}
/**
* 执行增删改操作，返回受影响的记录条数
* @param string $sql
* @return bool|int
*/
private static function execute($sql = null)
{
$link = self::$link;
if (!$link) {
return false;
}
if (!empty(self::$statement)) {
self::free();
}
self::$lastQuery = $sql;
$res = $link->exec($sql);
self::have_error();
if (!$res) {
return false;
}
self::$lastInsertId = $link->lastInsertId();
self::$numRows = $res;
return $res;
}
/**
* 执行查询操作
* @param string $sql
* @return bool
*/
private static function query($sql = '')
{
$link = self::$link;
if (!$link) {
return false;
}
if (!empty(self::$statement)) {
// 判断之前是否有结果集，如果有，则释放结果集
self::free();
}
self::$lastQuery = $sql;
self::$statement = $link->prepare($sql);
$res = self::$statement->execute();
self::have_error();
return $res;
}
/**
* 解析字段
* @param $fields
* @return string
*/
private static function parse_field($fields)
{
if (is_array($fields)) {
array_walk($fields, [
'PdoMySQL',
'add_special_char',
]);
$fieldsStr = implode(',', $fields);
} elseif (is_string($fields) && !empty($fields)) {
if (strpos($fields, '`') === false) {
$fields = explode(',', $fields);
array_walk($fields, [
'PdoMySQL',
'add_special_char',
]);
$fieldsStr = implode(',', $fields);
} else {
$fieldsStr = $fields;
}
} else {
$fieldsStr = '*';
}
return $fieldsStr;
}
/**
* 解析 where 条件
* @param $where
* @return string
*/
private static function parse_where($where)
{
$whereStr = '';
if (is_string($where) && !empty($where)) {
$whereStr = $where;
}
return empty($whereStr) ? '' : ' where ' . $whereStr;
}
/**
* 解析 group by 条件
* @param $group
* @return string
*/
private static function parse_group($group)
{
$groupStr = '';
if (is_array($group)) {
$groupStr .= ' group by ' . implode(',', $group);
} elseif (is_string($group) && !empty($group)) {
$groupStr .= ' group by ' . $group;
}
return empty($groupStr) ? '' : $groupStr;
}
/**
* 解析 having 条件
* @param $having
* @return string
*/
private static function parse_having($having)
{
$havingStr = '';
if (is_string($having) && !empty($having)) {
$havingStr .= ' having ' . $having;
}
return $havingStr;
}
/**
* 解析 order by 条件
* @param $order
* @return string
*/
private static function parse_order($order)
{
$orderStr = '';
if (is_array($order)) {
$orderStr .= ' order by ' . join(',', $order);
} elseif (is_string($order) && !empty($order)) {
$orderStr .= ' order by ' . $order;
}
return $orderStr;
}
/**
* 解析 limit 条件
* @param $limit
* @return string
*/
private static function parse_limit($limit)
{
$limitStr = '';
if (is_array($limit)) {
if (count($limit) > 1) {
$limitStr .= ' limit ' . $limit[0] . ',' . $limit[1];
} else {
$limitStr .= ' limit ' . $limit[0];
}
} elseif (is_string($limit) && !empty($limit)) {
$limitStr .= ' limit ' . $limit;
}
return $limitStr;
}
/**
* 通过反引号引用字段
* @param $value
* @return string
*/
private static function add_special_char(&$value)
{
if ($value === '*' || strpos($value, '.') !== false || strpos($value, '`') !== false) {
// 不做处理
} elseif (strpos($value, '`') === false) {
$value = '`' . trim($value) . '`';
}
return $value;
}
/**
* 释放结果集
*/
private static function free()
{
self::$statement = null;
}
/**
* 数据库错误信息
*/
private static function have_error()
{
$obj = empty(self::$statement) ? self::$link : self::$statement;
$error = $obj->errorInfo();
if ($error[0] != '00000') {
self::$error = 'SQLSTATE' . $error[0] . 'SQL Error：' . $error[2] . '<br>Error SQL：' . self::$lastQuery;
self::throw_exception(self::$error);
}
if (self::$lastQuery == '') {
self::throw_exception('没有执行 SQL 语句！');
}
}
/**
* 自定义错误处理
* @param string $errMsg
* @return bool
*/
private static function throw_exception($errMsg)
{
echo $errMsg;
return false;
}
/**
* 销毁连接对象，关闭数据库
*/
private static function close()
{
self::$link = null;
}
}