最近接手一个内部管理系统的重构后台权限这块要重做。要说这里头最基础、也最容易被糊弄过去的就是用户表和角色表怎么建。很多同行写 T-SQL 建表要么直接丢给 ORM 自动生成要么随手CREATE TABLE一把梭等到上线后要加索引、要查角色、要做权限控制的时候才发现表结构埋了一堆雷。这篇文章就把我在实际项目里反复打磨过的一套用户与角色表设计方案完整放出来从字段怎么定、类型怎么选到外键要不要级联、索引怎么建全部按 T-SQL 规范讲清楚代码可以直接拿去改着用。1. 先把业务模型理清楚用户、角色、关联表缺一不可1.1 常见权限模型怎么选做权限设计之前先别急着写 DDL得想清楚你到底要支持哪种权限模型。我见过不少系统用户表里加一列Role字符串填“管理员、编辑、访客”这种方案看起来简单实际上后患无穷角色多了没法扩展改一个角色名要 UPDATE 一大片用户数据更别提“一个用户既是管理员又是审核员”这种多角色需求。现在主流做法是 RBAC也就是基于角色的访问控制。简单说就是把“用户”和“权限”解耦中间加“角色”这一层。用户不再直接跟权限挂钩而是通过角色间接获得权限。这样新增一个角色、调整一组权限只需要改角色和权限的映射用户表完全不用动。如果项目里已经有人用 ABAC基于属性的访问控制或者更细粒度的权限方案那通常也是在 RBAC 底座之上做的增强用户和角色的基础表结构依然绕不开。模型核心思想适合场景复杂度硬编码角色字段用户表加一个角色字段人数极少、角色固定的一次性工具最低但扩展性差RBAC 用户-角色用户、角色分开中间关联大多数管理后台、业务系统适中RBAC 权限点用户-角色-权限三层大型系统、多端权限隔离较高我的经验是除非你确定系统只跑三个月就报废否则直接上 RBAC。基础结构就是三张表用户表、角色表、用户角色关联表。就算前期只做一个角色也把三张表建好后面扩展会轻松得多。1.2 用户角色关联表为什么不能用逗号拼接代替有人图省事在用户表加一列RoleIds存“1,3,5”这种逗号分隔的字符串觉得这样查起来也方便。真要这么做你会遇到几个很恶心的问题统计某角色下有多少用户得把字符串拆开SQL Server 里STRING_SPLIT还得 SQL Server 2016 以上才有老版本只能写自定义拆分函数。用户和角色的关系无法用外键约束删了一个角色关联数据就成了脏数据。加角色时要先读出来拼字符串并发操作极易丢数据。正确的做法是建一张独立的关联表每一行只存一个 UserID 和一个 RoleID。这本质上就是数据库里的“多对多关系”标准建模一张中间表把用户和角色两个实体松耦合地连起来。这样做的好处是查询用 JOIN 就能搞定删除角色时外键能兜底增加角色就是 INSERT 一行记录所有操作都干净利落。1.3 从 T-SQL 规范角度重新审视建表这件事为什么强调 T-SQL 规范因为在 SQL Server 里表结构不只是“存数据的地方”它直接影响索引效率、约束策略、甚至数据库迁移的兼容性。比如 ANSI_NULLS 设置、用NVARCHAR还是VARCHAR、排序规则选什么这些细节在 MySQL 里可能无所谓在 SQL Server 里却可能成为线上故障的导火索。举个例子SQL Server 对索引视图、计算列、筛选索引有硬性要求创建这些对象时ANSI_NULLS和QUOTED_IDENTIFIER必须是 ON。如果你建的表在 OFF 状态下创建了计算列后面想在这个计算列上建索引会直接报错。这就是规范的价值——它不是形式主义而是背后的存储引擎行为决定的。2. T-SQL 建表规范的几个硬性要求2.1 命名规范表名、字段名、约束名、索引名命名这关是很多团队最随意的地方。表名一会儿users一会儿t_user一会儿中文拼音首字母字段名name、username、user_name混着用后面写 SQL 全凭猜。这里我用的是一套经过多个项目验证的约定表名用单数形式比如Users、Roles不用t_前缀也不加tbl_这种过时前缀。字段名统一用 PascalCase 加下划线分隔或者直接用 CamelCase在 SQL Server 里我习惯用 PascalCase比如CreatedAt因为 SQL Server 默认不区分大小写CREATEDAT和CreatedAt效果一样。约束名必须写清楚PK_开头是主键FK_开头是外键UQ_开头是唯一约束DF_开头是默认值后面跟表名和列名。比如PK_Users_Id、FK_UserRoles_Users_UserId。索引名同样有规则IX_表名_列名唯一索引用UX_表名_列名。这么做的原因很简单当系统里出现一个外键冲突或者死锁错误消息里会直接给出约束名你能一眼定位到是哪张表、哪个字段出了问题。如果所有约束都叫FK__xxx__1234这种系统默认名排查问题就得逐个去系统表里翻。另外注意一点表名尽量不要用User。虽然 SQL Server 里USER是关键字加了方括号[User]也能用但我见过不少同事因为忘加方括号被语法错误折磨。所以我统一用Users既符合习惯又避开关键字。2.2 数据类型选择长度、精度、排序规则都不是小事用户表最常见的字段是用户名、密码哈希、状态、创建时间。这几个字段的类型选择各有讲究。用户名建议用NVARCHAR(50)不是VARCHAR(50)。虽然VARCHAR在纯英文环境下更省空间但管理系统难免要支持中文用户名。NVARCHAR是 Unicode 存储每个字符固定占两个字节或更多取决于排序规则中文英文字符都能存。如果不确定未来会不会出现邮箱、手机号等 Unicode 字符直接上NVARCHAR是最稳妥的。密码绝对不要用VARCHAR存明文。我见过最离谱的项目密码字段长度 50存的是明文密码安检一查就被打回。规范做法是存哈希值。如果使用HASHBYTES(SHA2_256, ...)结果长度是固定 32 字节字段类型用BINARY(32)就够。如果考虑加盐和未来算法升级我会预留到VARBINARY(64)。注意HASHBYTES对超过 8000 字节的输入会报错所以对密码这种短字符串没问题。很多项目也会选择用NVARCHAR(128)存十六进制哈希字符串这也是常见的兼容做法但用BINARY更省空间查询也能走索引。状态字段用BIT就够表示启用/禁用。别用INT然后传 0/1纯属浪费空间还容易填错。创建时间用DATETIME2(3)精度到毫秒比DATETIME精度更高而且DATETIME2的范围更大。如果你只用GETDATE()用DATETIME其实也没毛病但现在新写的库我基本统一DATETIME2。排序规则COLLATION这块容易被忽略。SQL Server 实例默认排序规则可能是SQL_Latin1_General_CP1_CI_AS对中文支持不友好。国内项目我一般把数据库排序规则设为Chinese_PRC_CI_AS这样中文排序和比较都正常。列级排序规则如果跟库级不一致JOIN 两个表时可能报“无法解析排序规则冲突”解决方案要么统一库级排序规则要么在 JOIN 条件里加COLLATE DATABASE_DEFAULT。这些细节在后面的“常见问题”部分还会细讲。2.3 NULL 与默认值该严格的字段一个都不能松T-SQL 建表时每个字段都要明确NULL还是NOT NULL千万不要依赖默认值。NULL和空字符串在业务上含义不同NULL代表“未知”空字符串代表“已知但为空”。在权限系统里用户名、密码哈希、主键、关联表的外键这些字段必须是NOT NULL否则会出现一个没有用户名的用户或者一条不知道属于谁的关联记录。主键建议用INT IDENTITY(1,1)自增列。IDENTITY在 SQL Server 里是标准做法插入时自动生成性能好、占用空间小。如果你有分布式合并数据的需求比如多个分库的数据要合并到中心库自增主键会产生冲突这时候才考虑UNIQUEIDENTIFIER也就是 GUID。GUID 不用NEWID()生成的话是无序的会导致聚集索引频繁页分裂所以 SQL Server 2015 以后一般用NEWSEQUENTIALID()。但大多数单库管理系统自增 INT 足够。默认值这块建表时就定义好别完全依赖应用层。比如IsActive默认 1CreatedAt默认SYSDATETIME()这能确保绕过应用直连数据库插数据时字段也有合理的值。 SQL Server 里的默认值约束DF_Users_CreatedAt就是干这件事的。注意GETDATE()返回的是DATETIME精度 3 毫秒如果你用DATETIME2(3)最好用SYSDATETIME()避免隐式转换的精度损失。3. 完整实战用户表、角色表、关联表 DDL 一步步写出来3.1 架构(Schema)与对象权限规划建表之前先想清楚放哪个 schema。默认dbo也不是不行但多人协作、权限隔离时按模块分 schema 更清晰。我习惯这类权限相关的表统一放在Security或Authschema 下这样所有跟用户、角色、权限相关的对象一目了然也好做权限控制。-- 创建安全模块架构 IF SCHEMA_ID(NSecurity) IS NULL EXEC(NCREATE SCHEMA Security);这里有个细节IF SCHEMA_ID(...) IS NULL EXEC(NCREATE SCHEMA Security)必须用动态 SQL 包一层因为CREATE SCHEMA在 SQL Server 的语法规则里不能在 IF 语句块中直接执行会被解析器当成语法错误。这个小技巧在实际部署脚本里很管用。3.2 用户表完整 DDL 及字段解读我直接给出最终版本的用户表脚本然后逐段解释为什么这么写。SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; GO IF OBJECT_ID(NSecurity.Users, NU) IS NOT NULL DROP TABLE Security.Users; GO CREATE TABLE Security.Users ( UserId INT IDENTITY(1,1) NOT NULL, UserName NVARCHAR(50) NOT NULL, PasswordHash VARBINARY(64) NOT NULL, PasswordSalt VARBINARY(32) NOT NULL, DisplayName NVARCHAR(50) NULL, Email NVARCHAR(128) NULL, Phone NVARCHAR(20) NULL, IsActive BIT NOT NULL CONSTRAINT DF_Users_IsActive DEFAULT (1), CreatedAt DATETIME2(3) NOT NULL CONSTRAINT DF_Users_CreatedAt DEFAULT (SYSDATETIME()), UpdatedAt DATETIME2(3) NULL, LastLoginAt DATETIME2(3) NULL, CONSTRAINT PK_Users_UserId PRIMARY KEY CLUSTERED (UserId ASC), CONSTRAINT UQ_Users_UserName UNIQUE (UserName) ); GOSET ANSI_NULLS ON和SET QUOTED_IDENTIFIER ON是很多自动生成脚本里必带的头上面说了索引视图、计算列、筛选索引都依赖这个设置。建表脚本统一带上能避免不少坑。UserId用INT IDENTITY做主键聚集索引默认建在主键上。INT最大 21 亿多一般管理系统完全够用。UserName加唯一约束这是硬性要求。同一个用户名出现两条记录说明系统已经错乱了。唯一约束会自动创建一个唯一索引查询用户名时能直接走索引。PasswordHash和PasswordSalt分开存。加盐哈希是当前密码存储的底线做法。即便数据库泄露攻击者拿到的也是不可逆的哈希值没有盐连彩虹表都懒得查你。DisplayName这种展示字段允许NULL因为有些人不想填昵称。CreatedAt、UpdatedAt、LastLoginAt是审计和排障字段。UpdatedAt不设默认值而是在应用层每次 UPDATE 时显式赋值。有人喜欢建触发器自动改UpdatedAt但触发器会拖慢批量更新性能也不方便应用层拿到更新后的时间我基本不用。CHECK (IsActive IN (0,1))对BIT类型其实是多余的因为BIT本身只有 0 和 1SQL Server 不会接受其他值所以不用加。3.3 角色表与用户角色关联表角色表相对简单。主键、角色编码、角色名称、描述、是否系统内置、创建时间。注意角色编码是给程序识别用的比如Admin、Editor所以也要加唯一约束而且角色编码一般不会变变了会导致程序里的判断逻辑全部失效。CREATE TABLE Security.Roles ( RoleId INT IDENTITY(1,1) NOT NULL, RoleCode NVARCHAR(50) NOT NULL, RoleName NVARCHAR(50) NOT NULL, Description NVARCHAR(200) NULL, IsSystem BIT NOT NULL CONSTRAINT DF_Roles_IsSystem DEFAULT (0), CreatedAt DATETIME2(3) NOT NULL CONSTRAINT DF_Roles_CreatedAt DEFAULT (SYSDATETIME()), CONSTRAINT PK_Roles_RoleId PRIMARY KEY CLUSTERED (RoleId ASC), CONSTRAINT UQ_Roles_RoleCode UNIQUE (RoleCode) ); GO关联表这里要注意外键策略。我见过有人把外键全写成ON DELETE CASCADE觉得删用户的时候自动把关联数据清掉很方便。但这在权限系统里是很危险的做法删一个用户连带着把他的角色映射全部物理删除如果后续需要审计“这个用户曾经有什么角色”数据就找不回来了。更稳妥的做法是用户表加IsActive位禁用用户只需要 UPDATE 状态而不是 DELETE 记录。在这个前提下关联表的外键可以不设级联甚至可以把用户的删除做成物理删除 审计表双写。我这里给一个折中方案关联表外键不设级联删除强制业务层先处理关联关系这样至少不会出现误删一把梭的情况。CREATE TABLE Security.UserRoles ( UserId INT NOT NULL, RoleId INT NOT NULL, CreatedAt DATETIME2(3) NOT NULL CONSTRAINT DF_UserRoles_CreatedAt DEFAULT (SYSDATETIME()), CONSTRAINT PK_UserRoles_UserId_RoleId PRIMARY KEY CLUSTERED (UserId, RoleId), CONSTRAINT FK_UserRoles_Users_UserId FOREIGN KEY (UserId) REFERENCES Security.Users (UserId), CONSTRAINT FK_UserRoles_Roles_RoleId FOREIGN KEY (RoleId) REFERENCES Security.Roles (RoleId) ); GO -- 关键查询索引按角色查用户时避免全表扫描 CREATE NONCLUSTERED INDEX IX_UserRoles_RoleId_UserId ON Security.UserRoles (RoleId ASC, UserId ASC); GO复合主键(UserId, RoleId)本身就解决了“同一用户同一角色重复关联”的问题。主键索引默认就是聚集索引所以UserIds的查询走聚集索引很快。但反过来按RoleId反查有哪些用户时聚集索引就没法直接用了所以补一个非聚集索引IX_UserRoles_RoleId_UserId让按角色查用户的场景也能走索引覆盖。3.4 初始化基础数据和查询用户角色表建好之后一般要插入基础角色。INSERT INTO Security.Roles (RoleCode, RoleName, Description, IsSystem) VALUES (NSuperAdmin, N超级管理员, N系统内置拥有全部权限, 1), (NAdmin, N管理员, N日常管理操作, 1), (NUser, N普通用户, N默认注册用户角色, 1);注意N...前缀。SQL Server 里字符串默认是单字节字符集遇到中文容易乱码加上N前缀表示 Unicode 字符串这是写 T-SQL 字符串常量的基本习惯尤其建库脚本、插入中文数据时一定要带上。查询用户角色的语句SELECT u.UserId, u.UserName, r.RoleCode, r.RoleName FROM Security.Users u LEFT JOIN Security.UserRoles ur ON ur.UserId u.UserId LEFT JOIN Security.Roles r ON r.RoleId ur.RoleId WHERE u.UserName N某开发者;给用户分配角色-- 给用户 ID1 分配 Admin 角色 INSERT INTO Security.UserRoles (UserId, RoleId) SELECT 1, RoleId FROM Security.Roles WHERE RoleCode NAdmin;这种写法比手动填RoleId更不容易出错因为RoleCode是稳定的编码而自增RoleId在不同环境可能不一样。部署到测试库、正式库角色 ID 可能不同但编码不会变所以业务代码里永远通过RoleCode去查角色而不是硬编码RoleId。这是权限系统设计里一个非常重要的原则。4. 常见问题排查与避坑实录4.1 并发插入时唯一约束冲突用户表UserName加了唯一约束两个请求同时注册同一个用户名第二个 INSERT 会触发唯一键冲突报错信息类似Violation of UNIQUE KEY constraint UQ_Users_UserName. Cannot insert duplicate key in object Security.Users.很多新手遇到这个就慌其实这正是唯一约束在保护你的数据。处理方式是应用层捕获这个错误给用户提示“用户名已存在”。有些团队会用MERGE或者IF NOT EXISTS先判断再插入但并发场景下IF NOT EXISTS并不能完全避免冲突因为判断和插入之间可能有另一个事务抢先插入。所以真正可靠的方案就是唯一约束 应用层错误捕获。如果确实存在极少量的“先判断后插入”场景SQL Server 的错误号2601就是唯一索引冲突2627是唯一约束冲突在应用层可以根据这些错误码返回友好提示。4.2 中文乱码和 N 前缀问题很多人写 INSERT 语句不带N前缀插入中文后查出来是一堆问号或者乱码。原因就是字符串字面量在没有N前缀时会先按数据库默认代码页转换如果这个代码页不是 UTF-8 或 Unicode中文字符就无法正确表示。-- 错误示范 INSERT INTO Security.Roles (RoleCode, RoleName) VALUES (Admin, 管理员); -- 正确写法 INSERT INTO Security.Roles (RoleCode, RoleName) VALUES (NAdmin, N管理员);另外一个容易踩的坑是数据库排序规则不同导致的 JOIN 失败。比如用户表在Chinese_PRC_CI_AS库另一个历史库是SQL_Latin1_General_CP1_CI_AS两个库 JOIN 时 SQL Server 会报Cannot resolve the collation conflict between Chinese_PRC_CI_AS and SQL_Latin1_General_CP1_CI_AS in the equal to operation.解决方案是在 JOIN 条件里显式指定排序规则SELECT ... FROM Security.Users u JOIN LegacyDB.dbo.UserExtras ue ON u.UserName ue.UserName COLLATE DATABASE_DEFAULT;这个写法在跨库关联时特别实用比改整个库的排序规则影响范围小得多。4.3 删除用户时外键阻塞关联表有外键删除用户时会被告知外键冲突因为UserRoles里还引用着这个用户。这时候有两种选择先删关联数据再删用户或者把用户标记为禁用。我强烈推荐第二种也就是软删除。软删除就是只更新IsActive 0不物理删除记录。好处极其明显历史订单、操作日志里关联的“用户姓名”还能通过 JOIN 查出来数据量不是特别大的情况下表不会因为频繁 DELETE 造成索引碎片可以随时恢复被误禁用的账号不用去备份里捞数据。如果你一定要物理删除那就得写清楚顺序BEGIN TRANSACTION; DELETE FROM Security.UserRoles WHERE UserId 123; DELETE FROM Security.Users WHERE UserId 123; COMMIT TRANSACTION;包在事务里的原因是保证两个 DELETE 要么都成功要么都失败避免出现用户删了但角色关联还残留的中间状态。4.4 索引设计的两个细节第一不要在索引列上做函数运算。比如查询WHERE UPPER(UserName) NABC即使UserName上有唯一索引SQL Server 也无法使用这个索引因为索引里存的是原始值而不是UPPER之后的值。要么在应用层统一大小写后再查询要么新增一个计算列UserNameUpper AS UPPER(UserName) PERSISTED并在这个列上建索引。但要注意计算列上建索引要求ANSI_NULLS等会话设置正确这就是文章前面为什么强调建表脚本头部要加SET ANSI_NULLS ON。第二关联表的索引方向。上面加了IX_UserRoles_RoleId_UserId这样按角色查用户时可以用。如果你经常按“用户ID 角色ID”查复合主键(UserId, RoleId)已经覆盖了。如果未来业务里还有按时间范围查用户角色变更历史的场景可以考虑加CreatedAt到索引里但注意索引不是越多越好每个索引都会拖慢写入速度得权衡。4.5 别忘了给关联表加审计时间很多初版设计里UserRoles关联表只放UserId和RoleId没加CreatedAt。当时省事后面要查“这个用户是什么时候变成管理员的”就完全查不到。有人说可以去应用日志里翻但日志一般最多保留 30 天权限回溯的需求往往要查一年前的记录。所以我在关联表里始终保留CreatedAt如果条件允许再配一张独立的关系变更日志表每一次分配角色、回收角色都记一条。这张日志表甚至可以不需要外键只记录UserId、RoleId、ActionType、OperatorId、CreatedAt查询时按时间筛选数据量大就做分区归档。这套设计在出事追责的时候能救你一命。5. 部署时容易忽略的 T-SQL 脚本细节建表脚本通常要放进自动化部署工具里反复执行所以脚本本身要具备“可重复执行”的能力。我习惯在每个建表语句前面加上IF OBJECT_ID(...) IS NOT NULL DROP TABLE ...但要注意表之间的外键依赖不能上来就 DROP 所有表。常见做法是按依赖顺序处理IF OBJECT_ID(NSecurity.UserRoles, NU) IS NOT NULL DROP TABLE Security.UserRoles; IF OBJECT_ID(NSecurity.Roles, NU) IS NOT NULL DROP TABLE Security.Roles; IF OBJECT_ID(NSecurity.Users, NU) IS NOT NULL DROP TABLE Security.Users;顺序是从子表到父表先删外键引用者再删被引用者。如果你忘了这个顺序SQL Server 会直接报外键依赖错误。还有就是部署脚本的编码格式。T-SQL 脚本文件里有中文保存时一定要选 UTF-8 带 BOM 或者 UCS-2 Little Endian否则用sqlcmd执行时中文注释和字符串都可能乱码。这个问题在 Windows 环境下特别常见很多同事脚本写对了结果保存编码不对执行完插入的中文全变问号排查半天才发现是文件编码的问题。另外日志和数据的文件组规划也可以在建表时考虑。如果系统量大可以把Users这类热表放在单独的PRIMARY文件组把审计类的历史表放到另一个文件组。这不是必须的但属于“未雨绸缪”的规范操作。给表指定文件组可以在CREATE TABLE语句末尾用ON [文件组名]不过一般的内部管理系统用不到这一步单独拿出来说说免得新手误以为建表只能写字段和约束。回到用户与角色表设计这件事本身。我个人的习惯是每次新建一个涉及权限的系统都会先把这几张表的 DDL 写好并评审一遍再动业务代码。多花半小时把类型、约束、索引这些细节定死后面能省下数不清的改表时间。尤其像UserName加不加唯一索引、关联表要不要审计时间这种决策一旦表里有了数据再回头改方案难度和工作量完全不是一个量级。最后再分享一个小心得这类基础表设计好之后随手把常用查询和初始化数据也写成脚本放到项目的database/seed目录里跟 DDL 放一起。以后换环境部署、拉新同事上手只需要按顺序执行一遍整个权限体系就立起来了比自己临时拼 SQL 靠谱得多。