PostgreSQL, SQLite, MySQL 間での書き方の差異

普段はもっぱら PostgreSQLSQLite を使用しているが、 MySQL を使うことになったので書き方が異なる部分をまとめておこうと思う。

コマンドラインツールの起動

PostgreSQL psql [データベース名 [ユーザ名] ]
SQLite sqlite3 ファイル名
MySQL mysql [-u ユーザ名] [データベース名]

ブラケット [ ] で囲んだ部分は任意で設定する項目。
SQLite では データベース = ファイル なのでファイル名の指定が必須である。
PostgreSQL で全てのデータベースを管理するためのユーザ名は postgres だが、MySQL では root である。

PC-UNIX 上の PostgreSQL では UNIX ユーザ「postgres」でログイン後、単に「psql」として起動する場合がある。Windows では su や sudo の代わりに(runas というコマンドがあるらしいが未検証)次のようにユーザを指定して起動する。

psql -U postgres

MySQL では次のようにする。-p オプションはログイン時にパスワードを入力するという意味。

mysql -u root -p

ユーザの作成

PostgreSQL CREATE USER ユーザ名 WITH ENCRYPTED PASSWORD 'パスワード';
SQLite (不要)
MySQL CREATE USER ユーザ名@localhost;
SET PASSWORD FOR ユーザ名@localhost=PASSWORD('パスワード');

MySQL ではユーザに対しホスト名を指定する必要がある。アクセス権を細かく指定できるがいささか冗長。

データベースの作成

PostgreSQL CREATE DATABASE データベース名 OWNER ユーザ名 ENCODING 'UTF-8';
SQLite (不要)
MySQL CREATE DATABASE データベース名 DEFAULT CHARACTER SET utf8mb4;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON データベース名.* TO ユーザ名@localhost;
FLUSH PRIVILEGES;

SQLite では sqlite3 コマンドもしくはプログラミング言語で用意されたインタフェースでデータベースファイル名を指定すると、ファイルがない場合に自動的に作成される。

MySQL の場合、内部エンコーディングは “UTF-8” ではなく “utf8” という独自の表記を用いるので注意。また “utf8” は 4 バイト以上の文字が扱えないため、絵文字なども格納したい場合は “utf8mb4” を設定し、クライアント側もそれに合わせる必要がある。

テーブルの作成

PostgreSQL CREATE TABLE テーブル名 (...);
SQLite CREATE TABLE テーブル名 (...);
MySQL CREATE TABLE テーブル名 (...) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

MySQL ではテーブルごとにストレージエンジンを指定できる。標準では MyISAM が選ばれるが、排他処理がテーブルロック方式、トランザクションや外部キー制約をサポートしない、壊れやすい、断片化しやすい、といったデメリットが大きいので InnoDB を用いる。また文字コードの設定方法が複雑怪奇なので、必ずテーブルごとに文字コードを指定しておく。

整数型の自動採番ID

PostgreSQL id serial PRIMARY KEY
SQLite id integer PRIMARY KEY AUTOINCREMENT
MySQL id integer PRIMARY KEY AUTO_INCREMENT

テーブル定義時に id というカラムを定義する例。
どのシステムでも PRIMARY KEY を指定すると暗黙的に NOT NULL となる。
SQLite では AUTOINCREMENT と続けて綴り、MySQL は AUTO_INCREMENT とアンダーバーが入ることに注意。

日付と時刻の扱い

テーブル定義時に created というレコードの作成日時を格納するカラムを定義する例。

PostgreSQL created timestamp with time zone DEFAULT CURRENT_TIMESTAMP
SQLite created timestamp DEFAULT CURRENT_TIMESTAMP
MySQL created timestamp DEFAULT CURRENT_TIMESTAMP
created datetime DEFAULT CURRENT_TIMESTAMP

PostgreSQL では指定したタイムゾーンでの時刻を返してくれ、挿入/更新時にもタイムゾーンを指定できる timestamp with time zone 型が便利で間違いがない。秒の小数点以下の精度を持つ。標準 SQL では timestamp 型はデフォルトでタイムゾーン情報を持つことになっているが、PostgreSQL では with time zone と明示する必要がある。

SQLite では timestamp 型として宣言しておき、日時を表す文字列として格納する。タイムゾーンUTC となる。ローカル時間は datetime(カラム名, 'localtime') で取得できるが、OS のタイムゾーン設定に依存し他のタイムゾーンへの変換はできないので使い勝手が悪い。アプリケーション側で必要に応じて変換するのがよいだろう。集計時は strftime 関数を使って時刻を加算するとよい。

MySQL では timestamp 型または datetime 型を用いる。
timestamp 型では実質 32 ビットの UNIX タイムスタンプを格納しているので、2038年までしか扱えないことに注意する。また NULL に設定できない。また、クライアントのタイムゾーン設定によって取得できる結果が変わる。クライアントのタイムゾーンを設定するには

SET SESSION time_zone = '+09:00';

のようにする。

datetime 型では PostgreSQL の timestamp 型と同じように広い範囲の日付を扱える。ただしタイムゾーンを扱えないため、アプリケーション側で常に UTC で読み書きするよう対処する必要がある。CURRENT_TIMESTAMP の値が現在の接続のタイムゾーンに依存するので注意。こちらは NULL を設定できる。

文字列結合

PostgreSQL SELECT 'foo' || 'bar' AS foobar;
SQLite SELECT 'foo' || 'bar' AS foobar;
MySQL SELECT concat('foo', 'bar') AS foobar;

オプションで縦棒2本を使うこともできるようだ (→MySQL 文字列結合をパイプ(||)でやろう! - Quiita)

PHP からの利用: PDO による接続

PostgreSQL new PDO('pgsql:host=localhost;port=5432;dbname=データベース名', $user, $password)
SQLite new PDO('sqlite:ファイル名')
MySQL new PDO('mysql:host=localhost;port=3306;dbname=データベース名;charset=utf8mb4', $user, $password)

ここでも MySQLUTF-8 ではなく utf8 と設定しなければいけない。

MySQL 固有の注意事項

MySQL には標準的な RDBMS にはない独自仕様が多数ある。

  • カラムに付与した NOT NULL 制約を無視する:
    カラムに NOT NULL 制約を設定しても、想定に反して INSERT や UPDATE による NULL の入力を許してしまう。しかも integer や double precision では 0、文字列型では空文字列、datetime 型では 0000-00-00 00:00:00 というように予期せぬ値が入る。
  • 文字列比較がデフォルトでは大文字小文字を区別しない
  • 文字列型 (text) にデフォルト値を設定できない
  • 古いバージョン (5.6.13以前?) では DEFAULT CURRENT_TIMESTAMP を2カラム以上に設定できない
  • UPDATE 文を実行したあとに取得できる更新件数は、WHERE 句の対象となったレコード数ではなく、データに変更のあったレコードの数となる
  • datetime 型、timestamp 型にはタイムゾーンの概念がない:
    すべてローカルタイムまたは UTC タイムゾーンとして設定し、必要に応じてアプリケーションで変換する。あるいは integer 型で UNIX タイムスタンプを格納する
  • 同じテーブルに対するサブクエリからの操作 (UPDATE, DELETE) ができない


以下追加予定...