Skip to content

搞英语 → 看世界

翻译英文优质信息和名人推特

Menu
  • 首页
  • 独立博客
  • 专业媒体
  • 名人推特
  • 邮件列表
  • 关于本站
  • Product Hunt
  • Visual Capitalist
  • Elon Musk
Menu

直接在数据库中生成唯一的随机值

Posted on 2022-03-09

您经常需要生成随机字符串,例如用于登录 cookie 和唯一入口代码。

这些字符串总是需要存储在数据库中。因此,通过直接在数据库中生成随机字符串来让您的生活更简单。这是一个非常方便的 PostgreSQL 函数:

 create function gen_random_bytes(int) returns bytea as '$libdir/pgcrypto', 'pg_random_bytes' language c strict; create function random_string(len int) returns text as $$ declare chars text[] = '{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,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}'; result text = ''; i int = 0; rand bytea; begin -- generate secure random bytes and convert them to a string of chars. rand = gen_random_bytes($1); for i in 0..len-1 loop -- rand indexing is zero-based, chars is 1-based. result = result || chars[1 + (get_byte(rand, i) % array_length(chars, 1))]; end loop; return result; end; $$ language plpgsql;

下载代码

给它一个数字:你想要的随机字符串的长度。它将返回该长度的随机字母数字文本。

 select random_string(8); random_string ─────────────── yBuXga02 select random_string(8); random_string ─────────────── eP3X7yqe

下载代码

随机冲突的机会非常小。但是您需要完全确定新的随机字符串是唯一的——在该表的该列中不存在。所以这里有一个函数,你可以给它字符串长度、表名和列名。它将返回一个确认为唯一的随机字符串——不存在于那里。它获取一个随机字符串,在该表和列中搜索它,如果找不到,则返回它。否则,如果找到,则获取一个新的随机字符串并循环返回,再次尝试直到找不到。

 -- return random string confirmed to not exist in given tablename.colname create function unique_random(len int, _table text, _col text) returns text as $$ declare result text; numrows int; begin result = random_string(len); loop execute format('select 1 from %I where %I = %L', _table, _col, result); get diagnostics numrows = row_count; if numrows = 0 then return result; end if; result = random_string(len); end loop; end; $$ language plpgsql;

下载代码

我曾经使用数据库触发器调用这样的函数,在任何插入时调用。但后来我发现了一些非常酷且简单得多的东西:您可以直接在表定义中将函数调用为默认值。

查看此表,调用 unique_random 作为其默认值:

 create table things ( code char(8) primary key default unique_random(8, 'things', 'code'), name text );

下载代码

如此简单明了!要使用它,您只需执行常规插入,它会生成保证唯一的默认值。

 insert into things (name) values ('one') returning *; code │ name ──────────┼────── nRSXbVWQ │ one insert into things (name) values ('two') returning *; code │ name ──────────┼────── EAS9wGcl │ two

下载代码

我发现这对于创建登录 cookie 特别方便:

 create table cookies ( person_id int primary key, cookie char(32) unique default unique_random(32, 'cookies', 'cookie') );

下载代码

与其让你的客户端代码、你的 JavaScript、Python、Ruby 或其他任何东西生成随机代码,不如直接将它放在你的数据库中,不仅因为它更干净,而且因为它节省了客户端代码之间的重复调用和数据库,确认唯一性。一个简单的 person_id 插入返回唯一且已保存的随机 cookie 字符串:

 insert into cookies (person_id) values (1) returning *; person_id │ cookie ───────────┼────────────────────────────────── 1 │ 0P8Tp4wjXuTqCCh1NCR9XIom20z9IcYv

下载代码

在/code/rand1.sql下载代码。

来源: https://sive.rs/rand1

发表回复 取消回复

要发表评论,您必须先登录。

本站文章系自动翻译,站长会周期检查,如果有不当内容,请点此留言,非常感谢。
  • Bob Nystrom (1)
  • Dan Wang (1)
  • Joel on Software (1)
  • John Resig (1)
  • Laurence Gellert's Blog (1)
  • Matt Might's blog (3)
  • News Letter (191)
  • Philip Walton (1)
  • Pivotal (1)
  • Sam Julien (1)
  • Scott Hanselman's Blog (2)
  • Tom's blog (1)
  • Wait But Why (1)
  • 英文媒体 (36,595)
    • Ars Technica (2,472)
    • Daily Infographic (285)
    • Engadget (5,712)
    • Enonomist (77)
    • FlowingData (254)
    • Hacker News (773)
    • Hacker News Daily (314)
    • Hacker Noon (125)
    • Harvard Health (150)
    • KK – Cool Tools (213)
    • KK – Recomendo (263)
    • Make Use Of (158)
    • NASA Astronomy Picture (267)
    • Product Hunt (7,487)
    • Psyche (220)
    • Quanta Magazine (187)
    • Science current issue (536)
    • Sidebar (1,029)
    • Singularity HUB (278)
    • TechCrunch (9,039)
    • The Practical Developer (99)
    • The Verge (6,276)
    • Visual Capitalist (381)
  • 英文推特 (17,682)
    • Bill Gates (342)
    • Brett Winton (1,333)
    • Cathie Wood (303)
    • Durov's Channel (25)
    • Elon Musk (5,422)
    • GeekWire (2,868)
    • Hunter Walk (55)
    • Mark Gurman (1,061)
    • Naval (699)
    • Parag Agrawal (52)
    • Ray Dalio (1,045)
    • Riccardo Mori (16)
    • Steph Smith (2,269)
    • Tim Cook (169)
    • Vitalik Buterin (2,023)
  • 英文独立博客 (3,800)
    • A learning a day (327)
    • A Smart Bear (2)
    • AddyOsmani.com (10)
    • Adwyat Krishna (29)
    • Ahmad Shadeed (2)
    • Alex Turek (3)
    • All Poetry (1)
    • All That is Solid (49)
    • André Staltz (2)
    • arxivblog (37)
    • Astral Codex Ten (15)
    • Atoms vs Bits (26)
    • AVC (40)
    • Basic Apple Guy (41)
    • Ben Thompson (13)
    • Benedict Evans (8)
    • Blog – storytelling with data (43)
    • Built For Mars (11)
    • Caleb Porzio (1)
    • Cameron Sun (2)
    • Christian Heilmann (39)
    • Christopher C (3)
    • Chun Tian (binghe) (1)
    • Codrops (16)
    • Cold Takes (16)
    • Dan Luu (1)
    • Daniel Lemire's blog (51)
    • David Amos (23)
    • David Perell (6)
    • David Walsh Blog (36)
    • Derek Sivers (28)
    • Desvl (14)
    • Devon's Site (5)
    • Digital Inspiration (26)
    • DKB Blog (4)
    • Douglas Vaghetti (12)
    • dropsafe (56)
    • DSHR (37)
    • Dunk (5)
    • DYNOMIGHT (38)
    • eagereyes (7)
    • Endless Metrics (135)
    • Entitled Opinions (8)
    • Exception Not Found (6)
    • Experimental History (21)
    • Farnam Street (6)
    • Fed Guy (10)
    • Felix Krause (3)
    • Florent Crivello (2)
    • Free Mind (7)
    • Full Stack Economics (40)
    • Funny JS (3)
    • Future A16Z (47)
    • Glassnode Insights (55)
    • Human Who Codes (4)
    • Infographics – Cool Infographics (10)
    • Information is Beautiful (11)
    • Irrational Exuberance (40)
    • Jacob Kaplan-Moss (13)
    • Jakob Greenfeld (44)
    • James Sinclair (3)
    • Jason Fried (17)
    • Jeff Kaufman (178)
    • John's internet house (31)
    • Johnny Rodgers (4)
    • Julia Evans (25)
    • Julian.com (2)
    • Kalzumeus (1)
    • Kevin Cox (10)
    • Kevin Norman (3)
    • KK – The Technium (50)
    • Krishna (7)
    • Lee Robinson (5)
    • Lines and Colors (51)
    • Lyn Alden – Investment Strategy (3)
    • Martin Fowler (28)
    • More To That (13)
    • Morgan Housel (76)
    • My Super Secret Diary (30)
    • Naval Blog (3)
    • Neckar's New Money (76)
    • Nick Whitaker (4)
    • Nicky's New Shtuff (1)
    • nutcroft (10)
    • Paul Graham (2)
    • Paul Graham: Essays (2)
    • Penguin Random House (66)
    • Phoenix's island (1)
    • Prof Galloway (35)
    • Python Weekly (30)
    • Rachel (33)
    • Real Life (34)
    • Sasha (63)
    • Science & technology (122)
    • Sébastien Dubois (6)
    • Secretum Secretorum (13)
    • Seth's Blog (146)
    • Shu Ding (3)
    • SignalFire (9)
    • Simon Willison's Weblog (167)
    • Simons Foundation (85)
    • SLIME MOLD TIME MOLD (23)
    • Slyar Home (8)
    • Spencer Greenberg (10)
    • Stay SaaSy (11)
    • Stephen Malina (4)
    • Stephen Wolfram Writings (2)
    • Strange Loop Canon (23)
    • Stratechery (10)
    • Tech Notes (11)
    • The Commonplace (29)
    • The Generalist (2)
    • The Intrinsic Perspective (30)
    • The Latest in Hearing Health | HeardThat (8)
    • The Mad Ned Memo (2)
    • The Rabbit Hole (37)
    • TLDR Newsletter (81)
    • Tomasz Tunguz (91)
    • Tony Kulesa (2)
    • Troy Hunt (58)
    • Tychlog (1)
    • Uncharted Territories (57)
    • Visualising Data (9)
    • Weichen Liu (20)
    • What's New (55)
    • Works in Progress (1)
    • Workspaces (32)
    • Writing (8)
    • Xe's Blog (35)
    • xkcd.com (117)
    • Yihui Xie (13)
    • Zoran Jambor (11)
©2023 搞英语 → 看世界 | Design: Newspaperly WordPress Theme