如何在SQL Server中为竖线分隔文件生成MD5哈希值

Hey there! Since you’ve already got the hang of generating MD5 hashes for strings with HASHBYTES in SQL Server, let’s break down how to do the same for the actual content of a text file.

First, you need to get the file's content into SQL Server—here are two reliable methods to do that, along with generating the hash:

Option 1: Directly Read and Hash the File #

If the text file is accessible to the SQL Server service account (double-check those read permissions!), you can use OPENROWSET to pull in the content and hash it in one go:

DECLARE @FileContent VARCHAR(MAX);
DECLARE @MD5HASH VARCHAR(32);

-- Load the entire text file content into a variable
SELECT @FileContent = BulkColumn
FROM OPENROWSET(BULK 'C:\Your\File\Path\example.txt', SINGLE_CLOB) AS FileData;

-- Generate the MD5 hash (same syntax you already use, but now for file content)
SET @MD5HASH = CONVERT(VARCHAR(32), HashBytes('MD5', @FileContent), 2);

-- View the result
SELECT @MD5HASH AS FileContentMD5;

Key Notes for This Method: #

• Replace 'C:\Your\File\Path\example.txt' with your file's full absolute path.
• SINGLE_CLOB works for standard text files; use SINGLE_BLOB if you're dealing with binary files, or SINGLE_NCLOB for Unicode/UTF-8 text to preserve encoding.
• Ensure the SQL Server service account has read access to the file's directory—this is a common gotcha!

Option 2: Store Content in a Table First (Good for Multiple Files) #

If you need to process several files or want to keep the content stored temporarily, insert it into a table before generating the hash:

-- Create a temporary table to hold file content
CREATE TABLE #TempFileContents (
    FileID INT IDENTITY(1,1) PRIMARY KEY,
    FileContent VARCHAR(MAX)
);

-- Insert the text file content into the temp table
INSERT INTO #TempFileContents (FileContent)
SELECT BulkColumn
FROM OPENROWSET(BULK 'C:\Your\File\Path\example.txt', SINGLE_CLOB) AS FileData;

-- Generate the MD5 hash from the stored content
DECLARE @MD5HASH VARCHAR(32);
SET @MD5HASH = CONVERT(VARCHAR(32), HashBytes('MD5', (SELECT FileContent FROM #TempFileContents WHERE FileID = 1)), 2);

-- Output the hash
SELECT @MD5HASH AS FileContentMD5;

-- Clean up the temp table
DROP TABLE #TempFileContents;

Critical Reminders #

• If your text file is larger than 8000 bytes, always use VARCHAR(MAX) or NVARCHAR(MAX) to avoid truncating the content (which would give you an incorrect hash).
• For UTF-8 encoded files, SINGLE_NCLOB ensures you read the content correctly—using SINGLE_CLOB might mangle special characters and produce a mismatched hash compared to tools like certutil or md5sum.

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