SQL利用Function创建长整形的唯一ID示例代码
<p><span><strong>前言</strong></span></p>
<p>
在设计表的时候考虑主键的数据类型是长整形还是字符串，最简单的方式当然是newid()，但这也有个问题，就是主键长度过长(36个字)，数据量一多，必然会影响数据库操作的效率，而且大大增加了数据文件和索引文件所占用的空间。而且，newid返回的字符串是随机的，查询结果不能保证按保存顺序返回。这对于有顺序要求的系统来说，需要额外增加顺序列来进行排序，这也导致查询语句更加复杂。这也是主要放弃newid作为主键的主要原因。因此考虑用长整形来作数据表主键的数据类型。</p>
<p>
<span><strong>实现方法</strong></span></p>
<p>
一开始在C#等面向对像语言中编写一个获取PK的方法，那是很顺序就完成了。</p>
<p>
接着是SQL中，如果要用脚本导入数据，那就要提供一个SQL的方法来获取PK。</p>
<p>
最初设计PK的组成：时间(yyMMddHHmmssmsS) + '4位随机数' ，于是卡卡很快完成<code>dbo.pk()</code></p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_14417">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">Create</code> <code class="sql keyword">function</code> <code class="sql plain">dbo.pk()</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">returns</code> <code class="sql keyword">bigint</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">as</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql keyword">begin</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces"> </code><code class="sql keyword">declare</code> <code class="sql plain">@pk </code><code class="sql keyword">as</code> <code class="sql keyword">bigint</code><code class="sql plain">,@fix </code><code class="sql keyword">bigint</code><code class="sql plain">,@idx </code><code class="sql keyword">int</code><code class="sql plain">,@ts </code><code class="sql keyword">as</code> <code class="sql plain">datetime</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql spaces"> </code><code class="sql keyword">set</code> <code class="sql plain">@ts = GETDATE()</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql spaces"> </code><code class="sql keyword">set</code> <code class="sql plain">@pk = </code><code class="sql color2">convert</code><code class="sql plain">(</code><code class="sql keyword">bigint</code><code class="sql plain">,</code><code class="sql color2">convert</code><code class="sql plain">(</code><code class="sql keyword">varchar</code><code class="sql plain">(6),@ts,12) + </code><code class="sql color2">replace</code><code class="sql plain">(</code><code class="sql color2">convert</code><code class="sql plain">(</code><code class="sql keyword">varchar</code><code class="sql plain">(12),@ts,114),</code><code class="sql string">':'</code><code class="sql plain">,</code><code class="sql string">''</code><code class="sql plain">))*10000</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql spaces"> </code><code class="sql keyword">select</code> <code class="sql plain">@idx = A*10000</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql spaces"> </code><code class="sql keyword">from</code> <code class="sql plain">vRand</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql spaces"> </code><code class="sql keyword">return</code> <code class="sql plain">(@pk + @idx)</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql keyword">end</code>
</div>
<div class="line number12 index11 alt1">
<code class="sql plain">go</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
然后来获取一个10000PK测试：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_996872">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">declare</code> <code class="sql plain">@tab </code><code class="sql keyword">as</code> <code class="sql keyword">table</code><code class="sql plain">(pk </code><code class="sql keyword">bigint</code><code class="sql plain">)</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">declare</code> <code class="sql plain">@i </code><code class="sql keyword">as</code> <code class="sql keyword">integer</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">set</code> <code class="sql plain">@i =0</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql plain">while(@i<10000)</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql keyword">begin</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql keyword">insert</code> <code class="sql plain">@tab</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql keyword">select</code> <code class="sql plain">dbo.pk() </code>
</div>
<div class="line number8 index7 alt1">
<code class="sql keyword">set</code> <code class="sql plain">@i = @i+1</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql keyword">end</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql keyword">select</code> <code class="sql plain">pk,</code><code class="sql color2">count</code><code class="sql plain">(1) cnt</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql keyword">from</code> <code class="sql plain">@tab</code>
</div>
<div class="line number12 index11 alt1">
<code class="sql spaces"> </code><code class="sql keyword">group</code> <code class="sql keyword">by</code> <code class="sql plain">pk</code>
</div>
<div class="line number13 index12 alt2">
<code class="sql spaces"> </code><code class="sql keyword">having</code> <code class="sql color2">COUNT</code><code class="sql plain">(1)>1</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
oh my god!竟然有30多个重复的。</p>
<p>
可见这个方法，做为获取单个PK，那问题不大，但在做批量保存的时候，可能会发生主键冲突。</p>
<p>
因此再设计一个支持批量保存的。</p>
<p>
既然4位随机数不能保证毫秒级的唯一，那就只能用有序数了，把PK的组成改为：时间(yyMMddHHmmssmsS) + '4位有序数'</p>
<p>
再考虑到年份只是2位数，跟面向对像中的PK组成有机会在202x年之后存在冲突，因此增加一个标识 ‘1'+yy作为年以延长千年虫问题，虽然还是有机会发生冲突，但那也是几百年以后的事情了。</p>
<p>
但是为了保持效率和冲突的概率，还是将PK改为：'1'+时间(yyMMddHHmmssms) + '4位有序数'.</p>
<p>
接下来又是一顿卡卡卡，dbo.pks(@count)已出：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_278620">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
<div class="line number17 index16 alt2">
17</div>
<div class="line number18 index17 alt1">
18</div>
<div class="line number19 index18 alt2">
19</div>
<div class="line number20 index19 alt1">
20</div>
<div class="line number21 index20 alt2">
21</div>
<div class="line number22 index21 alt1">
22</div>
<div class="line number23 index22 alt2">
23</div>
<div class="line number24 index23 alt1">
24</div>
<div class="line number25 index24 alt2">
25</div>
<div class="line number26 index25 alt1">
26</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">CREATE</code> <code class="sql keyword">function</code> <code class="sql plain">dbo.pks(@</code><code class="sql color2">count</code> <code class="sql keyword">as</code> <code class="sql keyword">int</code><code class="sql plain">)</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">returns</code> <code class="sql plain">@pks </code><code class="sql keyword">table</code><code class="sql plain">(pk </code><code class="sql keyword">bigint</code><code class="sql plain">,id </code><code class="sql keyword">int</code><code class="sql plain">)</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">as</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql keyword">begin</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces"> </code><code class="sql keyword">declare</code> <code class="sql plain">@pk </code><code class="sql keyword">as</code> <code class="sql keyword">bigint</code><code class="sql plain">,@fix </code><code class="sql keyword">bigint</code><code class="sql plain">,@idx </code><code class="sql keyword">int</code><code class="sql plain">,@ts </code><code class="sql keyword">as</code> <code class="sql plain">datetime,@lop </code><code class="sql keyword">int</code><code class="sql plain">,@i </code><code class="sql keyword">int</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql spaces"> </code><code class="sql keyword">set</code> <code class="sql plain">@ts = GETDATE()</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql spaces"> </code><code class="sql keyword">set</code> <code class="sql plain">@pk = </code><code class="sql color2">convert</code><code class="sql plain">(</code><code class="sql keyword">bigint</code><code class="sql plain">,</code><code class="sql string">'1'</code><code class="sql plain">+</code><code class="sql color2">convert</code><code class="sql plain">(</code><code class="sql keyword">varchar</code><code class="sql plain">(6),@ts,12) + </code><code class="sql color2">replace</code><code class="sql plain">(</code><code class="sql color2">convert</code><code class="sql plain">(</code><code class="sql keyword">varchar</code><code class="sql plain">(11),@ts,114),</code><code class="sql string">':'</code><code class="sql plain">,</code><code class="sql string">''</code><code class="sql plain">))*10000</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql spaces"> </code><code class="sql keyword">set</code> <code class="sql plain">@idx =0</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql spaces"> </code><code class="sql keyword">set</code> <code class="sql plain">@lop = CEILING(@</code><code class="sql color2">count</code><code class="sql plain">/10000.0) </code>
</div>
<div class="line number10 index9 alt1">
<code class="sql spaces"> </code><code class="sql keyword">set</code> <code class="sql plain">@i = 1</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql spaces"> </code><code class="sql plain">while(@lop >0)</code>
</div>
<div class="line number12 index11 alt1">
<code class="sql spaces"> </code><code class="sql keyword">begin</code>
</div>
<div class="line number13 index12 alt2">
<code class="sql spaces"> </code><code class="sql keyword">set</code> <code class="sql plain">@pk = @pk + 10000</code>
</div>
<div class="line number14 index13 alt1">
<code class="sql spaces"> </code><code class="sql keyword">set</code> <code class="sql plain">@idx = 0</code>
</div>
<div class="line number15 index14 alt2">
<code class="sql spaces"> </code><code class="sql plain">while(@idx<10000 </code><code class="sql color1">and</code> <code class="sql plain">@idx<@</code><code class="sql color2">count</code><code class="sql plain">)</code>
</div>
<div class="line number16 index15 alt1">
<code class="sql spaces"> </code><code class="sql keyword">begin</code>
</div>
<div class="line number17 index16 alt2">
<code class="sql spaces"> </code><code class="sql keyword">insert</code> <code class="sql plain">@pks(pk,id)</code>
</div>
<div class="line number18 index17 alt1">
<code class="sql spaces"> </code><code class="sql keyword">values</code><code class="sql plain">(@pk+@idx,@idx+ @i)</code>
</div>
<div class="line number19 index18 alt2">
<code class="sql spaces"> </code><code class="sql keyword">set</code> <code class="sql plain">@idx = @idx +1</code>
</div>
<div class="line number20 index19 alt1">
<code class="sql spaces"> </code><code class="sql keyword">end</code>
</div>
<div class="line number21 index20 alt2">
<code class="sql spaces"> </code><code class="sql keyword">set</code> <code class="sql plain">@lop = @lop -1</code>
</div>
<div class="line number22 index21 alt1">
<code class="sql spaces"> </code><code class="sql keyword">set</code> <code class="sql plain">@i = @i+10000</code>
</div>
<div class="line number23 index22 alt2">
<code class="sql spaces"> </code><code class="sql keyword">end</code>
</div>
<div class="line number24 index23 alt1">
<code class="sql spaces"> </code><code class="sql keyword">return</code>
</div>
<div class="line number25 index24 alt2">
<code class="sql keyword">end</code>
</div>
<div class="line number26 index25 alt1">
<code class="sql plain">go</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
批量测试一下</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_150697">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">dbo.pks(500000)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
正常返回500000行，没有一行重复！</p>
<p>
在返回的结果列中，ID是从1开始编号的，这也保持与SQL的Row_number保持一致，方便SQL编程引用。</p>
<p>
OK，到这里用利用SQL function获取PK就搞定了！</p>
<p>
<span><strong>总结</strong></span></p>
<p>
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对的支持。</p>
<p>
原文链接：https://www.cnblogs.com/adamvv/p/9289993.html</p>
頁:
[1]