將系統的數據庫從MySQL 5.5遷移到PostgreSQL 9.1
環境
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
* DDL定義中的各種寫法區別
** 前者可用符號"`"(不含引號，在鍵盤上是Esc鍵下面的那個鍵對應的字符)來包裹住表名、列名等，後者不可以。
* 轉義字符
MySQL www.2cto.com \
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 。
* 改變已有表的自增長序列
01 www.2cto.com
-- 更安全有效的解決方案請參考<a href="http://stackoverflow.com/questions/244243/how-to-reset-postgres-primary-key-sequence-when-it-falls-out-of-sync" target="_blank" rel="nofollow">http://stackoverflow.com/questions/244243/how-to-reset-postgres-primary-key-sequence-when-it-falls-out-of-sync</a>/* 假如有表a從MySQL遷移過來，其中有數據100條，若在建立此表時沒有指定序列，PG會默認給此表的主鍵列一個
02
sequence——它是增長的，幅度為1。此時若用Hibernate來給表a添加數據會報錯，說主鍵1已經存在！
03
所以可以在遷移表之後，將表的sequence作少許修改，讓其從當前表的主鍵的最大值再加1來開始！即可解決
04
Hibernate添加數據時報錯的問題
05
06
ALTER SEQUENCE "public"."表名_主鍵名_seq" RESTART WITH (PK的最大值 + 1);
07
或
08
ALTER SEQUENCE 表名_主鍵名_seq RESTART WITH (PK的最大值 + 1);
09
10
e.g.
11
ALTER SEQUENCE file_types_id_seq" RESTART WITH 10;
12 www.2cto.com
13
從上面stackoverflow.com網站上得到的更加簡單有效的一句SQL語句如下：
14
SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), (SELECT MAX(id) FROM table_name)+1);
15
16
*/
* 唯一鍵約束
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')
* 日期類型
MySQL date time datetime
PostgreSQL timestamp
* 布爾類型
MySQL 木有，可用int(1)、枚舉或者字符串的方式來模擬。
PostgreSQL boolean
www.2cto.com
* 外鍵約束
在MySQL中創建帶有外鍵的表寫法如下:
01
DROP TABLE IF EXISTS recipient_recipientgroup;
02
03
CREATE TABLE IF NOT EXISTS recipient_recipientgroup (
04
id serial NOT NULL,
05
recipient_id INTEGER DEFAULT NULL,
06
recipient_group_id INTEGER DEFAULT NULL,
07
PRIMARY KEY (id),
08
KEY FK_recipient_recipientgroup_recipient (recipient_id),
09
KEY FK_recipient_recipientgroup_recipient_group (recipient_group_id),
10
CONSTRAINT FK_recipient_recipientgroup_recipient FOREIGN KEY (recipient_id) REFERENCES recipient (id),
11
CONSTRAINT FK_recipient_recipientgroup_recipient_group FOREIGN KEY (recipient_group_id) REFERENCES recipient_group (id)
12
);
在 PostgreSQL 中創建帶有外鍵的表寫法如下: (即在MySQL的寫法上去掉 KEY ... 這句!)
01
DROP TABLE IF EXISTS recipient_recipientgroup;
02
03
CREATE TABLE IF NOT EXISTS recipient_recipientgroup (
04 www.2cto.com
id serial NOT NULL,
05
recipient_id INTEGER DEFAULT NULL,
06
recipient_group_id INTEGER DEFAULT NULL,
07
PRIMARY KEY (id),
08
-- KEY FK_recipient_recipientgroup_recipient (recipient_id),
09
-- KEY FK_recipient_recipientgroup_recipient_group (recipient_group_id),
10
CONSTRAINT FK_recipient_recipientgroup_recipient FOREIGN KEY (recipient_id) REFERENCES recipient (id),
11
CONSTRAINT FK_recipient_recipientgroup_recipient_group FOREIGN KEY (recipient_group_id) REFERENCES recipient_group (id)
12
);
代碼修改
----------
* 將SpringSide 3.3.4中提供的IdEntity類修改如下
01
/**
02
* 參考SpringSide3，統一定義id的entity基類.
03
*
04
* 基類統一定義id的屬性名稱、數據類型、列名映射及生成策略.
05
* 子類可重載getId()函數重定義id的列名映射和生成策略.
06
*/ www.2cto.com
07
//JPA 基類的標識
08
@MappedSuperclass
09
public abstract class IdEntity {
10
11
protected Long id;
12
13
@Id
14
// @GeneratedValue(strategy = GenerationType.AUTO)
15
@GeneratedValue(strategy = GenerationType.IDENTITY)
16
@Column(unique = true, nullable = false)
17
public Long getId() {
18
return this.id;
19
}
20
21
public void setId(Long id) {
22
this.id = id;
23 www.2cto.com
}
24
25
}
* 代碼中的SQL/HQL 更改
01
public List<LeakageDetail> findExceptLeakageDetailList(String ids) {
02
String queryString = "SELECT * FROM leakage_detail "
03
+ "WHERE " // -- DATE_FORMAT(find_date, '%Y%m')<(DATE_FORMAT(NOW(), '%Y%m')-1) AND
04
+ "CONCAT(find_date, find_process) IN ( "
05
+ "SELECT CONCAT(find_date, find_process) AS xx "
06
+ "FROM leakage_detail WHERE id IN(" + ids + ")"
07
+ "GROUP BY find_date, find_process "
08
// + "HAVING COUNT(xx)>5)"; // 這種寫法MySQL支持，PostgreSQL不支持！
09
+ "HAVING COUNT(CONCAT(find_date, find_process))>5) AND id IN(" + ids + ") ORDER BY find_date, find_process";
10
logger.info("Leakage模塊數據導入時發送漏液異常郵件的查詢sql->"+queryString);
11
Query query = getSession().createSQLQuery(queryString).addEntity(LeakageDetail.class);
12
return query.list();
13 www.2cto.com
}
01
public List<StatisticalAnalysisVo> getStatisticalAnalysisList() {
02
// 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";
03
String sql = "select workshop as name, count(id) as num "
04
+ "from data_models "
05
// + "where date_format(create_at, '%Y-%m')=date_format(now(), '%Y-%m') " // date_format函數是MySQL專用的
06
+ "where to_char(create_at, 'yyyy-MM')=to_char(now(), 'yyyy-MM') " // PostgreSQL中的日期格式化函數是to_char
07
+ "group by workshop";
08
// Query query = getSession().createSQLQuery(hql);
09
Query query = getSession().createSQLQuery(sql).addScalar("name", Hibernate.STRING).addScalar("num", Hibernate.LONG);
10
query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
11
.setResultTransformer(Transformers.aliasToBean(StatisticalAnalysisVo.class));
12
return query.list();
13 www.2cto.com
}
* 存儲過程更改
MySQL
01
DROP PROCEDURE IF EXISTS `calcUlclp`;
02
DELIMITER //
03
CREATE DEFINER=`root`@`localhost` PROCEDURE `calcUlclp`()
04
COMMENT '計算Hipot不良率的上下限的存儲過程'
05
BEGIN
06
07
SELECT (@rownum := @rownum + 1) AS `id`, DATE_FORMAT(lot_no_to_date, '%Y%m') AS year_and_month,
08
`model_no`, group_no,
09
SUM(liquid_injected_input_num) AS total_input,
10
SUM(short_circuit_num) AS total_short,
11
COUNT(DISTINCT(lot_no)) AS month_num_of_product_days
12
,ROUND(SUM(liquid_injected_input_num) / COUNT(DISTINCT(lot_no))) AS sample_size_n
13
,ROUND(SUM(short_circuit_num) / SUM(liquid_injected_input_num), 4) AS nonconforming_rate_mean_p
14
FROM hipot, (SELECT @rownum := 0) AS r WHERE liquid_injected_input_num!=0
15 www.2cto.com
GROUP BY `model_no`, group_no, year_and_month
16
;
17
18
END//
19
DELIMITER ;
PostgreSQL
view sourceprint?
01
DROP FUNCTION IF EXISTS calcUlclp();
02
03
CREATE OR REPLACE FUNCTION calcUlclp()
04
RETURNS SETOF record AS
05
$BODY$
06
declare
07
-- sql varchar;
08
rownum int;
09
v_rc record;
10
BEGIN
11
for v_rc in
12 www.2cto.com
SELECT (rownum = rownum + 1) AS id, to_char(lot_no_to_date, 'yyyyMM') AS year_and_month,
13
model_no, group_no,
14
SUM(liquid_injected_input_num) AS total_input,
15
SUM(short_circuit_num) AS total_short,
16
COUNT(DISTINCT(lot_no)) AS month_num_of_product_days
17
,ROUND(SUM(liquid_injected_input_num) / COUNT(DISTINCT(lot_no))) AS sample_size_n
18
,ROUND(SUM(short_circuit_num) / SUM(liquid_injected_input_num), 4) AS nonconforming_rate_mean_p
19
FROM hipot, (SELECT rownum = 0) AS r WHERE liquid_injected_input_num!=0
20
GROUP BY model_no, group_no, year_and_month
21
loop
22
return next v_rc;
23
end loop;
24
25
END;
26
$BODY$
27
LANGUAGE 'plpgsql' VOLATILE;
28 www.2cto.com
29
-- 調用存儲過程
30
/*
31
SELECT * from calcUlclp() as
32
t(id_ boolean, year_and_month text, model_no varchar, group_no varchar,
33
total_input bigint, total_short numeric, month_num_of_product_days bigint,
34
sample_size_n double precision, nonconforming_rate_mean_p numeric);
35
*/
作者 leeoo
