如何在wp_usermeta表中替换meta_key？线上WordPress数据库修改求助

Hey there! I totally get the anxiety of modifying a live database—let’s break this down into safe, actionable steps to get this done without breaking your site.

First: Backup, Backup, Backup! #

Before touching any database data, always create a full backup (at minimum, backup the wp_usermeta table). You can do this via:

• Your hosting control panel’s built-in database backup tool
• phpMyAdmin: Select the wp_usermeta table, click "Export" and save the generated file
• A WordPress backup plugin (ensure it includes database backups)

Option 1: Direct SQL Query (Fastest, but Test First) #

This is the most efficient method, but we’ll verify exactly what we’re changing before making edits to avoid mistakes.

Step 1: Confirm Target Entries #

First, run a SELECT query to check which entries you’ll modify. Replace wp_ with your actual table prefix if it’s different:

SELECT * FROM wp_usermeta WHERE meta_key = '_address_1';

Review the results—this should show all user meta entries with the _address_1 key. Double-check these are the ones you want to update.

Step 2: Run the Update Query #

Once you’ve confirmed, execute the UPDATE query (again, adjust the table prefix if needed):

UPDATE wp_usermeta 
SET meta_key = 'address_1' 
WHERE meta_key = '_address_1';

Critical note: The WHERE clause ensures you only modify the exact entries you want—never run an UPDATE without it, as you could overwrite unrelated data.

Option 2: WordPress Native Function (No Direct SQL) #

If you’re more comfortable using WordPress’s built-in tools, use the $wpdb class to run the update safely:

1. Create a new file named fix-meta-key.php in your WordPress root directory (where wp-config.php lives)
2. Paste this code into the file:

<?php
// Load the WordPress environment
require_once('wp-load.php');

global $wpdb;

// Count how many entries need updating
$entry_count = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$wpdb->usermeta} WHERE meta_key = %s", '_address_1' ) );

echo "Found {$entry_count} entries with meta_key '_address_1'.<br>";

// Proceed only if there are entries to update
if ( $entry_count > 0 ) {
    $updated_rows = $wpdb->query( $wpdb->prepare( 
        "UPDATE {$wpdb->usermeta} SET meta_key = %s WHERE meta_key = %s", 
        'address_1', 
        '_address_1' 
    ) );
    echo "Successfully updated {$updated_rows} entries!";
} else {
    echo "No entries found to update.";
}

3. Visit your-site.com/fix-meta-key.php in your browser to run the script and view results
4. Immediately delete this file from your server after it runs—leaving it could pose a security risk

Post-Update Checks #

After using either method:

• Verify user address data displays correctly (check user profiles, checkout pages, etc.)
• Scan your site for any errors or warnings
• If something goes wrong, restore your database backup right away

Hope this helps you get this sorted safely!

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