SQLiteでデータ更新(INSERT)時に自動で現在の時刻を入れるSQL文を走らせる

● あるテーブルに対して 【UPDATE】(データ更新)が行われた時に「現在時刻をセットするSQL文」を実行するにはSQLiteのトリガー機能を使います。

・データ更新時に現在時刻をセットするトリガー

CREATE TRIGGER my_trigger AFTER UPDATE ON my_table
BEGIN
    UPDATE my_table SET modified_date = datetime('now', 'localtime') WHERE data_id = OLD.data_id ;
END;

テーブル my_table に何かしらの更新があった時に
my_table のカラム modified_date に現在時刻をセットするSQL文が自動的に走ります。

UPDATEINSERT,DELETE に変更すると、【INSERT】(データ登録)、【DELETE】(データ削除)時にSQL文が実行 されるようになります。

・【AFTER】【BEFORE】

【AFTER】のところを【BEFORE】にするとテーブルの更新前に指定したSQL文が動きます。

・【OLD】と【NEW】

【OLD】はUPDATE と DELETE 時に有効です。
【NEW】はUPDATE と INSERT 時に有効です。
INSERT時に OLD.data_id とやるとエラーとなります。

・作成したトリガーの確認

SELECT * FROM sqlite_master WHERE type = 'trigger';

・トリガーの削除

DROP trigger my_trigger;
No.961
02/01 13:58

edit

日付

SQLiteでデータ挿入(INSERT)時に自動で現在の時刻を入れるようにする

テーブルを作成するときに以下のようにします。

CREATE TABLE test_dt (
  data_id integer PRIMARY KEY AUTOINCREMENT,
  user_name varchar NOT NULL,
  modified_date TIMESTAMP DEFAULT(DATETIME(‘now’,’localtime’))
);

このようにすると `modified_date` カラムはデータ登録に何も入力しなくても現在時刻が入力されます。

No.960
07/13 23:17

edit

日付

SQLiteでINSERT, UPDATE 時に現在の日付を入れるSQL文

SQLite3で現在の日付時刻は

datetime('now', 'localtime')

です。

INSERTやUPDATE時に現在の日付を入れるには以下のようにします

INSERT INTO
	my_table (name, modified_date)
VALUES
	'hogehoge, datetime('now', 'localtime');
No.952
07/14 10:48

edit

日付

SQLiteのバージョン、データベースファイルのバージョンを調べる

● SQLiteのバージョンを調べるには

コマンドラインから

sqlite3 --version

と入力します。

● データベースファイル「my.db」のバージョンを調べるには

コマンドラインから

 file my.db

と入力します。

my.db: SQLite 3.x database

というようにバージョンが返ってきます

No.941
01/06 11:56

edit

重複したデータを抽出する

SQLiteではHAVING句が使用できますので以下のように記述します。

テーブル【shop_master】から【shop_name】が重複するデータを抽出します。

SELECT * FROM shop_master GROUP BY shop_name HAVING COUNT(shop_name) > 1

No.909
01/20 23:59

edit

ランダムにデータを取り出す

SQliteではランダム関数は RANDOM() となります。

ですので

SELECT * FROM data_table ORDER BY RANDOM();

とすればOKです。

No.908
01/20 23:59

edit

SQLiteのデータベースをダンプ、リストアする。

データベースファイル「my.db」の内容をダンプして新たなファイル test.sql に保存

echo '.dump' | sqlite3 my.db > test.sql

SQL文を記述した「test.sql」から新しいデータベース「new.db」を作成(リストア)

cat test.sql | sqlite3 new.db

No.902
10/31 15:48

edit

SQLiteのデータベースファイルが壊れているかどうか調べる

SQLiteの操作をしているとたまに

SQL error: database disk image is malformed

というメッセージに遭遇することがあります。

これはデータファイルが壊れている可能性があります。

そこで

SQLiteのデータベースファイル(my.db)が壊れているかどうか調べる

sqlite3 my.db "PRAGMA integrity_check";

を実行します。

問題なければ【OK】が帰ってきます。



No.901
12/05 14:44

edit