PHP面向过程版MySQLi增删改查

1. 数据库配置 (config.php)

<?php
/**
 * 数据库配置文件 - 面向过程 + 安全设计
 */

// 数据库配置（生产环境应放入环境变量）
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', '');
define('DB_NAME', 'test_db');
define('DB_CHARSET', 'utf8mb4');

/**
 * 建立数据库连接
 * @return mysqli|false
 */
function db_connect() {
    $conn = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);
    
    if (!$conn) {
        error_log("数据库连接失败: " . mysqli_connect_error());
        return false;
    }
    
    // 设置字符集，防止字符注入
    mysqli_set_charset($conn, DB_CHARSET);
    
    return $conn;
}

/**
 * 安全关闭连接
 * @param mysqli $conn
 */
function db_close($conn) {
    if ($conn && is_object($conn)) {
        mysqli_close($conn);
    }
}

/**
 * 输入过滤 - 防止XSS
 * @param string $data
 * @return string
 */
function clean_input($data) {
    $data = trim($data);
    $data = stripslashes($data);
    $data = htmlspecialchars($data, ENT_QUOTES, 'UTF-8');
    return $data;
}

/**
 * SQL转义 - 防止SQL注入（面向过程版）
 * @param mysqli $conn
 * @param string $data
 * @return string
 */
function escape_sql($conn, $data) {
    if (!is_string($data)) {
        return $data;
    }
    return mysqli_real_escape_string($conn, $data);
}

/**
 * 生成安全WHERE条件（键值对数组转SQL）
 * @param mysqli $conn
 * @param array $conditions
 * @return string
 */
function build_where($conn, $conditions) {
    if (empty($conditions)) {
        return '1=1';
    }
    
    $parts = [];
    foreach ($conditions as $field => $value) {
        $field = preg_replace('/[^a-zA-Z0-9_]/', '', $field); // 白名单过滤字段名
        $safe_value = escape_sql($conn, $value);
        $parts[] = "`{$field}` = '{$safe_value}'";
    }
    
    return implode(' AND ', $parts);
}

/**
 * 操作结果封装
 */
function result_success($data = null, $msg = '操作成功') {
    return ['code' => 200, 'msg' => $msg, 'data' => $data];
}

function result_error($msg = '操作失败', $code = 400) {
    return ['code' => $code, 'msg' => $msg, 'data' => null];
}
?>

2. 核心CRUD操作 (db_functions.php)

<?php
/**
 * 面向过程的MySQLi CRUD操作
 * 包含完整的安全防护机制
 */

require_once 'config.php';

// ============================================
// 【CREATE】插入操作
// ============================================

/**
 * 插入单条记录
 * @param mysqli $conn
 * @param string $table 表名
 * @param array $data 数据数组 ['字段名' => '值']
 * @return array 操作结果
 */
function db_insert($conn, $table, $data) {
    // 安全过滤表名
    $table = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
    
    if (empty($data) || !is_array($data)) {
        return result_error('数据不能为空');
    }
    
    // 提取字段和值
    $fields = [];
    $values = [];
    
    foreach ($data as $field => $value) {
        // 字段名白名单过滤
        $safe_field = preg_replace('/[^a-zA-Z0-9_]/', '', $field);
        $fields[] = "`{$safe_field}`";
        
        // 值转义处理
        if ($value === null) {
            $values[] = 'NULL';
        } elseif (is_bool($value)) {
            $values[] = $value ? '1' : '0';
        } elseif (is_int($value) || is_float($value)) {
            $values[] = $value;
        } else {
            $safe_value = escape_sql($conn, (string)$value);
            $values[] = "'{$safe_value}'";
        }
    }
    
    $field_str = implode(', ', $fields);
    $value_str = implode(', ', $values);
    
    $sql = "INSERT INTO `{$table}` ({$field_str}) VALUES ({$value_str})";
    
    $result = mysqli_query($conn, $sql);
    
    if (!$result) {
        error_log("插入失败: " . mysqli_error($conn) . " | SQL: " . $sql);
        return result_error('插入失败: ' . mysqli_error($conn));
    }
    
    $insert_id = mysqli_insert_id($conn);
    return result_success(['id' => $insert_id], '插入成功');
}

/**
 * 批量插入
 * @param mysqli $conn
 * @param string $table
 * @param array $dataArray 二维数组
 * @return array
 */
function db_insert_batch($conn, $table, $dataArray) {
    $table = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
    
    if (empty($dataArray)) {
        return result_error('数据不能为空');
    }
    
    // 开启事务
    mysqli_begin_transaction($conn);
    
    try {
        $success_count = 0;
        $ids = [];
        
        foreach ($dataArray as $data) {
            $result = db_insert($conn, $table, $data);
            if ($result['code'] === 200) {
                $success_count++;
                $ids[] = $result['data']['id'];
            } else {
                throw new Exception($result['msg']);
            }
        }
        
        mysqli_commit($conn);
        return result_success([
            'count' => $success_count,
            'ids' => $ids
        ], "成功插入 {$success_count} 条记录");
        
    } catch (Exception $e) {
        mysqli_rollback($conn);
        return result_error('批量插入失败: ' . $e->getMessage());
    }
}

// ============================================
// 【READ】查询操作
// ============================================

/**
 * 查询单条记录
 * @param mysqli $conn
 * @param string $table 表名
 * @param mixed $where 条件字符串或数组
 * @param string $fields 查询字段
 * @return array
 */
function db_find_one($conn, $table, $where = '1=1', $fields = '*') {
    $table = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
    
    // 处理字段白名单
    if ($fields !== '*') {
        $field_array = explode(',', $fields);
        $safe_fields = [];
        foreach ($field_array as $f) {
            $safe_fields[] = '`' . preg_replace('/[^a-zA-Z0-9_]/', '', trim($f)) . '`';
        }
        $fields = implode(', ', $safe_fields);
    }
    
    // 处理WHERE条件
    if (is_array($where)) {
        $where_str = build_where($conn, $where);
    } else {
        // 如果传入字符串，需要确保已经安全处理
        $where_str = $where;
    }
    
    $sql = "SELECT {$fields} FROM `{$table}` WHERE {$where_str} LIMIT 1";
    
    $result = mysqli_query($conn, $sql);
    
    if (!$result) {
        error_log("查询失败: " . mysqli_error($conn));
        return result_error('查询失败');
    }
    
    $row = mysqli_fetch_assoc($result);
    mysqli_free_result($result);
    
    if (!$row) {
        return result_error('记录不存在', 404);
    }
    
    return result_success($row);
}

/**
 * 查询多条记录
 * @param mysqli $conn
 * @param string $table
 * @param mixed $where
 * @param string $fields
 * @param string $order 排序
 * @param int $limit 限制条数
 * @param int $offset 偏移量
 * @return array
 */
function db_find_all($conn, $table, $where = '1=1', $fields = '*', 
                      $order = '', $limit = 0, $offset = 0) {
    $table = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
    
    // 字段处理
    if ($fields !== '*') {
        $field_array = explode(',', $fields);
        $safe_fields = [];
        foreach ($field_array as $f) {
            $safe_fields[] = '`' . preg_replace('/[^a-zA-Z0-9_]/', '', trim($f)) . '`';
        }
        $fields = implode(', ', $safe_fields);
    }
    
    // WHERE处理
    if (is_array($where)) {
        $where_str = build_where($conn, $where);
    } else {
        $where_str = $where;
    }
    
    $sql = "SELECT {$fields} FROM `{$table}` WHERE {$where_str}";
    
    // 安全处理ORDER BY
    if (!empty($order)) {
        // 只允许特定格式：字段名 ASC/DESC
        if (preg_match('/^[a-zA-Z0-9_]+\s*(ASC|DESC)?$/i', $order)) {
            $sql .= " ORDER BY {$order}";
        }
    }
    
    // 安全处理LIMIT
    $limit = intval($limit);
    $offset = intval($offset);
    
    if ($limit > 0) {
        $sql .= " LIMIT {$offset}, {$limit}";
    }
    
    $result = mysqli_query($conn, $sql);
    
    if (!$result) {
        error_log("查询失败: " . mysqli_error($conn) . " | SQL: " . $sql);
        return result_error('查询失败');
    }
    
    $data = [];
    while ($row = mysqli_fetch_assoc($result)) {
        $data[] = $row;
    }
    
    mysqli_free_result($result);
    
    return result_success($data);
}

/**
 * 执行自定义SQL查询（只读，用于复杂查询）
 * @param mysqli $conn
 * @param string $sql
 * @param array $params 参数数组，用于sprintf格式化
 * @return array
 */
function db_query($conn, $sql, $params = []) {
    // 只允许SELECT语句
    $sql_trim = ltrim($sql);
    if (stripos($sql_trim, 'SELECT') !== 0) {
        return result_error('只允许SELECT查询');
    }
    
    // 参数转义
    if (!empty($params)) {
        $safe_params = [];
        foreach ($params as $param) {
            $safe_params[] = escape_sql($conn, $param);
        }
        $sql = vsprintf($sql, $safe_params);
    }
    
    $result = mysqli_query($conn, $sql);
    
    if (!$result) {
        return result_error('查询失败: ' . mysqli_error($conn));
    }
    
    $data = [];
    while ($row = mysqli_fetch_assoc($result)) {
        $data[] = $row;
    }
    
    mysqli_free_result($result);
    
    return result_success($data);
}

/**
 * 统计记录数
 * @param mysqli $conn
 * @param string $table
 * @param mixed $where
 * @return int
 */
function db_count($conn, $table, $where = '1=1') {
    $table = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
    
    if (is_array($where)) {
        $where_str = build_where($conn, $where);
    } else {
        $where_str = $where;
    }
    
    $sql = "SELECT COUNT(*) as total FROM `{$table}` WHERE {$where_str}";
    
    $result = mysqli_query($conn, $sql);
    if (!$result) {
        return 0;
    }
    
    $row = mysqli_fetch_assoc($result);
    mysqli_free_result($result);
    
    return intval($row['total']);
}

// ============================================
// 【UPDATE】更新操作
// ============================================

/**
 * 更新记录
 * @param mysqli $conn
 * @param string $table
 * @param array $data 要更新的数据
 * @param mixed $where 条件
 * @return array
 */
function db_update($conn, $table, $data, $where) {
    $table = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
    
    if (empty($data) || empty($where)) {
        return result_error('更新数据和条件不能为空');
    }
    
    // 构建SET部分
    $sets = [];
    foreach ($data as $field => $value) {
        $safe_field = preg_replace('/[^a-zA-Z0-9_]/', '', $field);
        
        if ($value === null) {
            $sets[] = "`{$safe_field}` = NULL";
        } elseif (is_bool($value)) {
            $sets[] = "`{$safe_field}` = " . ($value ? '1' : '0');
        } elseif (is_int($value) || is_float($value)) {
            $sets[] = "`{$safe_field}` = {$value}";
        } else {
            $safe_value = escape_sql($conn, (string)$value);
            $sets[] = "`{$safe_field}` = '{$safe_value}'";
        }
    }
    
    $set_str = implode(', ', $sets);
    
    // WHERE处理
    if (is_array($where)) {
        $where_str = build_where($conn, $where);
    } else {
        $where_str = $where;
    }
    
    // 安全保护：防止全表更新
    if ($where_str === '1=1' || empty($where_str)) {
        return result_error('禁止无条件更新全表');
    }
    
    $sql = "UPDATE `{$table}` SET {$set_str} WHERE {$where_str}";
    
    $result = mysqli_query($conn, $sql);
    
    if (!$result) {
        error_log("更新失败: " . mysqli_error($conn));
        return result_error('更新失败: ' . mysqli_error($conn));
    }
    
    $affected = mysqli_affected_rows($conn);
    
    return result_success([
        'affected_rows' => $affected
    ], "更新了 {$affected} 条记录");
}

/**
 * 字段自增/自减
 * @param mysqli $conn
 * @param string $table
 * @param string $field
 * @param int $step
 * @param mixed $where
 * @return array
 */
function db_increment($conn, $table, $field, $step = 1, $where) {
    $table = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
    $field = preg_replace('/[^a-zA-Z0-9_]/', '', $field);
    $step = intval($step);
    
    if (is_array($where)) {
        $where_str = build_where($conn, $where);
    } else {
        $where_str = $where;
    }
    
    if ($where_str === '1=1' || empty($where_str)) {
        return result_error('禁止无条件更新');
    }
    
    $operator = $step >= 0 ? '+' : '-';
    $abs_step = abs($step);
    
    $sql = "UPDATE `{$table}` SET `{$field}` = `{$field}` {$operator} {$abs_step} WHERE {$where_str}";
    
    $result = mysqli_query($conn, $sql);
    
    if (!$result) {
        return result_error('操作失败: ' . mysqli_error($conn));
    }
    
    return result_success(['affected_rows' => mysqli_affected_rows($conn)]);
}

// ============================================
// 【DELETE】删除操作
// ============================================

/**
 * 删除记录（物理删除）
 * @param mysqli $conn
 * @param string $table
 * @param mixed $where
 * @return array
 */
function db_delete($conn, $table, $where) {
    $table = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
    
    if (empty($where)) {
        return result_error('删除条件不能为空');
    }
    
    if (is_array($where)) {
        $where_str = build_where($conn, $where);
    } else {
        $where_str = $where;
    }
    
    // 安全保护
    if ($where_str === '1=1' || empty($where_str)) {
        return result_error('禁止无条件删除全表');
    }
    
    $sql = "DELETE FROM `{$table}` WHERE {$where_str}";
    
    $result = mysqli_query($conn, $sql);
    
    if (!$result) {
        error_log("删除失败: " . mysqli_error($conn));
        return result_error('删除失败: ' . mysqli_error($conn));
    }
    
    $affected = mysqli_affected_rows($conn);
    
    return result_success([
        'affected_rows' => $affected
    ], "删除了 {$affected} 条记录");
}

/**
 * 逻辑删除（推荐）
 * @param mysqli $conn
 * @param string $table
 * @param mixed $where
 * @param string $delete_field
 * @return array
 */
function db_soft_delete($conn, $table, $where, $delete_field = 'deleted_at') {
    $data = [$delete_field => date('Y-m-d H:i:s')];
    return db_update($conn, $table, $data, $where);
}
?>

3. 使用示例 (demo.php)

<?php
/**
 * 面向过程MySQLi CRUD 使用示例
 */

require_once 'db_functions.php';

// 建立连接
$conn = db_connect();
if (!$conn) {
    die("数据库连接失败");
}

echo "<h2>面向过程MySQLi CRUD 演示</h2><hr>";

// ============================================
// 1. 插入操作 (CREATE)
// ============================================

echo "<h3>1. 插入单条记录</h3>";

// 模拟用户输入（包含危险字符）
$username = "张三' OR '1'='1";  // 模拟SQL注入攻击
$email = "zhangsan@example.com";
$age = 25;

$result = db_insert($conn, 'users', [
    'username' => clean_input($username),  // 先XSS过滤，再SQL转义
    'email' => $email,
    'age' => $age,
    'status' => 1,
    'created_at' => date('Y-m-d H:i:s')
]);

echo "结果: " . json_encode($result, JSON_UNESCAPED_UNICODE) . "<br>";

// 批量插入
echo "<h3>2. 批量插入</h3>";
$batch_result = db_insert_batch($conn, 'users', [
    ['username' => '李四', 'email' => 'lisi@test.com', 'age' => 30, 'status' => 1, 'created_at' => date('Y-m-d H:i:s')],
    ['username' => '王五', 'email' => 'wangwu@test.com', 'age' => 28, 'status' => 1, 'created_at' => date('Y-m-d H:i:s')],
    ['username' => '赵六', 'email' => 'zhaoliu@test.com', 'age' => 35, 'status' => 0, 'created_at' => date('Y-m-d H:i:s')],
]);
echo "批量插入结果: " . json_encode($batch_result, JSON_UNESCAPED_UNICODE) . "<br>";

// ============================================
// 2. 查询操作 (READ)
// ============================================

echo "<hr><h3>3. 查询单条记录</h3>";
$user = db_find_one($conn, 'users', ['id' => 1]);
echo "查询用户: <pre>";
print_r($user);
echo "</pre>";

echo "<h3>4. 查询多条记录（带条件）</h3>";
$users = db_find_all(
    $conn, 
    'users', 
    ['status' => 1],           // WHERE条件
    'id, username, email',      // 指定字段
    'id DESC',                 // 排序
    10,                        // 限制10条
    0                          // 偏移量
);
echo "用户列表: <pre>";
print_r($users);
echo "</pre>";

echo "<h3>5. 复杂条件查询</h3>";
// 模拟搜索功能
$search_name = "张";  // 用户输入
$min_age = 20;

// 手动构建安全WHERE（用于复杂条件）
$safe_name = escape_sql($conn, "%{$search_name}%");
$sql_where = "`username` LIKE '{$safe_name}' AND `age` > " . intval($min_age);

$search_result = db_find_all($conn, 'users', $sql_where, '*', 'age ASC', 5);
echo "搜索结果: <pre>";
print_r($search_result);
echo "</pre>";

echo "<h3>6. 统计记录数</h3>";
$count = db_count($conn, 'users', ['status' => 1]);
echo "活跃用户数量: {$count}<br>";

// ============================================
// 3. 更新操作 (UPDATE)
// ============================================

echo "<hr><h3>7. 更新记录</h3>";
$update_result = db_update($conn, 'users', [
    'username' => '张三丰（已修改）',
    'age' => 26,
    'updated_at' => date('Y-m-d H:i:s')
], ['id' => 1]);  // 必须指定条件，否则会报错

echo "更新结果: " . json_encode($update_result, JSON_UNESCAPED_UNICODE) . "<br>";

echo "<h3>8. 字段自增（登录次数）</h3>";
$inc_result = db_increment($conn, 'users', 'login_count', 1, ['id' => 1]);
echo "自增结果: " . json_encode($inc_result, JSON_UNESCAPED_UNICODE) . "<br>";

// 测试安全限制：尝试无条件更新
echo "<h3>9. 安全测试：无条件更新（应失败）</h3>";
$unsafe_update = db_update($conn, 'users', ['status' => 0], '1=1');
echo "结果: " . json_encode($unsafe_update, JSON_UNESCAPED_UNICODE) . "<br>";

// ============================================
// 4. 删除操作 (DELETE)
// ============================================

echo "<hr><h3>10. 逻辑删除（推荐）</h3>";
$soft_delete = db_soft_delete($conn, 'users', ['id' => 3], 'deleted_at');
echo "逻辑删除结果: " . json_encode($soft_delete, JSON_UNESCAPED_UNICODE) . "<br>";

echo "<h3>11. 物理删除（谨慎使用）</h3>";
// 先插入一条测试数据
$test_insert = db_insert($conn, 'users', [
    'username' => '临时用户',
    'email' => 'temp@test.com',
    'age' => 99,
    'status' => 0,
    'created_at' => date('Y-m-d H:i:s')
]);

if ($test_insert['code'] === 200) {
    $test_id = $test_insert['data']['id'];
    echo "创建测试数据，ID: {$test_id}<br>";
    
    // 删除这条数据
    $delete_result = db_delete($conn, 'users', ['id' => $test_id]);
    echo "删除结果: " . json_encode($delete_result, JSON_UNESCAPED_UNICODE) . "<br>";
}

// 测试安全限制：尝试无条件删除
echo "<h3>12. 安全测试：无条件删除（应失败）</h3>";
$unsafe_delete = db_delete($conn, 'users', '');
echo "结果: " . json_encode($unsafe_delete, JSON_UNESCAPED_UNICODE) . "<br>";

// ============================================
// 5. 自定义查询
// ============================================

echo "<hr><h3>13. 自定义SQL查询（只读）</h3>";
// 使用参数化方式
$custom_sql = "SELECT * FROM users WHERE age > %s AND status = %s ORDER BY id DESC LIMIT 5";
$custom_result = db_query($conn, $custom_sql, [25, 1]);
echo "自定义查询: <pre>";
print_r($custom_result);
echo "</pre>";

// 尝试执行非SELECT（应失败）
echo "<h3>14. 安全测试：执行非SELECT（应失败）</h3>";
$unsafe_query = db_query($conn, "DELETE FROM users WHERE id = 1");
echo "结果: " . json_encode($unsafe_query, JSON_UNESCAPED_UNICODE) . "<br>";

// 关闭连接
db_close($conn);

echo "<hr><p style='color:green'>所有操作完成，连接已安全关闭</p>";
?>

4. 数据库结构 (schema.sql)

-- 创建测试数据库
CREATE DATABASE IF NOT EXISTS test_db 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE test_db;

-- 用户表
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INT DEFAULT 0,
    status TINYINT DEFAULT 1 COMMENT '0-禁用 1-启用',
    login_count INT DEFAULT 0,
    created_at DATETIME,
    updated_at DATETIME,
    deleted_at DATETIME NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

5. 安全机制总结

安全层	实现方法	防护目标
连接安全	`mysqli_set_charset()`	字符集注入
输入过滤	`clean_input()` + `htmlspecialchars()`	XSS攻击
SQL转义	`mysqli_real_escape_string()`	SQL注入
字段白名单	`preg_replace('/[^a-zA-Z0-9_]/', '')`	字段名注入
类型强制	`intval()`, `is_int()` 判断	类型混淆攻击
操作限制	禁止 `WHERE 1=1` 的全表操作	误删/误改全表
SQL类型限制	自定义查询只允许SELECT	权限提升
错误处理	`error_log()` 记录，不暴露详情	信息泄露

6. 函数速查表

操作	函数	参数说明
增	`db_insert($conn, $table, $data)`	插入单条
增	`db_insert_batch($conn, $table, $dataArray)`	批量插入（事务）
删	`db_delete($conn, $table, $where)`	物理删除
删	`db_soft_delete($conn, $table, $where, $field)`	逻辑删除
改	`db_update($conn, $table, $data, $where)`	更新记录
改	`db_increment($conn, $table, $field, $step, $where)`	字段自增/减
查	`db_find_one($conn, $table, $where, $fields)`	查询单条
查	`db_find_all($conn, $table, $where, $fields, $order, $limit, $offset)`	查询多条
查	`db_count($conn, $table, $where)`	统计数量
查	`db_query($conn, $sql, $params)`	自定义SELECT

这套方案完全使用面向过程写法，同时通过多层安全防护确保数据库操作的安全性。

转载请注明来源：https://www.cnyouyue.com 优悦教程

首页 > 学科教程 > PHP > PHP学习笔记

PHP面向过程版MySQLi增删改查

1. 数据库配置 (config.php)

2. 核心CRUD操作 (db_functions.php)

3. 使用示例 (demo.php)

4. 数据库结构 (schema.sql)

5. 安全机制总结

6. 函数速查表

相关阅读

PHP是最好的语言这个梗的出处

PhpMyAdmin 登录之后提示 ”配置文件现在需要一个短语密码/配置文件中的密文(blowfish_secret)太短” 解决办法

运算符的左结合或右结合是什么意思？

PHP整型进制数转换，并输出不同进制数

1. 数据库配置 (config.php)

2. 核心CRUD操作 (db_functions.php)

3. 使用示例 (demo.php)

4. 数据库结构 (schema.sql)

5. 安全机制总结

6. 函数速查表

相关阅读

PHP是最好的语言 这个梗的出处

PhpMyAdmin 登录之后提示 ”配置文件现在需要一个短语密码/配置文件中的密文(blowfish_secret)太短” 解决办法

运算符的左结合或右结合是什么意思？

PHP整型进制数转换，并输出不同进制数

PHP是最好的语言这个梗的出处