MySQL中含‘Cancer’的行无法删除？如何删除查询不显示的‘???’行？

Hey there, let's break down these two MySQL problems and get them sorted out for you:

1. Can't Delete Rows Containing 'Cancer' #

This usually boils down to either case sensitivity in your database collation or hidden special characters in the text. Here's how to fix it:

• First, verify exactly what's in your table to make sure you're targeting the right rows. Run this query to check (replace your_table and target_column with your actual table/column names):

  SELECT * FROM your_table WHERE target_column LIKE '%Cancer%';
  

  If no rows show up, try a case-insensitive check (useful if your collation is case-sensitive):

  SELECT * FROM your_table WHERE LOWER(target_column) LIKE '%cancer%';
  

  Or force a case-insensitive match with collation:

  SELECT * FROM your_table WHERE target_column LIKE '%Cancer%' COLLATE utf8mb4_general_ci;
  

• Once you confirm the rows exist, delete them using the same matching logic that worked in your SELECT query. For example:

  DELETE FROM your_table WHERE LOWER(target_column) LIKE '%cancer%';
  

  Pro tip: Always run the SELECT first to double-check which rows will be deleted—no one wants accidental data loss!

• If you still can't delete, check for foreign key constraints. If the table is linked to another table, you might need to either delete the related rows first or use ON DELETE CASCADE in your foreign key setup.

2. Deleting Rows That Show Up as '???' But Aren't Visible in Queries #

Those ??? are almost always a character set mismatch—your MySQL server is storing characters that your client can't display properly, so they show up as question marks. Here's how to track and delete them:

• First, expose the actual binary content of the problematic column to see what's really stored. Use the HEX() function to get the hexadecimal representation:

  SELECT target_column, HEX(target_column) FROM your_table WHERE target_column IS NOT NULL;
  

  Look for rows where the hex value doesn't correspond to normal readable text. For example, non-UTF8 characters might have hex codes that don't map to standard letters.

• Once you have the hex value of the ??? rows, use it to delete them:

  DELETE FROM your_table WHERE HEX(target_column) = 'YOUR_HEX_VALUE';
  

  Replace YOUR_HEX_VALUE with the hex string you found in the previous step.

• Alternatively, you can target rows with non-printable or invalid characters using a regular expression:

  DELETE FROM your_table WHERE target_column REGEXP '[^[:print:]]';
  

  This will delete any rows with characters that aren't printable (which often cause the ??? display issue).

• Another angle: Check your database and table character set. Run SHOW CREATE TABLE your_table; to see if the column is using a character set that matches your client's (like utf8mb4). Mismatches here are the root cause of most ??? display problems.

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