Mysql转PostGreSQL常见问题
目录
开发过程中将数据库从Mysql迁移至PostgreSQL,有些代码还是需要响应的更新的。PostgreSQL 严格遵循 SQL标准。所有我们平时使用Mysql写的一些SQL语句 在PostGreSQL中可能无法执行,或者得到的不是想要的结果。
ID自增问题
Mysql中创建一个自增ID语句如下:
CREATE TABLE user (
`id` bigint not null auto_increment primary key ,
`name` varchar(255)
)
PostGreSql对字段的自增语句使用 SERIAL
,比如使用postgres
数据库使id字段自增
CREATE TABLE user(
`id` SERIAL PRIMARY KEY ,
`name` varchar(20)
)
# 查询创建的表结构
sso_db=# \d users
Table "public.users"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+------------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
name | character varying(29) | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
以上创建users
表相当于执行了以下语句:
CREATE SEQUENCE users_id_seq;
CREATE TABLE users (
id integer not null DEFAULT nextval('users_id_seq'),
name varchar(20)
);
ALTER SEQUENCE users_id_seq OWNED BY users.id;
⚠️注意: 序列生成器不是事务安全的。并发环境中会有问题。在事务中如果其中一个客户端回滚事务,则该序列值将被丢弃。导致序列值不连续。 对表中添加数据的时候,如果要指定
id
的值,可以使用DEFAULT
关键字。如果使用某一个数值则会导致序列在使用到该值的时候重复报错。
以上情况需要手动更新对应sequence
的值.ALTER SEQUENCE users_id_seq RESTART WITH 8
;
详情示例:
sso_db=# insert into users (id,name) values (DEFAULT,'blog.68hub.com');
INSERT 0 1
sso_db=# select * from users;
id | name
----+----------------
1 | blog.68hub.com
(1 row)
sso_db=# INSERT INTO users (name) values ('blog.happyhack.cn');
INSERT 0 1
sso_db=# select * from users;
id | name
----+-------------------
1 | blog.68hub.com
2 | blog.happyhack.cn
(2 rows)
sso_db=# insert into users (id,name) values (3,'blog.68hub.com');
INSERT 0 1
sso_db=# INSERT INTO users (name) values ('这个语句会和上一个语句的id冲突');
ERROR: duplicate key value violates unique constraint "users_pkey"
DETAIL: Key (id)=(3) already exists.
sso_db=# alter sequence users_id_seq restart with 4;
ALTER SEQUENCE
sso_db=# INSERT INTO users (name) values ('只有手动更新sequence的值之后才可以');
INSERT 0 1
sso_db=# select * from users;
id | name
----+------------------------------------
1 | blog.68hub.com
2 | blog.happyhack.cn
3 | blog.68hub.com
4 | 只有手动更新sequence的值之后才可以
(4 rows)
列出所有的sequence
SELECT c.relname FROM pg_class WHERE c.relkind = 'S';
查询user_id_seq
的最后一个值,和递增值
SELECT last_value, increment_by FROM user_id_seq;
得到user_id_seq
下一个值
SELECT nextval('user_id_seq');
当执行nextval()
之后,可以使用currval()
来查看当前值
SELECT currval('user_id_seq');
大小写问题
如果使用驼峰法命名库名、表名、别名。在使用的时候需要加上双引号"
,否则会找不到对应参数。建议全局使用小写。
PostGreSQL按日期排序
Mysql中如果需要按一个日期字段排序,ORDER BY updated_at DESC
默认 null
值是在最后的。而PostGreSQL中 null
值却在最前面。需要使用
NULLS FIRST | LAST
子句来约束。
SELECT id,user_name,updated_at FROM users WHERE id > ? ORDER BY updated_at DESC NULLS LAST;
分组问题
常见错误: must appear in the GROUP BY clause or be used in an aggregate function
select sum(`score`) as total_score,user_name,user_class from user group by user_name, user_class;
这种SQL语句在Mysql中是没有问题的,但是在PostGreSQL中就会出现报错。
解决此问题可以使用子查询加join
链接解决.
SELECT m.user_name,m.user_class,n.total_score
FROM (
SELECT user_name,SUM(score) AS total_score
FROM users
GROUP BY user_name
) n
JOIN users m
ON m.user_name = n.user_name;