PostgreSQLをMySQLへコンバートしてみた

だいぶ久しぶりの技術ブログになります。CSチームのhiroです。某有名女優と結婚された方は
大文字ですが、当方は小文字ですのでお間違いなきようお願いします。

ところで、会社の組織でCSと言われるとなんの略だと思われるでしょうか？

Customer Service、もしくはCustomer Supportとほとんどの方が答えられると思います。

（…実際先日で開催されたServerworks Sonicで当社営業からもCustomer Supportと
紹介されてしまうぐらいです）

実はCustomer Satisfactionの略で、お客様に満足していただけるようサービスを
運用するのがチームスピリットになっています。

（…本当はそのままだと「顧客満足度」と言う意味になり、私的にはCustomer Satisfaction
Supportかなと思っています。略してCS2！なんだかAWSの1サービスっぽくてCool！です）

前置きはこれぐらいにして、現在、PostgreSQL＆Symfony1.0系で運用されている
オンプレミスの環境をAWS上に移行するプロジェクトに参加しています。私はDBデータを
MySQL5.5(Amazon RDS)へコンバートしアプリ側の対応を行う作業を担当しています。
私自身MySQLは初という事で移行作業で遭遇した数々の問題を愚痴…、もとい共有したいと
思います。

■ユーザについて

PostgreSQLではユーザ名は一意です。

CREATE USER hiro WITH ENCRYPTED PASSWORD 'パスワード';

DB接続の許可/不許可をアクセス元毎に設定する場合は別途pg_hba.confに下記のように記述します。

host    database         hiro         192.168.1.1/32          md5

MySQLではアクセス元毎にユーザを作成します。

CREATE USER hiro@192.168.1.1 IDENTIFIED BY 'パスワード';

同じユーザ名でもアクセス元分上記操作が必要です。@以降を取り除く事でアクセス元を特定しない事も
可能です。権限の指定も同様で、下記で同時にユーザも追加されます。

GRANT 権限 ON database.table hiro@192.168.1.1 IDENTIFIED BY 'パスワード';

他にも色々と管理方法での違いがありますが、一番最初に戸惑ったのがこの部分でした。
管理周りは他に詳しく書かれている記事があると思いますので軽く紹介するだけに
とどめておきます。

■文字コードについて

PostgreSQLではデータベース単位で作成時に文字コードを指定します。

createdb -E unicode database
CREATE DATABASE test ENCODING 'UTF8';

MySQLでは最小単位はカラムで、テーブル単位でも指定できます。デフォルト値はデータベースに
設定します。

CREATE TABLE mixed_names(utf8_name VARCHAR(20) CHARACTER SET UTF8, euc_name VARCHAR(20) CHARACTER SET UJIS);
CREATE TABLE utf8_names(name VARCHAR(20)) CHARACTER SET UTF8;
CREATE DATABASE test DEFAULT CHARACTER SET UTF8;

個別に指定する事はあまりないと思いますので、設定ファイルmy.cnfにデフォルト値を記述するのが
安全です。

[mysql]
default-character-set=utf8

[mysqld]
character-set-server=utf8
skip-character-set-client-handshake

■ストレージエンジンについて

PostgreSQLでは早い時期からトランザクションに対応していました。
そのため、PostgreSQLをその頃から使っている方が多いかと思います。

MySQLはストレージエンジンを変える事によってトランザクション対応を変える事ができます。
テーブルを作成するときにトランザクション対応する場合はストレージエンジンに
InnoDBを、しない場合はMyISAMを指定します。

CREATE TABLE innodb_test (name VARCHAR(20)) ENGINE=InnoDB;
CREATE TABLE myisam_test (name VARCHAR(20)) ENGINE=MyISAM;

MySQL5.5からはデフォルトでInnoDBストレージエンジンのテーブルが作成されます

■テーブル名で大文字小文字について

PostgreSQLではテーブル名で大文字小文字を区別しませんがファイル名で区別する環境の
MySQLのデフォルトは区別するようになっています。
※test,TESTというテーブルを別々に作成可

区別する事はあまりないので、基本的に小文字で統一しますが人間の手でやっていると統一性が
とれないため設定ファイルで対応するようにします。

[mysqld]
lower_case_table_names=1

【教訓】
・できるだけ、予約語は大文字を、それ以外は小文字を使用する！

■カラム名での大文字小文字について

どちらもカラム名は大文字小文字を区別しませんがMySQLではSELECT結果のカラム名が
指定したまま返ってきます。

SELECT GroupId, count(groupId) FROM grouplog GROUP BY groupid;
+---------+----------------+
| GroupId | count(groupId) |
+---------+----------------+
|       1 |            520 |
|       2 |            260 |
|       3 |           1299 |
|       4 |            634 |
+---------+----------------+

PostgreSQLでは小文字で返ってきて、関数の引数までは返ってきません。PHPでレコードを
連想配列で取得するとカラム名がそのまま連想配列のキーに使用されるため問題が発生します。

またPHPのフレームワークにSymfony1.0.6を使っていたのですが、フレームワークが生成する
SQLのカラム名が大文字になっていたため、今まで動いていたものが動かなくなってしまいました！

Symfony1.0系の最新にアップデートしてdatabases.ymlに下記を設定して対応しました。

all:
  propel:
    class: sfPropelDatabase
    param:
      phptype:  mysql
      ..
      compat_assoc_lower: true

【教訓】
・関数使用時のas指定は必須
・連想配列のキーに直値を使わない！(定数等を使うようにする)

■TIMESTAMPの精度について

TIMESTAMPの精度はPostgreSQLはマイクロ秒で、MySQL5.5は秒となっています。秒未満の精度を
期待していると互換性がとれなくなります。秒未満での精度に左右される仕様はかなり危険です。
※MySQL5.6からはDATETIME, TIMESTAMPでマイクロ秒まで扱いができるようになっています。

【教訓】
・DATETIME 、TIMESTAMP型の列だけでソートしてはいけません！

※今回の件では追加順にソートする必要がありました。レコードを削除しないためソートにID列を
含めれば実際には追加した順にソートされますが、本来ID列は識別子としてのみ使用するべきです。
必要に応じてソート用の列を追加することも検討しましょう。

■文字関連のカラムの大文字・小文字について

PostgreSQLでは文字関連の参照時に大文字小文字を区別しますがMySQLでは区別されません！

例えばgroupinfoテーブルのgroupcodeカラムに'SWX'という値をもったレコードがあるときに
下記のように検索すると

SELECT groupcode FROM groupinfo WHERE groupcode = 'swx';

PostgreSQLではレコードが引っかからず、MySQLでは引っかかってしまいます。

大文字小文字を区別する場合はテーブル作成時にカラムにBINARY指定を入れます。

 CREATE TABLE groupinfo (groupcode VARCHAR(20) BINARY);

■INTERVAL指定での単位について

PostgreSQLで日付・日時の計算で使うINTERVAL指定は単数形・複数形どちらもいけますが

SELECT CURRENT_DATE  + INTERVAL '1 days' as tomorrow;
---------------------
      tomorrow
---------------------
 2012-09-27 00:00:00

※PostgreSQLではINTERVALの精度はマイクロ秒になっています。

MySQLでは単数系のみとなっています(シングルクォートもいりません)。

select current_date + interval 1 day as tomorrow;
+------------+
| tomorrow   |
+------------+
| 2012-09-27 |
+------------+

■制限なし可変長文字列型について

PostgreSQLの制限なし可変長文字列型のTEXTはINDEXを張れますがMySQLのLONGTEXT型にはINDEXを張れません！

【教訓】
・安易に制限なし可変長文字列型を使わず、制限付きの型を使いましょう！

■自動採番について

PostgreSQLでの自動採番はSERIAL, BIGSERIAL型を使いますが、MySQLでは整数型に
AUTO_INCREMENTを指定します。また、AUTO_INCREMENTの指定を複数入れる事は出来ません。

PostgreSQLではトランザクション内で最後に採番された値をとるときには下記のように行います。

SELECT currval('シーケンス名');

MySQLでは最後にインサートしたレコードのAUTO_INCREMENT列の値を下記で取得できます。

SELECT last_insert_id();

この値をSQL内でそのまま使いたい場合は変数にセットして使用する必要があります。

SET @temp_value = (select last_insert_id());
INSERT INTO test VALUES(@temp_value);

■文字列連結演算子について

PostgreSQLでは文字列連結演算子に"||"を使いますが、

select '0' || '1';
 ?column?
----------
 01

MySQLでは困った事に"||"は論理和演算子になります。

select '0' || '1';
+------------+
| '0' || '1' |
+------------+
|          1 |
+------------+

シンタックスエラーにならないので、陥りやすい罠です。

ここはCONCAT関数に置き換える必要があります。

select CONCAT('0', '1');
+------------------+
| CONCAT('0', '1') |
+------------------+
| 01               |
+------------------+

<span style="font-size:24px;"><strong>まとめ</strong></span>

今回は気になったものを中心にまとめてみましたが、まだまだ差異が存在します。

データベースそれぞれで同じ機能でも名前が違ったり少し動作が違ったりします。アプリ開発時に
なるべくSQL標準にそった形にしようとしても、独自機能を使わざるを得ない場合がどうしてもあります。
特定のデータベースに依存しない作りにするのは現実的に難しいと改めて実感しました。

またAmazon RDSにPostgreSQLがあればどんなに良いか改めて思い知らされました。

もしあったら・・・

＜サーバーワークス＞AWS移行作業短縮（残業なし！？）

⬇

＜お客様＞移行（＆管理）コストが下がる（ウキウキ）

⬇

＜サーバーワークス＞AWS移行案件が増加する（ウハウハ）

⬇

＜AWS＞利用料金が増加する（オー！）

⬇

みんなハッピー！（パチパチパチ）

ということでAWSの中の人にRDSにPostgreSQLの最新バージョンを追加してもらうよう 皆でプレッシャーをかけ…、もといお願いしましょう！