最近接手一个内部管理系统的重构，后台权限这块要重做。要说这里头最基础、也最容易被糊弄过去的，就是用户表和角色表怎么建。很多同行写 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默认 1，CreatedAt默认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(N'Security') IS NULL EXEC(N'CREATE SCHEMA Security');这里有个细节：IF SCHEMA_ID(...) IS NULL EXEC(N'CREATE SCHEMA Security')必须用动态 SQL 包一层，因为CREATE SCHEMA在 SQL Server 的语法规则里不能在 IF 语句块中直接执行（会被解析器当成语法错误）。这个小技巧在实际部署脚本里很管用。
3.2 用户表完整 DDL 及字段解读
我直接给出最终版本的用户表脚本，然后逐段解释为什么这么写。
SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; GO IF OBJECT_ID(N'Security.Users', N'U') 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 和 1，SQL 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 (N'SuperAdmin', N'超级管理员', N'系统内置，拥有全部权限', 1), (N'Admin', N'管理员', N'日常管理操作', 1), (N'User', 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'某开发者';给用户分配角色：
-- 给用户 ID=1 分配 Admin 角色 INSERT INTO Security.UserRoles (UserId, RoleId) SELECT 1, RoleId FROM Security.Roles WHERE RoleCode = N'Admin';这种写法比手动填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 (N'Admin', 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) = N'ABC'，即使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(N'Security.UserRoles', N'U') IS NOT NULL DROP TABLE Security.UserRoles; IF OBJECT_ID(N'Security.Roles', N'U') IS NOT NULL DROP TABLE Security.Roles; IF OBJECT_ID(N'Security.Users', N'U') 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 靠谱得多。
