命名空间隔离：不同模式下的对象可以使用相同的名称，而不会发生冲突。例如，可以在模式A下创建一个名为"users"的表，在模式B下也可以创建一个名为"users"的表，它们互不冲突。
访问控制：可以通过授权不同用户或角色对不同模式中的对象进行访问，从而实现更细粒度的权限控制。
代码组织：将相关的数据库对象组织在同一个模式下，便于维护、管理和查询。

一、Mysql和PostgreSQL语法比较

1.数据类型差异

1.1整型

MySQL中的TEXT数据类型最大存储容量为64KB，而PostgreSQL中的TEXT类型没有此限制。
MySQL中使用TINYINT、MEDIUMINT和INT表示不同大小的整数，而PostgreSQL使用SMALLINT、INT和BIGINT

1.2浮点数类型

MySQL 提供了 FLOAT 和 DOUBLE 类型来表示浮点数，可以指定精度。
PostgreSQL 提供了 REAL 和 DOUBLE PRECISION 类型来表示浮点数。

1.3字符串差异

MySQL中可以使用''或""来表示字符串，而PostgreSQL只接受''来表示字符串。
PostgreSQL使用E'…' 来表示带有转义序列的字符串。
双引号在 PostgreSQL 中用于引用标识符（如表名、列名等），而不是字符串值。
MySQL 使用 VARCHAR 来表示可变长度的字符串，例如 VARCHAR(255)，VARCHAR(n)最大只能是64kb。而postgresql中的VARCHAR(n) 最大可以存 1GB。
PostgreSQL 使用 VARCHAR 或 TEXT 来表示可变长度的字符串，postgresql中的TEXT相当于MySQL中的longtext。

1.4数组类型

MySQL 不直接支持数组类型，但可以使用逗号分隔的字符串来模拟数组。
PostgreSQL 支持原生的数组类型，如，integer[]、varchar[]等。

2.字符串连接

在MySQL中，可以使用"+"或CONCAT函数来连接字符串，而在PostgreSQL中，可以使用"||"来连接字符串。

-- 使用双竖线进行字符串连接
SELECT 'Hello' || ' ' || 'World';  -- 输出结果为 'Hello World'

-- 使用 CONCAT 函数进行字符串连接
SELECT CONCAT('Hello', ' ', 'World');  -- 输出结果为 'Hello World'

-- 直接使用空格符号进行字符串连接
SELECT 'Hello' ' ' 'World';  -- 输出结果为 'Hello World'

3.日期和时间函数差异

MySQL使用NOW()来获取当前日期时间，而PostgreSQL使用CURRENT_TIMESTAMP或CURRENT_DATE来获取。
MySQL使用DATE_ADD()、DATE_SUB()等函数来添加或减少日期时间，而PostgreSQL使用INTERVAL进行类似操作。

-- 在当前日期上添加3天
SELECT DATE_ADD(CURDATE(), INTERVAL 3 DAY);

-- 在当前日期上减去1周
SELECT DATE_SUB(CURDATE(), INTERVAL 1 WEEK);

-- 在当前日期上添加3天
SELECT CURRENT_DATE + INTERVAL '3 day';

-- 在当前日期上减去1周
SELECT CURRENT_DATE - INTERVAL '1 week';

4.Limit子句的差异

在MySQL中，LIMIT子句用于限制结果集中返回的记录数，语法为"LIMIT offset, count"，offset为起始位置，count为返回记录数。
在PostgreSQL中，LIMIT子句用于限制结果集中返回的记录数，语法为"LIMIT count OFFSET offset"，count为返回记录数，offset为起始位置。
count表示要返回的记录数，offset 表示返回记录集开始的位置。如果省略 offset 参数，则默认从第一条记录开始。

SELECT name, score  FROM class ORDER BY score DESC LIMIT 5;

SELECT name, score FROM class ORDER BY score DESC LIMIT 5 OFFSET 3;
-- 跳过前3个记录，然后获取接下来的5个记录

注意：LIMIT 子句需要与 ORDER BY 子句一起使用来保证结果集的正确性。否则，返回的结果可能会是没有排序的任意行。

5.NULL值的处理

在MySQL中，使用"= NULL"或"IS NULL"来检查NULL值，使用"IS NOT NULL"来检查非NULL值。
在PostgreSQL中，使用"IS NULL"来检查NULL值，使用"IS NOT NULL"来检查非NULL值。

6.自增主键列

在MySQL中，可以使用AUTO_INCREMENT关键字将主键列设置为自增列。
在PostgreSQL中，可以使用SERIAL或BIGSERIAL类型来创建自增主键列。

-- 使用 SERIAL 类型创建自增主键列
CREATE TABLE users (id SERIAL PRIMARY KEY,username VARCHAR(50),email VARCHAR(100));

-- 使用 BIGSERIAL 类型创建自增主键列
CREATE TABLE products (id BIGSERIAL PRIMARY KEY,name VARCHAR(100),price DECIMAL(10,2));

注意：
使用 SERIAL 类型时，自增的主键列将存储 **4 字节大小的整数值，范围从 1 到 2,147,483,647。
而使用 BIGSERIAL 类型时，自增的主键列将存储 8 **字节大小的整数值，范围从 1 到 9,223,372,036,854,775,807。

无论使用 SERIAL 还是 BIGSERIAL 类型，都要将其指定为主键（PRIMARY KEY）以确保唯一性和索引优化。通过使用这些自增类型，可以方便地为表创建带有自动生成的唯一标识符的主键列。

7.变量和参数差异

MySQL使用@符号来声明和使用用户变量，使用?来作为占位符来传递参数。
PostgreSQL使用冒号（:）来声明和使用变量，使用$1、$2等占位符来传递参数。

-- 声明一个名为my_var的用户变量，并将其赋值为'Hello World'
SET @my_var := 'Hello World';

-- 打印变量的值
SELECT @my_var;

-- 查询指定用户的信息，使用占位符传递参数
SELECT username, email FROM users WHERE id = ?;

-- 声明一个名为my_var的变量，并将其赋值为'Hello World'
DO $$
DECLARE
    my_var TEXT := 'Hello World';
BEGIN
    -- 打印变量的值
    RAISE NOTICE 'The value of my_var is: %', my_var;
END $$;
-----------------------------------------
-- 查询指定用户的信息，使用占位符传递参数
SELECT username, email FROM users WHERE id = $1;

8.连接查询

在MySQL中，可以使用JOIN、LEFT JOIN、RIGHT JOIN和INNER JOIN来进行不同类型的连接。
在PostgreSQL中，使用JOIN来进行内连接，使用LEFT JOIN和RIGHT JOIN来进行左连接和右连接。

9.having语法差异

字符串比较：在 PostgreSQL 中，字符串比较是区分大小写的，默认情况下使用区分大小写的排序规则。而在 MySQL 中，字符串比较默认是不区分大小写的，可以使用 COLLATE 子句来指定不同的排序规则。
聚合函数的引用：在 PostgreSQL 中，HAVING 子句可以直接引用 SELECT 语句中定义的聚合函数，如 HAVING SUM(column) > 100。而在 MySQL 中，默认情况下不允许在 HAVING 子句中直接使用 SELECT 语句中定义的聚合函数结果，需要使用重复的聚合函数表达式，如 HAVING SUM(column) > 100 和 HAVING SUM(column) > 200。
NULL 值处理：在 PostgreSQL 中，HAVING 子句可以直接使用 NULL 进行比较，如 HAVING column IS NULL。而在 MySQL 中，需要使用特殊函数 IS NULL 或 IS NOT NULL 进行 NULL 值的比较，如 HAVING IS NULL(column)。
在 PostgreSQL 中，不能直接在 HAVING 子句中使用 SELECT 语句中定义的列别名进行引用。而在 MySQL 中，默认情况下也不支持在 HAVING 子句中直接使用 SELECT 语句中的列别名。然而，从 MySQL 5.7+ 版本开始，已经开始支持使用列别名。

10.Group by差异

10.1在选择列表中的非聚合列

MySQL 允许在 GROUP BY 子句中不列出选择列表中的非聚合列，而且结果集将使用该组中的任意一个值。这可能会导致不可预测的结果。
PostgreSQL 要求选择列表中的非聚合列要么包含在 GROUP BY 子句中，要么在聚合函数中使用。否则，会引发错误。

10.2聚合函数的处理

MySQL 允许在 SELECT 语句中使用聚合函数，即使没有使用 GROUP BY 子句。这将导致对整个结果集进行聚合计算并返回单个结果。
PostgreSQL 要求在使用聚合函数时必须使用 GROUP BY 子句，并按照指定的分组进行计算。

#Mysql

SELECT name, AVG(score) AS avg_score FROM student GROUP BY name;

#PostgreSQL

SELECT name, AVG(score) AS avg_score
FROM student
GROUP BY name;

--- 报错！！！

可以看到，MySQL 允许在 SELECT 子句中使用聚合函数，而 PostgreSQL 要求使用 GROUP BY 子句，并确保所有非聚合列都在GROUP BY子句中。

11.编码和排序方式

11.1编码

PostgreSQL 提供了许多字符集和编码，其中包括 UTF-8、Latin1、WIN1251 等。可以通过创建数据库时指定编码或者修改已有数据库的编码来设置。
MySQL 也提供了多种字符集和编码，其中包括 UTF-8、GBK、GB2312 等。可以通过创建数据库时指定字符集或者修改已有数据库的字符集来设置。

11.2排序方式

PostgreSQL 的默认排序规则是由其设置的区域设置（locale）确定的，可以使用 LC_COLLATE 环境变量或 SET 命令来更改。例如，如果使用以英文为基础的区域设置，则会在排序中将大写字母与小写字母一起处理。此外，PostgreSQL还支持自定义排序规则。
MySQL 的默认排序规则是根据字符集而不是区域设置确定的。例如，在 utf8 字符集下，MySQL 按照Unicode标准进行排序，因此对于不同语言的字符排序可能会有所不同。MySQL 支持使用 COLLATE 关键字来指定排序规则。

二、PostgreSQL特有的

1.几何类型

包括点（point）、直线（line）、线段（lseg）、路径（path）、多边形（polygon）、圆（cycle）等类型。

CREATE TABLE places (id SERIAL PRIMARY KEY,name VARCHAR(100),location GEOMETRY(Point, 4326));
INSERT INTO places (name, location) VALUES ('Central Park',ST_GeomFromText('POINT(-73.968500 40.785800)', 4326));

2.网络地址类型

有cidr、inet、macaddr 3种类型

CREATE TABLE users (id SERIAL PRIMARY KEY,name VARCHAR(100),ip_address INET);
INSERT INTO users (name, ip_address) VALUES ('Alice', '192.168.0.1');

3.数组类型

可以在表中存储和操作多个数值或对象

CREATE TABLE products (id SERIAL PRIMARY KEY,name VARCHAR(100),prices INTEGER[]);
INSERT INTO products (name, prices) VALUES ('Apple', ARRAY[10, 15, 20]);

4.范围类型

range类型，可以存储范围数据，其他数据库中无此类型
用于表示一定范围内的数值或时间戳等数据。

CREATE TABLE events (id SERIAL PRIMARY KEY,name VARCHAR(100),event_time TSRANGE);
INSERT INTO events (name, event_time) VALUES ('Meeting', '[2024-03-30 16:00, 2024-03-31 16:00]');

5.对象标识符类型

PostgreSQL 具有特殊的对象标识符类型，用于存储和引用数据库对象的标识，如表、列、模式等。

CREATE TABLE orders (id SERIAL PRIMARY KEY,order_number OID,product_name VARCHAR(100));
INSERT INTO orders (order_number, product_name) VALUES ('163125', 'Computer');

三、数据类型别名

为了提高SQL的兼容性，部分数据类型还有很多别名，如integer类型，可以用int、int4表示，smallint也可以用int2表示；char varying(n)可以用varchar(n)表示，numeric(m,n)也可以用decimal(m,n)表示，等等。

腾讯云开发者社区

腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。

更多推荐

Springboot+MySQL 实现从数据库获取数据展示到前端

1.运行效果效果图如下：2.创建数据库表数据库名为"springboot",数据库表名为"user"，数据库表里面放了5条数据，字段分别为：“id”，“name”，“age”，“email”。3.新建SpringBoot项目1、选择Spring Initial...

腾讯云开发者社区

腾讯云建官网：一个完整的自助建站的过程，不用代码，2小时就可以制作自己的网站

腾讯云开发者社区

数据库实验二数据库的简单查询和连接查询实验

– l）用Transact-SQL语句表示下列操作．在学生选课库中实现其数据查询操作．– ①求数学系学生的学号和姓名。select sno,sname from student where sdept='MA';– ②求选修了课程的学生学号。select distinct sno from sc;– ③求选修C1课程的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。s