小 T 導讀：為(wei)應對不同場景下的寫入需求，TDengine 共提(ti)(ti)供了(le)四種寫入協(xie)議，其中最(zui)常用的當屬 SQL 寫入了(le)，為(wei)了(le)讓大家在(zai)實際寫入操作時(shi)更加方便，我們(men)整理(li)了(le)一篇詳細解讀 SQL INSERT 語法規則的文章(zhang)。建議提(ti)(ti)前收藏(zang)，有需要時(shi)可隨(sui)時(shi)參考。
在諸多時序數據庫（Time Series Database）中，如(ru)果能支持 SQL，則可以(yi)大大降低用戶(hu)的學(xue)習和接入成(cheng)本，而(er) TDengine 正(zheng)是這樣一款產品。
TDengine SQL 是供(gong)(gong)給用戶(hu)進行數據(ju)寫(xie)入和(he)查(cha)詢的(de)(de)主(zhu)要工具。為了便于用戶(hu)快速上手，它在(zai)一定程度上與標準 SQL 風(feng)格(ge)和(he)模式類似。由(you)于 TDengine 的(de)(de)早期版本沒有(you)提(ti)供(gong)(gong)時序數據(ju)的(de)(de)刪(shan)除功能，因此其(qi) SQL 語(yu)法也沒有(you)提(ti)供(gong)(gong)數據(ju)刪(shan)除的(de)(de)相關功能。不過從(cong) TDengine 企業版從(cong) 2.6 開始提(ti)供(gong)(gong)了 DELETE 語(yu)句。
為(wei)更(geng)好地(di)說明(ming) SQL 語法的規則及其(qi)特點(dian)，本文假(jia)設存在一(yi)個(ge)數(shu)據集(ji)。以智(zhi)能電表（meters）為(wei)例，假(jia)設每個(ge)智(zhi)能電表采集(ji)電流(liu)、電壓和相位三(san)個(ge)量。其(qi)建模如下：
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 |創建(jian)超(chao)級表(biao)和(he)字表(biao)的語句可(ke)參考 TDengine 官網(wang)的。
以(yi)上數(shu)(shu)據集包含(han) 4 個(ge)智(zhi)能(neng)電(dian)表的(de)(de)數(shu)(shu)據，按照 TDengine 的(de)(de)建模規則，對應 4 個(ge)子表，其名稱分別是(shi) d1001、d1002、d1003 以(yi)及 d1004。下面(mian)我們將(jiang)以(yi)此模型為例(li)，詳(xiang)解 TDengine 中的(de)(de) SQL INSERT 語法(fa)規則。寫入(ru)語法(fa)如下：
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
...];插入一條或多條記錄
首(shou)先我們需要先指(zhi)定一個已經(jing)創建好的數(shu)(shu)據(ju)子(zi)表的表名(ming)，并通(tong)過(guo) VALUES 關(guan)鍵字提供一行(xing)(xing)或(huo)多行(xing)(xing)數(shu)(shu)據(ju)，即可向數(shu)(shu)據(ju)庫寫入這些數(shu)(shu)據(ju)。例如，執行(xing)(xing)如下(xia)語句可以寫入一行(xing)(xing)記錄：
INSERT INTO d1001 VALUES (NOW, 10.2, 219, 0.32);或者(zhe)，可以通過如下語(yu)句寫入兩(liang)行記(ji)錄：
INSERT INTO d1001 VALUES ('2021-07-13 14:06:32.272', 10.2, 219, 0.32) (1626164208000, 10.15, 217, 0.33);在此(ci)過程中，需要注意以下三點(dian)：
- 在第二個代碼示例中，兩行記錄的首列時間戳使用了不同格式的寫法。其中字符串格式的時間戳寫法不受所在 Database 的時間精度設置影響；而長整形格式的時間戳寫法會受到所在 Database 的時間精度設置影響——例子中的時間戳在毫秒精度下可以寫作 1626164208000，而如果是在微秒精度設置下就需要寫為 1626164208000000，納秒精度設置下需要寫為 1626164208000000000。
- 在使用“插入多條記錄”方式寫入數據時，不能把第一列的時間戳取值都設為 NOW，否則會導致語句中的多條記錄使用相同的時間戳，就可能出現相互覆蓋以致這些數據行無法全部被正確保存的風險。其原因在于，NOW 函數在執行中會被解析為所在 SQL 語句的實際執行時間，出現在同一語句中的多個 NOW 標記也就會被替換為完全相同的時間戳取值。
- 允許插入的最老記錄的時間戳，認定標準是用當前服務器時間減去配置的 keep 值（數據保留的天數）；允許插入的最新記錄的時間戳，認定標準是用當前服務器時間加上配置的 days 值（數據文件存儲數據的時間跨度，單位為天）。keep 和 days 都是可以在創建數據庫時指定的，缺省值分別是 3650 天和 10 天。
插入記錄，數據對應到指定的列
當我們向(xiang)數(shu)據子表中(zhong)插入(ru)(ru)記錄時，無論(lun)插入(ru)(ru)一行還是多行，都可以讓數(shu)據對應到指定的(de)(de)列。對于 SQL 語(yu)句中(zhong)沒有出現的(de)(de)列，數(shu)據庫將(jiang)自動填充為(wei) NULL。但要注(zhu)意主鍵（時間戳）不能(neng)為(wei) NULL。例(li)如：
INSERT INTO d1001 (ts, current, phase) VALUES ('2021-07-13 14:06:33.196', 10.27, 0.31);如(ru)果不指(zhi)定列(lie)，即(ji)使用(yong)全(quan)列(lie)模式，那么在(zai) VALUES 部分提(ti)供的數據，必須為(wei)數據表的每個(ge)列(lie)都顯式地提(ti)供數據。全(quan)列(lie)模式寫入速度會遠快于指(zhi)定列(lie)，因此建(jian)議盡可(ke)(ke)能采用(yong)全(quan)列(lie)寫入方式，此時空(kong)列(lie)可(ke)(ke)以填入 NULL。
向多個表插入記錄
可(ke)以(yi)在一(yi)條(tiao)語句中(zhong)，分別向多(duo)個表插入(ru)一(yi)條(tiao)或多(duo)條(tiao)記錄(lu)，并且也可(ke)以(yi)在插入(ru)過程中(zhong)指定列。例如：
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）;插入記錄時自動建表
如果你在(zai)寫數據(ju)時(shi)并不確定某個表(biao)是否存(cun)在(zai)，此時(shi)可以(yi)在(zai)寫入數據(ju)時(shi)使用自(zi)動建表(biao)語法來創建不存(cun)在(zai)的表(biao)，若該表(biao)已存(cun)在(zai)則不會建立(li)新(xin)表(biao)。在(zai)自(zi)動建表(biao)時(shi)，我們要求必須以(yi)超級表(biao)為模(mo)板，并寫明(ming)數據(ju)表(biao)的 TAGS 取值。例如：
INSERT INTO d21001 USING meters TAGS ('California.SanFrancisco', 2) VALUES ('2021-07-13 14:06:32.272', 10.2, 219, 0.32);也(ye)可以在自動建(jian)表時，只是(shi)指定(ding)部分 TAGS 列的取值，未被(bei)指定(ding)的 TAGS 列將置為 NULL。例如(ru)：
INSERT INTO d21001 USING meters (groupId) TAGS (2) VALUES ('2021-07-13 14:06:33.196', 10.15, 217, 0.33);自(zi)動(dong)建表(biao)語(yu)法(fa)也支持在一條語(yu)句中向多個(ge)表(biao)插入記錄。例(li)如(ru)：
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);如果(guo)應用的是(shi) 2.0.20.5 之(zhi)前的版(ban)本，那(nei)在使用自動(dong)建(jian)表(biao)(biao)語法(fa)并(bing)指定(ding)列時，子(zi)(zi)表(biao)(biao)的列名必(bi)須緊跟在子(zi)(zi)表(biao)(biao)名稱后面(mian)，而不(bu)(bu)能如例子(zi)(zi)里(li)那(nei)樣(yang)放在 TAGS 和(he) VALUES 之(zhi)間。從 2.0.20.5 版(ban)本開始，這兩種寫法(fa)都可(ke)以使用了，但也不(bu)(bu)能在一(yi)條 SQL 語句中混用，否則會報語法(fa)錯(cuo)誤。
插入來自文件的數據記錄
在(zai)進行(xing)文(wen)(wen)件的(de)數據記錄(lu)插(cha)入(ru)時，除了使用(yong) VALUES 關鍵字(zi)插(cha)入(ru)一行(xing)或多行(xing)數據外，也(ye)可以把要寫入(ru)的(de)數據放在(zai) CSV 文(wen)(wen)件中(zhong)（英文(wen)(wen)逗(dou)號分隔、英文(wen)(wen)單引號括住(zhu)每(mei)個值(zhi)）供 SQL 指(zhi)令讀(du)取，其中(zhong) CSV 文(wen)(wen)件無需表頭。例如，如果 /tmp/csvfile.csv 文(wen)(wen)件的(de)內容為：
'2021-07-13 14:07:34.630', '10.2', '219', '0.32'
'2021-07-13 14:07:35.779', '10.15', '217', '0.33'那么(me)通過如下指令可以把這個文件中的(de)數據寫入子(zi)表中：
INSERT INTO d1001 FILE '/tmp/csvfile.csv';如(ru)果(guo)想要執(zhi)行自動建表，從 2.1.5.0 版本開始(shi)，就已經(jing)支持在(zai)(zai)插入來(lai)自 CSV 文件的數據時(shi)，以超級(ji)表為模板來(lai)自動創建不存在(zai)(zai)的數據表。例如(ru)：
INSERT INTO d21001 USING meters TAGS ('California.SanFrancisco', 2) FILE '/tmp/csvfile.csv';歷史記錄寫入
在進行歷史數(shu)據寫入時(shi)，我們可使(shi)用 IMPORT 或者 INSERT 命令。由于 IMPORT 的語法、功(gong)能(neng)與(yu) INSERT 完(wan)全(quan)一(yi)樣，下面我們就(jiu)以 INSERT 來說明。
針對(dui) INSERT 類型(xing)的 SQL 語(yu)句，我們采用(yong)的是流式解析策略，在發現(xian)后面(mian)(mian)的錯誤之前(qian)，前(qian)面(mian)(mian)正確的部分 SQL 仍(reng)會執行。下面(mian)(mian)的 SQL 中，INSERT 語(yu)句是無效(xiao)的，但是 d1001 仍(reng)會被創建。
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;
name | created_time | columns | tags | tables |
============================================================================================
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)寫在最后
眾所周(zhou)知，傳(chuan)統 database 是把整個 SQL 語句發送到服務(wu)端(duan)，由服務(wu)端(duan)完(wan)成(cheng)所有的處理邏(luo)輯。和(he)傳(chuan)統數據(ju)庫(ku)不同的是，TDengine 會(hui)在(zai)客戶(hu)端(duan)完(wan)成(cheng)大部(bu)分(fen)處理邏(luo)輯。在(zai)語法解析階段(duan)，由于(yu)狀態機解析 SQL 效率(lv)不高， 因此針(zhen)對插(cha)入(ru)，TDengine 特地設計了(le)流式解析；此外，對于(yu)包含表 schema 信息和(he)表所在(zai) dnode 的 table meta，會(hui)在(zai)客戶(hu)端(duan)進(jin)行緩存，之(zhi)后只需要(yao)從本地 cache 中獲取即(ji)可，為了(le)能在(zai)服務(wu)端(duan)實現(xian)高效處理，還(huan)會(hui)在(zai)客戶(hu)端(duan)對每個 table 的數據(ju)進(jin)行排序和(he)去重(zhong)……
這些(xie)在設計(ji)上的(de)(de)諸多細節(jie)，也讓 TDengine 實現了(le)(le)高(gao)效的(de)(de)數據寫入。值得一提的(de)(de)是，為了(le)(le)滿(man)足(zu)部(bu)分客戶(hu)對性能的(de)(de)極端(duan)要(yao)求，TDengine 還開發了(le)(le) stmt 接口，進一步減輕語法解析(xi)帶來的(de)(de)性能問題，目前經過(guo)多個用戶(hu)的(de)(de)線(xian)上實踐(jian)佐證，性能至少提高(gao)了(le)(le) 4～5 倍(bei)，歡迎大(da)家試用。
如(ru)果你還有關于(yu) SQL 寫入的更多問題，可以(yi)移步至 TDengine 官(guan)網上的中查看更多代碼細節，也歡迎進入官(guan)方社群，尋求(qiu)社區技(ji)術(shu)人員的幫(bang)助。
