SQLite是一个轻量级的嵌入式关系型数据库引擎，由D. Richard Hipp于2000年创建。SQLite将整个数据库存储在单个文件中，不需要独立的服务器进程，被广泛应用于移动应用、桌面软件和小型Web应用中。
主要特点：
无服务器架构：直接嵌入到应用程序中
零配置：无需安装和配置
单文件数据库：整个数据库存储在一个文件中
轻量级：库文件大小仅几百KB
自包含：无外部依赖
二、架构差异深度分析
（一）系统架构对比
1. MySQL架构
graph TB
A[客户端应用] --> B[MySQL连接器]
B --> C[查询缓存]
B --> D[解析器]
D --> E[优化器]
E --> F[执行器]
F --> G[存储引擎层]
G --> H[InnoDB引擎]
G --> I[MyISAM引擎]
G --> J[Memory引擎]
H --> K[数据文件]
H --> L[日志文件]
I --> M[表文件]
J --> N[内存存储]
style A fill:#e1f5fe
style G fill:#f3e5f5
style K fill:#e8f5e8
MySQL采用分层架构：
连接层：处理客户端连接和认证
服务层：SQL解析、优化、缓存
引擎层：数据存储和检索
存储层：物理文件系统
2. SQLite架构
graph TB
A[应用程序] --> B[SQLite API]
B --> C[SQL编译器]
C --> D[虚拟机]
D --> E[B-Tree模块]
E --> F[页缓存]
F --> G[操作系统接口]
G --> H[数据库文件]
style A fill:#e1f5fe
style B fill:#f3e5f5
style H fill:#e8f5e8
# 4. 创建数据库和用户 mysql -u root -p CREATE DATABASE myapp; CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON myapp.* TO 'appuser'@'localhost';
-- MySQL复杂查询示例 -- 多表关联查询 SELECT u.name, u.email, COUNT(o.id) as order_count, SUM(o.amount) as total_amount, AVG(o.amount) as avg_amount FROM users u LEFTJOIN orders o ON u.id = o.user_id WHERE u.created_at >='2024-01-01' GROUPBY u.id, u.name, u.email HAVINGCOUNT(o.id) >5 ORDERBY total_amount DESC LIMIT 100;
-- 子查询优化 SELECT*FROM users WHERE id IN ( SELECT user_id FROM orders WHERE amount > ( SELECTAVG(amount) FROM orders ) );
-- 窗口函数（MySQL 8.0+） SELECT name, amount, ROW_NUMBER() OVER (ORDERBY amount DESC) as rank, SUM(amount) OVER (ORDERBY amount DESC) as running_total FROM orders;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
-- SQLite复杂查询示例 -- 公用表表达式（CTE） WITHRECURSIVE fibonacci(n, fib_n, next_fib_n) AS ( SELECT1, 0, 1 UNIONALL SELECT n+1, next_fib_n, fib_n + next_fib_n FROM fibonacci WHERE n <10 ) SELECT n, fib_n FROM fibonacci;
-- JSON查询（SQLite 3.38+） SELECT json_extract(data, '$.name') as name, json_extract(data, '$.age') as age FROM json_table WHERE json_extract(data, '$.active') =1;
-- 触发器 CREATETRIGGER update_user_stats AFTER INSERTON orders FOREACHROW BEGIN UPDATE users SET order_count = order_count +1, total_spent = total_spent + NEW.amount WHERE id = NEW.user_id; END;
-- 视图 CREATEVIEW user_order_summary AS SELECT u.id, u.name, u.email, COUNT(o.id) as order_count, COALESCE(SUM(o.amount), 0) as total_spent FROM users u LEFTJOIN orders o ON u.id = o.user_id GROUPBY u.id, u.name, u.email;
-- 分区表 CREATE TABLE orders_partitioned ( id INT AUTO_INCREMENT, user_id INT, amount DECIMAL(10,2), created_at DATE, PRIMARY KEY (id, created_at) ) PARTITIONBYRANGE (YEAR(created_at)) ( PARTITION p2022 VALUES LESS THAN (2023), PARTITION p2023 VALUES LESS THAN (2024), PARTITION p2024 VALUES LESS THAN (2025), PARTITION p_future VALUES LESS THAN MAXVALUE );
-- 1. 虚拟表 CREATE VIRTUAL TABLE fts_search USING fts5(title, content); INSERT INTO fts_search VALUES ('标题1', '这是内容1'); SELECT*FROM fts_search WHERE fts_search MATCH'内容';
-- 2. 公用表表达式（CTE） WITHRECURSIVE category_tree(id, name, parent_id, level) AS ( -- 根节点 SELECT id, name, parent_id, 0as level FROM categories WHERE parent_id ISNULL
UNIONALL
-- 递归部分 SELECT c.id, c.name, c.parent_id, ct.level +1 FROM categories c JOIN category_tree ct ON c.parent_id = ct.id ) SELECT*FROM category_tree ORDERBY level, name;
-- 3. 窗口函数 SELECT name, salary, department, AVG(salary) OVER (PARTITIONBY department) as dept_avg, RANK() OVER (PARTITIONBY department ORDERBY salary DESC) as dept_rank FROM employees;
-- 4. JSON支持 CREATE TABLE products ( id INTEGERPRIMARY KEY, name TEXT, attributes JSON );
-- JSON查询 SELECT name, json_extract(attributes, '$.brand') as brand, json_extract(attributes, '$.cpu') as cpu FROM products WHERE json_extract(attributes, '$.brand') ='Dell';
overridefunonCreate(db: SQLiteDatabase) { // 创建用户表 val createUsersTable = """ CREATE TABLE users ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, email TEXT UNIQUE NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ) """.trimIndent()
db.execSQL(createUsersTable) }
// 用户操作 funinsertUser(name: String, email: String): Long { val db = writableDatabase val values = ContentValues().apply { put("name", name) put("email", email) } return db.insert("users", null, values) } }
definit_database(self): """初始化数据库""" with sqlite3.connect(self.db_path) as conn: conn.execute(""" CREATE TABLE IF NOT EXISTS configs ( key TEXT PRIMARY KEY, value TEXT NOT NULL, type TEXT NOT NULL DEFAULT 'string', updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ) """)
defset_config(self, key: str, value: any): """设置配置项""" value_type = type(value).__name__ value_str = json.dumps(value) if value_type in ['dict', 'list'] elsestr(value)
with sqlite3.connect(self.db_path) as conn: conn.execute(""" INSERT OR REPLACE INTO configs (key, value, type, updated_at) VALUES (?, ?, ?, CURRENT_TIMESTAMP) """, (key, value_str, value_type))
defget_config(self, key: str, default=None): """获取配置项""" with sqlite3.connect(self.db_path) as conn: cursor = conn.execute( "SELECT value, type FROM configs WHERE key = ?", (key,) ) row = cursor.fetchone()
flowchart TD
A[开始选择数据库] --> B{应用类型?}
B -->|Web应用| C{并发用户数?}
B -->|移动应用| D[选择SQLite]
B -->|桌面应用| E{数据量大小?}
B -->|嵌入式系统| D
C -->|>1000| F[选择MySQL]
C -->|<1000| g{数据量?} g -->|>10GB| F
G -->|<10GB| h{团队技术水平?} h -->|有DBA| F
H -->|无DBA| I[选择SQLite]
E -->|>1GB| J{需要网络访问?}
E -->|<1GB| d j -->|是| F
J -->|否| D
style D fill:#c8e6c9
style F fill:#fff3e0
style I fill:#c8e6c9
微信
