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 (
+ C! |% I6 [* c2 N. j, ^
@character_name NVARCHAR(40)7 i7 o$ Z+ M7 l2 X' _: P
)) O. b/ _. T; s9 X# N% `, M; v
RETURNS TINYINT3 z K% y2 F4 U$ W1 Z, w
AS7 B+ U3 u( M# j0 O- s
BEGIN+ ?# R5 ~5 {3 ?( t. [) [9 l, T
DECLARE @result TINYINT = 0;. H- y; K+ I) ~. |& x0 X
DECLARE @char NVARCHAR(1);
/ ^/ u, i% I1 E+ M
DECLARE @i INT = 1;
) {2 d% K$ _9 y0 G1 c" l
2 b9 O$ F) O% U7 v
-- 遍历每个字符，检查是否合法
; D1 {. `$ e4 k, x; {
WHILE @i <= LEN(@character_name)) k X$ O: O: @; \
BEGIN( k. Q4 G1 }0 L6 \
SET @char = SUBSTRING(@character_name, @i, 1);
N8 S% C4 F& V. ]( u3 I
* s3 c3 ^& a; n/ |0 G
-- 检查是否为中文、英文、数字或允许的特殊符号
2 [' ?6 }7 i9 O( s; h: D
IF NOT (
& A2 Q9 ^4 d" c: n
-- 中文字符范围 (基本多文种平面)' ^0 f3 c8 z0 j/ X7 f
UNICODE(@char) BETWEEN 0x4E00 AND 0x9FFF OR
+ v+ d' P% p5 F
-- 英文字母和数字
' Z! x% X( @* z: V0 H
@char LIKE '[a-zA-Z0-9]' OR0 z8 e: G! s, A, A
-- 允许的特殊符号, t' Y+ {* m/ h0 G6 y
@char LIKE '[_ -]'
# J* j4 c; A0 r! }; U
)$ R; h2 p- `: K. _0 i# A0 t0 _/ v& A
BEGIN3 S; B, k8 b6 r# y! q/ k7 \; r
SET @result = 1;
5 y0 h5 V7 a1 ~0 J3 ]- v
BREAK;
$ I x# T, r( d l
END' X% e, ?/ b* ^4 q# T
" Z( b+ n* a. I: b5 n
SET @i = @i + 1;
) R! @, {# j0 n+ }+ B
END;
8 F. H7 Z; p2 _. N8 k) @' e# ~
2 W9 u: I U) E; H$ c
-- 检查是否在非法名称列表中
( L$ S% T) z* a% s
IF EXISTS (
( t3 {: E$ k" R# m! |9 `6 F
SELECT 1 " v T& ~6 ~" H$ [9 h$ i: J
FROM dbo.illegal_character_names 9 y+ e, A+ o2 J
WHERE @character_name LIKE '%' + partial_name + '%'0 v3 | D! c( X( A4 z& Q
)
Y1 @. [* e8 i# x$ C
SET @result = 1;
( }( e- k9 Q9 W$ p! r; ^
" _2 S# I! b( T
RETURN @result;: L) A8 s4 _8 e0 J) n; ^: _
END</span>
a' T, l/ }' @ G% j

复制代码

插入屏蔽的字符

-- 插入非法名称列表（明确列名并使用N前缀）# `; o8 b# Z5 R$ ^4 R
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%****%');* c% E( x+ [5 F! _7 M
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%nigg%');4 s3 h& v, G7 K; n3 Y- P
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%wank%');) Q$ R/ Q ]( r: \
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%whore%');
\9 T3 h# L* H' q) _' Y* U2 w
# A! a! u' r m: J: H! u
-- 示例：查询包含敏感词的角色名4 ?; ^5 Q1 w: G
SELECT * ' r" ~$ n6 b! j/ A
FROM dbo.characters ( S! N* Z0 t) d' i `4 |& p7 s
WHERE EXISTS (
- N6 H8 s9 m2 k6 B7 i3 C
SELECT 1
6 X5 [# G7 Y) U/ ]7 L+ S
FROM dbo.illegal_character_names
$ v( D9 O9 `& V7 C; e- k; F
WHERE characters.name LIKE N'%' + illegal_character_names.partial_name + N'%'
" N* g# ?4 z$ G9 n8 B2 I, }; `( F2 w
);

复制代码

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

EXEC sp_char_name_check @character_name, @v_ret OUTPUT6 f7 Q# G# i0 y
5 R4 H" G& M+ J
IF @v_ret < 0 ! W* ?4 B( N3 G) Y8 a0 P8 k
BEGIN
8 e+ g C, e I# G0 S9 [9 Z- c$ y
SET @sp_rtn = @v_ret; G. m1 R9 q/ m& D. v
RETURN1 J- @! X. o* U( j5 q
END

复制代码

修改为：

IF (dbo.NameBlock(@character_name) = 1)
$ t: y& m- V9 [) W5 I' I
BEGIN; C3 b% F5 n9 q$ F- n
SET @sp_rtn = -12
# }2 \, l1 a c5 I
RETURN% \. N$ p& n& P+ S, a
END

复制代码

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

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

相关帖子

浏览过的版块