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 (
( Q9 E4 V, [2 N* k0 c& d
@character_name NVARCHAR(40)
) }6 |# n* m3 G$ r
)1 U. e5 P. W' \( x* D; g. Y
RETURNS TINYINT: j" [ f1 i3 q2 G
AS
7 B& U2 ?, }% v( y a ?
BEGIN
; v5 t6 v1 T5 r) V& K
DECLARE @result TINYINT = 0;% X! U3 G: K( ~: G) n/ y
DECLARE @char NVARCHAR(1);6 c/ J( A% t5 ~+ m, _! ~
DECLARE @i INT = 1;
% E2 L# g) l4 Z# d% x! h
$ b9 C$ ^# f; p. Y5 f
-- 遍历每个字符，检查是否合法, d4 k4 O0 T( V
WHILE @i <= LEN(@character_name)
0 R' w4 w- i/ l7 h
BEGIN
) z: g% R( g5 U2 n6 Z f, V) q+ V9 ]
SET @char = SUBSTRING(@character_name, @i, 1);
7 U/ O! C, `. p- \) r- J' B
, @' S6 A; E# G" ]$ z
-- 检查是否为中文、英文、数字或允许的特殊符号
$ F+ _0 c9 M" r" v2 N5 W3 N
IF NOT (+ X- X: C) N3 T- f8 P
-- 中文字符范围 (基本多文种平面)
8 a: g( M3 E; V! x% N
UNICODE(@char) BETWEEN 0x4E00 AND 0x9FFF OR" h! r6 U2 o' B: }0 u9 j
-- 英文字母和数字* `6 v) ~" {& e9 Q: t( P
@char LIKE '[a-zA-Z0-9]' OR; t4 E$ w, N* y3 V" ?; h
-- 允许的特殊符号: `4 x, O" F6 T, }) O
@char LIKE '[_ -]'
' W& e5 j. B K: v) j* J4 S7 Y: b
)- S' k* ?6 ]5 d8 i1 m n
BEGIN
' c( Y! e6 q2 ~) l1 Z
SET @result = 1;
+ Y9 V* J. g) i' H3 [+ _' s
BREAK;% `/ o/ ]6 E$ W& o# v8 R2 u
END: X% _3 s# d2 D9 y" ?
& C" E8 y9 T! U4 {
SET @i = @i + 1;* q% L5 P8 Q0 [8 F
END;
# W" L1 p- Q9 ^8 U# P
5 t! x/ U, L1 A" ^8 w& O
-- 检查是否在非法名称列表中
, n: O+ I" ^& h
IF EXISTS (
7 L8 G0 L+ E! T/ [# k6 o( @, Y7 g
SELECT 1 & x5 X2 J7 z2 i
FROM dbo.illegal_character_names . G1 G. A3 r: Y% P- n4 p7 S8 l; x6 I
WHERE @character_name LIKE '%' + partial_name + '%'; M! g% t6 t% W+ Y, p2 j' ~3 s
)- q5 ` U' b3 C3 m; g7 ~9 Q
SET @result = 1;1 |% [( t% X. ^% e c6 W! N" Z
& c3 {7 v. P. x8 [3 m$ m# l, Y
RETURN @result;
1 a0 @# a7 L1 X6 ]+ u
END</span>
" ?9 d V$ b, H5 e h

复制代码

插入屏蔽的字符

-- 插入非法名称列表（明确列名并使用N前缀）
1 t$ V$ E7 U+ X$ ~
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%****%');: O, W/ b, D3 |
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%nigg%');
* p3 k2 j5 u1 }0 m* X
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%wank%');$ t6 E7 C1 n4 _+ F
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%whore%');
( }3 y5 t9 A7 Z1 b' ?8 S7 B; u5 R+ `
2 c( N! o& a+ |+ V% Y# X1 l
-- 示例：查询包含敏感词的角色名9 p Q S5 f% i) C/ h( r1 G9 M e o
SELECT * . f4 i' _) X8 L& N7 r1 d
FROM dbo.characters 2 f' l3 Y M: n/ z
WHERE EXISTS (
4 J4 P! L7 M" `
SELECT 1
0 z3 {$ _3 P. h' u* X. ]8 v" W
FROM dbo.illegal_character_names
' K: M& O5 G J$ R% Z" T/ o# k( e. I
WHERE characters.name LIKE N'%' + illegal_character_names.partial_name + N'%'6 Y& _4 M1 l2 U7 Y$ q2 F
);

复制代码

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

EXEC sp_char_name_check @character_name, @v_ret OUTPUT
1 v1 Y$ P- H9 l5 h: M
2 N1 E: u9 O) K0 A* w) l$ K
IF @v_ret < 0 # b/ V" f" A8 P& q) ^! j
BEGIN A- s' \4 o' a$ `
SET @sp_rtn = @v_ret. F+ ]. \0 W; X0 b' T
RETURN, D: T) a/ R; ^( i/ w
END

复制代码

修改为：

IF (dbo.NameBlock(@character_name) = 1)
) r7 a/ l' d- j
BEGIN: p/ L2 F% T- Q0 L- E P: w" t* [
SET @sp_rtn = -12
. d# ^$ {( q9 r) m: I/ [3 P# p
RETURN" Q6 ^1 k, c8 [1 ]3 v2 h- |
END

复制代码

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

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

相关帖子