Swift中从SQLite获取BLOB存储特定图片对应ID的问题求助

Hey there! Let's figure out how to get the ID of a specific image stored as a BLOB in your SQLite table, and troubleshoot why your current code isn't working. I'll walk through this step by step, with practical code examples and common pitfalls to watch out for.

Core Idea #

Since your table only has id and image (BLOB) columns, the goal is straightforward:

1. Convert your target image into the exact Data format you used when storing it in the database
2. Run a SQL query that matches this Data against the image column, then fetch the corresponding id

Step-by-Step Implementation & Troubleshooting #

1. Match the Image-to-Data Conversion Format #

This is the #1 reason matching fails! If you stored the image using UIImage.pngData(), you must convert your target image to Data the same way. If you used jpegData(compressionQuality:), stick with that for the query too—even the same image will produce different Data if you switch formats.

Example code to convert your target image correctly:

guard let targetImageData = yourTargetImage.pngData() else {
    print("Failed to convert target image to PNG data")
    return
}

2. Write a Safe SQL Query #

Never hardcode Data into your SQL string—use parameter binding (? placeholder) to avoid syntax errors and SQL injection. Your query should look like this:

SELECT id FROM your_table_name WHERE image = ?

Replace your_table_name with the actual name of your table (e.g., images).

3. Execute the Query (Using FMDB as an Example) #

Most Swift developers use FMDB (a wrapper around SQLite's C API) for easier database interactions. Here's a complete function to fetch the image ID:

import FMDB

func fetchImageID(for targetImage: UIImage) -> Int? {
    // Update this path to match your database location (e.g., Documents folder)
    guard let dbPath = Bundle.main.path(forResource: "YourDatabase", ofType: "sqlite"),
          let targetData = targetImage.pngData() else {
        return nil
    }

    let db = FMDatabase(path: dbPath)
    guard db.open() else {
        print("Failed to open database: \(db.lastError()?.localizedDescription ?? "Unknown error")")
        return nil
    }

    // Make sure to close the database when done
    defer { db.close() }

    let query = "SELECT id FROM images WHERE image = ?"
    guard let resultSet = db.executeQuery(query, withArgumentsIn: [targetData]) else {
        print("Query failed: \(db.lastError()?.localizedDescription ?? "Unknown error")")
        return nil
    }

    var matchingID: Int?
    if resultSet.next() {
        // Extract the id from the result set
        matchingID = Int(resultSet.int(forColumn: "id"))
    }

    return matchingID
}

4. Fix Common Issues with Your Code #

If your current code isn't working, check these things:

• Wrong database path: If your database is stored in the app's Documents folder (not the main bundle), you need to construct the path correctly instead of using Bundle.main.
• Unmatched Data format: Double-check that you're using the same image-to-Data method for storage and querying.
• Missing parameter binding: If you're concatenating Data directly into your SQL string, that's a syntax error—always use the ? placeholder and bind the Data as an argument.
• Image discrepancies: If the target image has even tiny differences (e.g., resized, compressed differently) from the stored one, their Data will not match. For this case, consider adding an image_hash column to store a SHA-256 hash of the image Data—querying by hash is faster and more reliable for near-identical images.

Optimization Tip #

Matching large BLOBs can be slow. For better performance, add an image_hash column to your table:

1. When storing an image, compute its SHA-256 hash and save it alongside the BLOB
2. When querying, compute the hash of your target image and search for that hash instead of the full BLOB

This reduces the amount of data SQLite needs to compare, making queries faster.

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