简介

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

错觉展示

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

postgresql中控制符带来的数据错觉_十六进制

控制符介绍

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

postgresql中控制符带来的数据错觉_分隔符_02

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

解决办法

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

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

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

postgresql中控制符带来的数据错觉_分隔符_03