

iPhoneアプリ(objective-c)でライブラリFMDBを用いて、SQLiteを操作する際の話です。

あるテーブルでWHERE句を1個だけ含む簡単なSQLを呼んだ際に、`=`と`like`で結果が異なります。  
具体的には`like`では結果が返ってきますが、`=`では結果が返ってきません。

例：  
テーブル「`table1`」が以下の時、

[code]

    id(text) val(text)
    00001 test1
    00002 test2
    00003 test3
    
[/code]

`SELECT * FROM table1 WHERE val = 'test1'`  
では1行も返ってきませんが  
`SELECT * FROM table1 WHERE val like 'test1'`  
では1行返ってきます。

空白や不可視の文字が入っていた場合など逆の結果ならばまだ分かるのですが、`like`のみで出る理由が分かりません。

このような動きをする原因について何か分かる方いますか？  
SQLiteを使用するのは今回が初めてなので根本的な理解不足があるかもしれません。

同じSQLiteファイルをWindowsやmacのDB browser for sqliteで試すと上記に反して正常に結果が返るため、  
文字コード(linux:utf-8,
ios:utf-16)の違いかあるいはFMDBの問題かと想像していますが、調べてもそれらしい情報を見つけられなかったので質問しました。

[環境]  
SQLiteファイルはサーバー上のphpにてPDO(sqlite:)で生成しており、iPhoneアプリでダウンロードし読み込もう(ライブラリ:FMDB)としています。  
サーバー: CentOS 7.9, php 7.1, pdo_sqlite 3.7.17  
端末: iPhone SE2, iPhone SE3, iPhone 13, iPhone 14 Pro, iOS 16, XCode 14.2,
objective-c


CC BY-SA 4.0
2023-04-14T06:01:28.453
94517
2023-04-17T07:51:11.987
2023-04-15T05:59:05.010
43025
57921
question
objective-c sqlite pdo
SQLiteにおける=とlikeの違い


解決しました。  
コメントの通り、TEXT型で定義されてる列にデータがBLOBでセットされていた事が原因でした。

[code]

    SELECT typeof(id) FROM MHYJS WHERE val like 'test1';
    result:blob
    
[/code]

列の定義とは異なる型が入ってしまうんですね。  
likeは文字毎比較だったと思うので、結果的に文字毎でエンコードされて認識されたという事でしょうか。

サーバー上では

[code]

    $sqlite_stmt->bindValue(':val', "hogehoge", SQLITE3_TEXT);
    
[/code]

とやっているんですが、何故BLOBで入ったんだろうか…

* * *

追記：  
BLOBで入ってしまった理由が分かりました。  
実に愚かで恥ずかしい話ですが、phpでPDOからSQLiteを使っているのにも関わらず、引数に与えた型を表す定数が通常のSQLite3クラスの物でした。単なる定数間違いです。

以下が正しいです  
`$sqlite_stmt->bindValue(':val', "hogehoge", PDO::PARAM_STR);`

間違ってSQLite3クラスの定数をPDOで呼んだSQLiteに型の引数として与えると、noticeレベルのエラーも吐くことなくとりあえず全てblobで動作するようです。

要らない知見かもしれませんが、追記しておきます


CC BY-SA 4.0
2023-04-14T08:37:54.220
94518
2023-04-17T07:51:11.987
2023-04-17T07:51:11.987
57921
57921
94517
answer


`=` 比較演算子と `LIKE` 演算子の結果の違い について  
本家に同様の Q＆A があり, いくつか回答があります (他にも原因があるかはよく分からないが)

参考: (stackoverflow.com) [Sqlite: LIKE matches value, but equality
doesn't](https://stackoverflow.com/questions/14820085/sqlite-like-matches-
value-but-equality-doesnt)

* * *

① `LIKE` 演算子の大文字と小文字の区別  
(基本的には `LIKE`では ASCIIの範囲の大文字小文字の違いは無視する, こと)

以下 [SQLite LIKE](https://www.sqlitetutorial.net/sqlite-like/) より

> Note that SQLite `LIKE` operator is case-insensitive. It means `"A" LIKE
> "a"` is true.

> However, for Unicode characters that are not in the ASCII ranges, the `LIKE`
> operator is case sensitive e.g., `"Ä" LIKE "ä"` is false.  
>  In case you want to make `LIKE` operator works case-sensitively, you need
> to use the following
> [PRAGMA](https://www.sqlite.org/pragma.html#pragma_case_sensitive_like):  
>  `PRAGMA case_sensitive_like = true;`

② TEXT カラムに別の storage classes (BLOB Storage Class) で格納されることもある

[The Advantages Of Flexible Typing](https://www.sqlite.org/flextypegood.html)
より

Column Datatype | Types Allowed In That Column  
---|---  
INTEGER | INTEGER, REAL, TEXT, BLOB  
REAL | REAL, TEXT, BLOB  
TEXT | TEXT, BLOB  
BLOB | INTEGER, REAL, TEXT, BLOB  
  
[Datatypes In SQLite](https://www.sqlite.org/datatype3.html) に詳細と 例 が記されています  
(以下抜粋  
１つ目の insertでは VALUES に文字列与えてもそれぞれに適した型で  
２つ目の insertでは BLOB を与えると, どのカラムでも BLOB)

[code]

    SQLite version 3.40.1 2022-12-28 14:03:47
    Enter ".help" for usage hints.
    sqlite> CREATE TABLE t1(
            t  TEXT,         -- text affinity by rule 2
            nu NUMERIC,      -- numeric affinity by rule 5
            i  INTEGER,      -- integer affinity by rule 1
            r  REAL,         -- real affinity by rule 4
            no BLOB          -- no affinity by rule 3
    );      --      ) strict;
    
    -- Values stored as TEXT, INTEGER, INTEGER, REAL, TEXT.
    INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0', '500.0');
    -- INSERT INTO t1 VALUES('500.0', 500.0, 500, 500.0, x'0500');
    sqlite> SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
    text|integer|integer|real|text
    
    sqlite> -- BLOBs are always stored as BLOBs regardless of column affinity.
    DELETE FROM t1;
    INSERT INTO t1 VALUES(x'0500', x'0500', x'0500', x'0500', x'0500');
    sqlite> SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
    blob|blob|blob|blob|blob
    
[/code]

対策としては以下の方法が考えられるでしょう

  1. STRICT を指定する ([STRICT Tables](https://www.sqlite.org/stricttables.html))
  2. CHECK 制約を設ける。(`check(typeof(val) == 'text')` などのように)
  3. データベースに変更があるたび内容を確認する

STRICT tableは, 変更が大変かもしれません(ものによっては)

データ内容の確認は最も容易です (データ量多ければ時間かかるが)

[code]

    select id,typeof(id),typeof(val) from table1 where not typeof(val) = "text";
    
[/code]

> 何故BLOBで入ったんだろうか

更新のタイミングで上記スクリプトで調べるのが, (時間はかかるかもだけど)追求可能かもしれません


CC BY-SA 4.0
2023-04-16T05:36:43.567
94537
2023-04-16T05:36:43.567
43025
94517
answer