Postgres 的 Array 类型

mysql 不支持 Array 类型

---

一、Postgres 原生SQL#

---

适用场景：可以用于实现贴标签功能

1、定义#

---

Copy

CREATE TABLE "Students"
(
   name VARCHAR(255),
   interest VARCHAR(255)[]
)

2、插入#

---

(1) 方法一#

Copy

INSERT INTO "Students"
VALUES
('colin',
'{"音乐", "电影", "读书"}'
)

注意1：双引号单引号这里不能混用：'{"音乐", "电影", "读书"}'

注意2：插入后在数据库查看是{音乐,电影,读书} ，而不是{'音乐','电影','读书'}

(2) 方法二 —— 数组构造函数#

Copy

INSERT INTO "Students"
VALUES
('colin',
 ARRAY['音乐', '电影', '读书']
)

注意：这里只能用单引号：ARRAY['音乐', '电影', '读书']

3、访问#

---

(1) 取一个#

Copy

select interest[1] from "Students" where id = 1

注意：这里 interest[1] 不能用引号

return：

Copy

'音乐'

注：这里的数组索引值不是从 0 开始的

(2) 取多个#

Copy

select interest[1:2] from "Students" where id = 1

return：

Copy

{音乐,电影}

(3) 取所有#

Copy

select "interest" from "Students" where id = 1

return：

Copy

{音乐,电影,读书}

[拓展]

Postgres 引号 使用规律的总结：

不用引号：内置函数、当键名是数组而取索引值时
单引号：值、当键名是数组而取索引值时、json字段取value值("edu_experience"->'name')
双引号：表名、键名、值里面嵌套的值

4、修改#

---

Copy

update "Students" set interest[2] = '睡觉' where id = 1; 

{音乐,睡觉,读书} => {音乐,睡觉,读书}

5、搜索#

---

ANY()

Copy

select "interest" from "Students" where '睡觉' = ANY("interest");

return：

Copy

{音乐,睡觉,读书}

6、展开数组#

---

unnest()

Copy

select unnest("interest") from "Students" where '睡觉' = ANY("interest");

return：

Copy

音乐
睡觉
读书

二、与 Sequelize 协作#

---

Sequelize如何定义array类型？

Copy

interest: DataTypes.ARRAY(DataTypes.STRING),

原生的 Postgres 有两点：

1、数组的形式不是 [] 包裹而是 {}

2、数组的索引不是从 0 开始而是从 1 开始

3、[未知]用 navicat 查看 table design，本来 define 为 array 的 interest 字段显示的确是 varchar 类型

而用 Sequelize 很好的把这些差异给抹平了，你可以直接采用数组的方式去与数据库做交互操作

---

参考资料

1.[Postgres 指南-数组] http://postgresguide.com/cool/arrays.html
2.[PostgreSQL Array] http://www.postgresqltutorial.com/postgresql-array/