SQL修改角色名字限制

admin · 发表于 3 天前

建立新的限制表

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 (' X: a: X: g+ O, ^) a4 T5 o# a$ j
@character_name NVARCHAR(40)
; c( e r) k4 D& q4 x
)" g; R, z2 M* p# n7 d/ m, U
RETURNS TINYINT
Y) K( i) g3 t0 m% ?
AS
* B% V6 v$ W; o' |8 z5 t
BEGIN/ c7 y, Z( K; q1 P7 }, G( V3 E
DECLARE @result TINYINT = 0;
' H7 e4 g6 T Q; B
DECLARE @char NVARCHAR(1);
4 F* b' h2 u2 W2 I9 |
DECLARE @i INT = 1;7 A/ f/ E" _! y! a8 g% D5 S' F8 B
3 J$ O3 i+ v+ u& F5 c+ r
-- 遍历每个字符，检查是否合法
; f7 D8 E* o5 Y& B6 C: {" }4 V
WHILE @i <= LEN(@character_name)8 x2 W# Z& E7 s7 T7 y
BEGIN
$ P5 H6 K8 y( S( C6 T+ l& j) w
SET @char = SUBSTRING(@character_name, @i, 1);
' H( |" V; ^# q& C j* V1 W( A& _3 g
) A+ v. M- r0 s& E( S* P
-- 检查是否为中文、英文、数字或允许的特殊符号5 T/ n# @, V+ Z. d) v0 G
IF NOT (
( x2 G$ p8 `$ t# K8 H
-- 中文字符范围 (基本多文种平面)
{. d: T) B5 _
UNICODE(@char) BETWEEN 0x4E00 AND 0x9FFF OR) H7 U1 }, H; q* |* b" l% p
-- 英文字母和数字# d; ~) d8 z( X8 M% o
@char LIKE '[a-zA-Z0-9]' OR
/ x% J7 I+ R3 K4 G: a! w
-- 允许的特殊符号
! X6 _1 _; U% _" W9 Q: b' u7 W
@char LIKE '[_ -]'+ }7 x+ J. l b$ c
)/ l9 i; O6 Q0 b. p- [
BEGIN2 L2 p# g1 M( U. p" u( @
SET @result = 1;$ E2 H& `9 k# ?: `+ f# F
BREAK;' D( m# T2 }! ?( y! F9 t( N7 L
END: \% ]# ^9 d' u) p. q8 [
* Q0 C" m6 E. s: ~% J
SET @i = @i + 1;, H2 u3 p+ o3 O) Y8 y: O
END;% }" \* G: j) ]2 k0 s
: v# d) u7 A! T. b0 E
-- 检查是否在非法名称列表中
: _2 P/ N/ @2 X! [( F9 Q/ `
IF EXISTS (% A7 s" O4 N8 i4 X
SELECT 1 $ }' g9 b0 M: g' F' q6 ?; @
FROM dbo.illegal_character_names
+ Y: N0 |9 W$ i C: l. Y
WHERE @character_name LIKE '%' + partial_name + '%'
- J- L( J; s3 k8 y7 s- K
)6 U: e$ C1 R3 C R6 R3 x
SET @result = 1;
4 C; p8 Y/ I+ @
b% H% {# _: m9 r; ~! a6 ]2 h% k" |
RETURN @result;( m9 l6 x N/ j5 U. L* u' ?
END</span>- @" H# `. [- e# N! B

复制代码

插入屏蔽的字符

-- 插入非法名称列表（明确列名并使用N前缀）/ y8 b3 F: `/ _/ e9 w8 H9 F" m
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%****%');
) e( r6 {3 z8 z
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%nigg%');$ r' p2 x/ A" l6 P+ H' g( z8 ~
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%wank%');: Q( P7 o4 p. O7 f. E
INSERT INTO dbo.illegal_character_names (partial_name) VALUES (N'%whore%');- ~, F# P2 u8 w- x, s U {2 S+ c
( r) n& D0 i$ x, t! h
-- 示例：查询包含敏感词的角色名
8 h/ A4 Z+ V5 A I7 B
SELECT * / s2 i& d; _! k3 O
FROM dbo.characters
% q* @, e$ D7 }0 u3 K
WHERE EXISTS (9 j$ K R" `1 w/ ^. j; Z( g6 K: \; u
SELECT 1 & O% G3 U/ b& F4 y J
FROM dbo.illegal_character_names $ q0 X6 F) }9 r$ a4 K% F
WHERE characters.name LIKE N'%' + illegal_character_names.partial_name + N'%'
$ D# I$ }, R! Z2 F
);

复制代码

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

EXEC sp_char_name_check @character_name, @v_ret OUTPUT2 N, E0 X3 U5 ]3 O
# n& P1 X4 I' I" I% ~ ?; t
IF @v_ret < 0 + n: X4 {1 _' C3 w7 {& Q9 Q
BEGIN! G; ~; C" B* ^- X1 h% z
SET @sp_rtn = @v_ret
/ y8 z; F7 R) c8 [( X9 s8 R( m& Y
RETURN6 I# ?6 A9 @# R" e
END

复制代码

修改为：

IF (dbo.NameBlock(@character_name) = 1)
K, Y" J5 I& R! r3 _7 U Y, C
BEGIN
# d6 x, ^" m% D& t
SET @sp_rtn = -12
, ?3 F2 h; ~; U; y/ V) k
RETURN
; s. l4 P' I" R: h
END

复制代码

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

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

相关帖子