久久久久久久av_日韩在线中文_看一级毛片视频_日本精品二区_成人深夜福利视频_武道仙尊动漫在线观看

為 SQL Server 中的字段生成唯一哈希

Generate Unique hash for a field in SQL Server(為 SQL Server 中的字段生成唯一哈希)
本文介紹了為 SQL Server 中的字段生成唯一哈希的處理方法,對(duì)大家解決問(wèn)題具有一定的參考價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)吧!

問(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)心 employeeNumuserid 之間的沖突?

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

【網(wǎng)站聲明】本站部分內(nèi)容來(lái)源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問(wèn)題,如果有圖片或者內(nèi)容侵犯了您的權(quán)益,請(qǐng)聯(lián)系我們刪除處理,感謝您的支持!

相關(guān)文檔推薦

Converting Every Child Tags in to a Single Column with multiple Delimiters -SQL Server (3)(將每個(gè)子標(biāo)記轉(zhuǎn)換為具有多個(gè)分隔符的單列-SQL Server (3))
How can I create a view from more than one table?(如何從多個(gè)表創(chuàng)建視圖?)
Create calculated value based on calculated value inside previous row(根據(jù)前一行內(nèi)的計(jì)算值創(chuàng)建計(jì)算值)
How do I stack the first two columns of a table into a single column, but also pair third column with the first column only?(如何將表格的前兩列堆疊成一列,但也僅將第三列與第一列配對(duì)?) - IT屋-程序員軟件開(kāi)發(fā)技
Recursive t-sql query(遞歸 t-sql 查詢(xún))
Convert Month Name to Date / Month Number (Combinations of Questions amp; Answers)(將月份名稱(chēng)轉(zhuǎn)換為日期/月份編號(hào)(問(wèn)題和答案的組合))
主站蜘蛛池模板: 欧美二区在线 | 欧美日韩国产欧美 | 亚洲二区视频 | 成人不卡视频 | 亚洲成人综合网站 | 日韩国产一区二区三区 | 麻豆av在线| 麻豆一区二区三区 | 成人在线观看网站 | 久久久久国产成人精品亚洲午夜 | 亚洲日本一区二区三区四区 | 国产色网| 一级片视频免费 | 草草精品| 狠狠操电影 | 色视频www在线播放国产人成 | 久久久国产一区二区三区 | 一级黄色片美国 | www.久久久.com | 亚洲小说图片 | 久草久草久草 | 在线亚洲一区 | 欧洲av在线 | 亚洲成人999 | aacc678成免费人电影网站 | 亚洲高清在线免费观看 | 国产精品69毛片高清亚洲 | 日本人麻豆 | 91中文在线观看 | 噜噜噜色网 | 国产成人一区二区 | 国产精品一区二区三区在线 | 日韩成人中文字幕 | 免费成人av网站 | 久久亚洲一区 | 国产午夜高清 | 国产精品久久久久久久久久久免费看 | 精品欧美乱码久久久久久 | 亚州成人 | 欧美成人一区二免费视频软件 | 日韩福利在线 |