OracleベースのWebアプリをMySQLベースに移行した(最終回)

2010-06-17 18:37:00

今回のエントリーが最終回です。

内容は「Webアプリケーションの改変(基本的にはSQL文)」についてです。

1)日付データの編集は? (to_char <-> date_format)
2)SEQUENCEの移行は? (SEQUENCE <-> auto_increment)
3)where句で使うrownumは? (rownum <-> limit)
4)NVL関数は? (nvl <-> ifnull)
5)CHR関数を使った「改行コード」の変換は?
6)そのまま使えた同一構文の関数
7)Oracle固有の表の外部結合を標準SQL化
8)日付型にNULLを許さないMySQL
9)DUAL表を使ったSQL文は?
10)弊社製品101NEO固有の問題
11)データベース接続について

参照:MySQLのSQLリファレンス
参照:OracleのSQL言語リファレンス

1)日付データの編集は?(to_char <-> date_format)
Oracle
SQL> select to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YYYY/MM/DDHH24:MI:SS')
---------------------------------------------------------
2010/06/17 11:18:58


MySQL
mysql> select date_format(now(), '%Y/%m/%d %H:%i:%s');
+-----------------------------------------+
| date_format(now(), '%Y/%m/%d %H:%i:%s') |
+-----------------------------------------+
| 2010/06/17 11:17:56 |
+-----------------------------------------+

Note:MySQLの現在日時は current_timestamp 関数も同義です。

2)SEQUENCEの移行は?(SEQUENCE <-> auto_increment)

カラムの値を自動採番させたい場合、OracleではSEQUENCEオブジェクトを使用しますが、MySQLではカラム属性の auto_increment を使用します。

Oracle
1.SEQUENCEオブジェクトの作成
 create sequence seq_tar_m;
2.INSERT
 insert into TAR_M
  values(seq_tar_m.nextval, 'Oracleの自動採番');
3.現在の採番値の確認
 select seq_tar_m.currval from dual;
MySQL
1.自動採番属性付きのカラム定義
 create table TAR_M (tar_id int(10) auto_increment
          , subject varchar(100));
2.INSERT
 insert into TAR_M (subject) values('Oracleの自動採番');
3.現在の採番済み最大値の確認
 select last_insert_id() from TAR_M;

Note:auto_increment 指定している tar_id の指定は不要です。

3)where句で使うrownumは?(rownum <-> limit)

検索条件にHitしたデータのうち10件だけ表示したいというようなクエリはMySQLの場合 limit 節を使用します。

Oracle
SQL> select t.* from
2 (select STATUS_ID, STATUS_NAME from TAR_STATUS_M order by STATUS_ID) t
3 where rownum < 6;

STATUS_ID STATUS_NAME
---------- --------------------------------------------------
1 ->未着手
2 ->検討中
5 ->連絡1回
6 ->連絡2回
7 ->緊急

MySQL
mysql> select t.* from
-> (select STATUS_ID, STATUS_NAME from TAR_STATUS_M order by STATUS_ID) t
-> limit 5;
+-----------+--------------+
| STATUS_ID | STATUS_NAME |
+-----------+--------------+
| 1 | ->未着手 |
| 2 | ->検討中 |
| 5 | ->連絡1回 |
| 6 | ->連絡2回 |
| 7 | ->緊急 |
+-----------+--------------+

MySQLのSELECT文のlimit節は引数が1つのときは取得する件数ですが以下のように「オフセット,取得件数」という指定ができます。
オフセットは0から始まります。

MySQL
mysql> select t.* from (select STATUS_ID, STATUS_NAME from TAR_STATUS_M order by status_id) t limit 0,4;
+-----------+--------------+
| STATUS_ID | STATUS_NAME |
+-----------+--------------+
| 1 | ->未着手  |
| 2 | ->検討中  |
| 5 | ->連絡1回  |
| 6 | ->連絡2回   |
+-----------+--------------+

4)NVL関数は? (nvl <-> ifnull)

OracleのNVL関数はIFNULL関数にそのまま置き換えればOKです。

Oracle
SQL> select nvl(null, 'TRUE') from dual;
NVL(
----
TRUE

MySQL
mysql> select ifnull(null, 'TRUE');
+----------------------+
| ifnull(null, 'TRUE') |
+----------------------+
| TRUE |
+----------------------+

5)CHR関数を使った「改行コード」の変換は?

クエリで取得するデータの文字列操作にCHR関数で「改行コード」を出力している箇所があったのでその部分を変換しました。
改行コードの扱いはOracleが特殊でMySQLは一般的ですね。

CR(キャリッジリターン):CHR(13) <-> '\r'
LF(ラインフィード)  :CHR(10) <-> '\n'

Oracle
select replace(h.his_report,chr(13)||chr(10), chr(10)) from TAR_HISTORY h;
MySQL
select replace(h.his_report,'\r\n', '\n') from TAR_HISTORY h;

6)そのまま使えた同一構文の関数
mod関数:mod(n,m)
case関数:case when [condition] then result else result end
replace関数:replace(a, 'AA', 'aa')

7)Oracle固有の表の外部結合を標準SQL化

Oracleの外部結合はwhere句の結合条件で以下のように指定することが可能です。

select p.name, i.ip_address
from pc p, iptbls i where p.key = i.key(+);

ただし、上記のSQL構文はOracle固有ですので標準的なSQL構文に変更しました。

select p.name, i.ip_address
from pc p left join iptbls i on where p.key = i.key;

内部結合は、そのままMySQLでも動作しますが、標準化として INNER JOIN に変更しました。
昔の書き方のほうが見た目はシンプルですが、わかりやすさはSQL標準の書き方ですね。

8)日付型にNULLを許さないMySQL

Oracleは日付型のデータにNULLを格納できるのですがMySQLはエラーになりますので'0000-00-00'セットします。
MySQLは格納する日付型のチェックはかなり緩いようです。詳しくはマニュアルを参照してください。

9)DUAL表を使ったSQL文は?

以下の通りシンプルです。

select 'xxxxxx' from dual; <-> select 'xxxxxx';

10)弊社製品101NEO固有の問題
現象
配列にセットした要素のデータ型が数値と文字列型が混在している場合にその配列内の日本語が文字化けした。


解決方法
クエリで値を取得する際に数値を文字列にcastしてした
SQL例:select cast(ifnull(VERSION,'-') as char) from PRODUCT_M;

以上 上記で説明したSQL文の変換をプログラム全体にわたり行ってWebアプリの移行は終了です。

11)データベース接続について

移行したWebアプリケーションは弊社の101NEOというスクリプト言語で記述されたServletアプリケーションですのでデータベースアクセスはJDBCドライバを介して行います。

具体的な作業は以下の通りです。

  • MySQL用のJDBCドライバの取得と配置
  • WebアプリケーションのJDBC接続構成ファイルの変更

◆MySQL用のJDBCドライバの取得と配置
以下のドライバーをsourceforgeサイトよりダウンロードしwarファイルを構成します。

mysql-connector-java-5.1.12-bin.jar

◆WebアプリケーションのJDBC接続構成ファイルの変更
101NEOアプリケーションのアプリケーション仕様定義書(構成ファイル)のDB接続識別子の記述をMySQL用に変更。

例)
db = org.gjt.mm.mysql.Driver,jdbc:mysql://localhost/helpdesk,helpdeskadmin,PASSWORD

(終わりに)
いかがでしたでしょうか。3回にわたって実際に弊社がプロジェクトで利用している課題管理サイト(インシデント管理)をOracleからMySQLへデータベース移行した内容を掲載しました。
実行結果がそまま張り付けてあったりとやや見栄え的に残念なところも多々ありますが。。ご容赦ください。

ご質問歓迎しますのでまずは twitter まで!(^^)/~

My Twitter account : tnikaido

(おわり)

※このエントリは ブロガーにより投稿されたものです。朝日インタラクティブ および ZDNet Japan編集部の見解・意向を示すものではありません。
  • 新着記事
  • 特集
  • ブログ