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 (
* q$ J- E8 N4 w9 T( }' N8 c* E
@character_name NVARCHAR(40)5 g3 U4 ~- {3 |- X% X
)
; k3 K7 r8 [5 a, }/ F5 w V3 p1 C
RETURNS TINYINT g- p% t1 S" A) K5 a$ A
AS: }6 g( C5 Y' M7 d+ ^8 s
BEGIN
/ ^ O2 H9 K9 n, ?' |
DECLARE @result TINYINT = 0;
9 p9 y3 Q$ [9 i/ G
DECLARE @char NVARCHAR(1);
& T7 c3 o8 V9 v* i, z- N# r
DECLARE @i INT = 1;9 I; h- V, ~9 [3 r# J
! I1 i0 {( t2 ~% u4 R
-- 遍历每个字符，检查是否合法
7 s: H) N6 [# `7 d: I
WHILE @i <= LEN(@character_name)
& P+ j# H: r, E# V# w+ D" l. m
BEGIN
% C) `( p6 J5 K6 g
SET @char = SUBSTRING(@character_name, @i, 1);; @* X& I" l$ Q/ _1 e
. ?! F1 q' `+ X Y0 F9 \
-- 检查是否为中文、英文、数字或允许的特殊符号4 q8 B- }, i1 Q( j- O7 p
IF NOT (
2 O6 e! G4 d% |2 G4 p5 d
-- 中文字符范围 (基本多文种平面)
h' S) a3 Z& T/ u
UNICODE(@char) BETWEEN 0x4E00 AND 0x9FFF OR/ G _- s: B6 ^& Q: t( s
-- 英文字母和数字; K" f6 Q! O8 U6 o1 ]
@char LIKE '[a-zA-Z0-9]' OR
4 z+ P4 A' N) t. `8 S1 _
-- 允许的特殊符号
: F& W- R* S" O
@char LIKE '[_ -]', d5 V$ I# O Q& J( V3 S* Y. l
)
/ C" r5 B. E. G* w& C) _/ t
BEGIN9 ^/ E2 A# ~$ u/ O
SET @result = 1;0 N3 {) O6 W& I O
BREAK;
: [6 M. @, h9 g) U- m
END
+ U+ Y$ x- U- d( D
% [& B+ a% M% X) e S2 g
SET @i = @i + 1;
% d) C( N5 I _0 }
END;
1 j2 Y a. b7 u) H+ ?! O
1 j* \' h) D. E9 T
-- 检查是否在非法名称列表中" T# T0 Z+ k6 N, B5 Q2 I2 j& `
IF EXISTS (
" I# s) h& B- X! `* W6 g1 C% P
SELECT 1 ( G# z$ i) `5 [5 b; k }
FROM dbo.illegal_character_names
: u! f( S! Q# \( z& Q
WHERE @character_name LIKE '%' + partial_name + '%'5 f9 J+ J% n) P' R. k
)" X1 G0 s m. P9 y( a
SET @result = 1;
: h) F8 K' T! p% H
1 r, m* b: f/ V9 ~1 u
RETURN @result;) f, R. e e* q4 ^1 `1 W; B& P
END</span>
+ I' @6 @( M3 F% F" o) t

复制代码

插入屏蔽的字符

-- 插入非法名称列表（明确列名并使用N前缀）8 [: n2 G+ l+ ]6 N
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%****%');
' k: C# H! B8 s7 L% c7 c
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%nigg%');0 f P0 M, E; C' f! r
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%wank%');, {/ H( ], `! S, N
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%whore%');" o* e9 s/ K2 K1 T
3 X3 a/ K7 m% L" W
-- 示例：查询包含敏感词的角色名% `$ ]: a6 Z! `9 \+ T" P1 G! U2 m1 G
SELECT *
) C% ]% O! p O7 j, U/ |
FROM dbo.characters
2 u V6 p# P2 o1 M, R
WHERE EXISTS (
8 k9 r0 v$ K% j& d' C* W! J' y( _% v& D
SELECT 1 7 H1 y. k4 e7 R$ @
FROM dbo.illegal_character_names
3 v2 }) Y. {+ n" v! ?( k* x* ~
WHERE characters.name LIKE N'%' + illegal_character_names.partial_name + N'%'
# o7 C7 f7 A4 v6 l0 Y
);

复制代码

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

EXEC sp_char_name_check @character_name, @v_ret OUTPUT
5 j5 q9 S% a. g5 n# v
0 J3 h" R- { K6 k
IF @v_ret < 0
* ]) M) Y: t8 d5 \ Q3 |* ~
BEGIN* M& V$ k6 `9 g& A
SET @sp_rtn = @v_ret; l4 |4 S- Z. E* y( c0 x
RETURN
0 l- t1 O4 X& @. ?. }) J8 l0 E
END

复制代码

修改为：

IF (dbo.NameBlock(@character_name) = 1)6 B- s1 t% t1 X
BEGIN
1 K4 A V2 u+ M8 j( {
SET @sp_rtn = -129 u( @$ p* J; I2 @
RETURN
& u. s) a8 e1 b" [6 u( f
END

复制代码

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

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

相关帖子

浏览过的版块