データ型を調べるとたくさんあって混乱してしまうので、これだけ覚えればなんとかなるデータ型を書き方含めまとめました。PostgreSQL 9.6.5を参考に書いています。
MySQLも書き方はほぼ同じです。
全てのデータ型は以下のサイトで確認できます。
postgreSQL.jp | PostgreSQL 9.6.5文書 第8章 データ型
動作確認
動作確認は環境構築するのが面倒なので、主にsqlfiddle.com/で行っています。
SQL FIDDLEが使えない時は(たまにサイトが停止します)、Rextester|PostgreSQLも便利です。
よく使うSQLデータ型
これだけでシステムを構築できるほど便利なデータ型です。
| 分類 | データ型名 | 格納サイズ | 説明 |
|---|---|---|---|
| 数値型 | integerまたはint | 4バイト | 範囲：-2147483648から+2147483647 |
| 数値型 | numeric ( p[ , s] ) または decimal(p[ , s]) | 可変長 | 小数点より上は131072桁まで、 小数点より下は16383桁まで ※明示できる桁は1000桁まで |
| 文字型 | character varying(n) または var char(n) | 可変長 (上限付) | 制限付き可変長文字列 可変長、固定長の詳細は以下 |
| 日付/ 時刻データ型 | date | 4バイト | 日付（時刻なし） |
| 日付/ 時刻データ型 | timestamp [ (p) ] [ without time zone ] | 8バイト | 日付と時刻両方（時間帯なし） |
それぞれの詳細に関しては次の項目で説明します。
INTEGER の書き方例
INTEGER
紹介したデータ型の中で一番簡単。INTEGERと書くだけです。
NUMERIC の書き方例
NUMERIC(precision, scale)
precisionは小数点以下を含めた桁数を表します。scaleには小数点以下の桁数です。
解説
NUMERIC(6,3) の場合
NUMERIC(6,3) の場合、整数が3つ小数点以下が3つの合計6桁が入力できます。
動きは以下になります。
- 500.005 OK
- 10.5 OK
- 5000 エラー
- 50.0004 → 50.000
- 50.0005 → 50.001
- -500.0005 → -500.001
小数点以下は四捨五入されます。
NUMERIC(3) の場合
NUMERIC(3) の整数3桁となります。
動きは以下になります。
- 500 OK
- 10 OK
- -10 OK
- 1000 エラー
- 500.3 → 500
- -500.5 → -501
データ型INTEGERと同等です。NUMERICで記述されているのもみかけます。
character varying 書き方例
character varying (n)
varchar(n)ともかけます。
解説
nの中には文字の桁数が入ります。
nに20を入れた場合、21文字以上入力でエラー。
※他のデータベースではバイト数を表すものもあるので混乱しないよう注意が必要です。
特定の文字の桁数を設けない場合はデータ型textを使用が推奨されている。
character varying(5)
5文字入力可能。
- コーラ OK
- コーラゼロ OK
- ダイエットコーラ エラー
DATE 書き方例
DATE
DATEはシンプルにDATEと記入するだけです。
解説
日付のみ登録できます。現在の日付の挿入は now() で取得できます。
※OracleはSYSDATEで現在の日付を取得できます。
日付を指定する場合は、2001-10-05 のように指定できます。
※TO_DATE(‘2020/03/01’, ‘YYYY/MM/DD’)とかけます。
TIMESTAMP 書き方例
TIMESTAMP WITHOUT TIME ZONE
タイムゾーンを除いた(WITHOUT TIME ZONE)日時を登録できます。
※MySQLではTIMESTAMPだけです
解説
日付プラス時間を登録できます。現在の日時の挿入は now() で取得できます。
日時を指定する場合は、2020/03/01 20:10:00のように指定することもできます。
※TO_TIMESTAMP(‘2020/03/01 20:10:00’, ‘YYYY/MM/DD HH24:MI:SS’) ともかけます。
可変長・固定長について
データ型を調べていると可変長、固定長という言葉がでてきます。
固定長の場合、指定したバイト数を必ず使用します。
例：character varying(10)
例の場合、’あいう’という文字をインサートした場合、
固定長の場合は ‘あいう ’ と残りのバイト文スペースがはいり(常に10バイト使用)、
可変長の場合は ‘あいう’ としてデータベースに登録されます(6バイトで対応)。
可変長は文字数に応じてバイト数が決まるため、人によっては予備としてデータ数を多めにとって使用することもあります。
数値型も同じでどんな桁数の文字を利用しても固定長なら決まったバイト数を使用します。
サンプルコードでテーブルを作ってみよう
せっかくデータ型を学んだので実際に入力して動きを見てみましょう。
（データ型の説明が長くなったので以下のサンプルコードの説明は省略します。）
CREATE TABLE
実際にCREATE TABLEでテーブルを作成します。
CREATE TABLE item (
ItemID integer unique not null,
ItemName character varying (5) not null,
Price numeric(20, 3),
Quantity integer,
Today date,
DateAndTime timestamp without time zone,
primary key(ItemID)
);
INSERT
テーブルの枠を作ったので実際に値を挿入してみます。
INSERT INTO item VALUES (1,’コーラゼロ’, ‘150’, ‘0’, now(), now());
SELECT
挿入した値を取り出して確認してみましょう。
SELECT * FROM ITEM
以上少しデータ型に詳しくなれたのではないでしょうか。
CREATE文のより詳しい説明は別記事で行っています。
