如何在CodeIgniter的数据库备份函数中添加全表截断功能？

Got it, let's tackle this. You want to truncate all tables after creating your backup (since you don't want to backup empty tables, right?), and you're using CodeIgniter's dbutil for the backup. Here's how to modify your export_db() function properly:

Key Notes Before Modifying #

• We'll truncate tables after the backup is saved and downloaded, so you don't lose your data before backing up.
• For InnoDB tables with foreign key constraints, we need to temporarily disable foreign key checks first—otherwise, truncate will fail due to relational locks.

Modified Full Function #

public function export_db() {
    // Step 1: Create the database backup
    $this->load->dbutil();
    $prefs = array(
        'format' => 'zip',
        'filename' => date("Y-m-d-H-i-s").'.sql'
    );
    $backup = & $this->dbutil->backup($prefs);
    $db_name = 'db-' . date("Y-m-d-H-i-s") . '.zip';
    $save = 'assets/database_backup/' . $db_name;
    $this->load->helper('file');
    // Fixed: Original code missed the file path parameter in write_file()
    write_file($save, $backup);
    $this->load->helper('download');
    force_download($db_name, $backup);

    // Step 2: Truncate all tables (AFTER backup is completed!)
    try {
        // Disable foreign key checks to avoid truncate errors for InnoDB tables
        $this->db->query('SET FOREIGN_KEY_CHECKS = 0;');

        // Get all table names in the current database
        $tables = $this->db->list_tables();

        foreach ($tables as $table) {
            // Optional: Skip specific tables if needed (e.g., system settings, logs)
            // if ($table === 'excluded_table_name') continue;
            
            // Truncate the table (backticks handle table names with special characters)
            $this->db->query("TRUNCATE TABLE `$table`;");
        }

        // Re-enable foreign key checks to maintain referential integrity
        $this->db->query('SET FOREIGN_KEY_CHECKS = 1;');

        // Optional: Add a success flash message if using sessions
        // $this->session->set_flashdata('success', 'Backup created and all tables truncated successfully!');
    } catch (Exception $e) {
        // Log or handle truncation errors without breaking the backup flow
        log_message('error', 'Failed to truncate tables: ' . $e->getMessage());
        // $this->session->set_flashdata('error', 'Backup created, but table truncation failed: ' . $e->getMessage());
    }
}

Important Fixes & Tips #

1. Fixed Write File: Your original write_file($backup); was missing the target file path ($save)—I added this so the backup is actually saved to your assets/database_backup/ directory.
2. Foreign Key Handling: The SET FOREIGN_KEY_CHECKS commands are non-negotiable for InnoDB databases; skip this only if you're using MyISAM (which doesn't support foreign keys).
3. Exclude Tables: If there are tables you don't want to truncate (like user roles or system configs), uncomment the skip line and add your table names.
4. Permissions: Ensure your database user has TRUNCATE permissions for all tables in the database.
5. Error Isolation: The try/catch block ensures that even if truncation fails, your backup still completes successfully.

内容的提问来源于stack exchange，提问作者Shambu