数据库备份操作

fl140125

已于 2023-05-08 17:09:20 修改

阅读量328

点赞数 2

分类专栏： PHP 文章标签：数据库服务器 java

于 2019-02-20 11:50:55 首次发布

本文链接：https://blog.csdn.net/fl140125/article/details/87785700

版权

PHP 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

/**
     * 数据表备份
     * @param $form
     * @return array|int
     */
    public function backUpTable($form)
    {
        function_exists('set_time_limit') && set_time_limit(0);
        $s_time = time();
        $tablePath = base_path('database/backup');
        if (!is_dir($tablePath)) {
            mkdir($tablePath);
        }
        $savaFilePath = $tablePath . DIRECTORY_SEPARATOR . date('Y_m_d') . '_create_table_' . $form['name'] . '.sql';
        $content = '';
        if ($form['form'] === 'table') {
            $content = $this->createTable($form['name']);
        }
        if ($form['form'] === 'source') {
            $content = $this->sourceTable($form['name']);
        }
        if ($form['form'] === 'all') {
            $content = $this->createTable($form['name']) . $this->sourceTable($form['name']);
        }
        $result = writeFile($savaFilePath, $content);
        $form['time'] = time() - $s_time;
        return !empty($result['code']) ? $result : ['code' => Code::SUCCESS, 'message' => 'backup table successfully', 'lists' => $form];
    }

    /**
     * 创建数据表SQL
     * @param string $tableName
     * @return string
     */
    protected function createTable(string $tableName): string
    {
        $result = DB::select(sprintf('SHOW CREATE TABLE %s', $tableName));
        $sql = "-- ----------------------------\n-- " . date('Y-m-d H:i:s') . " backup table start\n-- ----------------------------\n";
        foreach ($result as $item) {
            $item = (array)$item;
            $sql .= "-- ----------------------------\n-- Table structure for {$item['Table']}\n-- ----------------------------\n";
            $sql .= sprintf("DROP TABLE IF EXISTS `%s`", $item["Table"]) . ';';
            $sql .= "\n" . $item['Create Table'];
            $sql .= ";\n -- ";
        }
        return $sql;
    }

    /**
     * 数据表数据SQL
     * @param string $tableName
     * @return string
     */
    protected function sourceTable(string $tableName): string
    {
        $result = DB::select(sprintf('SELECT * FROM %s', $tableName));
        $sql = ";\n-- ----------------------------\n-- Records of $tableName\n-- ----------------------------\n";
        if (empty($result)) {
            $sql .= "-- ----------------------------\n-- " . date('Y-m-d H:i:s') . " backup table end\n-- ----------------------------\n";
            return $sql;
        }
        foreach ($result as $item) {
            $sql .= sprintf('INSERT INTO %s VALUES %s', $tableName, '(');
            foreach ($item as $rows) {
                $sql .= "'$rows',";
            }
            $sql .= ');';
            //删除最后三个字符串
            $sql = substr($sql, 0, strlen($sql) - 3);
            $sql .= ");\n";
        }
        $sql .= "-- ----------------------------\n-- " . date('Y-m-d H:i:s') . " backup table end\n-- ----------------------------\n";
        return rtrim($sql, ',');
    }