TDengine 如何进行 SQL 写入？官方最全教程来了

火币交易所 2022年10月21日 11:02 111 Connor

小 T 导读：为应对不同场景下的写入需求，TDengine 共提供了四种写入协议，其中最常用的当属 SQL 写入了，为了让大家在实际写入操作时更加方便，我们整理了一篇详细解读 SQL INSERT 语法规则的文章。建议提前收藏，有需要时可随时参考。

在诸多时序数据库（Time Series Database）中，如果能支持 SQL，则可以大大降低用户的学习和接入成本，而 TDengine 正是这样一款产品。

TDengine SQL 是供给用户进行数据写入和查询的主要工具。为了便于用户快速上手，它在一定程度上与标准 SQL 风格和模式类似。由于 TDengine 的早期版本没有提供时序数据的删除功能，因此其 SQL 语法也没有提供数据删除的相关功能。不过从 TDengine 企业版从 2.6 开始提供了 DELETE 语句。

为更好地说明 SQL 语法的规则及其特点，本文假设存在一个数据集。以智能电表（meters）为例，假设每个智能电表采集电流、电压和相位三个量。其建模如下：

taos> DESCRIBE meters;

Field | Type | Length | Note |

ts | TIMESTAMP | 8 | |

current | FLOAT | 4 | |

voltage | INT | 4 | |

phase | FLOAT | 4 | |

location | BINARY | 64 | TAG |

groupid | INT | 4 | TAG |

创建超级表和字表的语句可参考 TDengine 官网的官方文档（）。

以上数据集包含 4 个智能电表的数据，按照 TDengine 的建模规则，对应 4 个子表，其名称分别是 d1001、d1002、d1003 以及 d1004。下面我们将以此模型为例，详解 TDengine 中的 SQL INSERT 语法规则。写入语法如下：

INSERT INTO

tb_name

[USING stb_name [(tag1_name, ...)] TAGS (tag1_value, ...)]

[(field1_name, ...)]

VALUES (field1_value, ...) [(field1_value2, ...) ...] | FILE csv_file_path

[tb2_name

[USING stb_name [(tag1_name, ...)] TAGS (tag1_value, ...)]

[(field1_name, ...)]

VALUES (field1_value, ...) [(field1_value2, ...) ...] | FILE csv_file_path

插入一条或多条记录

首先我们需要先指定一个已经创建好的数据子表的表名，并通过 VALUES 关键字提供一行或多行数据，即可向数据库写入这些数据。例如，执行如下语句可以写入一行记录：

INSERT INTO d1001 VALUES (NOW, 10.2, 219, 0.32);

或者，可以通过如下语句写入两行记录：

INSERT INTO d1001 VALUES ('2021-07-13 14:06:32.272', 10.2, 219, 0.32) (1626164208000, 10.15, 217, 0.33);

在此过程中，需要注意以下三点：

插入记录，数据对应到指定的列

当我们向数据子表中插入记录时，无论插入一行还是多行，都可以让数据对应到指定的列。对于 SQL 语句中没有出现的列，数据库将自动填充为 NULL。但要注意主键（时间戳）不能为 NULL。例如：

INSERT INTO d1001 (ts, current, phase) VALUES ('2021-07-13 14:06:33.196', 10.27, 0.31);

如果不指定列，即使用全列模式，那么在 VALUES 部分提供的数据，必须为数据表的每个列都显式地提供数据。全列模式写入速度会远快于指定列，因此建议尽可能采用全列写入方式，此时空列可以填入 NULL。

向多个表插入记录

可以在一条语句中，分别向多个表插入一条或多条记录，并且也可以在插入过程中指定列。例如：

INSERT INTO d1001 VALUES ('2021-07-13 14:06:34.630', 10.2, 219, 0.32) ('2021-07-13 14:06:35.779', 10.15, 217, 0.33)

d1002 (ts, current, phase) VALUES ('2021-07-13 14:06:34.255', 10.27, 0.31）;

插入记录时自动建表

如果你在写数据时并不确定某个表是否存在，此时可以在写入数据时使用自动建表语法来创建不存在的表，若该表已存在则不会建立新表。在自动建表时，我们要求必须以超级表为模板，并写明数据表的 TAGS 取值。例如：

INSERT INTO d21001 USING meters TAGS ('California.SanFrancisco', 2) VALUES ('2021-07-13 14:06:32.272', 10.2, 219, 0.32);

也可以在自动建表时，只是指定部分 TAGS 列的取值，未被指定的 TAGS 列将置为 NULL。例如：

展开全文

INSERT INTO d21001 USING meters (groupId) TAGS (2) VALUES ('2021-07-13 14:06:33.196', 10.15, 217, 0.33);

自动建表语法也支持在一条语句中向多个表插入记录。例如：

INSERT INTO d21001 USING meters TAGS ('California.SanFrancisco', 2) VALUES ('2021-07-13 14:06:34.630', 10.2, 219, 0.32) ('2021-07-13 14:06:35.779', 10.15, 217, 0.33)

d21002 USING meters (groupId) TAGS (2) VALUES ('2021-07-13 14:06:34.255', 10.15, 217, 0.33)

d21003 USING meters (groupId) TAGS (2) (ts, current, phase) VALUES ('2021-07-13 14:06:34.255', 10.27, 0.31);

如果应用的是 2.0.20.5 之前的版本，那在使用自动建表语法并指定列时，子表的列名必须紧跟在子表名称后面，而不能如例子里那样放在 TAGS 和 VALUES 之间。从 2.0.20.5 版本开始，这两种写法都可以使用了，但也不能在一条 SQL 语句中混用，否则会报语法错误。

插入来自文件的数据记录

在进行文件的数据记录插入时，除了使用 VALUES 关键字插入一行或多行数据外，也可以把要写入的数据放在 CSV 文件中（英文逗号分隔、英文单引号括住每个值）供 SQL 指令读取，其中 CSV 文件无需表头。例如，如果 /tmp/csvfile.csv 文件的内容为：

'2021-07-13 14:07:34.630', '10.2', '219', '0.32'

'2021-07-13 14:07:35.779', '10.15', '217', '0.33'

那么通过如下指令可以把这个文件中的数据写入子表中：

INSERT INTO d1001 FILE '/tmp/csvfile.csv';

如果想要执行自动建表，从 2.1.5.0 版本开始，就已经支持在插入来自 CSV 文件的数据时，以超级表为模板来自动创建不存在的数据表。例如：

INSERT INTO d21001 USING meters TAGS ('California.SanFrancisco', 2) FILE '/tmp/csvfile.csv';

历史记录写入

在进行历史数据写入时，我们可使用 IMPORT 或者 INSERT 命令。由于 IMPORT 的语法、功能与 INSERT 完全一样，下面我们就以 INSERT 来说明。

针对 INSERT 类型的 SQL 语句，我们采用的是流式解析策略，在发现后面的错误之前，前面正确的部分 SQL 仍会执行。下面的 SQL 中，INSERT 语句是无效的，但是 d1001 仍会被创建。

taos> CREATE TABLE meters(ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS(location BINARY(30), groupId INT);

Query OK, 0 row(s) affected (0.008245s)

taos> SHOW STABLES;

meters | 2020-08-06 17:50:27.831 | 4 | 2 | 0 |

Query OK, 1 row(s) in set (0.001029s)

taos> SHOW TABLES;

Query OK, 0 row(s) in set (0.000946s)

taos> INSERT INTO d1001 USING meters TAGS('California.SanFrancisco', 2) VALUES('a');

DB error: invalid SQL: 'a' (invalid timestamp) (0.039494s)

taos> SHOW TABLES;

table_name | created_time | columns | stable_name |

d1001 | 2020-08-06 17:52:02.097 | 4 | meters |

Query OK, 1 row(s) in set (0.001091s)

写在最后

众所周知，传统 database 是把整个 SQL 语句发送到服务端，由服务端完成所有的处理逻辑。和传统数据库不同的是，TDengine 会在客户端完成大部分处理逻辑。在语法解析阶段，由于状态机解析 SQL 效率不高，因此针对插入，TDengine 特地设计了流式解析；此外，对于包含表 schema 信息和表所在 dnode 的 table meta，会在客户端进行缓存，之后只需要从本地 cache 中获取即可，为了能在服务端实现高效处理，还会在客户端对每个 table 的数据进行排序和去重……

这些在设计上的诸多细节，也让 TDengine 实现了高效的数据写入。值得一提的是，为了满足部分客户对性能的极端要求，TDengine 还开发了 stmt 接口，进一步减轻语法解析带来的性能问题，目前经过多个用户的线上实践佐证，性能至少提高了 4～5 倍，欢迎大家试用。

如果你还有关于 SQL 写入的更多问题，可以移步至 TDengine 官网上的技术文档（/）中查看更多代码细节，也欢迎进入官方社群，寻求社区技术人员的帮助。