問題描述
我正在編寫用于我們現有會員基礎的會員提供程序.我使用 EF4.1 進行所有數據庫訪問，我遇到的問題之一是最初設置數據庫時，關系是以編程方式完成的，而不是在數據庫中完成.如果需要在并非所有用戶都需要的列上建立關系，但為了建立關系確實需要是唯一的(根據我的理解).
I'm in the process of writing a Membership Provider for use with our existing membership base. I use EF4.1 for all of my database access and one of the issued that I'm running into is when the DB was originally setup the relationships were done programmatically instead of in the db. One if the relationships needs to be made on a column that isn't required for all of our users, but in order to make the relationships does need to be unique (from my understanding).
我認為可行的解決方案是在 userid 字段上執行 MD5 哈希(這是唯一的......這將/應該保證該字段中的唯一值).我在 sql server 上遇到問題的部分是在不替換存儲在 employeeNum 字段(有問題的那個)中的現有值的情況下執行此操作的查詢.
My solution that I believe will work is to do an MD5 hash on the userid field (which is unique ...which would/should guarantee a unique value in that field). The part that I'm having issues with on sql server is the query that would do this WITHOUT replacing the existing values stored in the employeeNum field (the one in question).
簡而言之，我的問題是.在值不存在的所有行的 employeeNum 字段(可能基于 userid 字段的 md5 哈希)中獲取唯一值的最佳方法是什么?t 已經存在.此外，在次要/主要程度上……這聽起來是個好計劃嗎?
So in a nutshell my question is. What is the best way to get a unique value in the employeeNum field (possibly based on an md5 hash of the userid field) on all the rows in which a value isn't already present. Also, to a minor/major extent...does this sound like a good plan?
推薦答案
如果您的問題只是如何為 userid 生成哈希值，您可以使用計算列以這種方式完成(或生成此值作為插入過程).我不清楚您是否了解 HASHBYTES 函數或當您說最佳"時您正在查看的其他標準.
If your question is just how to generate a hash value for userid, you can do it this way using a computed column (or generate this value as part of the insert process). It isn't clear to me whether you know about the HASHBYTES function or what other criteria you're looking at when you say "best."
DECLARE @foo TABLE
(
userid INT,
hash1 AS HASHBYTES('MD5', CONVERT(VARCHAR(12), userid)),
hash2 AS HASHBYTES('SHA1', CONVERT(VARCHAR(12), userid))
);
INSERT @foo(userid) SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 500;
SELECT userid, hash1, hash2 FROM @foo;
結果:
userid hash1 hash2
------ ---------------------------------- ------------------------------------------
1 0xC4CA4238A0B923820DCC509A6F75849B 0x356A192B7913B04C54574D18C28D46E6395428AB
2 0xC81E728D9D4C2F636F067F89CC14862C 0xDA4B9237BACCCDF19C0760CAB7AEC4A8359010B0
500 0xCEE631121C2EC9232F3A2F028AD5C89B 0xF83A383C0FA81F295D057F8F5ED0BA4610947817
在 SQL Server 2012 中，我強烈建議至少使用 SHA2_256，而不是上述任何一種.(您忘記提及您使用的版本 - 總是有用的信息.)
In SQL Server 2012, I highly recommend at least SHA2_256 instead of either of the above. (You forgot to mention what version you're using - always useful information.)
說了這么多，我仍然想提請注意我在評論中提出的觀點:這里的最佳"解決方案是修復模型.如果 employeeNum 是可選的，則不應讓 EF 認為它是必需的或唯一的，如果它實際上不是某種標識符，則不應在關系中使用它.如果您首先為關系使用正確的屬性，為什么用戶會關心 employeeNum 和 userid 之間的沖突?
All that said, I still want to call attention to the point I made in the comments: the "best" solution here is to fix the model. If employeeNum is optional, EF shouldn't be made to think it is required or unique, and it shouldn't be used in relationships if it is not, in fact, some kind of identifier. Why would a user care about collisions between employeeNum and userid if you're using the right attribute for the relationship in the first place?
EDIT 按照 OP 的要求
那么說UPDATE table SET EmployeeNum = 1000000 + UserID WHERE EmployeeNum IS NULL 有什么問題?如果 EmployeeNum 將保持在 1000000 以下，那么你就保證沒有沖突并且你完全避免了散列.
So what is wrong with saying UPDATE table SET EmployeeNum = 1000000 + UserID WHERE EmployeeNum IS NULL? If EmployeeNum will stay below 1000000 then you've guaranteed no collisions and you've avoided hashing altogether.
如果 employeeNum 可能包含一個字符串，您可以生成類似的填充，但又是 EF 促進了這些可怕的列名嗎?為什么帶有 Num 后綴的列除了數字之外不包含任何內容?
You could generate similar padding if employeeNum might contain a string, but again is it EF that promotes these horrible column names? Why would a column with a Num suffix contain anything but a number?
這篇關于為 SQL Server 中的字段生成唯一哈希的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！