SQL修改角色名字限制

admin · 发表于 2025-6-6 20:25:12

建立新的限制表

CREATE TABLE illegal_character_names (partial_name varchar(30))

复制代码

建立新的函数

<span style="color: rgb(28, 31, 35); font-family: Inter, -apple-system, BlinkMacSystemFont, "Segoe UI", "SF Pro SC", "SF Pro Display", "SF Pro Icons", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei", "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: medium; white-space: pre; background-color: rgb(255, 255, 255);">CREATE FUNCTION dbo.check_valid_character_name (- t% X5 D% f, T0 R0 o
@character_name NVARCHAR(40)
* n+ A) E) `# i \, ^
)
4 l1 w+ I6 k" ]( o6 V+ y; @
RETURNS TINYINT
AS
. a9 D$ J9 d4 z0 G( Z- K9 \' U
BEGIN
% o% z" s6 q( C
DECLARE @result TINYINT = 0;
2 C. g3 h+ ?8 u8 l5 t8 j
DECLARE @char NVARCHAR(1);0 i& L1 N7 z& S0 T4 C, e
DECLARE @i INT = 1;
3 Q' J k+ m) i& o: Z
- U2 D2 w2 Z; u4 R1 d3 j
-- 遍历每个字符，检查是否合法2 q. W9 ?6 n* t
WHILE @i <= LEN(@character_name), S5 ]5 Q W5 v' W" a6 @ X5 @6 Y
BEGIN
+ f7 k: Y* o' ]2 C3 w9 r# B3 k
SET @char = SUBSTRING(@character_name, @i, 1);8 Y9 @3 ^! w( w
, S9 a( ^; D% P- [) n% Y7 f9 Z
-- 检查是否为中文、英文、数字或允许的特殊符号
0 ^0 [3 }6 f' u s! |
IF NOT (
8 o$ G y* o7 D0 ~! r8 g: P) ~
-- 中文字符范围 (基本多文种平面) t# O0 Y2 I# ]5 v. e1 K
UNICODE(@char) BETWEEN 0x4E00 AND 0x9FFF OR
$ F. e V* Y7 w5 i1 J3 }
-- 英文字母和数字
, b* Q/ p* i; Z
@char LIKE '[a-zA-Z0-9]' OR
# j! ?- ~! y8 r
-- 允许的特殊符号
0 h1 i4 J) d$ r: M
@char LIKE '[_ -]'
( L2 X* W2 g3 x: P) J( w+ b2 n
)
0 o3 z: E' ~; S! _
BEGIN
6 G' K5 E5 B+ `& l5 P' T
SET @result = 1;1 w4 h) i8 I" A: a
BREAK;
% s- E6 v$ t. Q y: \9 z: q7 u8 a
END
0 \1 Y! h' B5 e* s1 s
6 g" Z! t6 z/ G
SET @i = @i + 1;1 L3 Z6 X; |$ B3 P V
END;
. N d, g" ^# \) |' q/ N
( D6 }) r" O$ A& c h; \) E, B
-- 检查是否在非法名称列表中
& s- X O# O6 p8 y6 O
IF EXISTS (
) Y% N# M3 \: f% }8 c' m/ ^
SELECT 1 ' B8 C. l5 L5 j' ~% ?% f! Z
FROM dbo.illegal_character_names
0 n, B' K# w. x# X
WHERE @character_name LIKE '%' + partial_name + '%'
" Y8 a' G3 S1 h2 V! U
)5 F, ?, |2 R( b3 j# i% `
SET @result = 1;+ `5 `9 |$ e) L
4 g. g9 ^0 g+ d1 b8 ?
RETURN @result;7 p" H6 r1 a! f- B" H( A* E: X
END</span>
# n& N( I' n# F2 k2 _! A

复制代码

插入屏蔽的字符

-- 插入非法名称列表（明确列名并使用N前缀）( |& T6 t" i) L( B
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%****%');
9 Q, p# q4 _0 O+ }
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%nigg%');
$ D8 c6 |4 K p5 `, `2 O
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%wank%');
6 J+ Z: @' ?( ~: w
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%whore%');
) Z7 u2 W: Q' t& B, T% U7 c- Y
& p2 |4 v) u; ^& t# v
-- 示例：查询包含敏感词的角色名
6 ~# n+ c/ M& I
SELECT * ) f' E2 T% a7 o0 x* g! A: ?- v) a
FROM dbo.characters % _; G$ q( b+ U" @5 X
WHERE EXISTS (& B* L0 r7 a3 }1 C
SELECT 1 ; ^6 `: p" W: C
FROM dbo.illegal_character_names . q B/ m% Q/ X( Q
WHERE characters.name LIKE N'%' + illegal_character_names.partial_name + N'%'
/ g7 q6 O1 U% t- G& A) M5 B1 l6 _
);

复制代码

修改储存过程 SP_CHAR_CREATE 里面数据
原始为：

EXEC sp_char_name_check @character_name, @v_ret OUTPUT+ s3 p6 ^* o, S3 j! w
! O* N5 c7 r2 g# r
IF @v_ret < 0 4 L- q: X& x) X+ M' F- l
BEGIN
& l5 { u2 Z7 ^1 m/ ^. H
SET @sp_rtn = @v_ret
) s; G5 _2 @/ U6 g
RETURN
5 u0 W$ T* ^5 ^3 i" W# J5 O
END

复制代码

修改为：

IF (dbo.NameBlock(@character_name) = 1)
7 O. O* R, b' e" ~; I) A
BEGIN2 {+ y- s; {0 k7 L+ p7 h d
SET @sp_rtn = -12
/ g* {* h2 v! K! g* A' W
RETURN7 ]# }/ S# P( U3 G4 y+ E0 [$ S
END

复制代码

		自动登录	找回密码
密码			立即注册

[技术文章] SQL修改角色名字限制

相关帖子