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 (* w" z v) m) E" L% Q
@character_name NVARCHAR(40)$ `7 s" w" V2 Q, j( G* N
)
3 \! `% c( g: K( F" ~
RETURNS TINYINT
; P' G/ [( F1 V" V7 m/ t6 f
AS
3 |) ?& Y/ G4 e6 y; K& e
BEGIN7 F& ~2 L% q8 R( E- h
DECLARE @result TINYINT = 0;# W6 ~$ W' a- o" T
DECLARE @char NVARCHAR(1);
7 ?/ m' w& z: x' M' Z
DECLARE @i INT = 1;; J. x. P! }6 m2 v( a
8 C* o( A- @! U" F
-- 遍历每个字符，检查是否合法8 G, v; q7 E; G4 K
WHILE @i <= LEN(@character_name)5 D' i$ x+ Z2 \8 d K6 F. u
BEGIN7 N: W/ c& X) c d) _0 y
SET @char = SUBSTRING(@character_name, @i, 1);6 m9 b1 j9 V2 o, \2 q0 S- t1 x
0 I- `" j1 a) M1 X& O
-- 检查是否为中文、英文、数字或允许的特殊符号
1 f7 t6 C( m0 U, A2 _( e
IF NOT (: E! k. ~6 v5 N9 c, ^
-- 中文字符范围 (基本多文种平面)$ x( Y1 y; ~4 E1 @" A
UNICODE(@char) BETWEEN 0x4E00 AND 0x9FFF OR( X7 G: m, H5 M1 i1 n
-- 英文字母和数字$ y3 I" X; M% v% K% c) Q. t0 R: _
@char LIKE '[a-zA-Z0-9]' OR
) u" T" j: M- S5 W+ u
-- 允许的特殊符号
0 M" w/ W" K1 l4 v3 b+ l7 |& Z
@char LIKE '[_ -]'2 Z1 `! H. g7 o* A
)3 x- O% L' A- q4 R- ^6 u% E5 c5 Z+ Y& P
BEGIN
5 G o, p5 @4 r7 v8 e
SET @result = 1;
+ x% C" b2 r3 x) F6 R. g& s5 r
BREAK;' E1 D0 \+ V: I4 |+ D
END9 b2 o2 L! o3 L. V) H1 d! ^
3 B0 N7 `9 V* c m" r/ L' C! O1 X
SET @i = @i + 1;
2 d% P7 Y- Z$ o, O" ]$ h& r
END;
; a" ^, k5 p; X6 L0 I
8 K \+ W$ U; S: P+ G J8 o0 C2 d; Y
-- 检查是否在非法名称列表中" G: ? X! W6 K& S9 v
IF EXISTS (
" p6 A% V+ ` N) L* a
SELECT 1 / D& n1 m* @, d; P7 J1 F
FROM dbo.illegal_character_names
$ O7 \1 g. \# k* M+ M. p" q
WHERE @character_name LIKE '%' + partial_name + '%'
( M/ @0 W5 H% Q
)' u, b3 A9 B# m2 Z4 `
SET @result = 1;
. E2 g# p: L3 I; k) z
* S, ~( C" r- U( I2 _
RETURN @result;
) N# e+ z1 j2 [4 f8 j4 z
END</span>4 Y0 S0 w- e* ^8 }9 i' Y

复制代码

插入屏蔽的字符

-- 插入非法名称列表（明确列名并使用N前缀） ~3 _/ ]6 M4 k( F
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%****%');
' q- ~* N2 S" G: V7 `
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%nigg%');5 K- H* T5 J2 y* n
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%wank%');
; K, r" B# n+ T9 U6 b# p0 c
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%whore%');& G8 K, c. H- m A
$ G6 |. i) c9 [( ]* J
-- 示例：查询包含敏感词的角色名9 S0 B$ ~+ I4 y+ V
SELECT *
; t. z8 x% J( j" Y( ?, ]! E
FROM dbo.characters
; b5 n! K+ H1 [
WHERE EXISTS (1 h+ f! h2 x3 E! m
SELECT 1
9 J- ~1 \1 \: \) V, s8 p, w
FROM dbo.illegal_character_names
1 E. l- M, M$ e* m C
WHERE characters.name LIKE N'%' + illegal_character_names.partial_name + N'%'3 M$ p7 x/ M& s0 }2 |
);

复制代码

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

EXEC sp_char_name_check @character_name, @v_ret OUTPUT
% B5 v' \- N w9 m7 \" t
9 }/ ~% P! Y' b3 y: F) y
IF @v_ret < 0
% D* }, l% }$ N3 q7 s. {$ I
BEGIN: v' S) \4 ]4 Q5 L. S5 Z# ]
SET @sp_rtn = @v_ret& [8 E T; Z) D- p
RETURN* s8 A9 O$ ~5 |2 n- {( {9 K* m
END

复制代码

修改为：

IF (dbo.NameBlock(@character_name) = 1)
$ N, o0 ] f- K& ^5 _5 y
BEGIN( p5 z+ l( g/ \( J( s
SET @sp_rtn = -12- @4 j& {# q$ X3 g8 C2 c
RETURN) ^; {5 Z9 O- M' t, D
END

复制代码

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

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

相关帖子