如何在phpMyAdmin SQL中删除adminID为0的重复记录？

Alright, let’s tackle those duplicate adminID entries (specifically the two with adminID=0) that you can’t delete through your web panel. Here’s a safe, step-by-step approach using phpMyAdmin’s SQL console:

First: Identify the Duplicate Records #

Before deleting anything, let’s confirm exactly which records we’re dealing with. Replace your_table_name with the actual name of your table, then run this query in phpMyAdmin’s SQL tab:

SELECT * FROM your_table_name WHERE adminID = 0;

This will show you both adminID=0 records. Look for a unique field that distinguishes them—this could be an auto-increment primary key like id, a creation timestamp created_at, or any other column that’s different between the two rows.

Option 1: Delete by Unique Primary Key (Most Reliable) #

If your table has a primary key (like id), use it to target the exact record you want to remove. For example, if one record has id=7 and the other id=14, run:

DELETE FROM your_table_name WHERE adminID = 0 AND id = 14; -- Replace 14 with the ID of the record you want to delete

This ensures only the duplicate you specify gets removed, leaving the other adminID=0 record intact.

Option 2: Delete Using a Timestamp or Other Unique Field #

If there’s no primary key, use a field like created_at to pick which record to keep. For example, to keep the oldest record and delete the newer one:

DELETE FROM your_table_name 
WHERE adminID = 0 
AND created_at = (SELECT MAX(created_at) FROM your_table_name WHERE adminID = 0);

To do the opposite (keep the newest, delete the oldest), swap MAX with MIN:

DELETE FROM your_table_name 
WHERE adminID = 0 
AND created_at = (SELECT MIN(created_at) FROM your_table_name WHERE adminID = 0);

Pro Tip: Prevent Future Duplicates #

Once you’ve cleaned up the duplicates, take steps to stop this from happening again:

• Add a unique constraint to adminID (if your business logic allows adminIDs to be unique):

  ALTER TABLE your_table_name ADD UNIQUE (adminID);
  

• If you want adminID to auto-increment going forward (make sure it’s an integer column first):

  ALTER TABLE your_table_name MODIFY COLUMN adminID INT AUTO_INCREMENT PRIMARY KEY;
  

Important Note: Always back up your table data before running DELETE queries, or run a SELECT first to double-check which records will be removed—better safe than sorry!

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