.NET生成多数据库有序Guid

244 0 0

作者:Heris

在使用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     }