数据库表中每个记录的唯一字符串 [英] Unique string for each record in the database table
问题描述
在我的Asp.Net MVC 5项目中，我首先使用Entity Framework代码与MS SQL数据库一起使用.假设这是表:
In my Asp.Net MVC 5 project I use Entity Framework code first to work with MS SQL database. Suppose this is the table:
public class Ticket
{
[Key]
public int Id { get; set; }
[Required]
public string ReferenceCode { get; set; }
//Rest of the table
}
在此表中，每当我添加新代码时，我都希望 ReferenceCode 列为具有特定字符的唯一且随机的AlphaNumeric(仅包含字母和数字)字符串长度.例如，这将允许用户引用特定的票证.
In this table, whenever I add a new code I want the ReferenceCode column to be a unique and random AlphaNumeric (containing only letters and digits) string with a specific length. This will allow users to refer to a specific ticket for instance.
以下是长度为10个字符的示例: TK254X26W1 ， W2S564Z111 ， 1STT135PA5 ...
These are some examples with 10 character lenght: TK254X26W1, W2S564Z111, 1STT135PA5...
现在，我能够生成具有给定长度的随机字符串.但是，我不确定如何保证其唯一性.我是这样的:
Right now, I'm able to generate random strings with the given length. However, I'm not sure how to guarantee their uniqueness. I do it like this:
db.Tickets.Add(new Ticket()
{
ReferenceCode = GenerateRandomCode(10),
//...
});
确切地说，我希望使用 GenerateRandomCode 函数或其他方法来确保生成的字符串没有用于其他记录.
To be exact, I want the GenerateRandomCode function or maybe another method to be able to make sure the generated string has not been used for another record.
我可以使用 for 循环来检查每个生成的代码，但是我认为这不是一个好主意.尤其是一段时间后，该表将具有成千上万的记录.
I can use a for loop to check each generated code but I don't think it's a good idea. Especially after a while when the table will have thousands of records.
推荐答案
这是我保证唯一性并引入一些随机性的方法.
Here's my approach that guarantees uniqueness and introduces some randomness.
- 使用可确保提供唯一编号的序列生成器.由于您正在使用SQL Server，因此它可以是
IDENTITY列的值.您也可以在C#代码中增加应用程序级别的值以实现此目的. - 生成一个随机整数以使结果具有一定的随机性.可以使用
Random.Next()和任何种子(甚至是上一步中生成的数字)来完成. - 使用方法
EncodeInt32AsString将前两个步骤中的整数转换为两个字符串(一个是 unique字符串，一个是 random字符串).该方法返回仅由方法中指定的允许字符组成的字符串.此方法的逻辑类似于在不同基数之间进行数字转换的方式(例如，将允许的字符串更改为仅0-9或仅0-9A-F以获得小数/十六进制)表示形式).因此，结果是由allowedList中的数字"组成的数字". - 连接返回的字符串.保持整个唯一字符串不变(以确保唯一性)，并从 random string 中添加尽可能多的字符，以将总长度填充到所需的长度.如果需要，可以通过将随机字符串中随机点处的字符注入到唯一字符串中来实现这种串联.
- Use a sequence generator that is guaranteed to give a unique number. Since you're working with SQL Server, this can be an
IDENTITYcolumn's value. You could alternatively increment an application-level value within your C# code to achieve this. - Generate a random integer to bring in some randomness to the result. This could be done with
Random.Next()and any seed, even the number generated in the preceding step. - Use a method
EncodeInt32AsStringto convert the integers from the previous two steps into two strings (one is the unique string, one the random string). The method returns a string composed of only the allowed characters specified in the method. The logic of this method is similar to how number conversion between different bases takes place (for example, change the allowed string to only 0-9, or only 0-9A-F to get the decimal/hex representations). Therefore, the result is a "number" composed of the "digits" inallowedList. - Concatenate the strings returned. Keep the entire unique string as-is (to guarantee uniqueness) and add as many characters from the random string to pad the total length to the desired length. If required, this concatenation can be fancy, by injecting characters from the random string at random points into the unique string.
通过保留整个唯一字符串，可以确保最终结果的唯一性.通过使用随机字符串，这会引入随机性.如果目标字符串的长度非常接近唯一字符串的长度，则不能保证随机性.
By retaining the entire unique string, this ensures uniqueness of the final result. By using a random string, this introduces randomness. Randomness cannot be guaranteed in case the target string's length is very close to the length of the unique string.
在我的测试中，为 Int32.MaxValue 调用 EncodeInt32AsString 返回一个唯一的字符串，该字符串长6个字符:
In my testing, calling EncodeInt32AsString for Int32.MaxValue returns a unique string 6 characters long:
2147483647:ZIK0ZJ
2147483647: ZIK0ZJ
在此基础上，理想的目标字符串长度为12，尽管10也是合理的.
On that basis, a target string length of 12 will be ideal, though 10 is also reasonable.
EncodeInt32AsString 方法
The EncodeInt32AsString Method
/// <summary>
/// Encodes the 'input' parameter into a string of characters defined by the allowed list (0-9, A-Z)
/// </summary>
/// <param name="input">Integer that is to be encoded as a string</param>
/// <param name="maxLength">If zero, the string is returned as-is. If non-zero, the string is truncated to this length</param>
/// <returns></returns>
static String EncodeInt32AsString(Int32 input, Int32 maxLength = 0)
{
// List of characters allowed in the target string
Char[] allowedList = new Char[] {
'0', '1', '2', '3', '4', '5', '6', '7', '8', '9',
'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J',
'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T',
'U', 'V', 'W', 'X', 'Y', 'Z' };
Int32 allowedSize = allowedList.Length;
StringBuilder result = new StringBuilder(input.ToString().Length);
Int32 moduloResult;
while (input > 0)
{
moduloResult = input % allowedSize;
input /= allowedSize;
result.Insert(0, allowedList[moduloResult]);
}
if (maxLength > result.Length)
{
result.Insert(0, new String(allowedList[0], maxLength - result.Length));
}
if (maxLength > 0)
return result.ToString().Substring(0, maxLength);
else
return result.ToString();
}
GetRandomizedString 方法
The GetRandomizedString Method
现在，前面的方法只需要对字符串进行编码.为了获得唯一性和随机性，可以使用以下逻辑(或类似逻辑).
Now, the preceding method just takes care of encoding a string. In order to achieve the uniqueness and randomness properties, the following logic (or similar) can be used.
在评论中，凯文指出了 EncodeInt32AsString 方法的实现存在以下风险:
In the comments, Kevin pointed out the following risk with the implementation of the EncodeInt32AsString method:
需要对代码进行调整，以使其返回固定长度的字符串.否则，您将永远无法保证最终结果是唯一的.如果有帮助，请想象一个生成ABCDE(唯一)的值+F8CV1(Random)...然后再生成另一个值ABCDEF(唯一)+ 8CV1(随机).这两个值都是ABCDEF8CV1
The code needs to be tweaked so that it returns a fixed-length string. Otherwise, you can never be guaranteed of the final result is unique. If it helps, picture one value generating ABCDE (Unique) + F8CV1 (Random)... and then later on, another value generating ABCDEF (Unique) + 8CV1 (Random). Both values are ABCDEF8CV1
这是非常有效的一点，在下面的 GetRandomizedString 方法中，通过指定唯一字符串和随机字符串的长度，可以解决此问题.还对 EncodeInt32AsString 方法进行了修改，以将返回值填充到指定的长度.
This is a very valid point, and this has been addressed in the following GetRandomizedString method, by specifying lengths for the unique and random strings. The EncodeInt32AsString method has also been modified to pad out the return value to a specified length.
// Returns a string that is the encoded representation of the input number, and a random value
static String GetRandomizedString(Int32 input)
{
Int32 uniqueLength = 6; // Length of the unique string (based on the input)
Int32 randomLength = 4; // Length of the random string (based on the RNG)
String uniqueString;
String randomString;
StringBuilder resultString = new StringBuilder(uniqueLength + randomLength);
// This might not be the best way of seeding the RNG, so feel free to replace it with better alternatives.
// Here, the seed is based on the ratio of the current time and the input number. The ratio is flipped
// around (i.e. it is either M/N or N/M) to ensure an integer is returned.
// Casting an expression with Ticks (Long) to Int32 results in truncation, which is fine since this is
// only a seed for an RNG
Random randomizer = new Random(
(Int32)(
DateTime.Now.Ticks + (DateTime.Now.Ticks > input ? DateTime.Now.Ticks / (input + 1) : input / DateTime.Now.Ticks)
)
);
// Get a random number and encode it as a string, limit its length to 'randomLength'
randomString = EncodeInt32AsString(randomizer.Next(1, Int32.MaxValue), randomLength);
// Encode the input number and limit its length to 'uniqueLength'
uniqueString = EncodeInt32AsString(input, uniqueLength);
// For debugging/display purposes alone: show the 2 constituent parts
resultString.AppendFormat("{0}\t {1}\t ", uniqueString, randomString);
// Take successive characters from the unique and random strings and
// alternate them in the output
for (Int32 i = 0; i < Math.Min(uniqueLength, randomLength); i++)
{
resultString.AppendFormat("{0}{1}", uniqueString[i], randomString[i]);
}
resultString.Append((uniqueLength < randomLength ? randomString : uniqueString).Substring(Math.Min(uniqueLength, randomLength)));
return resultString.ToString();
}
示例输出
为各种输入值调用上述方法会导致:
Calling the above method for a variety of input values results in:
Input Int Unique String Random String Combined String
------------ ----------------- -------------- ---------------------
-10 000000 CRJM 0C0R0J0M00
0 000000 33VT 03030V0T00
1 000001 DEQK 0D0E0Q0K01
2147 0001NN 6IU8 060I0U18NN
21474 000GKI VNOA 0V0N0OGAKI
214748 004LP8 REVP 0R0E4VLPP8
2147483 01A10B RPUM 0R1PAU1M0B
21474836 0CSA38 RNL5 0RCNSLA538
214748364 3JUSWC EP3U 3EJPU3SUWC
2147483647 ZIK0ZJ BM2X ZBIMK20XZJ
1 000001 QTAF 0Q0T0A0F01
2 000002 GTDT 0G0T0D0T02
3 000003 YMEA 0Y0M0E0A03
4 000004 P2EK 0P020E0K04
5 000005 17CT 01070C0T05
6 000006 WH12 0W0H010206
7 000007 SHP0 0S0H0P0007
8 000008 DDNM 0D0D0N0M08
9 000009 192O 0109020O09
10 00000A KOLD 0K0O0L0D0A
11 00000B YUIN 0Y0U0I0N0B
12 00000C D8IO 0D080I0O0C
13 00000D KGB7 0K0G0B070D
14 00000E HROI 0H0R0O0I0E
15 00000F AGBT 0A0G0B0T0F
从上面可以看出，唯一字符串对于序号是可预测的，因为它只是在不同基数中表示的同一数字.但是，随机字符串引入了一些熵，以防止用户猜测后续数字.此外，通过交织唯一字符串和随机字符串的数字"，对于用户来说，观察任何模式都变得更加困难.
As can be seen above, the unique string is predictable for sequential numbers, given it is just the same number represented in a different base. However, the random string brings in some entropy to prevent users from guessing subsequent numbers. Moreover, by interleaving the "digits" of the unique string and random string it becomes slightly more difficult for users to observe any pattern.
在上面的示例中，唯一字符串的长度设置为6(因为它可以表示 Int32.MaxValue )，但是 random string 设置为4，因为OP希望总长度为10个字符.
In the above example, the length of the unique string is set to 6 (since that allows it to represent Int32.MaxValue), but the length of the random string is set to 4 because the OP wanted a total length of 10 characters.
这篇关于数据库表中每个记录的唯一字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
