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 (
# [& D) j0 x; M- w# K
@character_name NVARCHAR(40)" a3 f9 r6 F; f) q: [% i- h. S4 I
), v( d7 j6 u" F: S
RETURNS TINYINT
0 W& O* U/ D+ e( f
AS
# j8 \7 m. d) D- n* a' \: P' t3 @
BEGIN
, g7 f( H2 y9 p7 x# D/ u9 J
DECLARE @result TINYINT = 0;
7 j8 O; h: z/ D% d5 v. H
DECLARE @char NVARCHAR(1);
7 q( c9 n! V7 {0 C2 c
DECLARE @i INT = 1;
8 x/ G- K9 q1 T3 K* E/ Z
$ O; ]3 Y4 B# P( r9 }
-- 遍历每个字符，检查是否合法
6 T$ T4 F- }0 ]. W& a5 q
WHILE @i <= LEN(@character_name)
. |$ j2 n4 Z3 m6 U$ c
BEGIN' n" h7 q; g: x0 z* k, {$ |
SET @char = SUBSTRING(@character_name, @i, 1);
* v0 w' d! Q3 b |) i
, _0 D! i7 y% K5 p" P2 U% y
-- 检查是否为中文、英文、数字或允许的特殊符号
+ U8 b. A) s/ i2 w
IF NOT (" I) B5 R8 b( V) }) A6 e2 p) v: |
-- 中文字符范围 (基本多文种平面)
1 }# w7 p$ I8 Y( \# V$ ?
UNICODE(@char) BETWEEN 0x4E00 AND 0x9FFF OR
2 w1 D) a x6 b. R) o* S: ]3 _
-- 英文字母和数字
+ J2 W$ ^ f+ O& L, m9 l/ D
@char LIKE '[a-zA-Z0-9]' OR7 q2 d4 \' B K' r1 X
-- 允许的特殊符号3 Y6 K: \" W# r4 {1 R6 _
@char LIKE '[_ -]'
" n; m# E% _& _8 n
)! l Q4 Z. m( Y+ f% O3 \: i0 H8 h
BEGIN, A. P, B- ?* S! H
SET @result = 1;
2 |* z1 L: U" o) l! Y' t3 s1 a
BREAK;
# T- P) Z8 L5 I4 [
END+ r' c9 `5 d4 _- `
9 M2 y) L4 j: @& [" _3 `6 U
SET @i = @i + 1;
: b ]) a1 p y3 X0 ^
END;
0 O- Y) _6 R- T' X
6 `- W' F$ |8 \0 O9 c: G
-- 检查是否在非法名称列表中$ I7 d9 u% p) N: r! w. s: v
IF EXISTS (
& P" D* }4 g3 R2 L# {
SELECT 1
" c# D" F# A. W9 C9 a1 o
FROM dbo.illegal_character_names
; |" p8 R9 B1 s+ j
WHERE @character_name LIKE '%' + partial_name + '%'
: T5 j+ X4 N9 d/ H, [& b
)
! X0 [# q+ c ]
SET @result = 1;- A7 f! f n6 ~2 Y% ^! w. t
4 K" N( a! i- b+ @( j" s1 t2 e
RETURN @result;
7 x* P4 p7 q D5 Z: }, z" v8 F) D6 @
END</span>
& I* L4 @5 p# G. V, V

复制代码

插入屏蔽的字符

-- 插入非法名称列表（明确列名并使用N前缀）+ b: e' O0 t+ v: Z" S o
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%****%');
( f" U$ q2 B, I. i
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%nigg%');
% [* H! b5 |; O# Q/ Q: O( |- q/ ^
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%wank%');
" l( {1 \2 [- V& e i- J7 d- L
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%whore%');9 {9 ?: s2 q, h% Y& {
. ] W$ [; y _
-- 示例：查询包含敏感词的角色名4 u' V! t$ o" X4 l2 a6 G
SELECT * " x+ |/ f+ v! h
FROM dbo.characters
5 ?1 c# ?4 n4 s. E. b) a* _8 W
WHERE EXISTS (
0 T- S: ?! p/ c4 @, V
SELECT 1 , W' V! V. V& R
FROM dbo.illegal_character_names
6 q) k @- u! a- j$ @7 P. Q/ N( Q
WHERE characters.name LIKE N'%' + illegal_character_names.partial_name + N'%'6 I" M3 }+ ]3 G$ M, W. }
);

复制代码

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

EXEC sp_char_name_check @character_name, @v_ret OUTPUT3 n: ~! B8 R& i1 n H
3 J! r: c% G; u4 z7 B
IF @v_ret < 0 & B% y5 w2 f+ T% c8 y
BEGIN
' Q, g- ~. H: x
SET @sp_rtn = @v_ret, d' v8 b' E2 @' a- x
RETURN' J% Y; c) a; \+ U9 Y W
END

复制代码

修改为：

IF (dbo.NameBlock(@character_name) = 1)- V4 U, _. K3 P" U9 x( J+ g
BEGIN+ ]/ ?( o+ h% }, G" k! A( o
SET @sp_rtn = -12
! F! Q4 _1 p3 X
RETURN- X2 D+ g% `# x9 ~- W8 Z ]' A7 L
END

复制代码

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

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

相关帖子