mysql 函数 addscalar_[笔记]将系统的数据库从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

-----------------------------分隔线-----------------------------

* 分页写法的区别

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中写成这样

CREATETABLEusers(idINT(11)NOTNULLAUTO_INCREMENT,nameVARCHAR(50)NOTNULL,PRIMARYKEY(id) );

PostgreSQL中写成这样

CREATETABLEusers(idserialNOTNULL,nameVARCHAR(50)NOTNULL,PRIMARYKEY(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 findExceptLeakageDetailList(String ids) {

String queryString = "SELECT * FROM leakage_detail "

+ "WHERE " // -- DATE_FORMAT(find_date, '%Y%m')

+ "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 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);

*/