.NET生成多数据库有序Guid
在使用MySQL时发现MySQL无法像SQLServer一样修改聚集索引的位置，聚集索引的位置会优先在主键上。然而在MySQL使用.NET的Guid.NewGuid()生成的无序Guid作为主键对性能影响较大，因此需要使用有序的Guid来作为MySQL等不支持修改聚集索引数据库的主键。
由于将UuidCreateSequential生成的Data4中与设备相关的部分替换成与时间相关的，因此不适用于集群环境下，多台设备同时生成Guid，会有重复的风险，可以考虑在一台设备统一生成。
1 /// <summary> 2 /// Guid工具类 3 /// </summary> 4 public static class GuidUtils 5 { 6 #region 外部方法 7 /// <summary> 8 /// 生成有序Guid 9 /// </summary> 10 /// <param name="guid"></param> 11 /// <returns></returns> 12 [DllImport("rpcrt4.dll", SetLastError = true)] 13 private static extern int UuidCreateSequential(out Guid guid); 14 #endregion 15 16 #region 公有方法 17 /// <summary> 18 /// 生成SqlServer有序Guid 19 /// </summary> 20 /// <param name="guid"></param> 21 /// <returns></returns> 22 public static Guid NewSqlServerSequentialGuid() 23 { 24 return CreateSequentialGuid(SequentialGuidDatabaseType.SqlServer); 25 } 26 27 /// <summary> 28 /// 生成MySql有序Guid 29 /// </summary> 30 /// <param name="guid"></param> 31 /// <returns></returns> 32 public static Guid NewMySqlSequentialGuid() 33 { 34 return CreateSequentialGuid(SequentialGuidDatabaseType.MySql); 35 } 36 37 /// <summary> 38 /// 生成Oracle有序Guid 39 /// </summary> 40 /// <param name="guid"></param> 41 /// <returns></returns> 42 public static Guid NewOracleSequentialGuid() 43 { 44 return CreateSequentialGuid(SequentialGuidDatabaseType.Oracle); 45 } 46 47 /// <summary> 48 /// 生成PostgreSql有序Guid 49 /// </summary> 50 /// <param name="guid"></param> 51 /// <returns></returns> 52 public static Guid NewPostgreSqlSequentialGuid() 53 { 54 return CreateSequentialGuid(SequentialGuidDatabaseType.PostgreSql); 55 } 56 #endregion 57 58 #region 私有 59 /// <summary> 60 /// 根据数据库类型生成有序Guid 61 /// </summary> 62 /// <param name="databaseType">数据库类型</param> 63 /// <returns></returns> 64 private static Guid CreateSequentialGuid(SequentialGuidDatabaseType databaseType) 65 { 66 if (UuidCreateSequential(out Guid guid) != 0) 67 { 68 throw new System.ComponentModel.Win32Exception(Marshal.GetLastWin32Error()); 69 } 70 71 byte[] guidBytes = guid.ToByteArray(); 72 long timestamp = DateTime.UtcNow.Ticks / 10000L; 73 byte[] timestampBytes = BitConverter.GetBytes(timestamp); 74 //按需将timestampBytes Reverse 75 if (BitConverter.IsLittleEndian) 76 { 77 Array.Reverse(timestampBytes); 78 } 79 80 switch (databaseType) 81 { 82 case SequentialGuidDatabaseType.MySql: 83 case SequentialGuidDatabaseType.PostgreSql: 84 //时间放在最前 85 Buffer.BlockCopy(guidBytes, 0, guidBytes, 6, 10); 86 Buffer.BlockCopy(timestampBytes, 2, guidBytes, 0, 6); 87 //按需将Data1、Data2（时间）分开Reverse 88 if (BitConverter.IsLittleEndian) 89 { 90 Array.Reverse(guidBytes, 0, 4); 91 Array.Reverse(guidBytes, 4, 2); 92 } 93 break; 94 95 case SequentialGuidDatabaseType.Oracle: 96 //时间放在最前 97 Buffer.BlockCopy(guidBytes, 0, guidBytes, 6, 10); 98 Buffer.BlockCopy(timestampBytes, 2, guidBytes, 0, 6); 99 break; 100 101 case SequentialGuidDatabaseType.SqlServer: 102 //时间放在最后 103 Buffer.BlockCopy(timestampBytes, 2, guidBytes, 10, 6); 104 //将Data1、Data2、Data3分开Reverse以适应SqlServer的Guid排序 105 Array.Reverse(guidBytes, 0, 4); 106 Array.Reverse(guidBytes, 4, 2); 107 Array.Reverse(guidBytes, 6, 2); 108 break; 109 } 110 111 return new Guid(guidBytes); 112 } 113 114 /// <summary> 115 /// 需要生成有序Guid的数据库类型 116 /// </summary> 117 private enum SequentialGuidDatabaseType 118 { 119 SqlServer, 120 MySql, 121 Oracle, 122 PostgreSql, 123 } 124 #endregion 125 }
参考文章：https://www.cnblogs.com/tdfblog/p/SequentialGuid.html
http://www.cnblogs.com/wangzhanbo/p/8865562.html
https://github.com/aspnetboilerplate/aspnetboilerplate/blob/b36855f0c238c3592203f058c641862844a0614e/src/Abp/SequentialGuidGenerator.cs
https://docs.microsoft.com/zh-cn/windows/desktop/api/rpcdce/nf-rpcdce-uuidcreatesequential
https://msdn.microsoft.com/zh-cn/14288352-43c3-4e4d-a3f1-e924a8261d2b
