thinkphp6中数据库的操作和增删改查导出Excel示例

上一篇：《thinkphp6的项目结构配置和常用功能封装以及消息队列的用法》

数据库规范

建表应当避免太多冗余字段的产生，不确定某个业务或者某个字段是否已经存在，应当先确认不存在，然后再去创建。创建数据表和字段应该有明确的备注说明。对于较复杂的业务场景，创建数据表应该多人协同沟通后再创建。

DDL规范

建议通过CREATE语句创建表结构，通过ALTER语句修改表结构。一个数据表的DDL语句参考如下：

    CREATE TABLE `demo` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
      `type` tinyint(11) NOT NULL COMMENT '类型:1类型A,2类型B',
      `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',
      `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态:1正常,2异常',
      `is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除:0未删除,1已删除',
      `creator_id` int(11) NOT NULL COMMENT '创建人id',
      `add_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
      `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
      PRIMARY KEY (`id`),
      KEY `type` (`type`) COMMENT '类型'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='demo表';

细节说明：

• 一般情况下建议字段都使用 "NOT NULL DEFAULT xxx"，注意 DEFAULT xxx不是必须的！有时候要求不能为空的情况下就不要设置 DEFAULT xxx

• 至少应该包含如下字段：id、is_deleted、add_time、update_time；

• add_time 和 update_time 统一使用datetime类型，而不要使用 timestamp类型；除非某些业务有特殊情况，应该尽可能统一对这两个时间使用 "NOT NULL DEFAULT CURRENT_TIMESTAMP"

• 类似于 status、is_xxx 这样表示“状态”或者“是否xxx”的，尽量使用 tinyint 类型；

• 针对 类似 type、status 这样的使用 数值类型表示不同状态的，尽量在代码中model 层定义好相关常量和注释；

• 主键id一定要有，其他普通索引根据具体查询情况来定；

• 针对“创建人id”，建议统一使用 creator_id 字段，并且不建议冗余存储“创建人的姓名”；

• 数据表和字段的 COMMENT 一定要有；

• 针对多表有关联的情况，或者其他复杂业务的情况，最好能够有数据表说明文档；

项目分层

代码流程流转：路由 --> controller --> service --> model

路由

• 路由统一放到 app/{$module_name}/route/{$xxx}.php

controller

• 统一放到 app/{$module_name}/controller/{$xxx}/{$xxx}.php

• 无特殊情况的话，都应该继承自 BaseController，所有方法都应该在最外层使用 try...catch... 包裹，使用统一的返回方式，成功返回使用 ApiReturn::retSuccess($list); 错误返回使用 ApiReturn::retError($e);

• 示例代码： app/admin/controller/demo/Demo.php

class Demo extends CommonBaseController
{
    /**
     * 获取 Demo 列表
     * 注意, 应该对此处的整块代码使用 try...catch... 包裹
     * @param Request $request
     * @return void
     */
    public function getDemoList(Request $request)
    {
        try{
            //参数校验
            validate(DemoValidate::class)->scene('getList')->check($this->params); // 场景验证
            //调用Service层处理业务逻辑
            $list = DemoService::getDemoList($this->params);
            MyLog::record()->info("成功返回");
            //统一的成功返回方法
            ApiReturn::retSuccess($list);
        }catch (Exception | Error $e){
            //异常或错误 被捕获到以后,调用统一方法处理
            ApiReturn::retError($e);
        }
    }
}

service

• 统一放到 app/{$module_name}/service/{$xxx}/{$xxx}Service.php

• 用来处理业务逻辑，非必要情况下不要直接操作数据库，而应该在model层做数据库的 增删改查。

• 逻辑层应该“快速失败”，优先判断异常的情况，快速返回。

• 异常情况尽可能使用 throw new MyException(SystemCode::CODE_UNKNOWN);所有的错误码和错误文案统一管理：app/common/code/{xxx}.php

• 尽量避免在多层的 if...else... 中编写大块业务代码，应该改为使用 “专而小” 的方法块，尽可能的保证一个小块方法只处理一个业务逻辑，这样能很大限度的保证代码复用。

• 代码示例： app/admin/service/demo/DemoService.php

class DemoService extends BaseService
{
    public static function getDemoList($params)
    {
        //异常情况尽可能使用 throw new MyException，传入错误码
        //所有的错误码和错误文案统一管理：app/common/code/{xxx}Code.php
        if(empty($params['name'])){
            MyLog::record()->warning($params, "错误的name值,请留意!");
            throw new MyException(ErrorCode::CODE_PARAMS_ERROR);
        }

        //调试信息或者需要收集的数据通过MyLog统一记录
        //log内容可以传入任意数据类型：数组、json、字符串、数值
        MyLog::record()->notice(['name'=>$params['name']],"参数name值");

        //调用 model 层对数据库增删改查
        $list = Demo::getAllList();
        if(empty($list)){
            return [];
        }

        foreach($list as &$v){
            $v['type_name'] = Demo::TYPE_MAP[$v['type']] ?? '';
            $v['status_name'] = Demo::STATUS_MAP[$v['status']] ?? '';
        }
        return $list;
    }
}

model

• 统一放到 app/common/model/{$xxx}/{$xxx}.php
• 对于数据表中用数字表示枚举值的字段，应该在model 层定义常量并标注说明。
• 常用的 增删改查 应该放到 model 层处理，并写明注释。
• 当需要查询或更新一个表的数据时，应该先查看 model中已有的方法是否能满足需求，能复用尽量复用。
• 代码示例： app/common/model/demo/Demo.php

class Demo extends CommonBaseModel
{
    /*
    CREATE TABLE `demo` (
      `id` int(11) NOT NULL COMMENT 'id',
      `type` int(11) NOT NULL COMMENT '类型:1类型A,2类型B',
      `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',
      `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态:1正常,2异常',
      `is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除:0未删除,1已删除',
      `creator_id` int(11) NOT NULL COMMENT '创建人id',
      `add_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
      `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
      PRIMARY KEY (`id`),
      KEY `type` (`type`) COMMENT '类型'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='demo表';
     */

    //type 类型:1类型A,2类型B
    const TYPE_A = 1;
    const TYPE_B = 2;
    const TYPE_MAP = [
        self::TYPE_A => '类型A',
        self::TYPE_B => '类型B',
    ];

    //status 状态:1正常,2异常
    const STATUS_NORMAL = 1;
    const STATUS_DISABLED = 2;
    const STATUS_MAP = [
        self::STATUS_NORMAL => '正常',
        self::STATUS_DISABLED => '已禁用',
    ];

    /**
     * 获取正常的列表
     * @return array
     */
    public static function getNormalList(){
        $where = [];
        $where[] = ['status','=',self::STATUS_NORMAL];
        return self::whereNotDelete($where)->select()->toArray();
    }

    /**
     * 更新status
     */
    public static function updateDemoStatus($id, $status){
        return self::where(['id'=>$id])->update(['status'=>$status]);
    }

}

运行效果

查询方法封装

CommonBaseModel

在 app/common/model/CommonBaseModel.php 中，对数据库 model 层公用的方法进行了封装，这个类继承自app\common\model：

class CommonBaseModel extends Model
{
  
}

比如，只查询未删除的数据：

/**
 * 只查询 is_deleted = 0 的数据,需要传入二维数组结构的 $where 条件
 * @param $where
 */
public static function whereNotDelete($where = [])
{
    $where[] = ['is_deleted', '=', 0];
    return parent::where($where);
}

获取数据列表,分页处理:

public static function getDataList(array $where, string $order_field = 'id', string $order_style = 'desc', $page = null, $page_size = null): array
{
    $query = self::whereNotDelete($where)->order($order_field, $order_style);
    if ($page && $page_size) {
        $query->page($page, $page_size);
    }
    $result = $query->select()->toArray();
    return $result;
}

MysqlUtils

在 app/common/utils/MysqlUtils.php 中封装了MySQL常用的公共方法，比如根据客户端传递的参数，进行不同类型的where条件封装：

public static function getWhere(array $where_rule, array $params)
{
    $where = [];
    foreach ($where_rule as $field => $val) {
        $type = $val[0];
        $value1 = $params[$val[1][0]] ?? null;
        if ($type == 'like') {
            $where[] = [$field, 'like', '%' . $value1 . '%'];
        } elseif ($type == 'between') {
            //...
        } else {
            $where[] = [$field, $type, $value1];
        }
    }
    return $where;
}

在需要封装where条件的地方，只需要如下调用即可：

$where_rule = [
    'username' => ['=', ['username']],
    'nickname' => ['like', ['nickname']],
    'id' => ['in', ['id']],
    'add_time' => ['between', ['add_time_start', 'add_time_end']],
];
$where = MysqlUtils::getWhere($where_rule, $params);

得到的where条件如下：

Array ( 
  [0] => Array ( [0] => username [1] => = [2] => zhangsan ) 
  [1] => Array ( [0] => nickname [1] => like [2] => %王% ) 
  [2] => Array ( [0] => id [1] => in [2] => 1,2,6 ) 
)

查询列表并导出Excel

针对数据列表，一般常见的业务场景，除了增删改查之外，还会有导出数据的需求。

在 app/admin/service/user/UserService.php 这个业务逻辑代码中，我封装了查询数据列表并根据传递参数的不同导出Excel和Zip压缩包的功能。

数据表准备

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `username` varchar(50) NOT NULL DEFAULT '' COMMENT '用户名',
  `mobile` varchar(50) NOT NULL DEFAULT '' COMMENT '手机号',
  `password` varchar(255) NOT NULL DEFAULT '' COMMENT '密码',
  `nickname` varchar(50) NOT NULL DEFAULT '' COMMENT '昵称',
  `avatar` varchar(512) NOT NULL DEFAULT '' COMMENT '头像URL',
  `last_login_time` datetime DEFAULT NULL COMMENT '最后一次登录时间',
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态:1正常,2禁用',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除:0未删除,1已删除',
  `add_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1002 DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

生成Mock数据

为了方便测试，我通过循环生成了1000条用户数据信息，你可以通过 insertMockData() 这个方法查看。

public static function insertMockData()
{
    $count = User::getDataCount();
    if ($count >= 1000) {
        throw new \Exception("当前用户数据总数{$count}, 已经足够测试用了");
    }

    $pinyin = new Pinyin();
    for ($i = 0; $i <= 1000; $i++) {
        $nickname = MyTools::getChineseRandomName();
        $add_data = [
            'username' => str_replace(' ', '', $pinyin->sentence($nickname)),
            'mobile' => '1' . rand(300000, 999999),
            'nickname' => $nickname,
            'avatar' => 'https://profile-avatar.csdnimg.cn/default.jpg',
            'last_login_time' => echoNowDate(),
        ];
        User::insert($add_data);
    }
    return true;
}

生成的数据示例如下：

如果前端传递的参数中不包含export 参数，那么就正常返回用户列表数据；如果传递了参数export并且不为data_and_attachment则仅导出Excel；如果传递的export参数值为data_and_attachment则将用户头像的文件下载下来生成压缩包一起导出。

导出Excel

先说说直接导出的逻辑，先对数据列表的字段和表头设定如下，可以支持二维结构的解析：

//设置导出的字段和表头
$field_map = [
    "id" => "ID",
    "username" => "用户名",
    "mobile" => "手机号",
    "nickname" => "昵称",
    "status" => "状态",
    //针对二维模型导出的字段定义
    "time.last_login_time" => "最后登录时间",
    "time.add_time" => "添加时间",
    "time.update_time" => "修改时间",
];

核心导出的方法：app/common/utils/FileUtil.php文件的outputExcel方法。

/**
 * 导出Excel文件
 * @param array $list 数据列表数组
 * @param array $field_map 数组字段数组
 * @param string $file_path 文件路径
 * @param string $filename 文件名称
 * @param bool $filename_append_now_time 文件名是否追加当前时间
 * @param string $suffix 文件后缀
 * @return false|string
 * @throws Exception
 */
public static function outputExcel(array $list, array $field_map, string $file_path, string $filename, bool $filename_append_now_time = true, string $suffix = 'xlsx')
{
    //处理 $list 数据列表
    $data = [];
    foreach ($list ?? [] as $v) {
        $item = [];
        foreach ($field_map as $field_key => $field_val) {
            //解析 $field_map 中 数组多级key的数据,需要在 $field_map 中定义多个key 以.分隔, 例如: user.info.name
            $field_key_split = explode('.', $field_key);
            $container = $v;
            for ($index = 0; $index <= 2; $index++) {
                if (isset($field_key_split[$index]) && isset($container[$field_key_split[$index]])) {
                    $container = $container[$field_key_split[$index]];
                }
            }
            if (is_array($container)) {
                $container = '';
            }
            $item[$field_key] = $container;
        }
        //$data[] = array_values($item);
        $data[] = $item;
    }

    //表头A-Z列定义
    $cellName = [];
    for ($i = 'A'; $i <= 'Z'; $i++) {
        $cellName[] = $i;
    }
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();

    //设置表头行
    $titleList = array_values($field_map);
    //设置表头单元格样式
    $styleArray = [
        'font' => [
            'bold' => true, // 字体加粗
            'color' => [
                'rgb' => '0000ff',
            ],
        ]
    ];
    foreach ($titleList as $tkey => $tval) {
        $sheet->setCellValue($cellName[$tkey] . '1', $tval);
        $sheet->getStyle($cellName[$tkey] . '1')->applyFromArray($styleArray);
    }

    //设置表数据内容行和列
    foreach ($data ?? [] as $key => $val) {
        $val = array_values($val);
        foreach ($val as $kk => $vv) {
            if (is_string($vv)) { //对于字符串类型,防止较长的数字字符串被格式化为科学计数法
                $sheet->setCellValueExplicitByColumnAndRow(($kk + 1), ($key + 2), $vv, DataType::TYPE_STRING);
            } else {
                //导出的某个字段值里面包含换行符(\n)会导致报错,因此需要过滤前后的空格和换行符
                $vv=trim($vv);
                //导出的数据中如果存在 "=" 开头的字符串,会出错 Formula Error: An unexpected error occurred",因此需要对 "=" 特殊处理
                if (strpos($vv, '=') === 0 ) {
                    $vv = "'" . $vv; // 在 = 前面加个单引号
                }
                $sheet->setCellValue($cellName[$kk] . ($key + 2), $vv);
            }
        }
    }

    //创建目录并写入Excel文件
    if (!is_dir($file_path)) {
        if (!mkdir($file_path, 0777, true)) {
            return false;
        }
    }
    if ($filename_append_now_time) {
        $filename .= '-' . date("YmdHis", time());
    }
    $filename .= '.' . strtolower($suffix);
    $objWriter = new Xlsx($spreadsheet);
    $objWriter->save($file_path . '/' . $filename);

    return $filename;
}

执行后，结果如下：

我这里直接返回了项目配置的HOST路径。如果条件允许，还可以对生成的文件上传到OSS后，返回OSS的URL。

查看文件：

导出压缩包

如果需要导出数据表中的用户头像文件，和表格一起导出，可以对文件下载后生成压缩包。

核心文件位于： app/common/utils/FileUtil.php 文件的 downloadRemoteFile() 方法和 createZipFile() 方法：

/**
 * 根据URL地址下载文件
 * @param $url
 * @param $savePath
 * @return void
 */
public static function downloadRemoteFile($url, $savePath)
{
    $file_folder = dirname($savePath);
    if (!file_exists($file_folder)) {
        mkdir($file_folder, 0777, true);
    }

    ob_start();
    readfile($url);
    $img = ob_get_contents();
    ob_end_clean();
    $size = strlen($img);
    $fp = fopen($savePath, 'a');
    fwrite($fp, $img);
    fclose($fp);
}

/**
 * 压缩整个文件夹
 */
public static function createZipFile($dir_path, $filename = null, $delete_dir = true)
{
    if (!$filename) {
        $filename = $dir_path . '.zip';
    }

    // 创建一个新的ZIP文件
    $zip = new ZipArchive();

    // 如果压缩包创建成功
    if (!$zip->open($filename, ZipArchive::CREATE | ZipArchive::OVERWRITE) === true) {
        throw new \Exception("压缩包创建失败", 2001);
    }

    $files = new RecursiveIteratorIterator(new RecursiveDirectoryIterator($dir_path), RecursiveIteratorIterator::LEAVES_ONLY);
    foreach ($files as $name => $file) {
        // Skip directories (they would be added automatically)
        if (!$file->isDir()) {
            // Get real and relative path for current file
            $filePath = $file->getRealPath();
            $relativePath = substr($filePath, strlen($dir_path) + 1);
            //echo $relativePath . PHP_EOL;
            // Add current file to archive
            $zip->addFile($filePath, $relativePath);
        }
    }

    // 关闭ZIP文件
    $zip->close();

    //删除原本的文件夹
    if ($delete_dir) {
        self::deleteFolder($dir_path);
    }

    return true;
}

执行效果如下：

异步导出

这里需要考虑到一个情况，如果导出的数据耗费的时间特别长（比如导出的数据量特别多，或者需要导出附件的时候），那么应该考虑通过消息队列异步导出。

//导出Excel
if ($is_export) {
    //如果超过设定的最大直接导出条数,或者需要导出附件("export":"data_and_attachment"),则通过队列异步导出
    if (!$is_from_queue && (count($list) > User::MAX_EXPORT_COUNT || $params['export'] == 'data_and_attachment')) {
        self::exportUserListDataByQueue($list, $params);
    } else {
        return self::exportUserListData($list, $params);
    }
}

只需要将当前需要导出的数据的查询条件写入到队列中，然后通过消费者根据条件去查询到结果，再执行导出即可。

//写入队列
$jobHandlerClassName = 'app\job\user\UserExportConsumer';
$queue_data = [
    'time' => time(),
    'params' => $params,
    'count' => count($list),
    'operate_user_id' => 1, //操作人的id
];
$isPushed = Queue::push($jobHandlerClassName, $queue_data, $jobQueueName);

然后启动消费者，文件位于 app/job/user/UserExportConsumer.php

/**
 * 导出用户数据消息队列:
 * php think queue:work --queue exportUserDataQueue
 */
class UserExportConsumer extends BaseJob
{
    /**
     * fire方法是消息队列默认调用的方法
     * @param Job $job 当前的任务对象
     * @param array $data 发布任务时自定义的数据
     */
    public function fire(Job $job, array $data)
    {
        MyLog::record()->info($data, 'UserExportConsumerData');

        // 有些任务在到达消费者时，可能已经不再需要执行了
        $isJobHasDone = $this->checkIfJobHasDone($data);
        if (!$isJobHasDone) {
            MyLog::record()->info('checkIfJobHasDoneReturnFalse', 'UserExportConsumerResult');
            $job->delete();
            return;
        }

        try {
            //执行具体的业务逻辑
            $result = UserService::getUserList($data['params'], true, true);
            MyLog::record()->info($result, 'UserExportConsumerResult');

            //todo 将导出成功的URL通过 邮箱/企业微信/钉钉 推送给指定人员, 或者更新到某个地方,让指定人员去下载这个URL的文件

            //任务执行成功
            $job->delete();
            echo "任务执行成功,已从队列中删除\n";
        } catch (\Exception $e) {
            //判断任务执行失败的次数,超过一定次数后从队列中删除,防止一直失败占用队列资源
            echo "任务已经执行失败 " . $job->attempts() . "次 \n";
            if ($job->attempts() >= 3) {
                //todo 暂存数据库,等待后续人工介入后手动处理

                //删除队列中的数据
                $job->delete();
                echo "任务已执行失败 " . $job->attempts() . " 次,已暂存数据库,已从队列中删除 \n";
            }
            return;
        }
    }

    /**
     * 有些消息在到达消费者时,可能已经不再需要执行了
     * @param array $data
     * @return bool
     */
    private function checkIfJobHasDone(array $data): bool
    {
        //todo 根据业务需求, 与当时写入队列时的数据校验

        return true;
    }

}

关于 thinkphp6 中 消息队列的使用，可以参考上一篇文章：《thinkphp6的项目结构配置和常用功能封装》

完整源代码：https://gitee.com/rxbook/rx-php-box

原文地址：https://blog.csdn.net/rxbook/article/details/142916215

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！