SQLite数据类型详解

发布时间: 2025-04-02 22:46

↑

# SQLite数据类型详解

本文将详细介绍SQLite支持的数据类型，帮助您理解每种数据类型的特点和适用场景，从而在设计数据库时做出正确的选择。

## 数据类型概述

### SQLite的类型系统

1. **动态类型系统**
   - 类型亲和性（Type Affinity）
   - 类型灵活性
   - 自动类型转换

2. **存储类**
   - NULL
   - INTEGER
   - REAL
   - TEXT
   - BLOB

## 基本数据类型

### NULL类型

1. **特点**
   - 表示空值
   - 区分大小写
   - 不同于空字符串

2. **使用场景**
   ```sql
   CREATE TABLE example (
       id INTEGER PRIMARY KEY,
       optional_field INTEGER NULL
   );
   ```

### INTEGER类型

1. **特点**
   - 整数值
   - 1, 2, 3, 4, 6, 8字节存储
   - 范围：-9223372036854775808 到 9223372036854775807

2. **使用示例**
   ```sql
   CREATE TABLE products (
       id INTEGER PRIMARY KEY AUTOINCREMENT,
       quantity INTEGER NOT NULL,
       price INTEGER  -- 存储价格（单位：分）
   );
   ```

### REAL类型

1. **特点**
   - 浮点数值
   - 8字节存储
   - IEEE浮点数标准

2. **使用示例**
   ```sql
   CREATE TABLE measurements (
       id INTEGER PRIMARY KEY,
       temperature REAL,
       weight REAL
   );
   ```

### TEXT类型

1. **特点**
   - 文本字符串
   - UTF-8编码
   - 动态长度

2. **使用示例**
   ```sql
   CREATE TABLE users (
       id INTEGER PRIMARY KEY,
       name TEXT NOT NULL,
       email TEXT UNIQUE
   );
   ```

### BLOB类型

1. **特点**
   - 二进制数据
   - 原样存储
   - 无类型转换

2. **使用示例**
   ```sql
   CREATE TABLE files (
       id INTEGER PRIMARY KEY,
       name TEXT,
       data BLOB
   );
   ```

## 类型亲和性

### TEXT亲和性

1. **匹配规则**
   - 包含"TEXT"或"CLOB"
   - 包含"CHAR"、"STRING"或"VARCHAR"

2. **转换行为**
   ```sql
   CREATE TABLE example (
       id INTEGER PRIMARY KEY,
       name VARCHAR(100)  -- 实际是TEXT亲和性
   );
   ```

### NUMERIC亲和性

1. **匹配规则**
   - 包含"INT"或"REAL"
   - 包含"FLOA"或"DOUB"
   - 包含"DEC"或"NUM"

2. **转换行为**
   ```sql
   CREATE TABLE example (
       id INTEGER PRIMARY KEY,
       price DECIMAL(10,2)  -- 实际是NUMERIC亲和性
   );
   ```

### INTEGER亲和性

1. **匹配规则**
   - 包含"INT"
   - 不包含小数点或指数

2. **转换行为**
   ```sql
   CREATE TABLE example (
       id INTEGER PRIMARY KEY,
       count BIGINT  -- 实际是INTEGER亲和性
   );
   ```

## 类型转换

### 自动转换

1. **文本转数字**
   ```sql
   INSERT INTO numbers (value) VALUES ('123');
   -- '123'会自动转换为整数123
   ```

2. **数字转文本**
   ```sql
   INSERT INTO texts (content) VALUES (456);
   -- 456会自动转换为文本'456'
   ```

### 显式转换

1. **CAST函数**
   ```sql
   SELECT CAST(price AS INTEGER) FROM products;
   SELECT CAST(number AS TEXT) FROM data;
   ```

2. **类型函数**
   ```sql
   SELECT typeof(value) FROM data;
   ```

## 最佳实践

### 类型选择

1. **整数类型**
   - 使用INTEGER存储ID
   - 使用INTEGER存储布尔值（0/1）
   - 使用INTEGER存储时间戳

2. **文本类型**
   - 使用TEXT存储可变长度字符串
   - 使用TEXT存储JSON数据
   - 使用TEXT存储UUID

### 性能考虑

1. **存储优化**
   - 选择合适的数据类型
   - 避免过度使用BLOB
   - 合理使用NULL

2. **查询优化**
   - 避免不必要的类型转换
   - 使用适当的索引
   - 注意排序性能

## 常见问题

### 类型不匹配

1. **问题表现**
   - 数据截断
   - 精度丢失
   - 转换错误

2. **解决方案**
   - 使用正确的数据类型
   - 验证输入数据
   - 处理异常情况

### 存储限制

1. **TEXT和BLOB**
   - 最大长度：2^31-1 字节
   - 注意内存使用
   - 考虑分块存储

2. **INTEGER和REAL**
   - INTEGER：8字节限制
   - REAL：双精度浮点数
   - 注意数值范围

## 总结

SQLite的数据类型系统具有以下特点：

- 动态类型系统
- 五种存储类
- 类型亲和性
- 自动类型转换

建议：

- 了解类型特点
- 选择合适类型
- 注意类型转换
- 考虑性能影响

元素码农