脱力系備忘録BloG ホーム »データベース

SQLite3でUNIQUE制約とCREATEで作成したインデックスに違いがあるのか調べてみた  

UNIQUE制約を定義すると自動的にインデックスが作成されますが、自分で「CREATE INDEX」した場合と違いはないのか?ちょっと気になったので速度を調べてみることに。

category: データベース

PostgreSQLの格納データサイズによって速度低下する問題  

Gigazinさんに非常に興味深い記事「PostgreSQLには「中途半端なサイズ」のデータを格納しないほうがいい」があったので備忘録。

なんでも、PostgreSQLは「固定長のページ」という概念の中にテーブルを格納して管理しているようです。

そのページに収まらない大きなデータを格納する場合は「TOAST」という機能が自動的に稼働してデータを専用のテーブルに分割格納しているようです。
「TOAST」のことは「PostgreSQL 12.3文書 第68章 データベースの物理的な格納 68.2. TOAST」をご覧ください。

で、興味深いのは、
このTOAST機能を利用するほど大きなデータを扱うとPostgreSQLのパフォーマンスが低下する。

のではなく、
TOAST機能を必要としない「ぎりぎりサイズ」程度のデータを格納するとパフォーマンスが低下する。というのがなるほどと思ったわけです。

データベースは奥が深すぎて私には使いこなせないです。
(´・ω・`)
これからも「便利な格納庫」程度の認識で使わせてもらいます。

category: データベース

PostgreSQLで列項目(文字列)と列項目(文字列)のパターンマッチをさせるSQL  

あるテーブルに「A」と「B」という文字型項目があったとして、「A」が「B」に含まれているかの文字列パターンマッチを行うSQLがすぐ忘れそうなので書いておく。

select * from テーブル名 where B like '%' || A || '%';

です。
「%」は0文字以上の並びとの一致を意味します。詳しくはPostgreSQLマニュアルのパターンマッチ「LIKE」をご覧ください。
「||」は文字列の結合です。

category: データベース

SQLITE3でUNIXタイムスタンプを日付文字列に変換する  

SQLITE3で日付を扱う際にテキスト型ではなく数値型でUNIXタイムスタンプを格納している場合があります。
それを、日付文字列形式で取得する方法です。

category: データベース

SQlite3 でソートするときにアルファベットの大文字と小文字を区別したくない  

そんなとき、COLLATE nocase を付けてあげたら解決。

select 項目 from テーブル名 order by 項目 COLLATE nocase;

category: データベース

PostgreSQLで16進数、2進数を扱う場合の表記  

16進数の場合「x」、2進数の場合は「b」を頭に付ける。
x'a' = b'1010' = 10

しかし、これだけだと10進数として計算できないので「::integer」を付ける。

select x'f'::integer + 5;

「20」と表示されます。

category: データベース

PHPとPostgreSQLで正規表現を使ったAND検索  

PostgreSQLで文字列のAND検索を正規表現でできんかなあ。

と思ったらできた。

category: データベース PHP

SQLite3での文字列連結と数値の文字列結合  

SQLite3で文字列結合の関数はなんだろう?と思ったけど関数じゃなくて「||」という演算子で結合できる。
数値はそのまま文字列結合できるのかな?と思ったら、そのままできた。

(例)
select 100 || '*' || 200;
⇒100*200

もちろん、数値のカラムを指定しても連結できる。

category: データベース

PostgreSQLで「invalid input syntax for type timestamp with time zone」  

というエラーが出た。
どうやってこのエラーが出たかと言うと、

select CURRENT_TIMESTAMP-'24 hours';

でエラーになりました。
正しくは、

select CURRENT_TIMESTAMP-interval '24 hours';

と書かなくちゃいけない。
これが足し算の場合

select CURRENT_TIMESTAMP+'24 hours';

は、エラーにならないんだなこれが。
まあ、

select CURRENT_TIMESTAMP+interval '24 hours';

と、「interval」を書くクセを付けた方が良いみたい。

ということで、思い込みによるバグは見つけるのが難しい一例でした。

category: データベース

64ビット環境でのデータベースの符号付INTEGER型整数の範囲  

64ビット環境ではPHP符号付INTEGER型整数の上限は 9223372036854775807 になります。
PostgreSQLはどうなるんだろう?と思って調べてみたら 2147483647 でした。
PostgreSQLの場合、OSのビット数に関係なく「型宣言」で整数の範囲が決まるようです。

INTEGER → 2147483647
BIGINT → 9223372036854775807

気を付けなくちゃいけないのはPHPでビット操作をする場合。PostgreSQLのINTEGER型をPHPの変数に格納した時点で64ビット整数になります。
INTEGER型で「-1」は「0xffffffff」ですが、PHPの変数では「0xffffffffffffffff」になる。
注意が必要です。

ついでにSQlite3も調べてみました。
64ビット環境でのINTEGER型の上限は 9223372036854775807 です。

category: データベース

プロフィール

お問い合わせ

最新記事

最新コメント

▲ Pagetop