文档章节

[笔记]将系统的数据库从MySQL 5.5迁移到PostgreSQL 9.1

leeoo
 leeoo
发布于 2012/07/22 14:10
字数 1329
阅读 1080
收藏 10

环境
Windows Server 2003 x64 简体中文, MySQL 5.5 (UTF8编码), PostgreSQL 9.1.4-1 (UTF8编码)
Spring 3.0.7, Struts 2.3.4, Hibernate 3.5.5

从MySQL迁移到PostgreSQL

-----------------------------分隔线-----------------------------
* 分页写法的区别


PostgreSQL
MySQL
LIMIT 数量
支持
支持
LIMIT 下标, 数量
不支持
支持
LIMIT 数量 OFFSET 下标
支持
支持

e.g.
SELECT * FROM user LIMIT 10; -- PostgreSQL与MySQL均支持

SELECT * FROM user LIMIT 10, 10; -- PostgreSQL不支持,MySQL支持

SELECT * FROM user LIMIT 10 OFFSET 10; -- PostgreSQL与MySQL均支持
-----------------------------分隔线-----------------------------

* DDL定义中的各种写法区别
** 前者可用符号"`"(不含引号,在键盘上是Esc键下面的那个键对应的字符)来包裹住表名、列名等,后者不可以。
 
* 转义字符
MySQL            \
PostgreSQL    默认不支持惯用的\,而是用的英文单引号'
 
* 使用DB
MySQL中可以 use DB名;
PostgreSQL中不支持 use 关键字!

* 主键约束
二者没什么区别
 
* 自增长主键
MySQL中写成这样
CREATE TABLE users (     id INT(11) NOT NULL AUTO_INCREMENT,     name VARCHAR(50) NOT NULL,     PRIMARY KEY (id) );
 
PostgreSQL中写成这样
CREATE TABLE users(     id serial NOT NULL,     name VARCHAR(50) NOT NULL,     PRIMARY KEY (id ) );
注: PostgreSQL会对 serial数据类型的列隐式生成名为 表名_PK名_seq 的SEQUENCE,此例的SEQUENCE名为 users_id_seq 。
 
* 改变已有表的自增长序列
-- 更安全有效的解决方案请参考http://stackoverflow.com/questions/244243/how-to-reset-postgres-primary-key-sequence-when-it-falls-out-of-sync/* 假如有表a从MySQL迁移过来,其中有数据100条,若在建立此表时没有指定序列,PG会默认给此表的主键列一个
sequence——它是增长的,幅度为1。此时若用Hibernate来给表a添加数据会报错,说主键1已经存在!
所以可以在迁移表之后,将表的sequence作少许修改,让其从当前表的主键的最大值再加1来开始!即可解决
Hibernate添加数据时报错的问题

ALTER SEQUENCE "public"."表名_主键名_seq" RESTART WITH (PK的最大值 + 1);
或
ALTER SEQUENCE 表名_主键名_seq RESTART WITH (PK的最大值 + 1);

e.g.
ALTER SEQUENCE file_types_id_seq" RESTART WITH 10;

从上面stackoverflow.com网站上得到的更加简单有效的一句SQL语句如下:
SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), (SELECT MAX(id) FROM table_name)+1);

*/

 * 唯一键约束
MySQL            UNIQUE KEY name (name)
PostgreSQL     UNIQUE  (name)

* 内置SQL函数区别
MySQL格式化日期            DATE_FORMAT(CURRENT_TIMESTAMP,'%Y-%m-%d %H:%i:%s')
PostgreSQL格式化日期     to_char(CURRENT_TIMESTAMP,'yyyy-mm-dd hh24:mi:ss'), to_date(text, text), to_timestamp(text, text)

* 日期类型
MySQL            date    time    datetime
PostgreSQL    timestamp

* 布尔类型
MySQL            木有,可用int(1)、枚举或者字符串的方式来模拟。
PostgreSQL    boolean

* 外键约束
在MySQL中创建带有外键的表写法如下:  
DROP TABLE IF EXISTS recipient_recipientgroup; 

CREATE TABLE IF NOT EXISTS recipient_recipientgroup ( 
  id serial NOT NULL, 
  recipient_id INTEGER DEFAULT NULL, 
  recipient_group_id INTEGER DEFAULT NULL, 
  PRIMARY KEY (id), 
  KEY FK_recipient_recipientgroup_recipient (recipient_id), 
  KEY FK_recipient_recipientgroup_recipient_group (recipient_group_id), 
  CONSTRAINT FK_recipient_recipientgroup_recipient FOREIGN KEY (recipient_id) REFERENCES recipient (id), 
  CONSTRAINT FK_recipient_recipientgroup_recipient_group FOREIGN KEY (recipient_group_id) REFERENCES recipient_group (id) 
);
在 PostgreSQL 中创建带有外键的表写法如下: (即在MySQL的写法上去掉 KEY ... 这句!)
DROP TABLE IF EXISTS recipient_recipientgroup; 

CREATE TABLE IF NOT EXISTS recipient_recipientgroup ( 
  id serial NOT NULL, 
  recipient_id INTEGER DEFAULT NULL, 
  recipient_group_id INTEGER DEFAULT NULL, 
  PRIMARY KEY (id), 
--  KEY FK_recipient_recipientgroup_recipient (recipient_id), 
--  KEY FK_recipient_recipientgroup_recipient_group (recipient_group_id), 
  CONSTRAINT FK_recipient_recipientgroup_recipient FOREIGN KEY (recipient_id) REFERENCES recipient (id), 
  CONSTRAINT FK_recipient_recipientgroup_recipient_group FOREIGN KEY (recipient_group_id) REFERENCES recipient_group (id) 
);
 
代码修改
----------
* 将SpringSide 3.3.4中提供的IdEntity类修改如下
/**
 * 参考SpringSide3,统一定义id的entity基类.
 * 
 * 基类统一定义id的属性名称、数据类型、列名映射及生成策略.
 * 子类可重载getId()函数重定义id的列名映射和生成策略.
 */
//JPA 基类的标识
@MappedSuperclass
public abstract class IdEntity {

    protected Long id;

    @Id
//    @GeneratedValue(strategy = GenerationType.AUTO)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(unique = true, nullable = false)
    public Long getId() {
        return this.id;
    }

    public void setId(Long id) {
        this.id = id;
    }

}

 * 代码中的SQL/HQL 更改

public List<LeakageDetail> findExceptLeakageDetailList(String ids) {
        String queryString = "SELECT * FROM leakage_detail "
                + "WHERE " // -- DATE_FORMAT(find_date, '%Y%m')<(DATE_FORMAT(NOW(), '%Y%m')-1) AND 
                + "CONCAT(find_date, find_process) IN ( "
                + "SELECT CONCAT(find_date, find_process) AS xx "
                + "FROM leakage_detail WHERE id IN(" + ids + ")"
                + "GROUP BY find_date, find_process "
//                + "HAVING COUNT(xx)>5)"; // 这种写法MySQL支持,PostgreSQL不支持!
                + "HAVING COUNT(CONCAT(find_date, find_process))>5) AND id IN(" + ids + ") ORDER BY find_date, find_process";
        logger.info("Leakage模块数据导入时发送漏液异常邮件的查询sql->"+queryString);
        Query query = getSession().createSQLQuery(queryString).addEntity(LeakageDetail.class);
        return query.list();
    }

 

public List<StatisticalAnalysisVo> getStatisticalAnalysisList() {
//        String hql = "select workshop as name, count(id) as num from DataModel where date_format(create_at, '%Y-%m')=date_format(now(), '%Y-%m') group by workshop";
        String sql = "select workshop as name, count(id) as num "
                + "from data_models "
//                + "where date_format(create_at, '%Y-%m')=date_format(now(), '%Y-%m') " // date_format函数是MySQL专用的
                + "where to_char(create_at, 'yyyy-MM')=to_char(now(), 'yyyy-MM') " // PostgreSQL中的日期格式化函数是to_char
                + "group by workshop";
//        Query query = getSession().createSQLQuery(hql);
        Query query = getSession().createSQLQuery(sql).addScalar("name", Hibernate.STRING).addScalar("num", Hibernate.LONG);
        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
            .setResultTransformer(Transformers.aliasToBean(StatisticalAnalysisVo.class));
        return query.list();
    }

* 存储过程更改
MySQL

DROP PROCEDURE IF EXISTS `calcUlclp`;
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `calcUlclp`()
    COMMENT '计算Hipot不良率的上下限的存储过程'
BEGIN

SELECT (@rownum := @rownum + 1) AS `id`, DATE_FORMAT(lot_no_to_date, '%Y%m') AS year_and_month,
 `model_no`, group_no,
 SUM(liquid_injected_input_num) AS total_input,
 SUM(short_circuit_num) AS total_short,
 COUNT(DISTINCT(lot_no)) AS month_num_of_product_days
 ,ROUND(SUM(liquid_injected_input_num) / COUNT(DISTINCT(lot_no))) AS sample_size_n
 ,ROUND(SUM(short_circuit_num) / SUM(liquid_injected_input_num), 4) AS nonconforming_rate_mean_p
    FROM hipot, (SELECT @rownum := 0) AS r WHERE  liquid_injected_input_num!=0
	 GROUP BY `model_no`, group_no, year_and_month
;

END//
DELIMITER ;


PostgreSQL

DROP FUNCTION IF EXISTS calcUlclp();

CREATE OR REPLACE FUNCTION calcUlclp()
    RETURNS SETOF record AS
$BODY$
 declare
--   sql varchar;
   rownum int;
   v_rc record;
BEGIN
  for v_rc in
SELECT (rownum = rownum + 1) AS id, to_char(lot_no_to_date, 'yyyyMM') AS year_and_month,
 model_no, group_no,
 SUM(liquid_injected_input_num) AS total_input,
 SUM(short_circuit_num) AS total_short,
 COUNT(DISTINCT(lot_no)) AS month_num_of_product_days
 ,ROUND(SUM(liquid_injected_input_num) / COUNT(DISTINCT(lot_no))) AS sample_size_n
 ,ROUND(SUM(short_circuit_num) / SUM(liquid_injected_input_num), 4) AS nonconforming_rate_mean_p
    FROM hipot, (SELECT rownum = 0) AS r WHERE  liquid_injected_input_num!=0
	 GROUP BY model_no, group_no, year_and_month
  loop
  return next v_rc;
  end loop;

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

-- 调用存储过程
/*
 SELECT * from calcUlclp() as
  t(id_ boolean, year_and_month text, model_no varchar, group_no varchar,
   total_input bigint, total_short numeric, month_num_of_product_days bigint,
    sample_size_n double precision, nonconforming_rate_mean_p numeric);
*/

© 著作权归作者所有

leeoo
粉丝 27
博文 136
码字总数 83885
作品 0
浦东
程序员
私信 提问
加载中

评论(3)

kenyon_君羊
kenyon_君羊

引用来自“leeoo”的评论

引用来自“kenyon”的评论

很感兴趣你们迁移的原因是什么?

主要是一条包含IN查询的SQL语句在MySQL 5.5下执行较慢,要近3秒钟,而PostgreSQL 9.1只需几十毫秒~
其它的原因:
1. MySQL在Oracle下会发展成什么样你懂的~
2. PostgreSQL的文档质量很好
3. PostgreSQL对SQL92、SQL99和SQL2003标准的支持很好~

I agree,but it will be a large job if your system complex enough。
leeoo
leeoo 博主

引用来自“kenyon”的评论

很感兴趣你们迁移的原因是什么?

主要是一条包含IN查询的SQL语句在MySQL 5.5下执行较慢,要近3秒钟,而PostgreSQL 9.1只需几十毫秒~
其它的原因:
1. MySQL在Oracle下会发展成什么样你懂的~
2. PostgreSQL的文档质量很好
3. PostgreSQL对SQL92、SQL99和SQL2003标准的支持很好~
kenyon_君羊
kenyon_君羊
很感兴趣你们迁移的原因是什么?
intall postgresql 9.1 on CentOS

http://people.planetpostgresql.org/devrim/index.php?/archives/48-What-is-new-in-PostgreSQL-9.0-RPMs.html # rpm -Uvh http://download.fedoraproject.org/pub/epel/6/i386/epel-releas......

mark35
2012/03/01
393
0
PostgreSQL 与 MySQL 相比,优势何在?

一、 PostgreSQL 的稳定性极强, Innodb 等引擎在崩溃、断电之类的灾难场景下抗打击能力有了长足进步,然而很多 MySQL 用户都遇到过Server级的数据库丢失的场景——mysql系统库是MyISAM的,相...

leycau
2015/06/29
355
0
Ubuntu 12.04下PostgreSQL-9.1安装与配置详解(在线安装)

说明: 我是用root用户在终端登陆的,如果是非root用户,那在命令前需要加上"sudo",你懂的... 第一步:在Ubuntu下安装Postgresql 1.使用 apt-get install安装 root@server2-virtual-machine...

今幕明
2015/03/17
163
0
pgsql ident鉴权方式实现无密码登录,无需.pgpass

PostgreSQL 命令行 psql,pg_dump等不支持口令参数,也就无法像mysql那样无需人工干预(输入口令)执行备份、恢复数据库的命令。 网上搜索有多种解决办法,比如通过设置PostgreSQL的环境变量...

mark35
2012/05/24
242
0
Ubuntu 安装 postgresql

每天晚上疲劳的睡在床上时,才感觉真真切切地过了一天。人生最重要的不仅是努力,还有方向。压力不是有人比你努力,而是比你牛叉几倍的人依然比你努力。即使看不到未来,即使看不到希望,也依...

威武不能笑
2014/04/29
5.1K
0

没有更多内容

加载失败,请刷新页面

加载更多

lopatkin俄大神Windows精简版系统 安装教程 简单版

1.制作U盘启动盘 或 安装pe到电脑 下载微pe工具箱.(为什么用这个呢,因为这个无毒,无广告,无后门.其它pe在安装完系统会安装一堆木马,垃圾软件,后门什么的) pe制作工具下载http://www.wepe.com...

xiaogg
19分钟前
3
0
【0917】Linux shell基础知识2

【0917】Linux shell基础知识2 8.7/8.8 shell变量 8.9 环境变量配置文件 8.10 shell特殊符号cut命令 8.11 sort_wc_uniq命令 8.12 tee_tr_split命令 8.13 shell特殊符号 一、shell变量 1、使用...

飞翔的竹蜻蜓
21分钟前
3
0
管理角色认知-新晋管理常常犯的错

背景 管理是一门实践科学,从知道到做到,需要长时间的刻意练习,提前知道那些坑,可以提前规避。 坑1:被动执行 现象: 不主动找活干,等上级派活; 上级有了安排,指望上级替他决定实现方案...

春天spring
23分钟前
4
0
MongoDB4.0.2集群搭建

MongoDB4.0.2集群搭建 2019.02.01 01:02 619浏览 MongoDB4.0.2集群搭建 根据对象存储平台Django+MongoDB+Ceph的需求,现搭建部署一个十节点的MongoDB集群,主要以下关键点: 根据最新版本Mon...

linjin200
25分钟前
4
0
面试官问你B树和B+树,就把这篇文章丢给他

原文链接:面试官问你B树和B+树,就把这篇文章丢给他 1 B树 在介绍B+树之前, 先简单的介绍一下B树,这两种数据结构既有相似之处,也有他们的区别,最后,我们也会对比一下这两种数据结构的区...

欧阳思海
30分钟前
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部