お勉強 - TSRSの技術情報メモ

MySQLとPostgreSQLの型の違い

数値データ型について

PostgreSQL
 smallint、int、bigint、decimal、numeric
MySQL
 tinyint、smallint、mediumint、int、bigint、decimal、numeric

smallint,int,bigint,decimal,numericは共用できそうだ。ただし、桁数が違う可能性があるので注意が必要。
(http://www.thinkit.co.jp/free/article/0703/13/1/)

文字列型

PostgreSQL
 char、varchar、text
MySQL
 char、varchar、text、mediumtext、longtext

char、varchar、textは共用できる。
MySQLは、char型は最大255文字の制限が明確になっており、それ以上はtext型を使う。一方、PostgreSQLは1,000万文字ほどの文字列をchar型に格納できる。ここはMySQLに準拠したほうがよさそうだ。
PostgreSQLのtext型は無制限だが、MySQLのtext型は65535文字までしか格納できない。text型についても、MySQLに合わせる。

日付型

PostgreSQL
date、time、timestamp、interval
MySQL
date、time、timestamp、datetime、year

date、time、timestampは共用可能。日付型は自動更新が可能だが、DBの種類とバージョンによって指定方法が異なる。
PostgreSQLの場合は、デフォルト制約で「current_timestamp」を定義しておく必要がある。また、自動更新は新規挿入時のみ(insert)で、更新時(update)には更新されない。
一方、MySQLのtimestamp型は、INSERTやUPDATEによって現在の日付時刻を自動で更新する。

MySQL(Ver4.x)では、システム変数は指定できず、定数のみ指定できない。(http://oshiete1.goo.ne.jp/qa1176802.html)

ただし、TIMESTAMP型にすれば、一つのテーブルにつき一つだけ値を自動更新可能。(http://www.mysql.gr.jp/Manual/mysql-4.00.12/manual.ja_Reference.htm...)

以上を踏まえ、日付更新処理はDB側の自動更新を使わずアプリ側で対応したほうが無難と言える。

また、格納する日付書式が間違っている場合、PostgreSQLはエラーを返すがMySQLは「0000-00-00」といった値（といった値って何だろう？）が格納されるようだ。

これは、MySQLが「SQL Modes」と呼ばれるMySQLサーバの設定状態があるため。PostgreSQLでも同様に設定可能なので、DBのチェックに頼らず、アプリ側で日付書式チェックを行う。また、日付型を使わず、文字列型で対応することも検討。
# Selectするときにキャストするのが面倒なんだよなぁ

参照URL

SQL文

おおむね、PostgreSQLに合わせておけば大丈夫。というか、標準の(SQL99だっけ？）に準拠すれば、大体の場合は大丈夫だと思われる。

文字コード

PostgreSQL 8.1.8
 EUC-JP
 UTF-8
MySQL 5.0.22
 EUC-JP
 Shift-JIS
 UTF-8
 UCS-2
 cp932

EUC-JPかUTF-8に合わせる。

ビュー、ストアドプロシージャ、トリガー

ビューは、閲覧目的で利用するのみとしたい。更新対応まで考えると挙動を合わせるのが複雑になる可能性があるから。
ストアドプロシージャ、トリガーとも、基本的には利用しない方向にしたい。利用する場合には、個別対応とすること。

PostgreSQLのメタデータを取得するためのSQL

select * from pg_tables 
where 
	not tablename like 'pg%' 
and
	schemaname like 'public'
order by tablename
;

select 
	attname, atttypid, attlen
from 
	pg_attribute
where 
	attnum > 0
and 
	attrelid = (select relfilenode from pg_class where relname = 'tbl_news')
order by 
	attnum
;

select * from pg_class where relname = 'tbl_news'


SELECT
    sut.relname,
    pg_attribute.attnum AS NUM,
    pg_attribute.attname AS COL_NAME,
    typ.typname AS COL_TYPE,
    CASE typ.typname
        WHEN 'varchar'   THEN pg_attribute.atttypmod-4
        WHEN 'bpchar'    THEN pg_attribute.atttypmod-4
        WHEN 'numeric'   THEN pg_attribute.atttypmod/65536
        WHEN 'date'      THEN pg_attribute.attlen
        WHEN 'timestamp' THEN pg_attribute.attlen
    END AS COL_LENGTH,
    pg_attribute.attnotnull
FROM
    pg_stat_user_tables sut inner join pg_attribute  on pg_attribute.attrelid = sut.relid
    inner join  pg_type typ on pg_attribute.atttypid = typ.oid
WHERE
    pg_attribute.attnum > 0

ORDER BY
    sut.relid , 
    pg_attribute.attnum

参照URL

ついでにPHPデータベース接続クラスのPDOマニュアル

http://php.net/manual/ja/book.pdo.php