SQLite Part.10

**NAME IS NULL** · 2012/08/16(木) 21:54:16.20

組み込み型データベース SQLite について語るスレッドです。

SQLite
http://www.sqlite.org/

・C/C++ API
　http://www.sqlite.org/c3ref/intro.html

・Syntax
　http://www.sqlite.org/lang.html

・Limits
　http://www.sqlite.org/limits.html

・Support
　http://www.sqlite.org/support.html

**NAME IS NULL** · 2017/01/07(土) 20:17:03.63

AとBを結合すりゃいいんじゃね

**NAME IS NULL** · 2017/01/07(土) 21:29:52.61

>>578
In の右辺が select なら exist に。そうでないなら素直に and と or 使いなされ。

>>579
A にも B にも現れない文字を区切りに使えるならいいけど、単純に連結するのは NG

**NAME IS NULL** · 2017/01/07(土) 22:57:02.36

SQLite Release 3.16.2 On 2017-01-06
http://www.sqlite.org/releaselog/3_16_2.html

**NAME IS NULL** · 2017/01/07(土) 22:59:43.97

いっぱいでるぅ

**NAME IS NULL** · 2017/01/08(日) 08:53:31.25

>>579, 580
やり方色々あるんですね。
とても参考になりました。
ありがとうございます。

ところで、

>> In の右辺が select なら exist

って具体的にはWHERE句にどう書くのでしょうか？

**NAME IS NULL** · 2017/01/08(日) 11:26:10.96

SELECT * FROM HOGE
WHERE EXISTS(SELECT * FROM FUGA WHERE HOGE.A = FUGA.A AND HOGE.B = FUGA.B)

**NAME IS NULL** · 2017/01/09(月) 12:41:36.74

>>584
コード、ありがとうございました！
さっそくやってみます。

**NAME IS NULL** · 2017/01/09(月) 15:11:12.56

>>584
横からですが、とん

**NAME IS NULL** · 2017/02/08(水) 09:16:30.67

explainの見方がよくわからないのですが、
よくわからない人は、どこ見てそれがいいSQLであると判断すればいいですか？
ステップ(addr)の数が少なければ少ないほどいいってことでしょうか？
アプリケーションから実行速度を測る事はできるのですが、
純粋なSQLの実行速度を知りたいのですが、何か方法ありますか？

**NAME IS NULL** · 2017/02/08(水) 12:43:05.00

複数行insertでprepared statementを使おうとしたら999制限に引っかかって挿入出来ません
何か良い解決策はありませんか？

**NAME IS NULL** · 2017/02/08(水) 14:08:11.63

SQLITE_LIMIT_VARIABLE_NUMBER 変更するとか？

**NAME IS NULL** · 2017/02/09(木) 19:02:21.92

https://www.youtube.com/watch?v=quIHgwuF6r4&;sns=em

**NAME IS NULL** · 2017/02/14(火) 02:29:06.57

3.17.0

https://sqlite.org/releaselog/3_17_0.html

**NAME IS NULL** · 2017/03/18(土) 06:19:56.54

SQLiteに保存する文字コードは、UTF-8が基本ですか？
SJISを保存すると問題おきますでしょうか？

**NAME IS NULL** · 2017/03/18(土) 09:45:20.02

>>592
utf sjisも
はい

**NAME IS NULL** · 2017/04/11(火) 17:38:40.65

SQLiteにSJISで保存すると何か問題が起きるんですか？
ホントなら具体例を教えてください。

**NAME IS NULL** · 2017/05/23(火) 22:36:11.99

3.19.0

https://sqlite.org/releaselog/3_19_0.html

**NAME IS NULL** · 2017/05/24(水) 07:42:43.39

>>592
DBのエンコーディングがutf8かutf16ってだけで
同じテーブルに別々の文字コード入れようがsjisで格納しようが別に不具合は起きない
当然そのままだと文字化けを起こす可能性があるが
それをどうするかはアプリケーション側の問題

**NAME IS NULL** · 2017/05/24(水) 21:23:46.06

>>596
LIKE演算子でちゃんと「%」を「%」と認識してくれるか、とか心配だが
大丈夫なのかな。

**NAME IS NULL** · 2017/05/25(木) 10:39:50.92

どのDBもクライアントのエンコーディングは意識しないとだけど
それはDBのエンコーディングとは別の話

**597** · 2017/05/25(木) 18:32:52.30

>>598
ごめんちょっと言い方が足りなかった。

マルチバイト文字列のSQL文を、DB側が正しく構文解析してるれるかなぁ・・・という質問でした。
たとえば、SELECT... WHERE xxx like '〇〇〇%';
というSQL文の場合、「〇〇〇」を正しく解釈し、次の文字の「%」を認識してくれるのかなぁという心配です。
〇がどんなマルチバイト文字でも大丈夫なのかな？

**NAME IS NULL** · 2017/05/25(木) 18:44:27.94

うーん、、そのつもりで答えたんだけどな
クライアントの環境によるだろうけど、固定文字コードかロケールで切り替えるようになってるはずで
そのコードに沿ってないとSQL解釈も期待どおりにならない可能性はあるね

**NAME IS NULL** · 2017/05/25(木) 18:47:20.11

ちなみにクライアント側が文字コードあってれば
サーバの文字コードに変換するのはクライアントの役目
通常サーバ側の文字コードはクライアントから切り替えられるようになってるので問題ないが
SQLiteの場合サーバ側の処理もクライアントがやるからそれだけ合わせとけばいいかと

**NAME IS NULL** · 2017/05/26(金) 08:02:08.57

3.19.2

https://www.sqlite.org/releaselog/3_19_2.html

**NAME IS NULL** · 2017/06/09(金) 02:35:11.50

3.19.3

https://sqlite.org/releaselog/3_19_3.html

**NAME IS NULL** · 2017/06/09(金) 19:27:52.36

日本と海外のユーチューバーは収入の仕組みが違う？！どっちが儲かるか？
http://youtube-youtuber.com/360.html

日本の従来型のユーチューバーは、広告収入をメインの収入源としています
１．動画にのせる広告（動画広告＋画像広告）からの報酬や
２．スポンサー収入（企業からの依頼を受けて商品を紹介）

海外のユーチューバーの傾向として、広告収入ではなく、自分のビジネスを
メインにすることによって広告収入を遥かに超える収入を叩き出しています。
例えば、beauty guru（美のカリスマ）と謳われる、ユーチューバーのミシェル・ファンは
広告収入は１８００万得ていますが・・他方で美容化粧品会社Ipsyを立ち上げて、
年収１億ドル（約１１０億円）を売り上げました。
彼女は「メイクのチュートリアル動画」を投稿していますが、動画の中で自分の会社の商品を
使っているんです。
そしてその中で使った商品を、online shopで買える仕組みにしています。
他人の商品を紹介して、そこから「おこぼれ」を貰うより、自分の商品を売った方が
遥かに儲かりますからね。

リンジー・スターリングは踊りながら、バイオリンを演奏する、アーティストですが・・・
彼女の場合も、あくまでも動画を投稿するのは、自分のビジネスである、
ツアーチケットやアルバムの販売のためなのです。

**NAME IS NULL** · 2017/06/18(日) 17:51:34.06

ついにV4が来てしまったようだな。
これでDB界のパワーバランスが変わる。

**NAME IS NULL** · 2017/06/18(日) 19:16:36.85

え？
まだ3.19じゃないの？

**NAME IS NULL** · 2017/06/18(日) 21:32:39.55

https://sqlite.org/src4/doc/trunk/www/index.wiki
まだ作業中なのかな。でもじきに出るだろう

**NAME IS NULL** · 2017/06/18(日) 22:08:31.74

(ﾟ∀ﾟ三ﾟ∀ﾟ)、

**NAME IS NULL** · 2017/06/21(水) 22:57:56.93

>>605
おぉ！

**NAME IS NULL** · 2017/06/22(木) 04:27:42.58

よしオラクルを空売りしよう！

**NAME IS NULL** · 2017/06/22(木) 23:52:30.19

4だとZIPVFSが無償で使えたりしないかなｗ

**NAME IS NULL** · 2017/06/23(金) 20:53:47.60

numeric 型が気になるなぁ

**NAME IS NULL** · 2017/06/26(月) 05:02:46.43

おぉ！

**NAME IS NULL** · 2017/07/07(金) 19:31:51.69

４番ファースト

**NAME IS NULL** · 2017/07/08(土) 09:01:20.67

>>605
ストアドプロシージャとか使える？

**NAME IS NULL** · 2017/08/02(水) 07:53:36.80

3.20.0

https://sqlite.org/releaselog/3_20_0.html

**NAME IS NULL** · 2017/08/11(金) 08:00:27.63

ファイルシステムよりも35％高速に
ttp://postd.cc/faster-than-fs/

**NAME IS NULL** · 2017/08/12(土) 00:57:24.03

なんだと

**NAME IS NULL** · 2017/08/29(火) 18:43:15.91

sqliteだと、managementstudioみたいなツールは何が使い勝手いいですか？

**NAME IS NULL** · 2017/08/29(火) 21:14:17.01

>>619
何年か前にフリーのを一通り試したときは
結局Firefoxのアドオンのやつがよかった

**NAME IS NULL** · 2017/08/29(火) 22:18:32.51

>>620
アドオンすか・・インストール型とかイメージしてました

**NAME IS NULL** · 2017/08/29(火) 22:18:48.87

>>620
アドオンすか・・インストール型とかイメージしてました

**NAME IS NULL** · 2017/08/29(火) 23:12:13.61

SQLiteStudioまあまあ
SQLite以外を扱う必要がなければ十分
見た目もManagement Studioに近い

**NAME IS NULL** · 2017/08/31(木) 13:06:37.85

いるいるいるよね貝社員

**NAME IS NULL** · 2017/08/31(木) 15:54:23.53

貝獣物語

**NAME IS NULL** · 2017/09/21(木) 14:38:04.47

MATCHだけど
SELECT * FROM vtbl AS v WHERE v MATCH 'foo';
だとvなんてカラムねーよとエラーになる
SELECT * FROM vtbl WHERE vtbl MATCH 'foo';
ならOK
なんか納得いかない

vtblはvirtual tableです

**NAME IS NULL** · 2017/10/02(月) 01:35:45.11

MATCHで指定するのはテーブルじゃなくてカラムだと思うんだが

**NAME IS NULL** · 2017/10/02(月) 02:46:34.33

>>627
if the identifier is the name of the FTS table itself, then the MATCH operator evaluates to true for each row of the FTS table for which any column contains the search term
https://www.sqlite.org/fts3.html#simple_fts_queries

**NAME IS NULL** · 2017/11/04(土) 19:10:59.78

3.21.0
https://www.sqlite.org/releaselog/3_21_0.html

**NAME IS NULL** · 2017/11/08(水) 11:10:42.96

SQLite4終了?

**NAME IS NULL** · 2017/11/08(水) 11:43:00.04

>>630
どういうこと？

**NAME IS NULL** · 2017/11/08(水) 12:18:11.09

>>631
開発に失敗したらしい

**NAME IS NULL** · 2017/11/08(水) 13:25:37.98

https://sqlite.org/src4/info/c0b7f14c0976ed5e

> All development work on SQLite4 has ended. The experiment has concluded.

**NAME IS NULL** · 2017/11/08(水) 15:34:14.65

>>633
肝心なところが引用できてないぞ

**NAME IS NULL** · 2017/11/08(水) 15:47:10.99

君が肝心と思ったところは君が引用すればいいと思うよ

**NAME IS NULL** · 2017/11/08(水) 15:47:57.57

SQLite4は元々リリースするつもりは無かったのか?

**NAME IS NULL** · 2017/11/08(水) 16:06:21.39

>>635
英語読めないのか

**NAME IS NULL** · 2017/11/08(水) 16:16:04.08

>>637
はい、そうです。

**NAME IS NULL** · 2017/11/11(土) 20:56:08.93

初歩的な質問なのですが、
SQLiteのテーブルにboolean型のカラムを作って
C#のプログラム側から参照してbool型の変数に代入しようとすると
型が違うと怒られます

sqliteのboolean型はbool型に入れられないのでしょうか

**NAME IS NULL** · 2017/11/11(土) 21:23:40.52

>>639
sqliteのboolean型はint型じゃないのかな。
intでバインド

**NAME IS NULL** · 2017/11/11(土) 21:46:12.74

>>640
ありがとうございます
C#側でint型変数に代入を試みたところ0 1で値が入りました

**NAME IS NULL** · 2017/11/11(土) 21:51:16.84

>>641
ここに書いてある
https://sqlite.org/datatype3.html

**NAME IS NULL** · 2017/11/11(土) 22:08:14.79

>>642
ありがとうございます
読んでみます

**NAME IS NULL** · 2017/11/18(土) 01:48:42.44

>>636
元々実験バージョンだったはず

**NAME IS NULL** · 2017/11/18(土) 12:09:57.30

なんのための実験？

**NAME IS NULL** · 2017/11/24(金) 19:36:24.57

質問です。
ATTACHをすると異常に遅くなるのですが、これは最新バージョンで直ってますか？
或いは何らかの対策がありますか？

[環境]
PHP7.1.8+SQLite3.15.1(今年9月時点でのxampp最新版)
SQLite3.21.0のDLLをDLして差し替えたが動作せず

[状況]
・openよりもattachの方が優先順位が低いのか、追い越されている時がある。
・基本的にシーケンシャルにリクエストをしている。
（30秒ごとにキューに溜まったリクエストを順次送信する。
前リクエストの結果を受け取ってから0.1秒待って次リクエスト発行、これでシーケンシャル。
ただし30秒でキューが掃けない場合、上に被せるので、一時的に2並列になるときがある。）
・連続した場合、2回目以降は早い（ことが多い）。1回目は6秒程度、2回目以降は0.1-0.2秒程度で終了する。
・Timeout設定は5秒だが25秒かかっても問題なく正常終了する（タイムアウトしない）
・用途は掲示板で、リクエストはスレ立て/投稿/閲覧/スレ落ちを混ぜてテスト中。
・試行錯誤した結果、ATTACHしただけで遅いようだと判明した（ATTACHしたDBを触らずとも遅い）

[推定]
連続した場合には早いので、EXCLUSIVEロック待ちだと推定している。
ただしリクエストはほぼ全部シーケンシャルなので、本来はロック待ち自体がおかしい。
なお以下仕様のため、sqlite_masterの合成に時間がかかっているのか？とも思い、
全部mainをつけてみたが変化なし。（連続した場合に早いのでこの線もおかしいが）
> If the name of the table is unique across all attached databases and the main and temp databases, then the schema-name prefix is not required.
> http://www.sqlite.org/lang_attach.html

今のところ納得できる理由（原因）が見つかっておらず、対策が出来ていません。これについて、
対策方法(最新バージョンで直っているのならそれでよし)をご存知であればご教授ください。

最新版をPHPで試す方法はPHPのスレで聞いてみます。

**NAME IS NULL** · 2017/11/25(土) 14:28:33.17

>>646
自己解決しました。

ATTACHの速度が対象ＤＢのtable数にかなり依存するため、
ATTACHするとその場でsqlite_masterの合成をするのだと推定しました。
従って、ATTACH順を変更することにより改善できます。
今回は3つのDBを同時に扱うため、3C3の6通りでしかなく、速いのを選ぶことにしました。

なお、この実装がよいのかは若干疑問です。
通常はATTACH対象をさほどゴリゴリやらないので、
合成ではなく直列(カスケード）にして、その都度順に引いた方が速いと思われます。
或いは、合成/直列を選択するpragmaがあればよいのですが、これもないようです。
これらの問題のため、最新版等では改善している可能性もあります。（試せていません）

何か対応しようとしてくださった方が居ましたら、ありがとうございました。

**NAME IS NULL** · 2017/11/25(土) 15:52:23.93

>>647
いやいやなになに

**NAME IS NULL** · 2017/11/25(土) 16:20:09.78

>>647
ついでに続報を言うと、
ATTACHが遅くなるのはシステムキャッシュが無効（容量が足りない）時だけのようです。
合成自体は十分速く、
断片化したDB内のsqlite_masterのHDDからの読み出しに時間がかかっているようです。
だからまあ、現在(SQLite 3.15.1)の実装で問題ない、ということにもなりますが、、、

私の環境では、
RAMに十分空きがある(システムキャッシュにヒットする)場合には、
ATTACHは0.04-0.06秒程度で終了します。
RAMを使い尽くしている（システムキャッシュに十分な容量がない)場合には、
ATTACHだけで2-6秒程度かかり、これはVACUUM等で改善します。
また、ATTACHの順番を変えると何となく雰囲気が変わります。
（ただしこの結果は少し矛盾してますが）

**NAME IS NULL** · 2017/11/25(土) 16:21:49.63

わざわざATTACHする理由は

**NAME IS NULL** · 2017/11/25(土) 17:33:35.10

>>650
DB間のデータ転送をtransactionで括るため

**NAME IS NULL** · 2017/11/25(土) 17:54:56.49

>>651
ATTACHするDBは違うホストにあるって事?
2相コミットのかわり?

**NAME IS NULL** · 2017/11/25(土) 18:59:03.84

>>652
> 2相コミット
なんだこりゃ？Wiki読んだが何に使うのかよく分からん。
分散掲示板という意味なら面白いと思うし一部に待望されているが、俺が今作っているのはこれではない。
DBは同一ホストにある。

単にSQLiteの性能を出すために適宜DBを分割しているだけ。
というか、ロック単位がDBだから、平行させるためには分割するしかなく、そうしてるだけ。
(テーブル単位でロックできる他DBなら分割する意味はない)
例えばtech板なら、
「スレ本体(tech.live)」「スレヘッダのみ（全板共通）(tags.db)」「スレアーカイブ(tech.archived)」にしてあり、
スレ落ち時に
・tags.dbからヘッダを消去
・tech.live->tech.archivedにスレ内容転記
・tech.liveからスレを消去
する為にこれら3つをトランザクションで括っている。
この間に別板のスレの読み出しは、例えばprog板ならprog.liveだけで完結するのでブロックされない。
書き込みは全板共通のtags.dbによってブロックされてしまうが、これは仕様上致し方なし。
掲示板なので読み出し性能重視でこの設計にしている。

**NAME IS NULL** · 2017/11/25(土) 19:07:59.44

>>653
2相コミットってSQLiteにあったのか
侮れんな

そんな面倒なことするのなら他のDBMSで良いんじゃね? とは思うけど
SQLiteじゃなきゃ駄目な理由があるんでしょうな

**NAME IS NULL** · 2017/11/25(土) 19:18:27.38

>>654
複数DBのトランザクションという意味なら、それは追加されたらしい。
いつからかは知らん。

> SQLiteじゃなきゃ駄目な理由があるんでしょうな
共用鯖上で、PCの知識がほぼ無いadminでも扱える掲示板を目指している。
そのデプロイの都合上、PHP+SQLiteだ。
上位版はNodeで用意する予定だが、こちらもSQLiteになる。
それ以上の上位版は、他に既に色々あるからそっち使え、で行く。
興味あるのなら以下を読んでくれ。
https://meguca.org/g/2724701

**NAME IS NULL** · 2017/11/25(土) 19:32:02.29

そういえばSQLiteのmainデータベースの名前を変える方法知らないか？
ググったけどなさそうなんだが。

具体的には>>647を試したコードがmainが3種類必要で結果的に3つコピペしてありうざい。
(live,tags,archiveにすれば共通化できるが、どれかをmainにしないといけない縛りのため)

**NAME IS NULL** · 2017/11/25(土) 19:40:25.85

>>655
DBエンジンのインストールや設定って素人は難しく感じるようですね。
SQLiteは1ファイルで独立エンジン不要なのでローカルDBに使う分には良いですわ。

**NAME IS NULL** · 2017/11/25(土) 19:42:22.24

>>656
データベースのファイル名を変えるだけでは？

**NAME IS NULL** · 2017/11/25(土) 19:47:20.46

>>658
違う。
というか、調べた限り、ATTACH自体が新しい機能らしくて、そこまで整備されてない。
どの名前でもopenしたものはmainになるし、メモリ上のはtempになる。
それにATTACHしたものは自由に名前を付けられる、というか、つけないとsyntax上アウト。
で、mainの別名をつけられれば助かるんだが、、、
なお、tableやcolumnは別名を付けられる。

**NAME IS NULL** · 2017/11/25(土) 19:54:47.58

>>659
あ、そう言う意味でしたか。
開発元に聞くか自分でソースコードを見るしか無さそうですが、
StackOverflowで聞けば教えて貰えるかも?
困った時はStackOverflow英語版!

**NAME IS NULL** · 2017/11/25(土) 20:03:08.43

>>660
まあググっても出てこないし、多分まだ機能自体がないと諦めてるんだわ。
ありがとう。

**NAME IS NULL** · 2017/11/25(土) 20:10:41.94

>>661
dummy db作って実際に使うDBは全部ATTACHでと言うのは駄目なの?
遅くはなるだろうけど。

**NAME IS NULL** · 2017/11/25(土) 20:34:48.34

>>662
いやこれはソースコード上の美学の問題だから、それを構造まで広げるのはマズい。
どうしてもというのなら関数にすればいいだけだし、その方がマシ。
どのみち最終的にはコメントアウトで捨てるコードだし、
特にいいやり方がなければコピペで放置だ。

ただしtempにいったん受けた方が速い可能性があり、結果的にそれをやるかも。
具体的に言うと、sqlite_masterのマージで
少ない方を多い方にINSERTするわけだが、（どうやらこの判定はしている）
今のところliveに150、archivedは1000-10000スレ程度で運用する予定なので、
最低150のINSERTが発生して、これが0.05sかかっている。（ように見える）
tempで空テーブルを用意してやればこれが不要になる。
落ちるスレに150レスなら転記でINSERT150回必要であり、単純には倍速になる可能性がある。
というわけだ。

**NAME IS NULL** · 2017/11/25(土) 21:11:41.89

>>663
旦那、ソースコードの美学なんてもんに拘ってたら、日本の薄汚いIT業界で食っていけませんぜwww

**NAME IS NULL** · 2017/11/27(月) 12:31:20.51

日本の薄汚いIT業界でSQLiteなんて使わんだろ。
だいたいは自分の自主開発用とか趣味用だろ。
SQLiteでクライアントに納品してる奴とか保守してる奴とかいるの？
いるなら俺も雇ってくれｗ

**NAME IS NULL** · 2017/11/27(月) 12:38:03.57

自主開発てなんかイヤラシイな

**NAME IS NULL** · 2017/11/27(月) 12:56:06.29

イヤラシイのは自家発電

**NAME IS NULL** · 2017/11/27(月) 13:09:39.99

>>665
居るよ!
SQLServer Express、mdbの代わりに使える。
Andoroidアプリならデフォでしょうし。

**NAME IS NULL** · 2017/11/27(月) 14:45:54.11

SQLiteの爆速さと手軽さは中毒になるからな

**NAME IS NULL** · 2017/11/27(月) 21:00:36.34

こおゆう大げさな事をゆう奴を信用してはならん

**NAME IS NULL** · 2017/11/27(月) 22:08:08.61

シングルユーザーで使う分には便利だよな
トランザクションとか自分で実装したくないし、遥かに信頼できる

**NAME IS NULL** · 2017/11/27(月) 22:10:53.86

>>669
速いかは微妙じゃないか？

俺は結局、PRAGMA SYNCHRONOUS = OFF; PRAGMA JOURNAL_MODE = MEMORY;
を常用する羽目になっているが、これってどうなのよ？と思うし。
ただし作者の「ジャーナルにはHDDの2回転が必要で、それくらいはかかる」
というのもまた正しいわけだが。

**NAME IS NULL** · 2017/11/27(月) 22:38:09.33

更新は遅いね

**NAME IS NULL** · 2017/11/28(火) 01:19:55.49

いっぱいあるだろ。
何と言ってもPublicDomainってのが使いやすい。
使ってても言わなくていいし。

**NAME IS NULL** · 2017/11/28(火) 19:58:13.47

ちょっと複雑なSQLを書けないところがNGかな。
副問い合わせとか。

それ以外はすんごくいい。

**NAME IS NULL** · 2017/11/28(火) 21:11:17.44

服問い合わせ程度ならSQLite3でできるでしょ

**NAME IS NULL** · 2017/11/28(火) 21:37:51.97

RIGHT OUTER JOIN、FULL OUTER JOIN が出来ないくらい?

**NAME IS NULL** · 2017/11/29(水) 14:42:34.87

>>673
AutoCommitしてんじゃねーの？

**675** · 2017/11/30(木) 05:52:18.47

>>676, 677
え？ほんと？
おぼろげな記憶だけど、

SELECT ...
FROM ...
WHERE xxx IN (SELECT...)

とかできなかったような・・・