Example of MySQL database backup function implemented by ThinkPHP framework-PHP Tutorial-php.cn

Table of Contents

您可能感兴趣的文章:

Home

Backend Development

PHP Tutorial

Example of MySQL database backup function implemented by ThinkPHP framework

jacklove

Jun 22, 2018 pm 04:01 PM

mysql database thinkphp framework backup

这篇文章主要介绍了ThinkPHP框架实现的MySQL数据库备份功能,结合实例形式分析了thinkPHP导出mysql数据库相关操作实现与使用技巧,需要的朋友可以参考下

本文实例讲述了ThinkPHP框架实现的MySQL数据库备份功能。分享给大家供大家参考，具体如下：

1、缘由

自从2010年开始试用ThinkPHP以来，的确带来了许多方便。的确给我带来了许多方便。此次应为数据频繁备份需要，而每次远程连接到服务器颇为不便。变萌生了写个ThinkPHP数据库备份SQL生成类的念头。

2、介绍

由于在数据库中有使用触发器。因此也需要一并备份。并且为了插入数据的时候不会受到触发器影响而破坏先前插入的数据，在插入数据之前生成了删除触发器的代码。本类并不能生成数据表的创建和删除代码，因此在使用中请注意保证两端表结构的一致。

做WEB开发，一直以来，都采用 Navicat For Mysql 将本地数据库同步到服务器上。前些天，突然心血来潮，将本地数据库升级到了 Mysql 5.5版本，再次将数据同步的时候却发生了错误。想起之前写过的 ThinkPHP 实现Mysql数据库备份只有备份数据的功能，而没有导出表结构的功能。于是想到了升级一下。让其更完整。

本次升级增加了备份表结构、视图功能。导出数据增加了类型判断，insert 语句当字段为空的时候会输出 NULL，当为数字的时候则不会带上单引号。

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

<?php
/**
 * 描述:基于ThinkPHP框架的Mysql数据库导出类
 * 日期:2012-07-15
 * 作者:龚辟愚
 */
class DBExport
{
  /**
   * @description 获取当前数据库的所有表名。
   * @static
   * @return array
   */
  static protected function getTables()
  {
    $dbName=C(&#39;DB_NAME&#39;);
    $result=M()->query("SHOW FULL TABLES FROM `{$dbName}` WHERE Table_Type = &#39;BASE TABLE&#39;");
    foreach ($result as $v){
      $tbArray[]=$v[&#39;Tables_in_&#39;.C(&#39;DB_NAME&#39;)];
    }
    return $tbArray;
  }
  static protected function getViews()
  {
    $dbName=C(&#39;DB_NAME&#39;);
    $result=M()->query("SHOW FULL TABLES FROM `{$dbName}` WHERE Table_Type = &#39;VIEW&#39;");
    foreach ($result as $v){
      $tbArray[]=$v[&#39;Tables_in_&#39;.C(&#39;DB_NAME&#39;)];
    }
    return $tbArray;
  }
  /**
   * @description 导出SQL数据，但不包含表创建代码。
   * @static
   * @return string
   */
  static public function ExportAllData()
  {
    $tables = self::getTables();
    $arrAll = array(
      "SET FOREIGN_KEY_CHECKS=0;",
      self::BuildAllTriggerDropSql(),
      self::BuildTableSql(),
      self::BuildViewSql()
    );
    $tbl = new Model();
    foreach($tables as $table)
    {
      $arrAll[]="\r\nDELETE FROM {$table};";
      /*
      $rs = $tbl->query("SHOW COLUMNS FROM {$table}");
      $arrFields = array();
      foreach ($rs as $k=>&$v){
        $arrFields[] = "`{$v[&#39;Field&#39;]}`";
      }
      $sqlFields = implode($arrFields,",");
      */
      $rs=$tbl->query("select * from `{$table}`");
      foreach ($rs as $k=>&$v){
        $arrValues = array();
        foreach($v as $key=>$val)
        {
          if(is_numeric($val)){
            $arrValues[]=$val;
          }else if(is_null($val)){
            $arrValues[]=&#39;NULL&#39;;
          }else{
            $arrValues[]="&#39;".addslashes($val)."&#39;";
          }
        }
        $arrAll[] = "INSERT INTO `{$table}` VALUES (".implode(&#39;,&#39;,$arrValues).");";
      }
    }
    $arrAll[]=self::BuildTriggerCreateSql();
    return implode("\r\n",$arrAll);
  }
  static protected function BuildTableSql()
  {
    $tables = self::getTables();
    $arrAll = array();
    foreach($tables as &$val){
      $rs = M()->query("SHOW CREATE TABLE `{$val}`");
      $tbSql = preg_replace("#CREATE(.*)\\s+TABLE#","CREATE TABLE",$rs[0][&#39;Create Table&#39;]);
      $arrAll[] = "DROP TABLE IF EXISTS `{$rs[0][&#39;Table&#39;]}`;\r\n{$tbSql};\r\n";
    }
    return implode("\r\n",$arrAll);
  }
  static protected function BuildViewSql()
  {
    $views = self::getViews();
    $arrAll = array();
    foreach($views as &$val){
      $rs = M()->query("SHOW CREATE VIEW `{$val}`");
      $tbSql = preg_replace("#CREATE(.*)\\s+VIEW#","CREATE VIEW",$rs[0][&#39;Create View&#39;]);
      $arrAll[] = "DROP VIEW IF EXISTS `{$rs[0][&#39;View&#39;]}`;\r\n{$tbSql};\r\n";
    }
    return implode("\r\n",$arrAll);
  }
  /**
   * @description 如果存在触发器，生成删除代码。原因是:插入数据的时候可能会受到触发器影响。
   * @static
   * @return string
   */
  static public function BuildAllTriggerDropSql()
  {
    $rs = M()->query("show triggers");
    $arrAll = array();
    foreach ($rs as $k=>&$v)
    {
      $arrSql = array(
        &#39;DROP TRIGGER IF EXISTS `&#39;,$v[&#39;Trigger&#39;],&#39;`;&#39;
      );
      $arrAll[] = implode(&#39;&#39;,$arrSql);
    }
    return implode("\r\n",$arrAll);
  }
  /**
   * @description 生成所有触发器的创建代码。
   * @static
   * @return string
   */
  static protected function BuildTriggerCreateSql()
  {
    $rs = M()->query("show triggers");
    $arrAll = array();
    foreach ($rs as $k=>&$v)
    {
      $arrSql = array(
        &#39;CREATE TRIGGER `&#39;,$v[&#39;Trigger&#39;],&#39;` &#39;,$v[&#39;Timing&#39;],&#39; &#39;,$v[&#39;Event&#39;],&#39; ON `&#39;,
        $v[&#39;Table&#39;],&#39;` FOR EACH ROW &#39;,$v[&#39;Statement&#39;],&#39;;&#39;
      );
      $arrAll[] = implode(&#39;&#39;,$arrSql);
    }
    return implode("\r\n",$arrAll);
  }
}

Copy after login

调用示例:

vendor(&#39;DBExport&#39;,COMMON_PATH);
header(&#39;Content-type: text/plain; charset=UTF-8&#39;);
$dbName = C(&#39;DB_NAME&#39;);
header("Content-Disposition: attachment; filename=\"{$dbName}.sql\"");
echo DBExport::ExportAllData()

Copy after login

您可能感兴趣的文章:

通过PHP实现微信小程序人脸识别刷脸登录功能

PHP实现防止表单重复提交功能【基于token验证】

tp框架(thinkPHP)实现三次登陆密码错误之后锁定账号功能示例

The above is the detailed content of Example of MySQL database backup function implemented by ThinkPHP framework. For more information, please follow other related articles on the PHP Chinese website!

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

How to fix KB5055523 fails to install in Windows 11?

3 weeks ago By DDD

How to fix KB5055518 fails to install in Windows 10?

3 weeks ago By DDD

Strength Levels for Every Enemy & Monster in R.E.P.O.

3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Roblox: Dead Rails - How To Tame Wolves

3 weeks ago By DDD

Blue Prince: How To Get To The Basement

3 weeks ago By DDD

Hot Tools

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Java Tutorial

1653

CakePHP Tutorial

1413

Laravel Tutorial

1306

PHP Tutorial

1251

C# Tutorial

1224

Related knowledge

How to backup Google Chrome extensions Jan 30, 2024 pm 12:36 PM

How to backup Google Chrome extension? For most Google Chrome users, more or less plug-ins are installed during daily use. The existence of plug-ins can improve our usage experience. When we reinstall the system or browser, these plug-ins cannot be retained, and it is troublesome to download and install them again. So is there a way to back up the currently installed plug-ins? Here’s how to do it. The tutorial method of backing up chrome plug-ins first opens Google Chrome, click the menu in the upper right corner, and select More Tools - Extensions. Click Package extension above the extensions page. In C:UsersAdministratorAppDataLocalGoogleChromeUserDataDe

How to use MySQL backup and restore in PHP? Jun 03, 2024 pm 12:19 PM

Backing up and restoring a MySQL database in PHP can be achieved by following these steps: Back up the database: Use the mysqldump command to dump the database into a SQL file. Restore database: Use the mysql command to restore the database from SQL files.

How to delete startup backup in Windows 11's File Explorer Feb 18, 2024 pm 05:40 PM

If you wish to hide the "Start Backup" option in Windows 11's File Explorer, here's what you can do. There are several ways to disable or hide the startup backup option in File Explorer, and we'll briefly list some methods to help you accomplish this task quickly. Before you get started, you need to understand that this option is closely tied to OneDrive. Once you open a library folder (such as Document, Pictures, Music, etc.), it will immediately appear in the file explorer's path. How to delete startup backup in Windows 11’s File Explorer To delete startup backup in Windows 11’s File Explorer, follow the steps below

How to install, uninstall, and reset Windows server backup Mar 06, 2024 am 10:37 AM

WindowsServerBackup is a function that comes with the WindowsServer operating system, designed to help users protect important data and system configurations, and provide complete backup and recovery solutions for small, medium and enterprise-level enterprises. Only users running Server2022 and higher can use this feature. In this article, we will explain how to install, uninstall or reset WindowsServerBackup. How to Reset Windows Server Backup If you are experiencing problems with your server backup, the backup is taking too long, or you are unable to access stored files, then you may consider resetting your Windows Server backup settings. To reset Windows

How to restore the deleted hosts file Feb 22, 2024 pm 10:48 PM

Title: How to restore the hosts file after deletion Summary: The hosts file is a very important file in the operating system and is used to map domain names to IP addresses. If you accidentally delete the hosts file, you may be unable to access certain websites or have other network problems. This article will introduce how to recover accidentally deleted hosts file in Windows and Mac operating systems. Text: 1. Restore hosts file in Windows operating system. Hosts file in Windows operating system

PHP development practice: Use PHPMailer to send emails to users in the MySQL database Aug 05, 2023 pm 06:21 PM

PHP development practice: Use PHPMailer to send emails to users in the MySQL database Introduction: In the construction of the modern Internet, email is an important communication tool. Whether it is user registration, password reset, or order confirmation in e-commerce, sending emails is an essential function. This article will introduce how to use PHPMailer to send emails and save the email information to the user information table in the MySQL database. 1. Install the PHPMailer library PHPMailer is

How to backup system with ghost-ghost backup tutorial Mar 06, 2024 pm 04:30 PM

Recently, many friends have asked the editor how to back up the system with ghost. Next, let us learn the tutorial on how to back up the system with ghost. I hope it can help everyone. 1. After running Ghost, click "OK", as shown in the figure. 2. Click "Local" → "Partition" → "ToImage" (meaning: local → partition → to image file), as shown in the figure. 3. The Select Local Hard Disk window appears, click the hard disk where the partition to be backed up is located, and then click "OK", as shown in the figure. 4. The Select Source Partition window appears (the source partition is the partition you want to back up), click on the partition where the system is located (usually Zone 1, be sure to get it right), and then click "OK", as shown in the figure. 5. Play at this time

How to perform system backup and restore on Kirin OS? Aug 07, 2023 pm 02:22 PM

How to perform system backup and restore on Kirin OS? Kirin operating system is an open source operating system independently developed in China and is widely used in various scenarios. System backup and recovery is a very important task when using Kirin operating system. Backup systems can prevent data loss due to malfunctions or misoperations, while system recovery can quickly restore normal functionality in the event of a system crash. This article will introduce in detail how to perform system backup and recovery on Kirin operating system, and attach relevant code examples. Backing Up the System In order to backup the entire system we can

See all articles