SQLiteにテーブルを作成する：CREATE TABLE

テーブルを作成する

今回はSQLite（バージョン3）のデータベースへテーブルを作成する方法をみていきます。
MySQLやPostgresqlとは異なる「型」や自動連番のID(AUTO INCREMENT)の設定方法についても解説します。

• テーブルで使用できるデータの「型」
• 主キーとして使うことの多い「ID」と「AUTO INCREMENT」（自動連番）の設定
• 登録日や更新日を記録する「日付」のローカライズを含む設定
• テーブルが存在するか確認し、存在しない場合のみ作成

今回は次のようなtest_tableという名前のテーブルを作成しながら進めていきます。

SQLiteのデータベースへの接続方法については、別記事「SQLiteのデータベースへ接続する」をご覧ください。

SQLiteのデータの「型」

まずはSQLiteで扱うことのできる型を確認しましょう。
基本的には次の5種類です。

SQLiteでは論理値の型はなく、INTEGERで「0」か「1」のような形で保存します。
日付についても「DATE」や「DATEITME」、「TIMESTAMP」のような型がないため、TEXTやNUMERICの型として保存します。

IDの連番(AUTO INCREMENT)設定

SQLiteでは「CREATE TABLE」でテーブルを作る際に「AUTO INCREMENT」を指定する必要はありません。
もし主キーを連番のIDにしたい場合は、INTEGERで「PRIMARY KEY」を指定するようにします。

コード例

// テーブル作成SQLの例
$sql = "CREATE TABLE test_table(
	id INTEGER PRIMARY KEY,
	name TEXT NOT NULL,
	age INTEGER NOT NULL,
	created TIMESTAMP DEFAULT (datetime(CURRENT_TIMESTAMP,'localtime'))
)";

こちらのSQLでテーブルを作成すると、データが追加される度に「id」が「1、2、3…」と自動連番で振られていきます。

日付のローカライズを含めた設定

データを登録や更新する際に、ほとんどの場合その時点の日時も保存します。
次のSQLにある「created」のように設定を宣言すると、その時点での日時を自動的に取得して保存してくれるようになります。

コード例

// テーブル作成SQLの例
$sql = "CREATE TABLE test_table(
	id INTEGER PRIMARY KEY,
	name TEXT NOT NULL,
	age INTEGER NOT NULL,
	created TIMESTAMP DEFAULT (datetime(CURRENT_TIMESTAMP,'localtime'))
)";

このSQLで登録したテーブルにデータを追加すると、「created」には「yyyy-mm-dd hh:mm:ss」の形式で「2017-07-26 12:10:30」のように日時を自動取得し登録します。
これは「DEFAULT」を使って、初期値をdatetime関数から取得した時刻を入れるように指定しているためです。

datetime関数は第一引数には時刻、第二引数にはオプションを指定することができます。
第一引数には現在時刻が入るように「CURRENT_TIMESTAMP」を指定。

第二引数に「localtime」を指定しなかった場合はUTCの標準時刻が入ります。
日本時間にしたい場合は必ず指定してください。

テーブルを作成する

これまでの内容を踏まえ、実際にテーブルを作成していきましょう。
テーブル作成のSQLは先述のものをそのまま使います。

コード例

<?php
// 変数の初期化
$db = null;
$sql = null;
$res = null;

// データベースと接続
$db = new SQLite3("./sqlite/test.db");

// テーブルを追加
$sql = "CREATE TABLE test_table(
	id INTEGER PRIMARY KEY,
	name TEXT NOT NULL,
	age INTEGER NOT NULL,
	created TIMESTAMP DEFAULT (datetime(CURRENT_TIMESTAMP,'localtime'))
)";
$res = $db->exec($sql);

// データベースの接続解除
$db->close();

上記のコードを実行すると、データベース「test.db」の中に「test_table」というテーブルが新しく作成されます。

変数$resにはテーブルを作成したらtrue、すでに同じ名前のテーブルが存在していたり、その他の理由でテーブルを作成できないときはfalseが入ります。
この値はテーブルを作成したか判定するときに活用できます。

テーブルが存在しない場合のみ作成

最後に、テーブルが存在しない場合のみテーブルを作成するようにします。
特定のテーブルが既に登録されているかを確認したい場合は、次のSQLを実行します。

コード例

SELECT count(*) FROM sqlite_master WHERE type="table" AND name="test_table"'

「sqlite_master」へ、「test_table」というテーブルが存在するかを問い合わせる内容です。
テーブルが存在する場合は整数で1、存在しない場合は0が返ってきます。

そこで、次のようにif文を追加することで「存在しない場合のみ（0のときのみ）テーブル作成をする」という処理にしていきます。
赤い箇所がコードを追加した部分です。

コード例

<?php
// 変数の初期化
$db = null;
$sql = null;
$res = null;

// データベースと接続
$db = new SQLite3("./sqlite/test.db");

// テーブルの存在確認
$sql = 'SELECT count(*) FROM sqlite_master WHERE type="table" AND name="test_table"';

// テーブルが存在しない場合のみテーブル作成
if( !$db->querySingle($sql) ) {

	// テーブルを追加
	$sql = "CREATE TABLE test_table(
		id INTEGER PRIMARY KEY,
		name TEXT NOT NULL,
		age INTEGER NOT NULL,
		created TIMESTAMP DEFAULT (datetime(CURRENT_TIMESTAMP,'localtime'))
	)";
	$res = $db->exec($sql);
}

// データベースの接続解除
$db->close();

こちらの記事は役に立ちましたか？

コメントありがとうございます！
運営の参考にさせていただきます。