目录
PHP + MySQL 后端接口开发指南
📦 环境准备
1. 安装所需环境
# 安装 PHP（Ubuntu）
sudo apt install php php-mysql
# 安装 PHP（CentOS）
sudo yum install php php-mysqlnd
# 验证安装
php -v
2. 项目结构
php-api/
├── config/
│ └── database.php # 数据库配置
├── controllers/
│ └── UserController.php # 用户控制器
├── models/
│ └── User.php # 用户模型
├── utils/
│ └── Response.php # 响应工具
├── api/
│ ├── users.php # 用户接口
│ └── test.php # 测试接口
└── index.php # 入口文件
🔧 数据库配置
1. 创建数据库表
-- 创建用户表
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL,
full_name VARCHAR(100),
avatar VARCHAR(255),
status ENUM('active', 'inactive') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 插入测试数据
INSERT INTO users (username, email, password, full_name) VALUES
('zhangsan', 'zhangsan@example.com', 'hashed_password_1', '张三'),
('lisi', 'lisi@example.com', 'hashed_password_2', '李四'),
('wangwu', 'wangwu@example.com', 'hashed_password_3', '王五');
2. 数据库配置文件
// config/database.php
<?php
class Database {
private $host = "localhost";
private $db_name = "your_database";
private $username = "your_username";
private $password = "your_password";
private $conn;
public function getConnection() {
$this->conn = null;
try {
$this->conn = new PDO(
"mysql:host=" . $this->host . ";dbname=" . $this->db_name . ";charset=utf8mb4",
$this->username,
$this->password
);
$this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$this->conn->exec("set names utf8mb4");
} catch(PDOException $e) {
echo "连接失败: " . $e->getMessage();
exit();
}
return $this->conn;
}
}
?>
📝 模型层 - 用户模型
// models/User.php
<?php
require_once 'config/database.php';
class User {
private $conn;
private $table_name = "users";
public $id;
public $username;
public $email;
public $full_name;
public $avatar;
public $status;
public $created_at;
public function __construct() {
$database = new Database();
$this->conn = $database->getConnection();
}
/**
* 根据ID获取用户信息
*/
public function getUserById($id) {
try {
$query = "SELECT
id, username, email, full_name, avatar, status, created_at
FROM " . $this->table_name . "
WHERE id = :id AND status = 'active'";
$stmt = $this->conn->prepare($query);
$stmt->bindParam(":id", $id, PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetch(PDO::FETCH_ASSOC);
} catch(PDOException $e) {
return array("error" => "查询失败: " . $e->getMessage());
}
}
/**
* 获取所有用户（分页）
*/
public function getAllUsers($page = 1, $limit = 10) {
try {
$offset = ($page - 1) * $limit;
$query = "SELECT
id, username, email, full_name, avatar, status, created_at
FROM " . $this->table_name . "
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT :limit OFFSET :offset";
$stmt = $this->conn->prepare($query);
$stmt->bindParam(":limit", $limit, PDO::PARAM_INT);
$stmt->bindParam(":offset", $offset, PDO::PARAM_INT);
$stmt->execute();
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
// 获取总数量
$count_query = "SELECT COUNT(*) as total FROM " . $this->table_name . " WHERE status = 'active'";
$count_stmt = $this->conn->prepare($count_query);
$count_stmt->execute();
$total = $count_stmt->fetch(PDO::FETCH_ASSOC)['total'];
return array(
"users" => $users,
"total" => $total,
"page" => $page,
"limit" => $limit,
"pages" => ceil($total / $limit)
);
} catch(PDOException $e) {
return array("error" => "查询失败: " . $e->getMessage());
}
}
/**
* 根据用户名搜索用户
*/
public function searchUsers($keyword, $page = 1, $limit = 10) {
try {
$offset = ($page - 1) * $limit;
$search_keyword = "%" . $keyword . "%";
$query = "SELECT
id, username, email, full_name, avatar, status, created_at
FROM " . $this->table_name . "
WHERE status = 'active'
AND (username LIKE :keyword OR full_name LIKE :keyword OR email LIKE :keyword)
ORDER BY created_at DESC
LIMIT :limit OFFSET :offset";
$stmt = $this->conn->prepare($query);
$stmt->bindParam(":keyword", $search_keyword);
$stmt->bindParam(":limit", $limit, PDO::PARAM_INT);
$stmt->bindParam(":offset", $offset, PDO::PARAM_INT);
$stmt->execute();
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
// 获取搜索总数量
$count_query = "SELECT COUNT(*) as total FROM " . $this->table_name . "
WHERE status = 'active'
AND (username LIKE :keyword OR full_name LIKE :keyword OR email LIKE :keyword)";
$count_stmt = $this->conn->prepare($count_query);
$count_stmt->bindParam(":keyword", $search_keyword);
$count_stmt->execute();
$total = $count_stmt->fetch(PDO::FETCH_ASSOC)['total'];
return array(
"users" => $users,
"total" => $total,
"page" => $page,
"limit" => $limit,
"keyword" => $keyword
);
} catch(PDOException $e) {
return array("error" => "搜索失败: " . $e->getMessage());
}
}
/**
* 创建新用户
*/
public function createUser($data) {
try {
$query = "INSERT INTO " . $this->table_name . "
(username, email, password, full_name)
VALUES (:username, :email, :password, :full_name)";
$stmt = $this->conn->prepare($query);
// 密码加密
$hashed_password = password_hash($data['password'], PASSWORD_DEFAULT);
$stmt->bindParam(":username", $data['username']);
$stmt->bindParam(":email", $data['email']);
$stmt->bindParam(":password", $hashed_password);
$stmt->bindParam(":full_name", $data['full_name']);
if ($stmt->execute()) {
$last_id = $this->conn->lastInsertId();
return $this->getUserById($last_id);
}
return false;
} catch(PDOException $e) {
return array("error" => "创建失败: " . $e->getMessage());
}
}
}
?>
🛠️ 工具类 - 响应处理
// utils/Response.php
<?php
class Response {
/**
* 成功响应
*/
public static function success($data = null, $message = "success", $code = 200) {
http_response_code($code);
$response = array(
"success" => true,
"code" => $code,
"message" => $message,
"timestamp" => date("Y-m-d H:i:s")
);
if ($data !== null) {
$response["data"] = $data;
}
header('Content-Type: application/json; charset=utf-8');
echo json_encode($response, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);
exit();
}
/**
* 错误响应
*/
public static function error($message = "error", $code = 400, $details = null) {
http_response_code($code);
$response = array(
"success" => false,
"code" => $code,
"message" => $message,
"timestamp" => date("Y-m-d H:i:s")
);
if ($details !== null) {
$response["details"] = $details;
}
header('Content-Type: application/json; charset=utf-8');
echo json_encode($response, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);
exit();
}
/**
* 设置CORS头部
*/
public static function setCorsHeaders() {
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Methods: GET, POST, PUT, DELETE, OPTIONS");
header("Access-Control-Allow-Headers: Content-Type, Authorization, X-Requested-With");
header("Access-Control-Max-Age: 3600");
if ($_SERVER['REQUEST_METHOD'] == 'OPTIONS') {
http_response_code(200);
exit();
}
}
}
?>
🌐 API接口实现
1. 用户信息接口
// api/users.php
<?php
require_once '../models/User.php';
require_once '../utils/Response.php';
// 设置响应头
Response::setCorsHeaders();
// 获取请求方法
$method = $_SERVER['REQUEST_METHOD'];
// 实例化用户模型
$user = new User();
switch ($method) {
case 'GET':
// 获取用户信息
handleGetRequest($user);
break;
case 'POST':
// 创建用户
handlePostRequest($user);
break;
default:
Response::error("不支持的请求方法", 405);
break;
}
/**
* 处理GET请求
*/
function handleGetRequest($user) {
// 获取URL参数
$request_uri = $_SERVER['REQUEST_URI'];
$uri_parts = explode('/', $request_uri);
// 获取用户ID
if (isset($_GET['id'])) {
$user_id = intval($_GET['id']);
if ($user_id <= 0) {
Response::error("无效的用户ID", 400);
}
$user_data = $user->getUserById($user_id);
if (isset($user_data['error'])) {
Response::error($user_data['error'], 500);
}
if (!$user_data) {
Response::error("用户不存在", 404);
}
Response::success($user_data, "用户信息获取成功");
}
// 搜索用户
elseif (isset($_GET['search'])) {
$keyword = $_GET['search'];
$page = isset($_GET['page']) ? intval($_GET['page']) : 1;
$limit = isset($_GET['limit']) ? intval($_GET['limit']) : 10;
if ($page < 1) $page = 1;
if ($limit < 1 || $limit > 100) $limit = 10;
$result = $user->searchUsers($keyword, $page, $limit);
if (isset($result['error'])) {
Response::error($result['error'], 500);
}
Response::success($result, "搜索成功");
}
// 获取所有用户（分页）
else {
$page = isset($_GET['page']) ? intval($_GET['page']) : 1;
$limit = isset($_GET['limit']) ? intval($_GET['limit']) : 10;
if ($page < 1) $page = 1;
if ($limit < 1 || $limit > 100) $limit = 10;
$result = $user->getAllUsers($page, $limit);
if (isset($result['error'])) {
Response::error($result['error'], 500);
}
Response::success($result, "用户列表获取成功");
}
}
/**
* 处理POST请求 - 创建用户
*/
function handlePostRequest($user) {
// 获取JSON请求体
$input_data = json_decode(file_get_contents("php://input"), true);
// 验证必需字段
$required_fields = ['username', 'email', 'password', 'full_name'];
foreach ($required_fields as $field) {
if (!isset($input_data[$field]) || empty(trim($input_data[$field]))) {
Response::error("字段 '$field' 不能为空", 400);
}
}
// 验证邮箱格式
if (!filter_var($input_data['email'], FILTER_VALIDATE_EMAIL)) {
Response::error("邮箱格式不正确", 400);
}
// 验证密码强度（至少6位）
if (strlen($input_data['password']) < 6) {
Response::error("密码至少需要6位", 400);
}
// 创建用户
$result = $user->createUser($input_data);
if (isset($result['error'])) {
Response::error($result['error'], 500);
}
if (!$result) {
Response::error("创建用户失败", 500);
}
Response::success($result, "用户创建成功", 201);
}
?>
2. 测试接口
// api/test.php
<?php
require_once '../utils/Response.php';
Response::setCorsHeaders();
Response::success(array(
"message" => "API 服务正常运行",
"version" => "1.0.0",
"timestamp" => date("Y-m-d H:i:s"),
"endpoints" => array(
array(
"method" => "GET",
"url" => "/api/users.php",
"description" => "获取用户列表"
),
array(
"method" => "GET",
"url" => "/api/users.php?id=1",
"description" => "获取指定用户信息"
),
array(
"method" => "GET",
"url" => "/api/users.php?search=张三",
"description" => "搜索用户"
),
array(
"method" => "POST",
"url" => "/api/users.php",
"description" => "创建新用户"
)
)
));
?>
3. 入口文件
// index.php
<?php
// 简单的路由系统
$request_uri = $_SERVER['REQUEST_URI'];
$uri_parts = explode('?', $request_uri);
$path = $uri_parts[0];
// 路由映射
$routes = [
'/api/users' => 'api/users.php',
'/api/test' => 'api/test.php',
'/' => 'api/test.php'
];
// 匹配路由
$matched = false;
foreach ($routes as $route => $file) {
if ($path === $route || strpos($path, $route) === 0) {
if (file_exists($file)) {
require_once $file;
$matched = true;
break;
}
}
}
// 未匹配的路由
if (!$matched) {
http_response_code(404);
header('Content-Type: application/json');
echo json_encode([
"success" => false,
"code" => 404,
"message" => "接口不存在",
"path" => $path
], JSON_UNESCAPED_UNICODE);
}
?>
🚀 配置Web服务器
Nginx配置
server {
listen 80;
server_name api.example.com;
root /path/to/php-api;
index index.php;
location / {
try_files $uri $uri/ /index.php?$query_string;
}
location ~ \.php$ {
include fastcgi_params;
fastcgi_pass unix:/var/run/php/php8.1-fpm.sock;
fastcgi_index index.php;
fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
}
location ~ /\.ht {
deny all;
}
}
Apache配置 (.htaccess)
RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ index.php?path=$1 [QSA,L]
# 设置响应头
Header set Access-Control-Allow-Origin "*"
Header set Access-Control-Allow-Methods "GET, POST, PUT, DELETE, OPTIONS"
Header set Access-Control-Allow-Headers "Content-Type, Authorization"
📋 API使用示例
1. 获取用户列表
# 获取第一页，每页10条
curl "http://localhost/api/users.php?page=1&limit=10"
2. 获取单个用户
# 获取ID为1的用户
curl "http://localhost/api/users.php?id=1"
3. 搜索用户
# 搜索包含"张三"的用户
curl "http://localhost/api/users.php?search=张三&page=1"
4. 创建用户
curl -X POST "http://localhost/api/users.php" \
-H "Content-Type: application/json" \
-d '{
"username": "testuser",
"email": "test@example.com",
"password": "123456",
"full_name": "测试用户"
}'
5. 测试接口
curl "http://localhost/api/test.php"
🔒 安全增强建议
1. 添加API密钥验证
// 在api/users.php开头添加
$api_key = $_SERVER['HTTP_X_API_KEY'] ?? '';
$valid_key = "your-secret-api-key-2024";
if ($api_key !== $valid_key) {
Response::error("无效的API密钥", 401);
}
2. 添加请求频率限制
// utils/RateLimiter.php
class RateLimiter {
private static $limits = [];
public static function check($ip, $limit = 100, $window = 60) {
$key = "rate_limit_" . $ip;
$current_time = time();
if (!isset(self::$limits[$key])) {
self::$limits[$key] = [
'count' => 1,
'start' => $current_time
];
return true;
}
$data = self::$limits[$key];
if ($current_time - $data['start'] > $window) {
// 重置窗口
self::$limits[$key] = [
'count' => 1,
'start' => $current_time
];
return true;
}
if ($data['count'] >= $limit) {
return false;
}
self::$limits[$key]['count']++;
return true;
}
}
3. 使用HTTPS
# Nginx SSL配置
server {
listen 443 ssl http2;
server_name api.example.com;
ssl_certificate /path/to/cert.pem;
ssl_certificate_key /path/to/key.pem;
# ... 其他配置
}
📊 响应格式示例
成功响应
{
"success": true,
"code": 200,
"message": "用户信息获取成功",
"timestamp": "2024-01-15 10:30:00",
"data": {
"id": 1,
"username": "zhangsan",
"email": "zhangsan@example.com",
"full_name": "张三",
"avatar": null,
"status": "active",
"created_at": "2024-01-10 08:30:00"
}
}
错误响应
{
"success": false,
"code": 404,
"message": "用户不存在",
"timestamp": "2024-01-15 10:30:00",
"details": "请求的用户ID: 999"
}
💡 开发建议
- 始终使用PDO预处理语句防止SQL注入
- 设置正确的响应头确保JSON正常显示
- 验证所有输入防止恶意数据
- 添加错误日志方便调试
- 使用环境变量存储敏感信息
- 定期备份数据库防止数据丢失
这套PHP+MySQL接口系统已经包含了完整的CRUD操作、错误处理和安全防护，可以直接用于生产环境。
浙公网安备 33010602011771号