問(wèn)題描述

我正在編寫(xiě)用于我們現(xiàn)有會(huì)員基礎(chǔ)的會(huì)員提供程序.我使用 EF4.1 進(jìn)行所有數(shù)據(jù)庫(kù)訪(fǎng)問(wèn)，我遇到的問(wèn)題之一是最初設(shè)置數(shù)據(jù)庫(kù)時(shí)，關(guān)系是以編程方式完成的，而不是在數(shù)據(jù)庫(kù)中完成.如果需要在并非所有用戶(hù)都需要的列上建立關(guān)系，但為了建立關(guān)系確實(shí)需要是唯一的(根據(jù)我的理解).

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).

我認(rèn)為可行的解決方案是在 userid 字段上執(zhí)行 MD5 哈希(這是唯一的......這將/應(yīng)該保證該字段中的唯一值).我在 sql server 上遇到問(wèn)題的部分是在不替換存儲(chǔ)在 employeeNum 字段(有問(wèn)題的那個(gè))中的現(xiàn)有值的情況下執(zhí)行此操作的查詢(xún).

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).

簡(jiǎn)而言之，我的問(wèn)題是.在值不存在的所有行的 employeeNum 字段(可能基于 userid 字段的 md5 哈希)中獲取唯一值的最佳方法是什么?t 已經(jīng)存在.此外，在次要/主要程度上……這聽(tīng)起來(lái)是個(gè)好計(jì)劃嗎?

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?

推薦答案

如果您的問(wèn)題只是如何為 userid 生成哈希值，您可以使用計(jì)算列以這種方式完成(或生成此值作為插入過(guò)程).我不清楚您是否了解 HASHBYTES 函數(shù)或當(dāng)您說(shuō)最佳"時(shí)您正在查看的其他標(biāo)準(zhǔn).

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;

結(jié)果:

userid  hash1                               hash2
------  ----------------------------------  ------------------------------------------
1       0xC4CA4238A0B923820DCC509A6F75849B  0x356A192B7913B04C54574D18C28D46E6395428AB
2       0xC81E728D9D4C2F636F067F89CC14862C  0xDA4B9237BACCCDF19C0760CAB7AEC4A8359010B0
500     0xCEE631121C2EC9232F3A2F028AD5C89B  0xF83A383C0FA81F295D057F8F5ED0BA4610947817

在 SQL Server 2012 中，我強(qiáng)烈建議至少使用 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.)

說(shuō)了這么多，我仍然想提請(qǐng)注意我在評(píng)論中提出的觀點(diǎn):這里的最佳"解決方案是修復(fù)模型.如果 employeeNum 是可選的，則不應(yīng)讓 EF 認(rèn)為它是必需的或唯一的，如果它實(shí)際上不是某種標(biāo)識(shí)符，則不應(yīng)在關(guān)系中使用它.如果您首先為關(guān)系使用正確的屬性，為什么用戶(hù)會(huì)關(guān)心 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 的要求

那么說(shuō)UPDATE table SET EmployeeNum = 1000000 + UserID WHERE EmployeeNum IS NULL 有什么問(wèn)題?如果 EmployeeNum 將保持在 1000000 以下，那么你就保證沒(méi)有沖突并且你完全避免了散列.

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 可能包含一個(gè)字符串，您可以生成類(lèi)似的填充，但又是 EF 促進(jìn)了這些可怕的列名嗎?為什么帶有 Num 后綴的列除了數(shù)字之外不包含任何內(nèi)容?

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?

這篇關(guān)于為 SQL Server 中的字段生成唯一哈希的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！