PHP操作MySQL

1. 连接MySQL

$config = [
        'host'     => '127.0.0.1',
        'user'     => 'root',
        'password' => 'root',
        'database' => 'test',
        'charset'  => 'utf8'
];
$dsn = sprintf(
    "mysql:host=%s;dbname=%s;charset=%s", 
    $config['host'], $config['database'], $config['charset']
);

try{
    $pdo = new PDO( $dsn, $config['user'], $config['password'] );
} catch (PDOException $e) {
    die('Exception: ' . $e->getMessage());
}

2. PDO 设置 SQL 执行出错时的行为表现

PDO 可以通过设置 ATTR_ERRODE 属性来控制 SQL 执行出错时的行为表现，具体可以设置以下三个值：

模式	说明
PDO::ERRMODE_SILENT	静默模式，不会报错，只会设置PDO的 errorCode
PDO::ERRMODE_WARNING	会报warning错误
PDO::ERRMODE_EXCEPTION	抛出异常

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //当 SQL 语句执行出错时会抛出异常

pdo 连接失败报错会直接抛出 PDOException 从而进入到 catch 模块，不受该属性设置影响

3. PDO 强制数据库查询返回的数据集中列名为指定的大小写

PDO 可以通过设置 PDO::ATTR_CASE 属性来控制数据库返回数据集中字段的大小写，具体可以设置以下三个值：

模式	说明
PDO::CASE_LOWER	强制列名小写
CASE_UPPER	强制列名大写
CASE_NATURAL	保留数据库驱动返回的列名，默认为该模式

$pdo->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER); //将数据库返回数据集中所有字段名都转换成小写

4. PDO SQL 执行语句

• exec

//1. 执行 “ 插入数据 ”， 返回 SQL 执行影响的行数
echo $pdo->exec("INSERT INTO logs (field1, field2) VALUES ('value11', 'value12'),('value21', 'value22')"); //2  

//2. 执行 “ 更新数据 ”， 返回 SQL 执行影响的行数
echo $pdo->exec("UPDATE logs set field1 = 'value111' WHERE field2 = 'value22'"); //1

//3. 执行 “ 删除数据 ”， 返回 SQL 执行影响的行数
echo $pdo->exec("DELETE FROM logs WHERE field2 = 'value22'"); //1

//4. 执行 “ 插入数据 ”， 并获取最后自增的主键
$pdo->exec("INSERT INTO logs (field1, field2) VALUES ('value31', 'value32')"); //1
echo $pdo->lastInsertId(); //3

5. PDO SQL 查询语句

• query

$query = $pdo->query("SELECT * FROM logs");

//1. 获取所有数据
$rows = $query->fetchAll(); //默认返回所有数据的 索引 + 关联 数组,可通过参数设置来只返回索引数组或关联数组或 索引+关联数组

//2. 获取一条数据
$row = $query->fetch();  

//3. 循环打印出所有数据
while($row = $query->fetch())
{
    print_r($row);
}

6. PDO SQL 预处理，解决 SQL 注入问题

• 命名符模式

  命名符模式有语义，所以绑定参数的顺序可以随意

// 1.查询
$sth = $pdo->prepare("SELECT * FROM logs WHERE id=:id");
$sth->execute([':id' => $_GET['id']]);
print_r($sth->fetchAll());

//2. 插入(delete、update同理)
$sth = $pdo->prepare("INSERT INTO logs (field1, field2) VALUES (:value1, :value2)");
$sth->execute([':value2' => $_GET['field2'], ':value1' => $_GET['field1']]); //绑定参数的顺序随意
echo $pdo->lastInsertId();

• 占位符模式

  占位符模式因为没有语义，所以参数绑定的时候占位符位置和绑定参数顺序必须严格对应

// 1.查询
$sth = $pdo->prepare("SELECT * FROM logs WHERE id=?");
$sth->execute([$_GET['id']]);
print_r($sth->fetchAll());

//2. 插入(delete、update同理)
$sth = $pdo->prepare("INSERT INTO logs (field1, field2) VALUES (?, ?)");
$sth->execute([$_GET['field1'], $_GET['field2']]); //绑定参数顺序严格对应
echo $pdo->lastInsertId();

7. 实战，初步简单封装数据库操作

// Db.php >>>
<?php
declare(strict_types=1);

namespace basic;

use PDO;
use PDOException;

/**
 * pdo数据库操作类
 * Class Db
 * @package basic
 */
class Db
{
    protected $link;
    protected $options = [
        'table' =>  '',
        'field' =>  '*',
        'where' =>  '',
        'whereData' =>  [],
        'order' =>  '',
        'limit' =>  '',
        'prefix'=>  '',
    ];

    //有效运算符
    protected $effctiveOperators = [
        'gt'    =>  '>',
        'lt'    =>  '<',
        'eq'    =>  '=',
        'neq'   =>  '<>',
        'gte'   =>  '>=',
        'lte'   =>  '<=',
    ];

    protected $fetchSql = false; //是否显示最后执行的 SQL 语句

    public function __construct(array $config)
    {
        $dsn = sprintf(
            "mysql:host=%s;dbname=%s;charset=%s",
            $config['host'], $config['database'], $config['charset']
        );

        try{
            $this->link = new PDO( $dsn, $config['user'], $config['password'], $config['options'] );
            $this->options['prefix'] = $config['prefix'];
        } catch (PDOException $e) {
            die('Exception: ' . $e->getMessage());
        }
    }

    /**
     * 占位符模式预处理查询
     * @param string $sql
     * @return array
     */
    protected function query(string $sql): array
    {
        $sth = $this->link->prepare($sql);
        $sth->execute($this->options['whereData']);
        return $sth->fetchAll();
    }

    protected function queryOne(string $sql)
    {
        $sth = $this->link->prepare($sql);
        $sth->execute($this->options['whereData']);
        return $sth->fetch();
    }

    public function table(string $table): Db
    {
        $this->options['table'] = $table;
        return $this;
    }

    public function name(string $table): Db
    {
        $this->options['table'] = $this->options['prefix'].$table;
        return $this;
    }

    public function field(string $field): Db
    {
        '*' != trim($field) && $this->options['field'] = '`'. str_replace(',', '`,`', trim($field)) .'`';
        return $this;
    }

    public function where(array $where): Db
    {
        $whereArray = [];
        foreach ($where as $subWhere) {
            [$key, $operator, $value] = array_map(function ($item){ return is_string($item) ? trim($item) : $item; }, $subWhere);
            $operator = in_array($operator, $this->effctiveOperators) ? $operator : ($this->effctiveOperators[$operator] ?? null);
            if (is_null($operator)) throw new \Exception("\n数据库查询操作符错误: [{$key}, {$subWhere[1]}, {$value}]");
            $whereArray["{$key} {$operator}?"] = $value;
            unset($key, $operator, $value);
        }
        if ($whereArray) {
            $this->options['where'] = ' WHERE ' . implode(' AND ', array_keys($whereArray));
            $this->options['whereData'] = array_values($whereArray);
        }
        return $this;
    }

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

    public function limit(string $limit): Db
    {
        trim($limit) && $this->options['limit'] = " LIMIT {$limit} ";
        return $this;
    }

    public function select(): array
    {
        $sql = "SELECT {$this->options['field']} FROM {$this->options['table']}{$this->options['where']}{$this->options['order']}{$this->options['limit']}";
        return $this->fetchSql ? compact('sql') : $this->query($sql);
    }

    public function fetchSql(bool $flag = false): Db
    {
        $this->fetchSql = $flag;
        return $this;
    }
}

// database.php >>>

<?php
return [
    'host'     => '127.0.0.1',
    'user'     => 'root',
    'password' => '123456',
    'database' => 'lyl_demo',
    'charset'  => 'utf8',
    'prefix'   => 'lyl_',
    'options'  => [
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,//设置返回关联数组
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,     //设置当 SQL 语句执行出错时会抛出异常
        PDO::ATTR_CASE => PDO::CASE_LOWER,               //设置返回数据集中所有字段名都转换成小写
    ],
];

// index.php >>>
$config = config('database');

$pdo = new \basic\Db($config);

$res = $pdo->name('user')
    ->field(' id,account,gender,nickname ')
    ->where([
        ['id', 'gte', 3],
        ['gender', '=', 2]
    ])
    ->order('id ASC')
    ->limit('0,5')
    ->fetchSql(false)
    ->select();