技术求助：上传图片与数据库图片二进制比对代码失效

Hey there! Let’s work through this image matching problem you’re stuck on—binary comparison sounds straightforward, but it’s easy to hit snags with file formats, storage quirks, or logic gaps. I’ve dealt with similar issues before, so let’s break this down step by step.

1. File Format/Metadata Discrepancies #

The biggest culprit here is usually subtle differences between your uploaded image and the one stored in the database. Even the same visual image can have different binary data if:

• One is saved as JPG and the other as PNG (or has an alpha channel vs. no alpha)
• There’s extra EXIF metadata (like camera settings) attached to one version
• The image was re-compressed slightly when stored

Fix: Normalize images before comparison
Standardize the format, remove metadata, and ensure consistent encoding before converting to binary. Here’s a quick Python example using PIL/Pillow:

from PIL import Image
import io

def normalize_image(image_file):
    # Open the image, convert to RGB (removes alpha channel if present)
    img = Image.open(image_file).convert('RGB')
    # Save to an in-memory buffer as JPG, stripping EXIF data
    buffer = io.BytesIO()
    img.save(buffer, format='JPEG', exif=None)
    # Return the raw binary data
    return buffer.getvalue()

Use this function on both your uploaded image and the images you pull from the database before comparing.

2. Database Storage Mismatches #

If your binary data is being altered during storage or retrieval, comparisons will fail every time. Common issues here:

• Using the wrong database column type (e.g., TEXT instead of BLOB/LONGBLOB in MySQL, or VARCHAR instead of BYTEA in PostgreSQL)
• Accidentally encoding the binary data as Base64 before storing, but forgetting to decode it when retrieving
• Byte order inconsistencies (rare, but possible with some drivers)

Fix: Validate storage/retrieval logic

• Double-check your database column type to ensure it’s designed for raw binary data.
• Store the normalized binary directly, without extra encoding. Here’s a quick SQLAlchemy example for reference:

from sqlalchemy import Column, LargeBinary, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class ImageRecord(Base):
    __tablename__ = 'images'
    id = Column(String, primary_key=True)
    image_binary = Column(LargeBinary)  # Correct type for raw binary
    associated_data = Column(String)  # Your linked data (e.g., descriptions, IDs)

When retrieving records, make sure you’re pulling the raw image_binary value without any extra processing.

3. Flawed Comparison Logic #

Sometimes the issue is just a simple bug in how you’re comparing the binary data. For example:

• Comparing strings instead of raw byte objects
• Forgetting to normalize the uploaded image before comparing
• Querying the database incorrectly (e.g., fetching partial binary data)

Fix: Simplify your comparison code
Once you’ve normalized both the uploaded image and the database images, a direct equality check should work. Here’s a sample comparison function:

def find_matching_record(uploaded_binary, db_session):
    # Fetch all image records from the database
    all_records = db_session.query(ImageRecord).all()
    
    for record in all_records:
        # Compare normalized binary data directly
        if record.image_binary == uploaded_binary:
            return record  # Return the match with associated data
    return None  # No match found

If you’re still stuck, run through these checks to narrow down the problem:

• Print the length of the uploaded binary and a database record’s binary—if they’re different, your normalization/storage is off.
• Use a hex viewer to compare the first 10-20 bytes of both binaries—this will tell you if the formats are mismatched (e.g., one starts with FF D8 for JPG, the other with 89 50 for PNG).
• Test with a control: Upload an image, save it to the database, then immediately re-upload the same file. If it doesn’t match, your storage/retrieval logic is broken.

Binary matching is extremely rigid—even a single pixel change or compression tweak will break the match. If you need more flexibility (e.g., matching similar images, not exact duplicates), consider using image hashing (like dHash or pHash):

1. Compute a compact hash for each image when storing it in the database.
2. Compute the hash of the uploaded image, then compare it to stored hashes using Hamming distance (small distance = similar images).

Here’s a quick dHash implementation:

def dhash(image, hash_size=8):
    # Resize to a small grayscale image
    img = Image.open(image).convert('L').resize((hash_size + 1, hash_size), Image.Resampling.LANCZOS)
    # Calculate differences between adjacent pixels
    diff = []
    for row in range(hash_size):
        for col in range(hash_size):
            left_pixel = img.getpixel((col, row))
            right_pixel = img.getpixel((col + 1, row))
            diff.append(left_pixel > right_pixel)
    # Convert differences to a string hash
    return ''.join(['1' if val else '0' for val in diff])

def hamming_distance(hash1, hash2):
    # Count differing bits between two hashes
    return sum(c1 != c2 for c1, c2 in zip(hash1, hash2))

Store the dHash string in your database, then compare uploaded hashes—if the Hamming distance is ≤5, the images are likely a match.

内容的提问来源于stack exchange，提问作者Mustafa Mohammed Alsalamy