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

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

最終更新日： 2021年3月26日

コマンドラインツールの起動
ユーザの作成
データベースの作成
テーブルの作成
整数型の自動採番ID
外部キー制約のテーブル定義
日付と時刻の扱い
日付と時刻の計算
文字列結合
PHP からの利用: PDO による接続
テーブル、カラムの変更
MySQL 固有の注意事項
参考

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

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

ブラケット [ ] で囲んだ部分は任意で設定する項目。

◆ SQLite ではデータベース = ファイルなのでファイル名の指定が必須である。

◆ PostgreSQL で全てのデータベースを管理するためのユーザ名は postgres である。

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

psql -U postgres

◆ MySQL では全権限を持ったユーザは root で、次のように起動する。-p オプションはログイン時にパスワードを入力するという意味。

mysql -u root -p

ユーザの作成

PostgreSQL	CREATE USER ユーザ名 WITH ENCRYPTED PASSWORD 'パスワード';
SQLite	(不要)
MySQL (≤ 5.6), MariaDB	CREATE USER ユーザ名@localhost; SET PASSWORD FOR ユーザ名@localhost=PASSWORD('パスワード');
MySQL (≥ 5.7)	CREATE USER ユーザ名@localhost IDENTIFIED BY 'パスワード';

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

データベースの作成

PostgreSQL	CREATE DATABASE データベース名 OWNER ユーザ名 ENCODING 'UTF-8';
SQLite	(不要)
MySQL	CREATE DATABASE データベース名 DEFAULT CHARACTER SET utf8mb4; GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, REFERENCES 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 ではテーブルごとにストレージエンジンを指定できる。MySQL 5.7 以前は、標準では MyISAM が選ばれるが、排他処理がテーブルロック方式、トランザクションや外部キー制約をサポートしない、壊れやすい、断片化しやすい、といったデメリットが大きいので通常は InnoDB を用いる (MySQL 8.0 では InnoDB が標準)。更新や削除をしないログ用のテーブルなどのみ、これらが問題にならないので速度面で有利な MyISAM を選ぶ。また文字コードの設定方法が複雑怪奇なので、必ずテーブルごとに文字コードを指定しておく。

整数型の自動採番ID

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

テーブル定義時に id というカラムを定義する例。

どのシステムでも PRIMARY KEY は NOT NULL と UNIQUE の組み合わせに等しい。

◆ PostgreSQL では serial というキーワードを使うことで integer の型を持ち、テーブル定義と同時に作成される「テーブル名_カラム名_seq」というシーケンスの nextval() の値が初期値として設定されるカラムを定義できる。もし id の数が 2³¹ 以上の必要な場合は bigint (64ビット符号付整数) に対応する bigserial が使用できる。

◆ SQLite では AUTOINCREMENT と続けて綴り、MySQL は AUTO_INCREMENT とアンダーバーが入ることに注意。

外部キー制約のテーブル定義

PostgreSQL と SQLite では単一カラムの外部キー制約は次のようにインラインで書ける。

CREATE TABLE tbl_order (
  product_id integer REFERENCES tbl_product (id)
);

しかし MySQL ではこの書き方はできないのと、制約の見落としを避けるため次のように書くのが望ましい。

CREATE TABLE tbl_order (
  product_id integer,
  FOREIGN KEY (product_id) REFERENCES tbl_product (id)
);

あるいは制約名と UPDATE, DELETE 時の動作を明示して、

CREATE TABLE tbl_order (
  product_id integer,
  CONSTRAINT tbl_order_product_id_fkey FOREIGN KEY (product_id)
    REFERENCES tbl_product (id)
    ON UPDATE CASCADE ON DELETE SET NULL
);

◆ SQLite では外部キー制約を扱うために、接続ごとに次の設定をする必要がある。

PRAGMA foreign_keys = ON;

日付と時刻の扱い

テーブル定義時に 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 型と同じように広い範囲の日付を扱える。ただしタイムゾーンを扱えず、CURRENT_TIMESTAMP の値が現在の接続のタイムゾーンに依存するため、アプリケーションからは常に UTC で接続して読み書きする必要がある。こちらは timestamp 型と違い NULL を設定できる。

SET time_zone = 'UTC'; などセッションのタイムゾーンを設定しようとしてエラーになる場合は、ゾーン情報が mysql データベースに設定されていない。UNIX-like なシステムでは mysql_tzinfo_to_sql コマンドで、それ以外の場合は次の場所から POSIX standard の SQL ファイルをダウンロードしてデータベースに追加する。

MySQL - Time zone description tables

日付と時刻の計算

現在日時 CURRENT_TIMESTAMP から 3 日加算した値を求める。

day 以外に second, minute, hour, month, year が使える
PostgreSQL, MySQL ではこれらに加え week が使える
PostgreSQL, SQLite では値が 1 かそれ以外かにかかわらず単数形・複数形どちらでもよい

PostgreSQL	SELECT CURRENT_TIMESTAMP + interval '3 day';
SQLite	SELECT datetime(CURRENT_TIMESTAMP, '+3 day');
MySQL	SELECT CURRENT_TIMESTAMP + INTERVAL 3 DAY;

減算の場合も同様に書ける。

PostgreSQL	SELECT CURRENT_TIMESTAMP - interval '3 day'; SELECT CURRENT_TIMESTAMP + interval '-3 day';
SQLite	SELECT datetime(CURRENT_TIMESTAMP, '-3 day');
MySQL	SELECT CURRENT_TIMESTAMP - INTERVAL 3 DAY;

文字列結合

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

◆ MySQL でもオプションで縦棒2本を使うこともできる。接続ごとに次のクエリを実行すればよい

SET @@session.sql_mode='PIPES_AS_CONCAT';

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)

テーブル、カラムの変更

カラム名を変更する

PostgreSQL	ALTER TABLE テーブル RENAME COLUMN 旧カラム TO 新カラム;
SQLite	新しいテーブルを作成してデータを移行する
MySQL	ALTER TABLE テーブル CHANGE 旧カラム新カラムカラム定義;

◆ SQLite ではカラムの変更ができないのでテーブルを作り直す。

◆ MySQL ではカラムの定義も書き直す必要がある。

NOT NULL 制約をつける

PostgreSQL	ALTER TABLE テーブル ALTER COLUMN カラム SET NOT NULL;
SQLite	新しいテーブルを作成してデータを移行する
MySQL	ALTER TABLE テーブル MODIFY カラム型 NOT NULL;

NOT NULL 制約を外す

PostgreSQL	ALTER TABLE テーブル ALTER COLUMN カラム DROP NOT NULL;
SQLite	新しいテーブルを作成してデータを移行する
MySQL	ALTER TABLE テーブル MODIFY カラム型;

制約を追加する

PostgreSQL	alter テーブル add constraint 制約名 unique (カラム); alter テーブル add constraint 制約名 foreign key (カラム) references 外部テーブル (カラム);
MySQL	alter テーブル add constraint 制約名 unique (カラム); alter テーブル add constraint 制約名 foreign key (カラム) references 外部テーブル (カラム);

珍しく同じ書き方ができる。

制約を外す

PostgreSQL	alter テーブル drop constraint 制約名;
MySQL	alter テーブル drop index インデックス名; alter テーブル drop foreign key 制約名;

◆ MySQL では制約の種類ごとに方法が異なる。

MySQL 固有の注意事項

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

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

以下追加予定...