SQLite問題集

如何創建自增字段？
SQLite 支持哪些數據類型？
為什麼能向 SQLite 數據庫的整型字段中插入字符串？
為什麼 SQLite 認為表達式 '0'=='00' 為真？
為什麼 SQLite 不允許在同一張表裡使用 '0' 和 '0.0' 作為兩個不同的行的主鍵？
為什麼不能在 Linux box 中讀取在 SparcStation 中創建的 SQLite 數據庫？
多個應用程序或者同一個應用程序的多個例程能同時存取同一個數據庫文件嗎？
SQLite是線程安全的嗎?
如何列出一個 SQLite 數據庫中的所有的表/索引？
SQLite數據庫是否有已知的大小限制？
在 SQLite 中 VARCHAR 的最大長度是多少？
SQLite 是否支持 BLOB 類型？
如何從一個已存在的 SQLite 數據表中添加/刪除字段？
我刪除了很多數據但是數據庫文件並沒有減小，是不是 Bug？
是否能將 SQLite 用於商業用途而不用交版權費用？
我如何使用含有單引號(')的字符串？
SQLITE_SCHEMA 錯誤代表什麼？
為什麼ROUND(9.95,1) 返回 9.9 而不是 10.0? 難道9.95 不該向上進位麼？
(1) 如何創建自增字段？
    簡單的回答：一個聲明為 INTEGER PRIMARY KEY 的字段將自動增加。
    這裡是詳細的答案： 從 SQLite 的 2.3.4 版本開始，如果你將一個表中的一個字段聲明為 INTEGER PRIMARY KEY，那麼無論你何時向該表的該字段插入一個 NULL 值，這個 NULL 值將自動被更換為比表中該字段所有行的最大值大 1 的整數；如果表為空，那麼將被更換為 1。比如，假設你有這樣的一張數據表:
        CREATE TABLE t1(
          a INTEGER PRIMARY KEY,
          b INTEGER
        );
    在這張數據表裡，聲明
        INSERT INTO t1 valueS(NULL,123);
    在邏輯意義上等價於：
        INSERT INTO t1 valueS((SELECT max(a) FROM t1)+1,123);
    一個新的API函數 sqlite3_last_insert_rowid() 返回最近的插入操作的整形鍵
    注意這個整型鍵始終比之前插入表中的最後一個鍵大1。新鍵相對於表中的已有鍵來說是唯一的， 但它可能與之前從表中刪除的鍵值重疊。要始終得到在整個表中唯一的鍵，在INTEGER PRIMARY KEY的聲明之前加關鍵詞AUTOINCREMENT.這樣被選的鍵將總是比表中已存在的最大鍵大1。若可能的 最大鍵已存在於表中，INSERT操作將失敗並返回一個SQLITE_FULL錯誤碼.
(2) SQLite 支持哪些數據類型？
    參見 http://www.sqlite.org/datatype3.html.
(3) 為什麼能向 SQLite 數據庫的整型字段中插入字符串？
    這是一個功能，不是一個 bug。你可以在任何字段中放任何信息，而不用管字段聲明為什麼類型。 你可以往整型字段中插入任意長度的字符串，或者往布爾字段中插入浮點數，或者往字符字段中 插入日期。在 CREATE TABLE 命令中你指定給這個字段的數據類型不會限制插入這個字段的數據。 所有的字段可以插入任意長度的字符串。但對於 INTEGER PRIMARY KEY 字段例外。這種字段只能 存放一個64位的整數，否則會出錯。
    但SQLite會默認你希望使用聲明的字段類型。所以，比如你希望在一個聲明為INTEGER的字段 中插入一個字符串，SQLite會試圖將其轉換為一個整數。如果轉換成功，那麼整數將被插入，否 則插入字符串，這種特性有時被稱作type or column affinity.
(4) 為什麼 SQLite 認為表達式 '0'=='00' 為真？
    在 2.7.0 之後，表達式不成立。參見文檔 datatypes in SQLite version 3
(5) 為什麼 SQLite 不允許在同一張表裡使用 '0' 和 '0.0' 作為兩個不同的行的主鍵？
    你的主鍵一定是數值類型的，把類型改為 TEXT 就可以了。
    每一行必須有一個唯一的主鍵。作為一個數字類型的字段，SQLite 認為 '0' 和 '0.0'的值是相同的， 因為他們在數字上的比較是相等的（看前面的問題）因此值不是唯一的。
(6) 為什麼不能在 Linux box 中讀取在 SparcStation 中創建的 SQLite 數據庫？
    你需要升級你的 SQLite 庫到 2.6.3 或更新版本。
    x86 處理器是 little-endian 型的而 Sparc 是 big-endian 型的。新版本的 SQLite 解決了這個問題。
    註： 　　big endian和little endian是CPU處理多字節數的不同方式。例如「漢」字的Unicode編碼是6C49。那麼寫到文件裡時，究竟是將6C寫在前面，還是將49寫在前面？如果將6C寫在前面，就是big endian。還是將49寫在前面，就是little endian。
(7) 多個應用程序或者同一個應用程序的多個例程能同時存取同一個數據庫文件嗎？
    多進程可以同時打開同一個數據庫，也可以同時 SELECT 。但只有一個進程可以立即改數據庫。
    SQLite使用讀/寫鎖定來控制數據庫訪問。(Win95/98/ME 操作系統缺乏讀/寫鎖定支持，在低於 2.7.0 的版本中，這意味著在 windows 下在同一時間內只能有一個進程讀數據庫。在版本 2.7.0 中 這個問題通過在 windows 接口代碼中執行一個用戶間隔幾率讀寫鎖定策略解決了。) 但如果數據庫文件在一個 NFS 文件系統中，控制並發讀書的鎖定機制可以會出錯。因為 NFS 的fcntl() 文件鎖定有時會出問題。如果有多進程可能並發讀數據庫則因當避免把數據庫文件放在 NFS 文件系統中。 根據微軟的文檔，如果不運行 Share.exe 後台程序則 FAT 文件系統中的鎖定可能不工作。對 Windows 非常有經驗的人告訴我網絡文件的鎖定有許多問題並且不可靠。如果是這樣，在2個或以上 Windows 系統中共享一個 SQLite 數據庫文件會導致不可預知的問題。
    我們知道沒有其他的嵌入式 SQL數據庫引擎比SQLite支持更多的並發性。 SQLite允許多進程 同時打開和讀取數據庫。任何一個進程需要寫入時，整個數據庫將在這一過程中被鎖定。但這一般僅耗時 幾毫秒。其他進程只需等待然後繼續其他事務。其他嵌入式SQL數據庫引擎往往只允許單進程訪問數據庫。
    但是，client/server型的數據庫引擎 (如 PostgreSQL, MySQL, 以及 Oracle) 通常支持更高的並發度， 並支持多進程同時寫入同一個數據庫。由於總有一個控制良好的服務器協調數據庫的訪問，這才保證了以上 特性的實現。如果你的應用需要很高的並發度，你應該考慮使用client/server數據庫。事實上，經驗告訴 我們大多數應用所需要的並發度比他們的設計者們想像的要少得多。
    當 SQLite 嘗試操作一個被另一個進程鎖定的文件時，缺省的行為是返回 SQLITE_BUSY。你可以用 C代碼更改這一行為。 使用 sqlite3_busy_handler() 或sqlite3_busy_timeout() API函數。
    如果兩個或更多進程同時打開同一個數據庫，其中一個進程創建了新的表或索引，則其它進程可能不能立即看見新的表。其它進程可能需要關閉並重新連結數據庫。
(8) SQLite是線程安全的嗎?
    有時候是的。為了線程安全，SQLite 必須在編譯時把 THREADSAFE 預處理宏設為1。在缺省的發行的已編譯版本中 Windows 版的是線程安全的，而 Linux 版的不是。如果要求線程安全，Linux 版的要重新編譯。
    「線程安全」是指二個或三個線程可以同時調用獨立的不同的sqlite3_open() 返回的"sqlite3"結構。而不是在多線程中同時使用同一個 sqlite3 結構指針。
    一個sqlite3結構只能在調用 sqlite3_open創建它的那個進程中使用。你不能在一個線程中打開一個數據庫然後把指針傳遞給另一個線程使用。這是因為大多數多線程系統的限制（或 Bugs？）例如RedHat9上。在這些有問題的系統上，一個 線程創建的fcntl()鎖不能由另一個線程刪除或修改。由於SQLite依賴fcntl()鎖來進行並發控制，當在線程間傳遞數據庫連接時會出現嚴重的問題。
    也許在Linux下有辦法解決fcntl()鎖的問題，但那十分複雜並且對於正確性的測試將是極度困難的。因此，SQLite目前不允許在線程間共享句柄。
    在UNIX下，你不能通過一個 fork() 系統調用把一個打開的 SQLite 數據庫放入子過程中，否則會出錯。
(9) 如何列出一個 SQLite 數據庫中的所有的表/索引？
    在sqlite3 命令行程序中你可以用命令 ".tables" 來顯示所有的表或者用 ".schema"來顯示所有的表結構和索引。但命令後不要跟 LIKE 語句，否則會限製表的顯示。
    在 C/C++ 程序中 (或使用 Tcl/Ruby/Perl/Python綁定的腳本中)你可以通過訪問名為"SQLITE_MASTER的表來實現。每個 SQLite 數據庫有一個 SQLITE_MASTER 表，表內有數據庫的結構。SQLITE_MASTER表是這樣的：
        CREATE TABLE sqlite_master (
          type TEXT,
          name TEXT,
          tbl_name TEXT,
          rootpage INTEGER,
          sql TEXT
        );
    對於表來說，type字段的值為'table'，name 字段是表的名稱。使用以下語句可以等到所有表的列表：
        SELECT name FROM sqlite_master
        WHERE type='table'
        ORDER BY name;
    對於索引來說， type = 'index', name 是索引的名稱， tbl_name 是索引所屬的表的名稱。對於表和索引，sql 字段是創建表或索引的原始語句文本。對於自動創建的索引(一般是使用 PRIMARY KEY 或 UNIQUE 創建的)，sql字段為 NULL.
    SQLITE_MASTER表是只讀的。你不能對該表使用 UPDATE, INSERT, 或 DELETE。該表自動由 CREATE TABLE, CREATE INDEX, DROP TABLE 和 DROP INDEX 命令更新。
    臨時表及其索引不在 SQLITE_MASTER 表中而在 SQLITE_TEMP_MASTER 中出現。SQLITE_TEMP_MASTER 與 SQLITE_MASTER 表一樣工作，但只對於創建臨時表的程序可見。要得到所在表包括臨時表可以使用如下命令：
        SELECT name FROM
           (SELECT * FROM sqlite_master UNION ALL
            SELECT * FROM sqlite_temp_master)
        WHERE type='table'
        ORDER BY name
(10) SQLite數據庫是否有已知的大小限制？
    數據庫大小被限制在 2TB(241 bytes). 這是理論限制。事實上，你應該把 SQLite數據庫的大小限制在100GB以下，以免出現運行性能上的問題。如果你需要儲存100GB或更多數據在一個數據庫中， 考慮使用為此而設計的企業版數據庫吧。
    一個數據庫的理論行數限制是 264-1，顯然你會在達到行數限制之前先超過文件大小的限制。目前一行可以存放 230 bytes 數據。而基本的文件格式可以支持行大小到約 262 bytes.
    可能還會有對於表、索引的數目或表和索引中的字段數的限制，但沒人知道是多少。事實上，每當新數據庫打開時，SQLite需要讀取和 分析所有表和索引聲明的初始SQL，所以，為了調用 sqlite3_open() 時獲得最佳性能，最好減少聲明的表的數目。同樣的，即使 對於表中字段數沒有限制，多於100個也顯得太多了。 只有表開頭的31個字段會得到優化。你可以在一個索引中放入任意多的字段但超過30字段的索引將不用於優化查詢。
    表，索引，視圖，觸發器和字段名稱可以任意長，但SQL 函數名 (由 sqlite3_create_function() API創建的)不得超過255個字符。
(11) 在 SQLite 中 VARCHAR 的最大長度是多少？
    SQLite不強制VARCHAR的長度。你可以聲明一個VARCHAR(10)，SQLite一樣可以讓你存放500個字符在裡面。 並且它們會始終完整無缺——決不會被截斷。
(12) SQLite 是否支持 BLOB 類型？
    SQLite 3.0 版支持在任何字段存放 BLOB 數據，不管字段聲明為什麼類型。
(13) 如何從一個已存在的 SQLite 數據表中添加/刪除字段？
    SQLite有有限的ALTER TABLE支持，可以用於添加字段到表的末尾 或更改表名。如果你要對表的結構作更複雜的修改，你需要重新創建表。你可以在一個臨時表中備份數據，撤銷舊表，重建新表後再恢復數據。
    例如，假設你有一個名為 "t1" 的表，有名為 "a", "b", 和 "c" 三個字段，你要刪除字段 "c" 。可按如下步驟操作：
        BEGIN TRANSACTION;
        CREATE TEMPORARY TABLE t1_backup(a,b);
        INSERT INTO t1_backup SELECT a,b FROM t1;
        DROP TABLE t1;
        CREATE TABLE t1(a,b);
        INSERT INTO t1 SELECT a,b FROM t1_backup;
        DROP TABLE t1_backup;
        COMMIT;
(14) 我刪除了很多數據但是數據庫文件並沒有減小，是不是 Bug？
    不是的。當你從 SQLite 刪除數據之後，未使用的磁盤空間被添加到一個內在的「空閒列表」中用於存儲你下次插入的數據。磁盤空間並沒有丟失，但是也不向操作系統返回磁盤空間。
    如果你刪除了大量的數據且想要減小數據庫文件，執行 VACUUM命令。VACUUM 命令會清空「空閒列表」，把數據庫尺寸縮到最小。注意， VACUUM 會耗費一些時間（在 Linux 系統下大約0.5秒/兆）並且要使用兩倍於數據庫文件大小的磁盤空間。
    對於SQLite version 3.1, 替代VACUUM命令的一個方法是auto-vacuum模式，用 auto_vacuum pragma語法開啟該模式。
(15) 是否能將 SQLite 用於商業用途而不用交版權費用？
    可以。SQLite 是公開的。代碼的任何部分都沒有聲明所有權。你可以用它來做你想要的任何事情。
(16) 如何插入有單引號（'）的字符串？
    使用雙單引號即可，例如：
            INSERT INTO xyz valueS('5 O''clock');
    插入數據庫的是：5 0'clock。
(17) SQLITE_SCHEMA 錯誤代表什麼？
    在 SQLite 版本3中，當一個預處理 SQL 語句不合法不能執行時就會返回一個 SQLITE_SCHEMA 錯誤。當這個錯誤發生時，該語句應當用 sqlite3_prepare() API函數重新編譯。在 SQLite 版本3中，只有使用 sqlite3_prepare()/sqlite3_step()/sqlite3_finalize() API函數執行 SQL 才會發生這個錯誤，而使用 sqlite3_exec(). 則不會。這與版本2不同。
    大部分發生這個錯誤的原因是當 SQL 預處理完時數據庫已經改變了（可能是被另一個進程改變的）。還可能有如下原因：
對一個數據庫進行DETACH操作
對一個數據庫進行VACUUM操作
一個用戶函數定義被刪除或改變了
一個排序定義被刪除或改變了
一個授權函數改變了
    解決的辦法是重新編譯並再次嘗試執行。所有涉及 sqlite3_prepare()/sqlite3_step()/sqlite3_finalize() API 函數的都應當重新編譯。參見下例：
            int rc;
            sqlite3_stmt *pStmt;
            char zSql[] = "SELECT .....";
            do {
              /* Compile the statement from SQL. Assume success. */
              sqlite3_prepare(pDb, zSql, -1, &pStmt, 0);
              while( SQLITE_ROW==sqlite3_step(pStmt) ){
                /* Do something with the row of available data */
              }
              /* Finalize the statement. If an SQLITE_SCHEMA error has
              ** occured, then the above call to sqlite3_step() will have
              ** returned SQLITE_ERROR. sqlite3_finalize() will return
              ** SQLITE_SCHEMA. In this case the loop will execute again.
              */
              rc = sqlite3_finalize(pStmt);
            } while( rc==SQLITE_SCHEMA );
(18) 為什麼ROUND(9.95,1) 返回 9.9 而不是 10.0? 難道9.95 不該向上進位麼？
    SQLite 內部使用二進制運算，9.95用 64-bit IEEE 浮點數 ( SQLite 內部使用的) 表示為9.949999999999999289457264239899814128875732421875。所以當你輸入 "9.95"時，SQLite 就理解為上述的數字，進而四捨五入得到9.9。這個問題在處理浮點二進制數總會產生。通常的規則是十進制的有限浮點數通常無法表示為二進制有限浮點數，只能由最接近的二進制數來代替。這個近似數會非常接近原數，但總一些細微的不同，所以可能無法得到你預期的結果。