Postgresql 隐式转换 — 数据库也来一次 “中保研”

快要过年了,此篇作为2019年最后一篇的技术文字,年后还有一批正在路上,感谢大家一年多的关注。

——————————————————————————————

汽车界最近最热的话题是,中保研碰撞试验 ,所以数据库是不是也应该来一次碰撞测试,继 SQL SERVER ,和 MYSQL 的隐式转换文字后,POSTGRESQL 也撞撞看。

作为一个未来更自由和先进的数据库,部分(传统)企业会选择一种理想的数据库及原型,别的数据库遇到的问题,在POSTGRESQL 存在吗,那如何注意,实际上隐式转换会给我们使用POSTGRESQL 带来什么影响这是需要了解和清楚的。

这里我们要列出的是POSTGRESQL 支持的数据常用类型(因为POSTGRESQL 支持的类型比较多,并且有些不是传统DBA 能理解的类型(例如范围类型),这次仅仅对字符类型进行“碰撞”)

当然其中的含义可不仅仅是弄清楚,这里还有更深层次的一层需求。(不建议使用char类型,原因请参见之前的文字)

目前postgresql 存储的数据的字符类型主要有三种 varchar char 和 text

1 先看一下这三种类型的隐式转换是否存在,如果存在并且给错对应的类型会有什么结果。

create table conver_test (id SERIAL primary key, info char(500),info2 varchar(500),info3 text, c_time timestamp);

insert into conver_test (info,info2,info3,c_time) select md5(random()::char),md5(random()::varchar),md5(random()::text),clock_timestamp() from generate_series(1,100000);

create index ix_conver on conver_test (info,info2,info3);

在建立好测试的数据后,我们开始下一步的探索实验

1 char 的类型如果接受到varchar 的字符还是否可以走索引 (info 为 char , info2 varchar, info3 text)

char 的字段接受 varchar 的类型是可以走索引的

char 的字段接受本身的类型是可以走索引的

char 的字段接受text 是不可以走索引

2 作为常用的varchar 字段类型,在接受不同的类型会有什么反应

1 接受本身同样的字符类型,一定是能走索引的

2 而如果将字符类型更换为char , 那就不走索引了,开始走了全表扫描

3 最后将将字符的类型定义为text ,结果很有趣的走了索引

3 最后就是text 字段的形式了

结果出人意料,啥格式的都能走索引。

这里要清楚一个概念,就能大概了解到 CHAR VARCHAR 为什么互不给对方面子,CHAR 类型的在定义大小后,不足的位置是要进行空格补齐的,而varchar 不会,或许就是双方不给面子的一个原因。

到这里先小结一下,POSTGRESQL 数据库和别的数据库在字符存储方面还是可圈可点的,例如在别的数据库中提出的text类型相对于其他的类型虽然存储的内容要多,但是性能要低的情况,在POSTGRESQL 是不存在的,也就是你使用char , varchar ,text 存储大小相同的字符类型的时候,除了char 的性能可能会稍微低一些,varchar , text 是不会有性能上的差别(同等字符量上,但存储的数据结构不同)这也就造成很多表设计中的字符大部分都是text 上面的结果也看到了,这可能就是原因之一。

另外最近在群里面讨论的一个问题,关于一个字符,占用几个字节,POSTGRESQL 如果你使用的字符集是 utf8 就想当一个字会占据(1-4)个字节,并且包含汉子不会在有乱码的可能。UTF8这是一种unicode 的编码,所以要尽量让你的客户端和服务器端的字符集都是utf8 这样一般是不会出现乱码的问题。(每种数据库是不一样的,MYSQL 是 UTF8 可和POSTGRESQL 的UTF8 不是一码事)

其中PostgreSQL扫描器/解析器将词法元素分为五个基本类别:整数、非整数、字符串、标识符和关键字。大多数非数值类型的常量首先被归类为字符串。SQL语言定义允许使用字符串指定类型名称,并且在PostgreSQL中可以使用这种机制来按照正确的路径启动解析器。

那我们在不指定类型的情况下,POSTGRESQL 的隐式转换又会是怎样的情况

很显然在POSTGRESQL 每种类型的自动隐式转换都是令人满意的。

另外纯属个人感受(仅仅代表个人此时此刻的知识和能力范畴),POSTGRESQL 的字符处理方面尤其在使用了text类型来处理字符,相对比其他的数据库系统来说是有优势的(从扩展性,处理速度,限制 对比其他的数据库),上面的部分结果也能对以上的看法有所支持。