postgresql中控制符带来的数据错觉

简介

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

错觉展示

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

控制符介绍

插入控制符

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

 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  

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

解决办法

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

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

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