简介

在数据库字符集中，由于数据质量的控制不够完善，每一个字符集都并不是所有字符的能看见，有些字符的展示可能会出现乱码，甚至出现不同字符展示成同样效果的可能，给开发人员造成分析错觉。 当数据库存入了控制符，类似制表符,展示的效果竟然和空格效果一样，而且还无法trim掉，对于开发人员可能会带来视觉错觉。

错觉展示

此时表中存入了一个制表符，展示看着是空格，而且长度为1，展示的效果与空格一样，常人根本无法一眼看出问题所在，如果用在表关联中还会导致数据无法关联上。 b59bff5b3a052ca818490eb5f65eb75.png

控制符介绍

Seq 十进 十六进 缩写 字符名
0x00 NUL Null (空)
^A 1 0x01 SOH Start of Heading (报头开始)
^B 2 0x02 STX Start of Text (正文开始)
^C 3 0x03 ETX End of Text (正文结束)
^D 4 0x04 EOT End of Transmission (传输结束)
^E 5 0x05 ENQ Enquiry (查询)
^F 6 0x06 ACK Acknowledge (确认)
^G 7 0x07 BEL Bell (振铃)
^H 8 0x08 BS Backspace (退格)
^I 9 0x09 HT Horizontal Tab (水平制表)
^J 10 0x0A LF Line Feed (换行)
^K 11 0x0B VT Vertical Tab (垂直制表)
^L 12 0x0C FF Form Feed (换页)
^M 13 0x0D CR Carriage Return (回车)
^N 14 0x0E SO Shift Out (移出)
^O 15 0x0F SI Shift In (移入)
^P 16 0x10 DLE Data Link Escape (数据链路转义)
^Q 17 0x11 DC1 Device Control 1 (设备控制1)
^R 18 0x12 DC2 Device Control 2 (设备控制2)
^S 19 0x13 DC3 Device Control 3 (设备控制3)
^T 20 0x14 DC4 Device Control 4 (设备控制4)
^U 21 0x15 NAK Negative Acknowledge (否认)
^V 22 0x16 SYN Synchronous Idle (同步空闲)
^W 23 0x17 ETB End of Transmission Block (传输块结束)
^X 24 0x18 CAN Cancel (取消)
^Y 25 0x19 EM End of Medium (介质结束)
^Z 26 0x1A SUB Substitute (替换)
^[ 27 0x1B ESC Escape (转义)
^| 28 0x1C FS File Separator (文件分隔符)
^] 29 0x1D GS Group Separator (分组符)
^^ 30 0x1E RS Record Separator (记录分隔符)
^_ 31 0x1F US Unit Separator (单元分隔符)
127 0x7F DEL Delete (删除)

插入控制符

此时我将控制符插入到表中去，展示看看控制符在数据库中呈现效果。

 DROP TABLE IF EXISTS public.hf;

CREATE TABLE public.hf (
	十进制编码 varchar NULL,
	十六进制编码 varchar NULL,
	缩写 varchar NULL,
	字符名 varchar NULL,
	展示效果 varchar NULL
);

插入数据

-- insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '0','0x00','NUL','Null (空)',E'\x00';   --  该编码无法插入到数据库中，暂时不参与讨论
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '1','0x01','SOH','Start of Heading (报头开始)',E'\x01';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '2','0x02','STX','Start of Text (正文开始)',E'\x02';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '3','0x03','ETX','End of Text (正文结束)',E'\x03';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '4','0x04','EOT','End of Transmission (传输结束)',E'\x04';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '5','0x05','ENQ','Enquiry (查询)',E'\x05';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '6','0x06','ACK','Acknowledge (确认)',E'\x06';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '7','0x07','BEL','Bell (振铃)',E'\x07';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '8','0x08','BSB','ackspace (退格)',E'\x08';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '9','0x09','HTH','orizontal Tab (水平制表)',E'\x09';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '10','0x0A','LF','Line Feed (换行)',E'\x0A';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '11','0x0B','VT','Vertical Tab (垂直制表)',E'\x0B';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '12','0x0C','FF','Form Feed (换页)',E'\x0C';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '13','0x0D','CR','Carriage Return (回车)',E'\x0D';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '14','0x0E','SO','Shift Out (移出)',E'\x0E';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '15','0x0F','SI','Shift In (移入)',E'\x0F';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '16','0x10','DLE','Data Link Escape (数据链路转义)',E'\x10';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '17','0x11','DC1','Device Control 1 (设备控制1)',E'\x11';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '18','0x12','DC2','Device Control 2 (设备控制2)',E'\x12';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '19','0x13','DC3','Device Control 3 (设备控制3)',E'\x13';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '20','0x14','DC4','Device Control 4 (设备控制4)',E'\x14';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '21','0x15','NAK','Negative Acknowledge (否认)',E'\x15';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '22','0x16','SYN','Synchronous Idle (同步空闲)',E'\x16';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '23','0x17','ETB','End of Transmission Block (传输块结束)',E'\x17';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '24','0x18','CAN','Cancel (取消)',E'\x18';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '25','0x19','EM','End of Medium (介质结束)',E'\x19';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '26','0x1A','SUB','Substitute (替换)',E'\x1A';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '27','0x1B','ESC','Escape (转义)',E'\x1B';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '28','0x1C','FS','File Separator (文件分隔符)',E'\x1C';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '29','0x1D','GS','Group Separator (分组符)',E'\x1D';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '30','0x1E','RS','Record Separator (记录分隔符)',E'\x1E';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '31','0x1F','US','Unit Separator (单元分隔符)',E'\x1F';
insert into hf(十进制编码,十六进制编码,缩写,字符名,展示效果)select '127','0x7F','DEL','Delete (删除)',E'\x7F';

查看数据库展示效果

select a.*,length(展示效果) ,length(trim(展示效果)) from  hf a

image.png

部分控制符展示为NULL 实际上占用有一个字节长度，使用trim去空实际上并无法去掉。

解决办法

首先最优解还是需要在数据输入端，严格规范的控制数据录入的质量。 如果诸如此类控制符仍然被录入表中，多数情况需要人工发现， 对其进行替换掉

select a.*,length(展示效果) ,length(trim(展示效果)),regexp_replace(展示效果, '[[:cntrl:]]', '', 'g') ,length(regexp_replace(展示效果, '[[:cntrl:]]', '', 'g')) from  hf a

此时使用regexp_replace()函数对其进正则替换，可以正则表达式，替换指定的控制符。 image.png