[メモ]システムのデータベースをMySQL 5.5からPostgreSQL 9.1に移行

10571 ワード
mysql PostgreSQL データベースの移動
環境Windows Server 2003 x 64簡体字中国語、MySQL 5.5(UTF 8エンコーディング)、PostgreSQL 9.1.4-1(UTF 8エンコーディング)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で外部キー付きテーブルを作成するには、次のように書きます.
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);
*/
[CSS]font-familyって何?
JavaScriptでサウンドを再生する