1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371

SELECT
    min_value::HUGEINT as min_val,
    max_value::HUGEINT as max_val,
    bytes
FROM (
    VALUES
        ('TINYINT', -128::HUGEINT, 127::HUGEINT, 1),
        ('SMALLINT', -32768::HUGEINT, 32767::HUGEINT, 2),
        ('INTEGER', -2147483648::HUGEINT, 2147483647::HUGEINT, 4),
        ('BIGINT', -9223372036854775808::HUGEINT, 9223372036854775807::HUGEINT, 8),
        ('HUGEINT', -170141183460469231731687303715884105728::HUGEINT,
                   170141183460469231731687303715884105727::HUGEINT, 16)
) AS t(name, min_value, max_value, bytes);
```sql

**选择建议：**
- 一般场景用INTEGER就够了，范围±21亿，4字节
- 需要更大范围用BIGINT，8字节
- 超大整数才考虑HUGEINT，16字节，运算开销明显增大

还有个BIGNUM类型，支持超过2万位的十进制数字。说实话，用到这个的场景应该不多，存储单个最大值需要8MB。

## DECIMAL：宽度超过18，性能直接跳水

这是上周踩坑的主角。DECIMAL的语法是`DECIMAL(WIDTH, SCALE)`：

- WIDTH：总位数（1-38）
- SCALE：小数位数（0-38，不能超过WIDTH）
- 默认：`DECIMAL(18, 3)`

内部实现会根据WIDTH选择不同的整数类型：

| WIDTH范围 | 内部类型 | 大小 |
|----------|---------|------|
| 1-4 | INT16 | 2字节 |
| 5-9 | INT32 | 4字节 |
| 10-18 | INT64 | 8字节 |
| 19-38 | INT128 | 16字节 |

关键问题来了：**WIDTH超过18后，内部用INT128存储，运算性能显著下降**。

实测对比：

```sql
-- 创建两张表，一张WIDTH=18，一张WIDTH=20
CREATE TABLE dec18 AS SELECT (i % 1000)::DECIMAL(18,2) as val FROM range(1000000) t(i);
CREATE TABLE dec20 AS SELECT (i % 1000)::DECIMAL(20,2) as val FROM range(1000000) t(i);

-- 聚合查询对比
SELECT SUM(val) FROM dec18;  -- 约0.004秒
SELECT SUM(val) FROM dec20;  -- 约0.040秒，慢了10倍
```text

10倍的差距，就因为WIDTH从18变成20。

**建议：** 除非真的需要超过18位的精度，否则DECIMAL的WIDTH不要超过18。对于金额计算，`DECIMAL(18,2)`或`DECIMAL(18,4)`完全够用。

还有个细节：DECIMAL的除法返回的是DOUBLE，不是DECIMAL。因为除法结果通常没有有限的十进制展开。

```sql
SELECT typeof(10.0::DECIMAL(10,2) / 3.0::DECIMAL(10,2));
-- 结果: DOUBLE
```sql

## VARCHAR：指定长度没用，得用CHECK约束

DuckDB的VARCHAR类型有几个特点：

1. **指定长度不影响性能或存储**：`VARCHAR(10)`和`VARCHAR`没区别
2. **内部UTF-8编码**：完整支持Unicode
3. **长度限制要用CHECK约束**

```sql
-- 这样写没用，长度限制不生效
CREATE TABLE t1 (val VARCHAR(10));
INSERT INTO t1 VALUES ('this is a very long string');  -- 成功插入！

-- 正确做法
CREATE TABLE t2 (
    val VARCHAR CHECK (length(val) <= 10)
);
INSERT INTO t2 VALUES ('this is a very long string');  -- 报错：CHECK constraint failed
```text

这个设计和PostgreSQL不一样。PostgreSQL的`VARCHAR(n)`会真正截断或报错，DuckDB为了兼容性接受这个语法但不执行限制。

如果需要压缩，可以这样：

```sql
CREATE TABLE tbl (s VARCHAR USING COMPRESSION zstd);
```sql

## 日期时间：时区处理是个坑

DuckDB的日期时间类型家族：

| 类型 | 精度 | 时区支持 |
|-----|------|---------|
| DATE | 天 | 无 |
| TIME | 微秒 | 无 |
| TIMESTAMP_S | 秒 | 无 |
| TIMESTAMP_MS | 毫秒 | 无 |
| TIMESTAMP | 微秒 | 无 |
| TIMESTAMP_NS | 纳秒 | 无 |
| TIMESTAMPTZ | 微秒 | 有 |

DATE类型有几个特殊值挺有意思：

```sql
SELECT
    '-infinity'::DATE as negative,
    'epoch'::DATE as epoch,
    'infinity'::DATE as positive;
-- 结果: -infinity | 1970-01-01 | infinity
```sql

**时区处理的坑：**

TIMESTAMPTZ并不存储时区信息，它只存储自Unix纪元以来的微秒数（INT64）。时区只在显示和计算时使用。

还有个限制：**没有TIMESTAMP_NS WITH TIME ZONE**。读取Parquet文件中纳秒精度+时区信息的时间戳时，会被转换成TIMESTAMPTZ，丢失纳秒精度。

字符串转时间戳的规则也比较复杂：

- 无UTC偏移的字符串 → WITHOUT TIME ZONE：直接转换
- 有UTC偏移的字符串 → WITH TIME ZONE：需要ICU扩展
- 无UTC偏移的字符串 → WITH TIME ZONE：使用配置的时区解释
- 有UTC偏移的字符串 → WITHOUT TIME ZONE：忽略偏移量

## 复杂类型：ARRAY/LIST/MAP/STRUCT

处理嵌套数据时，复杂类型就派上用场了。

### ARRAY vs LIST

```sql
-- ARRAY：固定长度
CREATE TABLE t1 (vals INTEGER[3]);
INSERT INTO t1 VALUES ([1, 2, 3]);
-- INSERT INTO t1 VALUES ([1, 2]);  -- 报错！长度必须是3

-- LIST：可变长度
CREATE TABLE t2 (vals INTEGER[]);
INSERT INTO t2 VALUES ([1, 2, 3]);
INSERT INTO t2 VALUES ([1, 2]);  -- 成功
```sql

### MAP

```sql
SELECT map(['a', 'b', 'c'], [1, 2, 3]) as test_map;
-- 结果: {'a': 1, 'b': 2, 'c': 3}
```text

注意：MAP的键是**区分大小写**的。

### STRUCT

```sql
SELECT {'name': 'Alice', 'age': 30, 'city': 'Beijing'} as person;
-- 结果: {'name': 'Alice', 'age': 30, 'city': 'Beijing'}
```sql

STRUCT的键是**不区分大小写**的，这点和MAP不同。

### 嵌套组合

复杂类型可以自由嵌套：

```sql
-- STRUCT包含LIST
SELECT {
    'name': 'Bob',
    'hobbies': ['reading', 'swimming', 'coding']
} as person;

-- LIST包含STRUCT
SELECT [
    {'product': 'Laptop', 'price': 999},
    {'product': 'Mouse', 'price': 29},
    {'product': 'Keyboard', 'price': 79}
] as shopping_cart;
```sql

## 类型转换：DuckDB不支持隐式转换

这点和PostgreSQL不一样。DuckDB要求显式CAST：

```sql
-- 报错！
SELECT 1 + '2';

-- 正确
SELECT 1 + CAST('2' AS INTEGER);
```sql

常用的转换：

```sql
SELECT
    CAST('123' AS INTEGER) as str_to_int,        -- 123
    CAST(123.45 AS VARCHAR) as float_to_str,     -- '123.45'
    CAST('2024-01-15' AS DATE) as str_to_date,   -- 2024-01-15
    CAST(true AS INTEGER) as bool_to_int;        -- 1
```sql

类型推断规则：

| 字面量 | 推断类型 |
|-------|---------|
| 42 | INTEGER |
| 3.14 | DECIMAL(3,2) |
| 'hello' | VARCHAR |
| true | BOOLEAN |
| NULL | "NULL" |
| [1, 2, 3] | INTEGER[] |

处理可能失败的转换时，用TRY_CAST：

```sql
SELECT
    TRY_CAST('123' AS INTEGER) as ok,      -- 123
    TRY_CAST('abc' AS INTEGER) as fail;    -- NULL（不报错）
```sql

## NULL处理

DuckDB的NULL处理比较标准：

```sql
-- 统计NULL值
SELECT
    COUNT(*) as total,
    COUNT(int_val) as non_null,
    COUNT(*) - COUNT(int_val) as null_count
FROM table_name;

-- 提供默认值
SELECT
    COALESCE(int_val, 0) as int_default,
    COALESCE(str_val, 'default') as str_default
FROM table_name;
```sql

## 性能实测

测试环境：macOS 13.7.6，DuckDB 1.5.5，Python 3.12.13

### 插入性能

| 数据类型 | 行数 | 插入时间 |
|---------|------|---------|
| INTEGER | 100万 | 0.029秒 |
| DOUBLE | 100万 | 0.077秒 |
| VARCHAR | 10万 | 0.010秒 |
| DATE | 10万 | 0.007秒 |

INTEGER的插入速度最快，DOUBLE因为浮点运算稍慢。

### 聚合查询性能

| 数据类型 | 行数 | 查询时间 | 操作 |
|---------|------|---------|------|
| INTEGER | 100万 | 0.005秒 | SUM/AVG/MIN/MAX |
| DOUBLE | 100万 | 0.004秒 | SUM/AVG/MIN/MAX |
| VARCHAR | 10万 | 0.003秒 | COUNT/MIN/MAX |
| DATE | 10万 | 0.001秒 | COUNT/MIN/MAX |

### 类型转换性能

| 转换 | 行数 | 时间 |
|-----|------|------|
| VARCHAR → INTEGER | 10万 | 0.010秒 |

**性能结论：**
1. INTEGER是性能最优选择
2. DECIMAL宽度超过18性能显著下降
3. WHERE条件中频繁类型转换会影响性能

## 6个常见陷阱

### 陷阱1：INT8不是8位整数

INT8是64位整数（8字节），INT1才是8位整数（1字节）。命名规则不统一，INT系列按字节，UINT系列按位数。

### 陷阱2：不支持隐式类型转换

`1 + '2'`会报错，必须`1 + CAST('2' AS INTEGER)`。

### 陷阱3：VARCHAR(n)的长度限制无效

要限制长度，用CHECK约束：`VARCHAR CHECK (length(val) <= 10)`。

### 陷阱4：BLOB用octet_length()，不是length()

```sql
-- 报错
SELECT length(blob_column) FROM table;

-- 正确
SELECT octet_length(blob_column) FROM table;
```text

### 陷阱5：DECIMAL除法返回DOUBLE

`DECIMAL / DECIMAL`的结果是DOUBLE，不是DECIMAL。

### 陷阱6：没有TIMESTAMP_NS WITH TIME ZONE

纳秒精度和时区支持不可兼得。

## 类型选择速查表

| 场景 | 推荐类型 | 原因 |
|-----|---------|------|
| 一般整数 | INTEGER | 性能最佳，范围足够 |
| 大整数 | BIGINT | 需要更大范围 |
| 精确小数 | DECIMAL(18,2) | 宽度≤18性能最佳 |
| 科学计算 | DOUBLE | 近似计算 |
| 节省空间 | SMALLINT/TINYINT | 磁盘紧张时 |
| 字符串 | VARCHAR | 无需指定长度 |
| 日期 | DATE | 仅日期 |
| 日期时间 | TIMESTAMP | 无时区需求 |
| 带时区时间 | TIMESTAMPTZ | 跨时区场景 |
| 固定长度数组 | ARRAY | 所有行元素数相同 |
| 可变长度列表 | LIST | 行之间元素数不同 |
| 键值对 | MAP | 字典结构 |
| 结构化数据 | STRUCT | 多字段组合 |

## 参考资源

- [DuckDB Data Types Overview](https://duckdb.org/docs/current/sql/data_types/overview.html)
- [DuckDB Numeric Types](https://duckdb.org/docs/current/sql/data_types/numeric.html)
- [DuckDB Text Types](https://duckdb.org/docs/current/sql/data_types/text.html)
- [DuckDB Timestamp Types](https://duckdb.org/docs/current/sql/data_types/timestamp.html)
- [DuckDB Date Types](https://duckdb.org/docs/current/sql/data_types/date.html)


### 陷阱5：DECIMAL除法返回DOUBLE

`DECIMAL / DECIMAL`的结果是DOUBLE，不是DECIMAL。

### 陷阱6：没有TIMESTAMP_NS WITH TIME ZONE

纳秒精度和时区支持不可兼得。

## 类型选择速查表

| 场景 | 推荐类型 | 原因 |
|-----|---------|------|
| 一般整数 | INTEGER | 性能最佳，范围足够 |
| 大整数 | BIGINT | 需要更大范围 |
| 精确小数 | DECIMAL(18,2) | 宽度≤18性能最佳 |
| 科学计算 | DOUBLE | 近似计算 |
| 节省空间 | SMALLINT/TINYINT | 磁盘紧张时 |
| 字符串 | VARCHAR | 无需指定长度 |
| 日期 | DATE | 仅日期 |
| 日期时间 | TIMESTAMP | 无时区需求 |
| 带时区时间 | TIMESTAMPTZ | 跨时区场景 |
| 固定长度数组 | ARRAY | 所有行元素数相同 |
| 可变长度列表 | LIST | 行之间元素数不同 |
| 键值对 | MAP | 字典结构 |
| 结构化数据 | STRUCT | 多字段组合 |

## 参考资源

- [DuckDB Data Types Overview](https://duckdb.org/docs/current/sql/data_types/overview.html)
- [DuckDB Numeric Types](https://duckdb.org/docs/current/sql/data_types/numeric.html)
- [DuckDB Text Types](https://duckdb.org/docs/current/sql/data_types/text.html)
- [DuckDB Timestamp Types](https://duckdb.org/docs/current/sql/data_types/timestamp.html)
- [DuckDB Date Types](https://duckdb.org/docs/current/sql/data_types/date.html)