本文目录导读：
我来为您设计一个PHP项目中的交易记录与流水查询系统，这是一个完整的解决方案，包含数据库设计、查询逻辑和前端展示。
数据库设计
交易记录表 (transaction_log)
CREATE TABLE `transaction_log` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `transaction_no` varchar(32) NOT NULL COMMENT '交易单号', `user_id` int(11) NOT NULL COMMENT '用户ID', `type` tinyint(4) NOT NULL COMMENT '交易类型：1-充值 2-消费 3-退款 4-提现 5-转账', `amount` decimal(12,2) NOT NULL COMMENT '交易金额', `balance_before` decimal(12,2) DEFAULT '0.00' COMMENT '交易前余额', `balance_after` decimal(12,2) DEFAULT '0.00' COMMENT '交易后余额', `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '状态：0-处理中 1-成功 2-失败 3-已取消', `payment_method` varchar(50) DEFAULT NULL COMMENT '支付方式：alipay/wechat/bank等', `remark` varchar(500) DEFAULT NULL COMMENT '交易备注', `order_id` varchar(32) DEFAULT NULL COMMENT '关联订单号', `ip_address` varchar(45) DEFAULT NULL COMMENT '操作IP', `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`id`), KEY `idx_user_id` (`user_id`), KEY `idx_transaction_no` (`transaction_no`), KEY `idx_type` (`type`), KEY `idx_status` (`status`), KEY `idx_created_at` (`created_at`), KEY `idx_user_created` (`user_id`, `created_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='交易记录表';
资金流水表 (account_flow)
CREATE TABLE `account_flow` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `flow_no` varchar(32) NOT NULL COMMENT '流水号', `account_id` int(11) NOT NULL COMMENT '账户ID', `user_id` int(11) NOT NULL COMMENT '用户ID', `direction` tinyint(4) NOT NULL COMMENT '方向：1-收入 2-支出', `amount` decimal(12,2) NOT NULL COMMENT '变动金额', `balance` decimal(12,2) NOT NULL COMMENT '变动后余额', `flow_type` varchar(30) NOT NULL COMMENT '流水类型：order_pay/recharge/withdraw/refund/etc', `business_key` varchar(64) DEFAULT NULL COMMENT '业务标识', `remark` varchar(500) DEFAULT NULL COMMENT '备注', `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_user_id` (`user_id`), KEY `idx_account_id` (`account_id`), KEY `idx_flow_no` (`flow_no`), KEY `idx_flow_type` (`flow_type`), KEY `idx_created_at` (`created_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='资金流水表';
核心PHP代码
TransactionService.php - 交易服务类
<?php
namespace App\Services;
use App\Models\TransactionLog;
use App\Models\AccountFlow;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;
class TransactionService
{
/**
* 查询交易记录
*/
public function queryTransactions($params, $page = 1, $pageSize = 15)
{
$query = TransactionLog::query();
// 基础过滤
if (!empty($params['user_id'])) {
$query->where('user_id', $params['user_id']);
}
// 交易类型
if (!empty($params['type'])) {
if (is_array($params['type'])) {
$query->whereIn('type', $params['type']);
} else {
$query->where('type', $params['type']);
}
}
// 交易状态
if (isset($params['status']) && $params['status'] !== '') {
$query->where('status', $params['status']);
}
// 时间范围
if (!empty($params['start_date'])) {
$query->where('created_at', '>=', $params['start_date']);
}
if (!empty($params['end_date'])) {
$query->where('created_at', '<=', $params['end_date'] . ' 23:59:59');
}
// 金额范围
if (!empty($params['min_amount'])) {
$query->where('amount', '>=', $params['min_amount']);
}
if (!empty($params['max_amount'])) {
$query->where('amount', '<=', $params['max_amount']);
}
// 交易单号
if (!empty($params['transaction_no'])) {
$query->where('transaction_no', 'like', "%{$params['transaction_no']}%");
}
// 关联订单
if (!empty($params['order_id'])) {
$query->where('order_id', $params['order_id']);
}
// 排序
$sortField = $params['sort_field'] ?? 'created_at';
$sortOrder = $params['sort_order'] ?? 'desc';
$query->orderBy($sortField, $sortOrder);
// 分页
$total = $query->count();
$items = $query->skip(($page - 1) * $pageSize)
->take($pageSize)
->get();
// 格式化数据
$items->transform(function ($item) {
return $this->formatTransaction($item);
});
return [
'total' => $total,
'page' => $page,
'page_size' => $pageSize,
'items' => $items,
'summary' => $this->getTransactionSummary($query)
];
}
/**
* 查询资金流水
*/
public function queryAccountFlows($params, $page = 1, $pageSize = 15)
{
$query = AccountFlow::query();
// 过滤条件
if (!empty($params['user_id'])) {
$query->where('user_id', $params['user_id']);
}
if (!empty($params['account_id'])) {
$query->where('account_id', $params['account_id']);
}
if (!empty($params['direction'])) {
$query->where('direction', $params['direction']);
}
if (!empty($params['flow_type'])) {
$query->where('flow_type', $params['flow_type']);
}
// 时间范围
if (!empty($params['start_date'])) {
$query->where('created_at', '>=', $params['start_date']);
}
if (!empty($params['end_date'])) {
$query->where('created_at', '<=', $params['end_date'] . ' 23:59:59');
}
// 排序
$query->orderBy('created_at', 'desc');
$total = $query->count();
$items = $query->skip(($page - 1) * $pageSize)
->take($pageSize)
->get();
return [
'total' => $total,
'page' => $page,
'page_size' => $pageSize,
'items' => $items->toArray()
];
}
/**
* 获取交易统计
*/
public function getTransactionStats($userId, $startDate, $endDate)
{
return TransactionLog::where('user_id', $userId)
->where('status', 1) // 只统计成功的
->whereBetween('created_at', [$startDate, $endDate])
->selectRaw("
SUM(CASE WHEN type = 1 THEN amount ELSE 0 END) as total_recharge,
SUM(CASE WHEN type = 2 THEN amount ELSE 0 END) as total_consume,
SUM(CASE WHEN type = 3 THEN amount ELSE 0 END) as total_refund,
SUM(CASE WHEN type = 4 THEN amount ELSE 0 END) as total_withdraw,
COUNT(*) as total_count
")
->first();
}
/**
* 格式化交易记录
*/
private function formatTransaction($item)
{
$typeMap = [
1 => ['name' => '充值', 'icon' => 'pay-circle'],
2 => ['name' => '消费', 'icon' => 'shopping-cart'],
3 => ['name' => '退款', 'icon' => 'rollback'],
4 => ['name' => '提现', 'icon' => 'bank'],
5 => ['name' => '转账', 'icon' => 'swap']
];
$statusMap = [
0 => ['name' => '处理中', 'color' => 'orange'],
1 => ['name' => '成功', 'color' => 'green'],
2 => ['name' => '失败', 'color' => 'red'],
3 => ['name' => '已取消', 'color' => 'grey']
];
return [
'id' => $item->id,
'transaction_no' => $item->transaction_no,
'type' => $item->type,
'type_name' => $typeMap[$item->type]['name'] ?? '未知',
'type_icon' => $typeMap[$item->type]['icon'] ?? '',
'amount' => $item->amount,
'amount_formatted' => number_format($item->amount, 2),
'balance_before' => $item->balance_before,
'balance_after' => $item->balance_after,
'status' => $item->status,
'status_name' => $statusMap[$item->status]['name'] ?? '未知',
'status_color' => $statusMap[$item->status]['color'] ?? '',
'payment_method' => $item->payment_method,
'remark' => $item->remark,
'order_id' => $item->order_id,
'created_at' => $item->created_at->format('Y-m-d H:i:s'),
'created_at_diff' => $item->created_at->diffForHumans()
];
}
/**
* 获取交易汇总
*/
private function getTransactionSummary($query)
{
return $query->selectRaw("
COUNT(*) as total_count,
SUM(CASE WHEN status = 1 THEN amount ELSE 0 END) as total_success_amount,
SUM(amount) as total_amount
")->first();
}
}
TransactionController.php - 控制器
<?php
namespace App\Http\Controllers\Api;
use App\Services\TransactionService;
use Illuminate\Http\Request;
class TransactionController extends Controller
{
protected $transactionService;
public function __construct(TransactionService $transactionService)
{
$this->transactionService = $transactionService;
}
/**
* 获取交易列表
*/
public function index(Request $request)
{
$params = $request->validate([
'user_id' => 'sometimes|integer',
'type' => 'sometimes|integer|in:1,2,3,4,5',
'status' => 'sometimes|integer|in:0,1,2,3',
'start_date' => 'sometimes|date',
'end_date' => 'sometimes|date|after_or_equal:start_date',
'min_amount' => 'sometimes|numeric|min:0',
'max_amount' => 'sometimes|numeric|min:0',
'transaction_no' => 'sometimes|string',
'order_id' => 'sometimes|string',
'page' => 'sometimes|integer|min:1',
'page_size' => 'sometimes|integer|min:1|max:100'
]);
$page = $params['page'] ?? 1;
$pageSize = $params['page_size'] ?? 15;
$result = $this->transactionService->queryTransactions($params, $page, $pageSize);
return response()->json([
'code' => 200,
'message' => 'success',
'data' => $result
]);
}
/**
* 获取流水列表
*/
public function flows(Request $request)
{
$params = $request->validate([
'user_id' => 'required|integer',
'account_id' => 'sometimes|integer',
'direction' => 'sometimes|integer|in:1,2',
'flow_type' => 'sometimes|string',
'start_date' => 'sometimes|date',
'end_date' => 'sometimes|date',
'page' => 'sometimes|integer|min:1',
'page_size' => 'sometimes|integer|min:1|max:100'
]);
$page = $params['page'] ?? 1;
$pageSize = $params['page_size'] ?? 15;
$result = $this->transactionService->queryAccountFlows($params, $page, $pageSize);
return response()->json($result);
}
/**
* 获取交易详情
*/
public function show($id)
{
$transaction = TransactionLog::findOrFail($id);
return response()->json([
'code' => 200,
'data' => $this->formatTransactionDetail($transaction)
]);
}
/**
* 获取交易统计
*/
public function stats(Request $request)
{
$params = $request->validate([
'user_id' => 'required|integer',
'start_date' => 'required|date',
'end_date' => 'required|date|after_or_equal:start_date'
]);
$stats = $this->transactionService->getTransactionStats(
$params['user_id'],
$params['start_date'],
$params['end_date']
);
return response()->json([
'code' => 200,
'data' => $stats
]);
}
}
导出功能
/**
* 导出交易记录
*/
public function export(Request $request)
{
$params = $request->validate([
'user_id' => 'required|integer',
'start_date' => 'required|date',
'end_date' => 'required|date',
'format' => 'sometimes|in:csv,xlsx'
]);
$transactions = TransactionLog::where('user_id', $params['user_id'])
->whereBetween('created_at', [$params['start_date'], $params['end_date'] . ' 23:59:59'])
->orderBy('created_at', 'desc')
->get();
$format = $params['format'] ?? 'csv';
if ($format === 'csv') {
return $this->exportCsv($transactions);
} else {
return $this->exportExcel($transactions);
}
}
private function exportCsv($transactions)
{
$headers = [
'Content-Type' => 'text/csv',
'Content-Disposition' => 'attachment; filename="transactions.csv"'
];
$callback = function() use ($transactions) {
$file = fopen('php://output', 'w');
fputcsv($file, ['交易单号', '类型', '金额', '状态', '支付方式', '时间', '备注']);
foreach ($transactions as $transaction) {
fputcsv($file, [
$transaction->transaction_no,
$this->getTypeName($transaction->type),
$transaction->amount,
$this->getStatusName($transaction->status),
$transaction->payment_method,
$transaction->created_at,
$transaction->remark
]);
}
fclose($file);
};
return response()->stream($callback, 200, $headers);
}
前端查询界面 (Vue.js示例)
<template>
<div class="transaction-query">
<!-- 搜索条件 -->
<el-form :model="searchForm" inline>
<el-form-item label="交易类型">
<el-select v-model="searchForm.type" placeholder="全部" clearable>
<el-option label="充值" :value="1" />
<el-option label="消费" :value="2" />
<el-option label="退款" :value="3" />
<el-option label="提现" :value="4" />
<el-option label="转账" :value="5" />
</el-select>
</el-form-item>
<el-form-item label="状态">
<el-select v-model="searchForm.status" placeholder="全部" clearable>
<el-option label="处理中" :value="0" />
<el-option label="成功" :value="1" />
<el-option label="失败" :value="2" />
<el-option label="已取消" :value="3" />
</el-select>
</el-form-item>
<el-form-item label="时间范围">
<el-date-picker
v-model="dateRange"
type="daterange"
range-separator="至"
start-placeholder="开始日期"
end-placeholder="结束日期"
value-format="yyyy-MM-dd"
/>
</el-form-item>
<el-form-item label="交易单号">
<el-input v-model="searchForm.transaction_no" placeholder="输入交易单号" />
</el-form-item>
<el-form-item>
<el-button type="primary" @click="handleSearch">查询</el-button>
<el-button @click="handleReset">重置</el-button>
<el-button @click="handleExport" type="success">导出</el-button>
</el-form-item>
</el-form>
<!-- 统计信息 -->
<el-row :gutter="20" class="statistics">
<el-col :span="6">
<el-card>
<div class="stat-item">
<span>总交易次数</span>
<h3>{{ summary.total_count }}</h3>
</div>
</el-card>
</el-col>
<el-col :span="6">
<el-card>
<div class="stat-item">
<span>成功交易额</span>
<h3>¥{{ summary.total_success_amount }}</h3>
</div>
</el-card>
</el-col>
<el-col :span="6">
<el-card>
<div class="stat-item">
<span>总金额</span>
<h3>¥{{ summary.total_amount }}</h3>
</div>
</el-card>
</el-col>
</el-row>
<!-- 交易列表 -->
<el-table :data="tableData" stripe style="width: 100%">
<el-table-column prop="transaction_no" label="交易单号" width="200" />
<el-table-column prop="type_name" label="类型" width="100">
<template slot-scope="scope">
<el-tag :type="scope.row.type | typeTag">{{ scope.row.type_name }}</el-tag>
</template>
</el-table-column>
<el-table-column prop="amount_formatted" label="金额" width="150" />
<el-table-column prop="balance_before" label="交易前余额" width="150" />
<el-table-column prop="balance_after" label="交易后余额" width="150" />
<el-table-column prop="status_name" label="状态" width="100">
<template slot-scope="scope">
<el-tag :type="scope.row.status | statusTag">{{ scope.row.status_name }}</el-tag>
</template>
</el-table-column>
<el-table-column prop="payment_method" label="支付方式" width="120" />
<el-table-column prop="created_at" label="时间" width="180" />
<el-table-column label="操作" width="150">
<template slot-scope="scope">
<el-button type="text" @click="viewDetail(scope.row)">查看详情</el-button>
</template>
</el-table-column>
</el-table>
<!-- 分页 -->
<el-pagination
@size-change="handleSizeChange"
@current-change="handleCurrentChange"
:current-page="currentPage"
:page-sizes="[15, 30, 50, 100]"
:page-size="pageSize"
layout="total, sizes, prev, pager, next, jumper"
:total="total">
</el-pagination>
</div>
</template>
<script>
export default {
data() {
return {
searchForm: {
type: '',
status: '',
transaction_no: '',
min_amount: '',
max_amount: ''
},
dateRange: [],
tableData: [],
currentPage: 1,
pageSize: 15,
total: 0,
summary: {
total_count: 0,
total_success_amount: 0,
total_amount: 0
}
}
},
methods: {
async fetchData() {
const params = {
...this.searchForm,
page: this.currentPage,
page_size: this.pageSize
}
if (this.dateRange && this.dateRange.length === 2) {
params.start_date = this.dateRange[0]
params.end_date = this.dateRange[1]
}
try {
const response = await axios.get('/api/transactions', { params })
this.tableData = response.data.data.items
this.total = response.data.data.total
this.summary = response.data.data.summary
} catch (error) {
this.$message.error('获取数据失败')
}
},
handleSearch() {
this.currentPage = 1
this.fetchData()
},
handleReset() {
this.searchForm = {
type: '',
status: '',
transaction_no: '',
min_amount: '',
max_amount: ''
}
this.dateRange = []
this.handleSearch()
},
handleSizeChange(val) {
this.pageSize = val
this.fetchData()
},
handleCurrentChange(val) {
this.currentPage = val
this.fetchData()
},
viewDetail(row) {
// 查看交易详情
this.$router.push(`/transactions/${row.id}`)
},
async handleExport() {
const params = {
...this.searchForm,
start_date: this.dateRange[0] || '',
end_date: this.dateRange[1] || '',
format: 'csv'
}
try {
const response = await axios.get('/api/transactions/export', {
params,
responseType: 'blob'
})
const url = window.URL.createObjectURL(new Blob([response.data]))
const link = document.createElement('a')
link.href = url
link.download = `transactions_${Date.now()}.csv`
link.click()
window.URL.revokeObjectURL(url)
} catch (error) {
this.$message.error('导出失败')
}
}
},
mounted() {
this.fetchData()
}
}
</script>
优化建议
性能优化
// 1. 使用索引优化查询
// 2. 大数据量使用分页查询
// 3. 缓存热点数据
public function getTransactionList($userId, $page, $pageSize)
{
$cacheKey = "transactions:{$userId}:{$page}:{$pageSize}";
return Cache::remember($cacheKey, 60, function() use ($userId, $page, $pageSize) {
return $this->queryTransactions(['user_id' => $userId], $page, $pageSize);
});
}
// 4. 使用数据库分区
ALTER TABLE transaction_log
PARTITION BY RANGE (TO_DAYS(created_at)) (
PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
PARTITION p_future VALUES LESS THAN MAXVALUE
);
安全考虑
// 1. 权限控制
public function index(Request $request)
{
$this->authorize('view-transactions');
// 只能查看自己的交易记录
if (auth()->user()->role !== 'admin') {
$params['user_id'] = auth()->id();
}
}
// 2. 敏感信息脱敏
private function maskTransactionNo($no)
{
return substr($no, 0, 4) . '****' . substr($no, -4);
}
// 3. 防止SQL注入 - 使用参数绑定
$query->whereRaw('amount BETWEEN ? AND ?', [$min, $max]);
这套完整的交易记录与流水查询方案包含了数据库设计、后端API、查询逻辑、前端界面、导出功能以及性能优化建议,可以根据实际项目需求进行调整和扩展。
版权声明：NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
