PHP实现SQL生成器（链式操作）

写多条SQL语句来实现对数据库的操作，那确实是件比较繁琐的事情，但是如果有了自定义的SQL生成器便可以在壹定程度上提高开发效率。

一、准备数据：Database->ankium、Table->news

/*
 Navicat Premium Data Transfer

 Source Server         : MariaDB
 Source Server Type    : MariaDB
 Source Server Version : 100411
 Source Host           : localhost:3306
 Source Schema         : ankium

 Target Server Type    : MariaDB
 Target Server Version : 100411
 File Encoding         : 65001

 Date: 20/03/2020 18:04:53
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for news
-- ----------------------------
DROP TABLE IF EXISTS `news`;
CREATE TABLE `news`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `author` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 40 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of news
-- ----------------------------
INSERT INTO `news` VALUES (1, 'ankium.com', '云已', 'C#图解教程');
INSERT INTO `news` VALUES (2, 'ankium.omc', '文文', 'C#高级编程');
INSERT INTO `news` VALUES (3, 'ankium.moc', '云文', 'C#核心指南');

SET FOREIGN_KEY_CHECKS = 1;

二、数据处理类DB：database.php

<?php

namespace Database;

use PDO;
use Exception;

class DB
{
    protected $link;
    protected $options = ['table' => '', 'field' => '*', 'order' => '', 'limit' => '', 'where' => ''];

    public function __construct(array $config)
    {
        $this->connect($config);
    }

    //PDO连接
    protected function connect(array $config)
    {
        $dsn = sprintf(
            'mysql:host=%s;dbname=%s;charset=%s',
            $config['host'],
            $config['dbname'],
            $config['charset']
        );
        $this->link = new PDO($dsn, $config['user'], $config['password']);
        $this->link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
        $this->link->setAttribute(PDO::ATTR_CASE, PDO::CASE_NATURAL);
    }

    //预准备查询
    public function query(string $sql, array $vars = [])
    {
        $sth = $this->link->prepare($sql);
        $sth->execute($vars);
        return $sth->fetchAll(PDO::FETCH_ASSOC);
    }

    //预准备执行
    public function execute(string $sql, array $vars = [])
    {
        $sth = $this->link->prepare($sql);
        return $sth->execute($vars);
    }

    //构建SQL组件
    public function table(string $table)
    {
        $this->options['table'] = $table;
        return $this;
    }

    public function field(...$fields)
    {
        $this->options['field'] = '`' . implode('`,`', $fields) . '`';
        return $this;
    }

    public function limit(...$limit)
    {
        $this->options['limit'] = " LIMIT " . implode(',', $limit);
        return $this;
    }

    public function order(string $order)
    {
        $this->options['order'] = "ORDER BY " . $order;
        return $this;
    }

    public function where(string $where)
    {
        $this->options['where'] = " WHERE " . $where;
        return $this;
    }

    //查询
    public function select()
    {
        //EX:SELECT * FROM news WHERE ORDER LIMIT 1
        $sql = "SELECT {$this->options['field']} FROM {$this->options['table']}{$this->options['where']}{$this->options['order']}{$this->options['limit']}";
        return $this->query($sql);
    }

    //增加
    public function insert(array $vars)
    {
        //EX:INSERT INTO news ('title','author') VALUES (?,?)
        $fields = '`' . implode('`,`', array_keys($vars)) . '`';
        $values = implode(',', array_fill(0, count($vars), '?'));
        $sql = "INSERT INTO {$this->options['table']} ($fields) VALUES ($values)";
        return $this->execute($sql, array_values($vars));
    }

    //更新
    public function update(array $vars)
    {
        //EX:UPDATE news SET title=?,author=? WHERE ...
        if (empty($this->options['where'])) {
            throw new Exception("更新必须设置条件");
        }
        $sql = "UPDATE {$this->options['table']} SET " . implode('=?,', array_keys($vars)) . "=? {$this->options['where']}";
        return $this->execute($sql, array_values($vars));
    }

    //删除
    public function delete()
    {
        //EX:DELETE FROM news WHERE
        if (empty($this->options['where'])) {
            throw new Exception("删除必须设置条件");
        }
        $sql = "DELETE FROM {$this->options['table']} {$this->options['where']}";
        return $this->execute($sql);
    }
}

DB处理类主要做的事情：通过PDO方式连接数据库（Connect方法实现）、预准备（包括查询和执行，目的是将解析与参数分开处理，可以有效的防止SQL注入）、构建SQL组件、提供增删改查方法以供外部调用。

三、调用：index.php

<?php
use Database\DB;
include "database.php";
header("Content-type:text/html;charset=utf8");
$config = [
    'host'=>'127.0.0.1',
    'user'=>'root',
    'password'=>'123456',
    'dbname'=>'ankium',
    'charset'=>'utf8'
];
try {
    $db = new DB($config);
    //更新操作
    $db->table('news')->where('id>20')->update(['title'=>'三年级','author'=>'郭明']);
    //添加操作
    $db->table('news')->insert(['title'=>'万里','author'=>'无云']);
    //查询操作
    $rows = $db->table('news')->limit(1)->select();
    print_r($rows);
    //删除操作
    $db->table('news')->where('id>4')->delete();
}catch(Exception $e){
    die($e->getMessage());
}

在定义DB处理类的SQL组件方法时指定返回值都是当前对象，所以在调用的时候可以通过链式操作完成。