MySQL特殊字符过滤替换

---

MySQL特殊字符过滤替换

---

1. 打印特殊字符/ 简单转换

 /* MYSQL 特殊字符*/
 select '中', HEX('中'), substring('中',1,1) , char_length('中'), length('中'), char(0xE4B8AD using utf8mb4)
 union all
 select '𢈈', HEX('𢈈'), substring('𢈈',1,1) , char_length('𢈈'),  length('𢈈'), char(0xF0A28888 using utf8mb4)

select x.*, hex(x.c1), hex(x.c2),'𢈈', HEX('𢈈'), hex(convert('𢈈' using utf16))
from(
select char(0xd840dc00 using utf16) c1, char(0xd848de08 using ucs2) c2
)x
where hex(x.c2)>='D840DC00' and hex(x.c2)<=upper('D87EDE1D')

2. 自定义函数: 替换特殊字符

CREATE ` FUNCTION `bi_f_convert_usergroupName_acl`(usergroupName text CHARSET utf8mb4) RETURNS text CHARSET utf8mb4
begin
	declare v_result text CHARSET utf8mb4 default '';
	declare v_char varchar(1000) CHARSET utf8mb4;
	declare v_hex varchar(1000);
	declare i integer default 1;
	declare v_len int;

	set v_len =char_length(usergroupName);
	if v_len > 0 then
		while(i <= v_len) do
			set v_char = substring(usergroupName,i,1);
			/* 转换逻辑, 使用类似JAVA unicode literal来校验是否中文 */
			set v_hex=hex(convert(v_char using utf16));
			
			if(v_hex in ('FF08', 'FF09','0023')) then  
				/*
				 * 几个单独的字符转换
				 */
				if(v_hex='FF08') then set v_char='('; end if;
				if(v_hex='FF09') then set v_char=')'; end if;
				if(v_hex='0023') then set v_char='号'; end if;
			else	
				/*QBI用户组允许的字符: Input string format error, only Chinese, English, numerals, underscores, slashes, backslashes, vertical lines, parentheses, middle brackets are allowed*/
				if(
						
					(v_hex>='0041' and v_hex<='005A') /* A-Z */
					or
					(v_hex>='0061' and v_hex<='007A') /* a-z */	
					or
					(v_hex>='0030' and v_hex<='0039') /* 0-9 */	
					or
					(v_char in ('_','/','\\','|','(',')','[',']')) /* underscores, slashes, backslashes, vertical lines, parentheses, middle brackets*/
					or
					(v_hex>='2E80' and v_hex<='2FD5')
					or
					(v_hex>='3190' and v_hex<='319F')
					or
					(v_hex>='3400' and v_hex<='4DBF')
					or
					(v_hex>='4E00' and v_hex<='9FCC')
					or
					(v_hex>='F900' and v_hex<='FAAD')
					or
					(v_hex>='D840DC00' and v_hex<='D87EDE1D')
				) then 
					set v_char=v_char;
				else 
					-- v_char=v_hex; -- 调试
					set v_char='_';
				end if ;
			end if;
		
			if(v_result = '') then
				set v_result=v_char;
			else
				set v_result=concat(v_result,v_char);
			end if;
			set i= i+1;
		end while;
		return v_result;
	else
		return '';
	end if;
end