NN2022081301/application/admin/controller/system/DataDictionary.php

<?php

namespace app\admin\controller\system;

use app\common\controller\AdminController;
use app\http\IResponse;
use think\Db;

class DataDictionary extends AdminController
{

    /**
     * 获取表数据
     *
     * @author 许祖兴 < zuxing.xu@lettered.cn>
     * @date 2020/3/16 17:05
     *
     * @return mixed
     */
    public function tables()
    {
        $tables = Db::query('show table status');

        $tablename = $this->request->get('tablename');
        $engine = $this->request->get('engine');

        $searchTables = [];
        $searchMode = false;
        if ($tablename || $engine) {
            $searchMode = true;
        }

        foreach ($tables as $key => &$table) {
            $table = array_change_key_case($table);
            $table['id'] = $table['name'];
            $table['index_length'] = $table['index_length'] > 1024 ? intval($table['index_length']/1024) .'KB' : $table['index_length'].'B';
            $table['data_length'] = $table['data_length'] > 1024 ? intval($table['data_length']/1024) .'KB' : $table['data_length'].'B';
            $table['create_time'] = date('Y-m-d', strtotime($table['create_time']));
            // 搜索
            if ($tablename && !$engine && stripos($table['name'], $tablename) !== false) {
                $searchTables[] = $table;
            }
            // 搜索
            if (!$tablename && $engine && stripos($table['engine'], $engine) !== false) {
                $searchTables[] = $table;
            }

            if ($tablename && $engine && stripos($table['engine'], $engine) !== false && stripos($table['name'], $tablename) !== false) {
                $searchTables[] = $table;
            }
        }

        return IResponse::success(['total' => count($tables),'list' => $tables]);

    }

    /**
     * 查看表字段
     *
     * @author 许祖兴 < zuxing.xu@lettered.cn>
     * @date 2020/3/18 10:39
     *
     * @param string $table 表名称
     * @return mixed
     */
    public function view($table)
    {
        $fields = Db::query('show full columns from ' . $table);

        array_walk($fields, function (&$item){
            $item = array_change_key_case($item);
        });

        return IResponse::success(['total' => count($fields), 'list' => $fields]);
    }

    /**
     * 优化表内容
     *
     * @author 许祖兴 < zuxing.xu@lettered.cn>
     * @date 2020/3/21 14:57
     *
     * @return mixed
     */
    public function optimize()
    {
        $tables = $this->request->param('ids');
        foreach (str2arr($tables) as $table){
            Db::query(sprintf('optimize table %s', $table));
        }
        return IResponse::success([], '优化成功');
    }

    /**
     * 备份表数据
     *
     * @author 许祖兴 < zuxing.xu@lettered.cn>
     * @date 2020/3/21 15:59
     *
     * @return mixed
     */
    public function backup()
    {

        $to_file_name = date('YmdHis') . ".sql";

        //数据库中有哪些表
        $tables = Db::query('SHOW TABLES ');
        $tablelist = array();
        foreach ($tables as $v) {
            foreach ($v as $vv) {
                $tablelist[] = $vv;
            }

        }
        $info = "-- ----------------------------\r\n";
        $info .= "-- 日期：" . date("Y-m-d H:i:s", time()) . "\r\n";
        $info .= "-- 仅用于测试和学习,本程序不适合处理超大量数据\r\n";
        $info .= "-- ----------------------------\r\n\r\n";
        file_put_contents($to_file_name, $info, FILE_APPEND);
        //将每个表的表结构导出到文件
        foreach ($tablelist as $val) {

            $res = Db::query('show create table ' . $val);


            foreach ($res as $v) {
                $newres = $v['Create Table'];
            }

            $info = "-- ----------------------------\r\n";
            $info .= "-- Table structure for `" . $val . "`\r\n";
            $info .= "-- ----------------------------\r\n";
            $info .= "DROP TABLE IF EXISTS `" . $val . "`;\r\n";
            $sqlStr = $info . $newres . ";\r\n\r\n";
            //追加到文件
            file_put_contents($to_file_name, $sqlStr, FILE_APPEND);


        }

        //将每个表的数据导出到文件
        foreach ($tablelist as $val) {
            $sql = "select * from " . $val;
            $res = Db::query('select * from ' . $val);

            //如果表中没有数据，则继续下一张表
            if (count($res) < 1) continue;
            //
            $info = "-- ----------------------------\r\n";
            $info .= "-- Records for `" . $val . "`\r\n";
            $info .= "-- ----------------------------\r\n";
            file_put_contents($to_file_name, $info, FILE_APPEND);
            //读取数据

            foreach ($res as $v) {
                $sqlstr = "INSERT INTO `" . $val . "` VALUES (";
                foreach ($v as $vv) {
                    //将数据中的单引号转义，否则还原时会出错
                    $newvv = str_replace("'", "\'", $vv);
                    $sqlstr .= "'" . $newvv . "', ";
                }
                //去掉最后一个逗号和空格
                $sqlstr = substr($sqlstr, 0, strlen($sqlstr) - 2);
                $sqlstr .= ");\r\n";
                file_put_contents($to_file_name, $sqlstr, FILE_APPEND);
            }


            file_put_contents($to_file_name, "\r\n", FILE_APPEND);
        }

        return IResponse::success([], '备份成功');
    }
}