一、常用DML语法差异
| MySQL | PostgreSQL | 说明 |
| # | -- | mysql的单行注释使用#，pg使用-- |
| '或" | ' |
mysql使用单引号或双引号代表字符串 pg只接受单引号作为字符串 |
| ` | " | mysql使用`标识表名、列名，pg使用双引号 |
| ... WHERE lastname="smith" | ... WHERE lower(lastname)='smith' | mysql字符串比较是大小写无关；pg的则是大小写敏感的，pg实现大小写无关的字符串比较，可以使用lower函数，或者ILIKE等 |
| `LastName` = `lastname` | "LastName" <> "lastname" |
pg 名字规范是只能用小写 ，不能用大写，如果一定要用大写字母，那么要加双引号。 |
| 'foo' || 'bar' | 'foo' or 'bar' |
mysql支持||、&&作为逻辑运算符 pg只支持and、or这种标准SQL语法 |
|
SELECT ... LIMIT off, size SELECT ... LIMIT size OFFSET off |
SELECT ... LIMIT size OFFSET off | 翻页语法 |
|
LAST_INSERT_ID() |
CURRVAL('serial_variable') |
mysql使用LAST_INSERT_ID()返回AUTO_INCREMENT生成的最后一个id 值 pg有更具可读性的方法，返回插入的数据列：INSERT INTO mytable VALUES (...) RETURNING my_serial_column_name; |
二、常用DDL语法差异
举个例子来说明：
MySQL的建表语句如下
CREATE TABLE `student` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '标识',
`name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
`age` bigint(20) NOT NULL COMMENT '年龄',
`number` bigint(20) NOT NULL COMMENT '学号',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_id`(`id`) USING BTREE
);PostgreSQL的建表语句如下
CREATE TABLE "student" (
"id" bigserial,
"name" varchar(64) NOT NULL,
"age" int NOT NULL,
"number" bigint NOT NULL,
primary key ("id")
);
CREATE INDEX public_student_number_idx ON public.student USING btree (number);
COMMENT ON COLUMN "student"."id" IS '标识';
COMMENT ON COLUMN "student"."name" IS '姓名';
COMMENT ON COLUMN "student"."age" IS '年龄';
COMMENT ON COLUMN "student"."number" IS '学号';简单来说，PostgreSQL的DDL语句，不能直接带注释,也不需要指定字符编码，字符编码在create database时指定。
PostgreSQL索引需要通过单独语句建，索引的名字要求唯一，鉴于与表名相关。
三、语法差异&改造建议
(1) LIMIT & OFFSET
| MySQL | PostggreSQL | 备注 |
| LIMIT n | LIMIT n | 偏移量0，取前n行数据。 |
| LIMIT m,n | LIMIT n OFFSET m | 偏移量m，取n行数据。 |
| LIMIT n OFFSET m | LIMIT n OFFSET m | 偏移量m，取n行数据。两者语法一致，兼容性好 |
（2）`字符串常量
字符串常量MySQL可以用单引号或双引号包含, 但PostgreSQL只能用单引号，如：
select * from t1 where t1.name = 'a'; // MySQL和PostgreSQL都可以
select * from t1 where t1.name = "a"; // PostgreSQL不可以
（3）插入数据时自增主键`
对于自增主键的value为null的插入语句，如insert into t1(id, name) values(null, 'zhangshan');
MySQL可正确插入，主键成功自增，PostgreSQL则报错。
所以PostgreSQL的插入语句，有2种写法：
1）删除自增主键id字段：insert into t1(name) values('zhangshan');
2）显示提供自增主键id值：insert into t1(id, name) values(nextval('t1_id_seq'::regclass), 'zhangshan');
推荐使用第1种写法，此写法兼容MySQL。
请注意，如果客户端使用Mybatis ORM框架，当不指定自增主键值插入数据时，使用Dao层的insertSelective方法，该方法最终生成写法1的SQL语句。
（4）数据记录顺序
记录被update后，在select不加order by的情况下：
MySQL会按照id顺序排序记录。
PostgreSQL不保证记录顺序，所以如果需要保证记录的顺序，需加order by。
(5) bool字段
一般mysql中用bit(1)表示布尔值bool，查询支持的参数为1、true或0、false。
PostgreSQL用bool表示bool值。查询支持的参数如下：
#### 真
> * TRUE
> * true
> * 't'
> * 'true'
> * 'y'
> * 'yes'
> * '1'
#### 假
> * FALSE
> * false
> * 'f'
> * 'false'
> * 'n'
> * 'no'
> * '0'
Mybatis框架生成的对应POJO为Boolean值
兼容性实践，建议：查询参数传true或false
