SQLite 3資料庫管理及使用

SQLite 3 是一個輕量級的嵌入式關係型資料庫管理系統，廣泛應用於移動應用、嵌入式系統和小型桌面應用中。SQLite 3 無需獨立的伺服器進程，資料存儲在單個檔案中，易於管理和使用。本文將詳細介紹如何在 ServBay 中管理和使用 SQLite 3 資料庫，包括安裝、配置、備份、恢復和性能優化等方面。

安裝和配置 SQLite 3

ServBay 自帶 SQLite 3，可以直接在命令行、PHP、Node.js 等環境中直接使用。

驗證 SQLite 3 安裝

您可以通過以下命令驗證 SQLite 3 是否已安裝：

sqlite3 --version

如果顯示版本資訊，則表示 SQLite 3 已安裝。

建立和連接 SQLite 3 資料庫

SQLite 3 資料庫存儲在單個檔案中，預設路徑為 /Applications/ServBay/db/sqlite。您可以使用命令行工具 sqlite3 或程式語言的 SQLite 庫來創建和連接資料庫。

使用命令行工具連接

1. 創建或連接資料庫：

   sqlite3 /Applications/ServBay/db/sqlite/your_database.db

2. 退出 SQLite 3：

   .exit

使用程式語言連接

PHP 範例

使用 SQLite3 類連接 SQLite 3：

<?php
// Connect to SQLite 3 database
$db = new SQLite3('/Applications/ServBay/db/sqlite/your_database.db');

// Create table
$db->exec("CREATE TABLE IF NOT EXISTS mytable (id INTEGER PRIMARY KEY, name TEXT)");

// Insert data
$db->exec("INSERT INTO mytable (name) VALUES ('Alice')");

// Query data
$result = $db->query("SELECT * FROM mytable");
while ($row = $result->fetchArray()) {
    echo "ID: " . $row['id'] . " Name: " . $row['name'] . "\n";
}

// Close database connection
$db->close();
?>

Node.js 範例

使用 sqlite3 庫連接 SQLite 3：

const sqlite3 = require('sqlite3').verbose();

// Connect to SQLite 3 database
let db = new sqlite3.Database('/Applications/ServBay/db/sqlite/your_database.db', (err) => {
  if (err) {
    console.error(err.message);
  }
  console.log('Connected to the SQLite database.');
});

// Create table
db.run(`CREATE TABLE IF NOT EXISTS mytable (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT
)`);

// Insert data
db.run(`INSERT INTO mytable (name) VALUES (?)`, ['Alice'], function(err) {
  if (err) {
    return console.error(err.message);
  }
  console.log(`A row has been inserted with rowid ${this.lastID}`);
});

// Query data
db.all(`SELECT * FROM mytable`, [], (err, rows) => {
  if (err) {
    throw err;
  }
  rows.forEach((row) => {
    console.log(row.id, row.name);
  });
});

// Close database connection
db.close((err) => {
  if (err) {
    console.error(err.message);
  }
  console.log('Close the database connection.');
});

資料庫管理

基本操作

1. 創建表：

   CREATE TABLE mytable (id INTEGER PRIMARY KEY, name TEXT);

2. 插入數據：

   INSERT INTO mytable (name) VALUES ('Alice');

3. 查詢數據：

   SELECT * FROM mytable;

4. 更新數據：

   UPDATE mytable SET name = 'Bob' WHERE id = 1;

5. 刪除數據：

   DELETE FROM mytable WHERE id = 1;

備份和恢復

備份資料庫

SQLite 3 資料庫存儲在單個檔案中，備份只需復製該檔案即可。建議將備份檔案存放在以下目錄：

/Applications/ServBay/backup/sqlite

使用命令行工具復製資料庫檔案進行備份：

cp /Applications/ServBay/db/sqlite/your_database.db /Applications/ServBay/backup/sqlite/your_database_backup.db

恢復資料庫

將備份檔案復製回原始位置進行恢復：

cp /Applications/ServBay/backup/sqlite/your_database_backup.db /Applications/ServBay/db/sqlite/your_database.db

性能優化

SQLite 3 提供了多種性能優化選項，以下是一些常見的優化方法。

索引優化

確保對常用查詢的列建立索引。例如：

CREATE INDEX idx_name ON mytable(name);

查詢優化

使用 EXPLAIN 命令分析查詢性能，並進行相應優化。例如：

EXPLAIN QUERY PLAN SELECT * FROM mytable WHERE name = 'Alice';

配置優化

調整 SQLite 3 的 PRAGMA 參數以提升性能。例如，啟用同步模式以提高寫入性能：

PRAGMA synchronous = OFF;

安全管理

確保資料庫的安全性是非常重要的，以下是一些安全管理的建議。

檔案權限

確保 SQLite 3 資料庫檔案的權限設置正確，防止未經授權的訪問。例如：

chmod 600 /Applications/ServBay/db/sqlite/your_database.db

數據加密

SQLite 3 支持資料庫加密，您可以使用 SQLite 加密擴展（如 SQLCipher）對資料庫進行加密。

常見問題及解決方案

無法連接到 SQLite 3 資料庫

1. 檢查資料庫檔案路徑： 確保資料庫檔案路徑正確且檔案存在。

2. 檢查檔案權限： 確保資料庫檔案的權限允許讀寫操作。

資料庫鎖定

1. 檢查並發訪問： 確保沒有其他進程正在訪問資料庫，SQLite 3 在寫操作時會鎖定資料庫。

2. 使用 WAL 模式： 啟用 WAL（Write-Ahead Logging）模式以提高並發性能：

   PRAGMA journal_mode = WAL;

總結

SQLite 3 是一個強大且靈活的嵌入式資料庫管理系統，ServBay 自帶 SQLite 3，使得資料庫管理和使用變得更加方便。通過本文的介紹，您可以輕鬆地進行 SQLite 3 的安裝、配置、連接、管理、備份、恢復和性能優化操作，確保資料庫的高效運行和安全性。