保证随机插入

小编典典

保证随机插入

sql

我正在尝试预生成一些字母数字字符串并将结果插入表中。字符串的长度为5。例如：a5r67。基本上，我想为客户生成一些可读的字符串，以便他们可以像那样访问他们的订单
www.example.com/order/a5r67。现在我有一条选择语句：

;WITH 
    cte1 AS(SELECT * FROM (VALUES('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')) AS v(t)),
    cte2 AS(SELECT * FROM (VALUES('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')) AS v(t)),
    cte3 AS(SELECT * FROM (VALUES('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')) AS v(t)),
    cte4 AS(SELECT * FROM (VALUES('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')) AS v(t)),
    cte5 AS(SELECT * FROM (VALUES('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')) AS v(t))
INSERT INTO ProductHandles(ID, Used)
SELECT cte1.t + cte2.t + cte3.t + cte4.t + cte5.t, 0
FROM cte1
CROSS JOIN cte2
CROSS JOIN cte3
CROSS JOIN cte4
CROSS JOIN cte5

现在的问题是我需要编写类似这样的东西来从表中获取一个值：

SELECT TOP 1 ID 
FROM ProductHandles
WHERE Used = 0

我将在该Used列上建立索引，因此速度会很快。问题在于它是随订单一起提供的：

我知道我可以按订购NEWID()，但这会慢很多。我知道除非我们指定Order By条款，否则不能保证订购。需要的是相反的。我需要保证混乱，但不需要NEWID()每次客户创建订单时都下订单。

我将像这样使用它：

WITH cte as (
                SELECT TOP 1 * FROM ProductHandles WHERE Used = 0
                --I don't want to order by newid() here as it will be slow
            )
UPDATE cte 
SET Used = 1
OUTPUT INSERTED.ID

阅读 172

2021-05-23

共1个答案

小编典典

如果您在表中添加一个标识列，并order by newid()在插入记录时使用（这会很慢，但是从我的理解来看这是一次性的事情，那么可以order by在identity列上使用该记录 来按顺序 选择记录） 他们插入桌子的地方 。

从Microsoft Docs页面的“ 限制和限制” 部分中INSERT：

使用SELECT和ORDER BY填充行的INSERT查询保证了如何计算标识值，但不能保证插入行的顺序。

这意味着通过这样做，您可以有效地使identity列以相同的随机顺序对insert...select语句中选定的行进行排序。

另外，无需重复相同的cte 5次-您已经在重复交叉应用了：

CREATE TABLE ProductHandles(sort int identity(1,1), ID char(5), used bit)


;WITH 
    cte AS(SELECT * FROM (VALUES('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')) AS v(t))        
INSERT INTO ProductHandles(ID, Used)
SELECT a.t + b.t + c.t + d.t + e.t, 0
FROM cte a
CROSS JOIN cte b
CROSS JOIN cte c
CROSS JOIN cte d
CROSS JOIN cte e
ORDER BY NEWID()

然后，cte可以具有order by子句，该子句保证与填充此表的select语句返回的行相同的随机顺序：

WITH cte as (
                SELECT TOP 1 * 
                FROM ProductHandles 
                WHERE Used = 0
                ORDER BY sort 
            )
UPDATE cte 
SET Used = 1
OUTPUT INSERTED.ID

您可以在rextester上观看现场演示。（只有数字，因为它花费的时间太长了）

2021-05-23