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 (* v; b; P( G$ ]1 ?: g
@character_name NVARCHAR(40)4 x2 I* w2 x- n2 J8 M2 B6 H) z# m" z
)
, U% ~0 w- H6 Y' y) d
RETURNS TINYINT8 e9 k' ], C+ S
AS4 c2 D+ C8 c% m( }3 t- D1 U7 g+ P
BEGIN8 s+ n$ i2 R+ w. K% n
DECLARE @result TINYINT = 0;! \" x6 }4 s. @/ Z3 r# A; E
DECLARE @char NVARCHAR(1);
6 z1 X( d( m/ Y' f/ b8 }% C
DECLARE @i INT = 1;
# [; @$ h& Q& g+ V
" K0 U' p- O/ W" x
-- 遍历每个字符，检查是否合法0 f5 B1 `9 M, ]
WHILE @i <= LEN(@character_name)
$ n, t9 F7 H7 ]6 d7 B1 G+ s
BEGIN
+ `/ w% f# [/ [ b
SET @char = SUBSTRING(@character_name, @i, 1);! `6 f+ ?# B5 |3 z+ o
2 {3 F, B5 ]5 c( A3 n) i$ d
-- 检查是否为中文、英文、数字或允许的特殊符号
, |; o& G; A3 j8 c
IF NOT (( e' V" f4 }% A5 [" W1 T6 V
-- 中文字符范围 (基本多文种平面)
0 `9 L. ^- W* \7 u* u+ g
UNICODE(@char) BETWEEN 0x4E00 AND 0x9FFF OR
4 Y- Q+ v* p. } b
-- 英文字母和数字
5 M% q2 H( _" H3 P
@char LIKE '[a-zA-Z0-9]' OR
( F2 Y9 c# D2 b: f
-- 允许的特殊符号6 r+ B" l7 r; U
@char LIKE '[_ -]'
/ ]# b& ]# `; w) r% N) x4 G+ s. ^+ t
)5 ^: N4 \ i6 k. ^( w$ D+ E
BEGIN
: K' z5 O0 L6 I1 Z
SET @result = 1;1 v0 g3 r0 v, u4 t
BREAK;
: S8 N) J7 D/ |6 y7 M
END' a: n! x* X" H* V2 S
" ~" `. c, ]0 I8 F
SET @i = @i + 1;# s$ S# s9 S& J8 B: T7 m% ~
END;
Z% v' |, w# b( _% f0 ~$ x1 M
- b; H1 n; b! Y+ I0 ?6 e
-- 检查是否在非法名称列表中4 A9 w: d9 d$ O% K& g8 @3 a5 m
IF EXISTS (/ H& W. W6 O; v3 X" x- J9 b4 n. H
SELECT 1
% Q% U; m# a7 M
FROM dbo.illegal_character_names
, t0 g" S' B( M8 Z4 j$ D
WHERE @character_name LIKE '%' + partial_name + '%'
w0 E ^5 d9 B- H, k: s
)6 \! ?+ p" l( c8 A% p; ]1 [$ A/ }6 F
SET @result = 1;
4 z/ X$ F# |+ ^( V* j: P0 H. S# W
8 h0 p; a+ G% G5 P8 {+ J; k/ O
RETURN @result;
r& B& K; V* R
END</span>
' y. o2 o. J; g8 `3 _2 I t! x2 L

复制代码

插入屏蔽的字符

-- 插入非法名称列表（明确列名并使用N前缀）$ E0 b$ } ]/ T2 z% }5 }
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%****%');, ` q; m. e' u# n) V1 X
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%nigg%');4 [, M; s; o `8 \% V
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%wank%'); W5 v: T1 H% ]1 v* m/ ?' @1 S! t
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%whore%');; x9 o$ ?! J1 b( _& O5 ]+ \2 f
2 r+ J2 ^! ]1 r' t/ I
-- 示例：查询包含敏感词的角色名
1 x2 J" A. O- u2 ~! O+ h
SELECT * # |, U6 Z# S" O
FROM dbo.characters
1 D; e$ o, s; T- j6 s
WHERE EXISTS (, S9 {: o) X7 b' A3 a( @& ~2 ?6 Z
SELECT 1 - d. R: U9 I5 P, @0 |2 c" j
FROM dbo.illegal_character_names
" v; c% s2 N9 Y9 d
WHERE characters.name LIKE N'%' + illegal_character_names.partial_name + N'%'
4 u* e; I4 f; J$ b: P: V0 I! E8 c
);

复制代码

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

EXEC sp_char_name_check @character_name, @v_ret OUTPUT& ?! J N# q) o+ s$ I- x* t4 {; O* q
3 X5 m( S: Z) W( f% X G
IF @v_ret < 0 ; I* l6 a" B' L3 L/ \1 [
BEGIN& O; n$ y. o3 k
SET @sp_rtn = @v_ret
& l/ D' K: _, H# @$ U8 S
RETURN
5 ^7 o, B0 S$ b" U
END

复制代码

修改为：

IF (dbo.NameBlock(@character_name) = 1)5 R. |; O' G, p# V4 F8 k2 E2 ?
BEGIN, ]$ ?$ U$ b6 [) x/ {
SET @sp_rtn = -124 N3 B8 N i# ?9 `9 _* k
RETURN+ c6 Y7 i3 j4 s% d- D+ I, M0 K
END

复制代码

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

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

相关帖子