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

本文主要是介绍postgresql中控制符带来的数据错觉，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

简介

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

错觉展示

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

控制符介绍

Seq	十进	十六进	缩写	字符名
	0	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

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

解决办法

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

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

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