MySQL使いが知るべきPostgreSQLとの違いと変わらない一つのこと
で気付いた。
多分本当にMySQL5.7の罠が理由でPostgreSQLに移行する人は上のエントリを求めてない。
つまり本来ターゲットにすべき人は
- SQLはORMが解決してくれるから違いなんて気にしない
- ロジックはSQLではなくアプリケーションコード側が行う
- DBはデータを置くストレージだ、いいね？
みたいな人だ。
前述のエントリでよしPostgreSQL使おう！！って人は多分MySQL使っても乗り越えていける人たちだ。
勿論そんな人達がPostgreSQLに来てくれるのは嬉しいし大歓迎。
それとは別にもっと窓口を拡げるために必要な移行時の罠をまとめておく。
これはMySQLと比較しながらPostgreSQLの事を書く。
だが初めてPostgreSQLを触る人は知っておいた方が良いことのまとめになるはずだ。
1. DBを作成するときの罠
一番最初にPostgreSQLをLinux等にインストールすると
service postgresql-9.4 initdbをまずはすると思う。
その場合、PostgreSQLのデフォルトのロケールはOS側に設定されているロケールを使用する。
つまり多くの場合は
ja_JP.UTF-8
となる。
これに伴いDBが壊れるということはない。
ただしソート順に影響する。
具体的には
日本語ロケールでは辞書順（カタカナ→ひらがな, 清音→濁音→半濁音) の順にソートされる
のだ。
多くのシステムの場合、これは想定外のソート順になる。
そのためDBを作成する際には
service postgresql-9.4 initdb --locale=C service postgresql-9.4 initdb --no-localeを指定することになる。
どちらも同義である。
こちらを行うことにより文字のバイナリ値を基準にしたソートになる。
絵文字でソートしたい場合も安心だ。
詳しくは下記のエントリを参考にして欲しい。
ロケール(国際化と地域化)
2. アクセス制御の罠
MySQLに対するアクセス制限はmy.cnfに
bind-address = 127.0.0.1と書いたりMySQLのuserテーブルで指定したりする。
それに対し、PostgreSQLはDBをインストールしたフォルダ内のpg_hba.confで制御する。
こちらについては先日紹介したとみたさんのエントリでも紹介されている。
MySQLユーザーがPostgreSQLを触ってみたメモ
その際に気をつけてほしいことがある。
それはMETHODの指定である。
host all all 192.168.0.0/24 trustとしてあったとする。
これは192.168.0.1~192.168.0.255のIPアドレスからのアクセスはパスワード認証無しでアクセスできる設定だ。
もしこのDBが外に晒されており、全てのIPを表す0.0.0.0/0を指定した場合は自由にアクセスできる事になる。
PostgreSQLはdefaultでスーパーユーザーとしてpostgresというユーザが作成される。
この状態でpostgresユーザでアクセスすれば...結果は明白である。
笑い事に聞こえるかもしれないがEC2やVPSでDBを作っている場合に一時的にtrustを指定する人を見かける。
そして設定は明示的にに読み込みを行わなければならない。
ここに大きな罠があり
- 確認のためpg_hba.confに0.0.0.0/0 trustを指定して起動。
- テスト終了後、pg_hba.confを修正
- 再起動時や再読み込みを忘れる
とするとpg_hba.confは正しいのに誰でもアクセス出来る状態のままとなる。
せめてmd5を指定するようにしよう。
host all all 192.168.1.1/32 md5
詳しいpg_hba.confの説明等は公式documentを読んで欲しい。
19.1. pg_hba.confファイル
なお蛇足だがそもそもpostgresql.confの設定で
listen_addresses = '*'
を指定しないとdefaultではlocalhost以外からアクセス出来ない。
設定箇所が2箇所あるので要注意だ。
3. テーブル作成時の罠
MySQLからPostgreSQLに移行した時、仮にpgadmin3を使って型を指定しようとしたら驚くだろう。余りにもデータ型の種類が多いからだ。
データ型についてはまず公式documentのリンクを紹介しておく。
第 8章データ型
君たちが欲しいのは
- 数値型
- 文字列型
- 日付/時刻型
だと思う。
それぞれについて簡単に解説しておく。
●数値型
通常は- bigint 8byte整数
- integer 4byte整数
- smallint 2byte整数
- numeric MySQLのDECIMAL相当
(MySQLではnumericはDECIMALのエイリアス)
で事足りると思う。
ただPostgreSQLにはこの他に論理値データ型として
- boolean 1byte
がある。
勿論入るのは0 or 1 or NULL（許可した場合）だ。
SQLとしてはtrue or falseでもよい。
他にも柔軟に受け入れるので使う場合は公式documentをチェックされたい。
論理値データ型
そしてサロゲートキーを使いたい場合にMySQLはAUTO INCREMENTを指定すると思う。
AUTO INCREMENTはPostgreSQLには無い。
その代わりシーケンスを作り、該当の整数型のdefaultにnextval(シーケンス名)を指定することで同義になる。
とは言ったもののその手順は煩雑だ。
そのため最初から
- 整数型の指定
- シーケンスの作成
- 該当シーケンスをdefaultに指定
を全て丸めてやってくれる型がある。
それがserial型だ。
- bigserial → bigint
- serial → integer
- smallserial → smallint
なおPostgreSQLのシーケンスは最大値に行った場合にCYCLEの指定の有無で周回するか決まる。
CYCLEを指定しない場合はNO CYCLEとなり周回せずにエラーが発生する。
だがserial型で作った場合はCYCLEが指定されない=最大値になるとエラーが発生する。
また自分で設定すれば連番の取得の昇降やSTEP、MAXやMINも指定できる。
CYCLEを指定して周回するIDも作れる。
またAUTO INCREMENTと違い複数テーブル（またはカラム）からも参照、指定することが出来る。
シーケンスはMySQLには無い概念なので一度調べてみると設計の幅が広がるのでオススメだ。
●文字型
PostgreSQLの文字列型は- character varying 可変長
- varchar character varyingのエイリアス
- character 空白を埋める固定長
- char characterのエイリアス
- text 制限なし可変長
となる。
実際は可変長、固定長、制限なしの可変長だ。
多くの運用の場合、固定長を使うメリットがない。
なので可変長のcharacter varying(以下varchar)かtextを使うことになる。
またvarcharとtextの違いは制限の有無のみだ。
そのため参照速度だけで言えば制限のオーバヘッドの少ないtextの方が早い。
また制限する場合は
- varchar(n)
とnを指定することになるがnは文字長(文字数)だ。
バイトでは無いので注意が必要だ。
(MySQLも文字数だった、yoku0825さんご指摘あざます！！あと誕生日もおめざす！！)
@soudai1025 MySQLのvarcharも文字数です。。— yoku0825 (@yoku0825) 2015, 8月 25仕様として標準SQLに準拠しているのだがそもそも標準SQLの仕様に癖がある。
一度公式documentを拝読しておくと救われるかもしれない。
8.3. 文字型
またMySQLとの大きな違いとしてPostgreSQLの文字列型は文字の大小を区別する。
('A' != 'a'である)
つまりMySQLのBINARY属性を指定した状態と同じ挙動である。
文字列に関してはMySQLと大きく違う仕様が多いので注意が必要だ。
（これは逆も然りでPostgreSQL使いがMySQLを使う際に多くの人がこの罠にハマる）
蛇足としてPostgreSQLの多くの関数は文字列を受け付ける際はvarcharを指定してもtextにCASTされる。
そういった理由からよく文字列型は全てtextを指定する設計も見かける。
その場合は不正なデータを入れられた時の予防やディスク容量計算が難しくなる。
適正な型指定はデータを守るのでCHECK制約と合わせて使いわけよう。
更にPostgreSQLには列挙型(enum)もある。
8.7.1. 列挙型の宣言
(MySQLにもEnumがあるらしい)
@soudai1025 あと、ENUM型MySQLにもあります。。— yoku0825 (@yoku0825) 2015, 8月 25CHECK制約とは違いデータにソート順を持たせることが出来る。
覚えておいて損はないだろう。
●日付/時刻型
日付/時刻型は- timestamp 日付と時刻両方を持つ 例：2015-01-01 00:00:00
- date 日付を持つ(時刻無し) 例：2015-01-01
- time 時刻を持つ(日付無し) 例：00:00:00
- interval 時間間隔 例：1 year 2 months 3 days 4 hours 5 minutes 6 seconds
がある。
timestampはMySQLのdatetime相当だ。
timestampとtimeについてはtime zoneを持たせることが出来る。
（これによりMySQLのtimestampを表現することが出来る）
指定した場合はUTCとして内部で持ち、表示の際に設定されたタイムゾーンに合わせて計算してくれる。
国内で使う場合はタイムゾーンを指定しないtimestamp without time zoneで問題ない。
またMySQLのtimestampのdefaultのようにUPDATE文の対象になった際に自動的に対象レコードの指定columnをCURRENT_TIMESTAMPで更新する機能は無い。
同じようにしたい場合はトリガーを書くことになる。
これは非常に便利なのでPostgreSQLにも欲しいところだ。
またintervalについては癖の強い型だ。
そのほかの日付/時刻データ型と合わせて公式documentを見ていただきたい。
8.5. 日付/時刻データ型
●配列型と範囲型
補足としてPostgreSQLは配列型と範囲型がある。どちらも強力な機能だが乱用は毒にもなる。
公式documentと例を上げているエントリを紹介しておく。
4. ORMの罠
RoRを使う人は問題ないがPHPerはFrameworkのORMが対応してないことがある。まさにFuelPHPの話だ。
私は標準のクエリビルダをラッパーし自作ORMを作成しているがこれは万人向けではない。
そのため、もしPostgreSQLに対応したORMが必要な場合はDoctrine2をオススメする。
ただし悲しいことに公式documentは英語しかない。
しかしDoctrine2はSymfony2のORMだ。
そのためSymfony2の公式documentを読むことで使い方を知ることが出来る。
Symfony2
FuelPHPでインストールする場合はComposerに対応しているので安心して欲しい。
FuelPHPでdoctrine2を使ってみた
またDoctrine2を利用すればスキーママイグレーションも出来る。
(FuelPHPの標準のスキーママイグレーションはMySQL専用なので動かない)
ここまで来た君は手元のアプリケーションからDBに接続し、自由にテーブル設計できたはずだ。
長くなったので今日はここまでとする。
この後、運用で困った事があればメーリングリストで聞いてみるといい。
きっと誰かが答えてくれるはずだ。
PostgreSQLユーザ会 メーリングリスト
それでは検討を祈る。
