SQL 写入

SQL 写入简介

应用通过连接器执行 INSERT 语句来插入数据，用户还可以通过 TDengine CLI，手动输入 INSERT 语句插入数据。

一次写入一条

下面这条 INSERT 就将一条记录写入到表 d1001 中：

  1. INSERT INTO d1001 VALUES (ts1, 10.3, 219, 0.31);

这里的ts1为Unix时间戳(Unix timestamp)，允许插入的最老记录的时间戳，是相对于当前服务器时间，减去配置的 KEEP 值。时间戳详情规则参考 TDengine SQL数据写入 关于时间戳一节

一次写入多条

TDengine 支持一次写入多条记录，比如下面这条命令就将两条记录写入到表 d1001 中：

  1. INSERT INTO d1001 VALUES (ts1, 10.2, 220, 0.23) (ts2, 10.3, 218, 0.25);

这里的ts1和ts2为Unix时间戳(Unix timestamp)，允许插入的最老记录的时间戳，是相对于当前服务器时间，减去配置的 KEEP 值。时间戳详情规则参考 TDengine SQL数据写入 关于时间戳一节

一次写入多表

TDengine 也支持一次向多个表写入数据，比如下面这条命令就向 d1001 写入两条记录，向 d1002 写入一条记录：

  1. INSERT INTO d1001 VALUES (ts1, 10.3, 219, 0.31) (ts2, 12.6, 218, 0.33) d1002 VALUES (ts3, 12.3, 221, 0.31);

这里的ts1、ts2和ts3为Unix时间戳(Unix timestamp)，允许插入的最老记录的时间戳，是相对于当前服务器时间，减去配置的 KEEP 值。时间戳详情规则参考 TDengine SQL数据写入 关于时间戳一节

详细的 SQL INSERT 语法规则参考 TDengine SQL 的数据写入。

SQL 写入 - 图1info
  • 要提高写入效率，需要批量写入。一般来说一批写入的记录条数越多，插入效率就越高。但一条记录不能超过 48KB，一条 SQL 语句总长度不能超过 1MB。
  • TDengine 支持多线程同时写入，要进一步提高写入速度，一个客户端需要打开多个同时写。但线程数达到一定数量后，无法再提高，甚至还会下降，因为线程频繁切换，会带来额外开销，合适的线程数量与服务端的处理能力，服务端的具体配置，数据库的参数，数据定义的 Schema，写入数据的 Batch Size 等很多因素相关。一般来说，服务端和客户端处理能力越强，所能支持的并发写入的线程可以越多；数据库配置时的 vgroups 参数值越多（但仍然要在服务端的处理能力以内）则所能支持的并发写入越多；数据定义的 Schema 越简单，所能支持的并发写入越多。
SQL 写入 - 图2warning
  • 对同一张表，如果新插入记录的时间戳已经存在，则指定了新值的列会用新值覆盖旧值，而没有指定新值的列则不受影响。
  • 写入的数据的时间戳必须大于当前时间减去数据库配置参数 KEEP 的时间。如果 KEEP 配置为 3650 天，那么无法写入比 3650 天还早的数据。写入数据的时间戳也不能大于当前时间加配置参数 DURATION。如果 DURATION 为 2，那么无法写入比当前时间还晚 2 天的数据。

示例程序

普通 SQL 写入

  • Java
  • Python
  • Go
  • Rust
  • Node.js
  • C#
  • C
  • PHP
  1. package com.taos.example;
  3. import java.sql.Connection;
  4. import java.sql.DriverManager;
  5. import java.sql.SQLException;
  6. import java.sql.Statement;
  7. import java.util.Arrays;
  8. import java.util.List;
  11. public class RestInsertExample {
  12.     private static Connection getConnection() throws SQLException {
  13.         String jdbcUrl = "jdbc:TAOS-RS://localhost:6041?user=root&password=taosdata";
  14.         return DriverManager.getConnection(jdbcUrl);
  15.     }
  17.     private static List<String> getRawData() {
  18.         return Arrays.asList(
  19.                 "d1001,2018-10-03 14:38:05.000,10.30000,219,0.31000,'California.SanFrancisco',2",
  20.                 "d1001,2018-10-03 14:38:15.000,12.60000,218,0.33000,'California.SanFrancisco',2",
  21.                 "d1001,2018-10-03 14:38:16.800,12.30000,221,0.31000,'California.SanFrancisco',2",
  22.                 "d1002,2018-10-03 14:38:16.650,10.30000,218,0.25000,'California.SanFrancisco',3",
  23.                 "d1003,2018-10-03 14:38:05.500,11.80000,221,0.28000,'California.LosAngeles',2",
  24.                 "d1003,2018-10-03 14:38:16.600,13.40000,223,0.29000,'California.LosAngeles',2",
  25.                 "d1004,2018-10-03 14:38:05.000,10.80000,223,0.29000,'California.LosAngeles',3",
  26.                 "d1004,2018-10-03 14:38:06.500,11.50000,221,0.35000,'California.LosAngeles',3"
  27.         );
  28.     }
  31.     /**
  32.      * The generated SQL is:
  33.      * INSERT INTO power.d1001 USING power.meters TAGS(California.SanFrancisco, 2) VALUES('2018-10-03 14:38:05.000',10.30000,219,0.31000)
  34.      * power.d1001 USING power.meters TAGS(California.SanFrancisco, 2) VALUES('2018-10-03 14:38:15.000',12.60000,218,0.33000)
  35.      * power.d1001 USING power.meters TAGS(California.SanFrancisco, 2) VALUES('2018-10-03 14:38:16.800',12.30000,221,0.31000)
  36.      * power.d1002 USING power.meters TAGS(California.SanFrancisco, 3) VALUES('2018-10-03 14:38:16.650',10.30000,218,0.25000)
  37.      * power.d1003 USING power.meters TAGS(California.LosAngeles, 2) VALUES('2018-10-03 14:38:05.500',11.80000,221,0.28000)
  38.      * power.d1003 USING power.meters TAGS(California.LosAngeles, 2) VALUES('2018-10-03 14:38:16.600',13.40000,223,0.29000)
  39.      * power.d1004 USING power.meters TAGS(California.LosAngeles, 3) VALUES('2018-10-03 14:38:05.000',10.80000,223,0.29000)
  40.      * power.d1004 USING power.meters TAGS(California.LosAngeles, 3) VALUES('2018-10-03 14:38:06.500',11.50000,221,0.35000)
  41.      */
  42.     private static String getSQL() {
  43.         StringBuilder sb = new StringBuilder("INSERT INTO ");
  44.         for (String line : getRawData()) {
  45.             String[] ps = line.split(",");
  46.             sb.append("power." + ps[0]).append(" USING power.meters TAGS(")
  47.                     .append(ps[5]).append(", ") // tag: location
  48.                     .append(ps[6]) // tag: groupId
  49.                     .append(") VALUES(")
  50.                     .append('\'').append(ps[1]).append('\'').append(",") // ts
  51.                     .append(ps[2]).append(",") // current
  52.                     .append(ps[3]).append(",") // voltage
  53.                     .append(ps[4]).append(") "); // phase
  54.         }
  55.         return sb.toString();
  56.     }
  58.     public static void insertData() throws SQLException {
  59.         try (Connection conn = getConnection()) {
  60.             try (Statement stmt = conn.createStatement()) {
  61.                 stmt.execute("CREATE DATABASE power KEEP 3650");
  62.                 stmt.execute("CREATE STABLE power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) " +
  63.                         "TAGS (location BINARY(64), groupId INT)");
  64.                 String sql = getSQL();
  65.                 int rowCount = stmt.executeUpdate(sql);
  66.                 System.out.println("rowCount=" + rowCount); // rowCount=8
  67.             }
  68.         }
  69.     }
  71.     public static void main(String[] args) throws SQLException {
  72.         insertData();
  73.     }
  74. }

查看源码

  1. import taos
  3. lines = ["d1001,2018-10-03 14:38:05.000,10.30000,219,0.31000,'California.SanFrancisco',2",
  4.          "d1004,2018-10-03 14:38:05.000,10.80000,223,0.29000,'California.LosAngeles',3",
  5.          "d1003,2018-10-03 14:38:05.500,11.80000,221,0.28000,'California.LosAngeles',2",
  6.          "d1004,2018-10-03 14:38:06.500,11.50000,221,0.35000,'California.LosAngeles',3",
  7.          "d1002,2018-10-03 14:38:16.650,10.30000,218,0.25000,'California.SanFrancisco',3",
  8.          "d1001,2018-10-03 14:38:15.000,12.60000,218,0.33000,'California.SanFrancisco',2",
  9.          "d1001,2018-10-03 14:38:16.800,12.30000,221,0.31000,'California.SanFrancisco',2",
  10.          "d1003,2018-10-03 14:38:16.600,13.40000,223,0.29000,'California.LosAngeles',2"]
  13. def get_connection() -> taos.TaosConnection:
  14.     """
  15.     create connection use firstEp in taos.cfg and use default user and password.
  16.     """
  17.     return taos.connect()
  20. def create_stable(conn: taos.TaosConnection):
  21.     conn.execute("CREATE DATABASE power")
  22.     conn.execute("USE power")
  23.     conn.execute("CREATE STABLE meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) "
  24.                  "TAGS (location BINARY(64), groupId INT)")
  27. # The generated SQL is:
  28. # INSERT INTO d1001 USING meters TAGS('California.SanFrancisco', 2) VALUES ('2018-10-03 14:38:05.000', 10.30000, 219, 0.31000) ('2018-10-03 14:38:15.000', 12.60000, 218, 0.33000) ('2018-10-03 14:38:16.800', 12.30000, 221, 0.31000)
  29. #             d1002 USING meters TAGS('California.SanFrancisco', 3) VALUES ('2018-10-03 14:38:16.650', 10.30000, 218, 0.25000)
  30. #             d1003 USING meters TAGS('California.LosAngeles', 2) VALUES ('2018-10-03 14:38:05.500', 11.80000, 221, 0.28000) ('2018-10-03 14:38:16.600', 13.40000, 223, 0.29000)
  31. #             d1004 USING meters TAGS('California.LosAngeles', 3) VALUES ('2018-10-03 14:38:05.000', 10.80000, 223, 0.29000) ('2018-10-03 14:38:06.500', 11.50000, 221, 0.35000)
  33. def get_sql():
  34.     global lines
  35.     lines = map(lambda line: line.split(','), lines)  # [['d1001', ...]...]
  36.     lines = sorted(lines, key=lambda ls: ls[0])  # sort by table name
  37.     sql = "INSERT INTO "
  38.     tb_name = None
  39.     for ps in lines:
  40.         tmp_tb_name = ps[0]
  41.         if tb_name != tmp_tb_name:
  42.             tb_name = tmp_tb_name
  43.             sql += f"{tb_name} USING meters TAGS({ps[5]}, {ps[6]}) VALUES "
  44.         sql += f"('{ps[1]}', {ps[2]}, {ps[3]}, {ps[4]}) "
  45.     return sql
  48. def insert_data(conn: taos.TaosConnection):
  49.     sql = get_sql()
  50.     affected_rows = conn.execute(sql)
  51.     print("affected_rows", affected_rows)  # 8
  54. if __name__ == '__main__':
  55.     connection = get_connection()
  56.     try:
  57.         create_stable(connection)
  58.         insert_data(connection)
  59.     finally:
  60.         connection.close()

查看源码

  1. package main
  3. import (
  4.     "database/sql"
  5.     "fmt"
  6.     "log"
  8.     _ "github.com/taosdata/driver-go/v3/taosRestful"
  9. )
  11. func createStable(taos *sql.DB) {
  12.     _, err := taos.Exec("CREATE DATABASE power")
  13.     if err != nil {
  14.         log.Fatalln("failed to create database, err:", err)
  15.     }
  16.     _, err = taos.Exec("CREATE STABLE power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)")
  17.     if err != nil {
  18.         log.Fatalln("failed to create stable, err:", err)
  19.     }
  20. }
  22. func insertData(taos *sql.DB) {
  23.     sql := `INSERT INTO power.d1001 USING power.meters TAGS('California.SanFrancisco', 2) VALUES ('2018-10-03 14:38:05.000', 10.30000, 219, 0.31000) ('2018-10-03 14:38:15.000', 12.60000, 218, 0.33000) ('2018-10-03 14:38:16.800', 12.30000, 221, 0.31000)
  24.     power.d1002 USING power.meters TAGS('California.SanFrancisco', 3) VALUES ('2018-10-03 14:38:16.650', 10.30000, 218, 0.25000)
  25.     power.d1003 USING power.meters TAGS('California.LosAngeles', 2) VALUES ('2018-10-03 14:38:05.500', 11.80000, 221, 0.28000) ('2018-10-03 14:38:16.600', 13.40000, 223, 0.29000)
  26.     power.d1004 USING power.meters TAGS('California.LosAngeles', 3) VALUES ('2018-10-03 14:38:05.000', 10.80000, 223, 0.29000) ('2018-10-03 14:38:06.500', 11.50000, 221, 0.35000)`
  27.     result, err := taos.Exec(sql)
  28.     if err != nil {
  29.         log.Fatalln("failed to insert, err:", err)
  30.     }
  31.     rowsAffected, err := result.RowsAffected()
  32.     if err != nil {
  33.         log.Fatalln("failed to get affected rows, err:", err)
  34.     }
  35.     fmt.Println("RowsAffected", rowsAffected)
  36. }
  38. func main() {
  39.     var taosDSN = "root:taosdata@http(localhost:6041)/"
  40.     taos, err := sql.Open("taosRestful", taosDSN)
  41.     if err != nil {
  42.         log.Fatalln("failed to connect TDengine, err:", err)
  43.     }
  44.     defer taos.Close()
  45.     createStable(taos)
  46.     insertData(taos)
  47. }

查看源码

  1. use taos::*;
  3. #[tokio::main]
  4. async fn main() -> anyhow::Result<()> {
  5.     let dsn = "ws://";
  6.     let taos = TaosBuilder::from_dsn(dsn)?.build()?;
  8.     taos.exec_many([
  9.         "DROP DATABASE IF EXISTS power",
  10.         "CREATE DATABASE power",
  11.         "USE power",
  12.         "CREATE STABLE power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)"
  13.     ]).await?;
  15.     let inserted = taos.exec("INSERT INTO 
  16.     power.d1001 USING power.meters TAGS('California.SanFrancisco', 2)
  17.      VALUES ('2018-10-03 14:38:05.000', 10.30000, 219, 0.31000) 
  18.      ('2018-10-03 14:38:15.000', 12.60000, 218, 0.33000) ('2018-10-03 14:38:16.800', 12.30000, 221, 0.31000)
  19.     power.d1002 USING power.meters TAGS('California.SanFrancisco', 3)
  20.      VALUES ('2018-10-03 14:38:16.650', 10.30000, 218, 0.25000)
  21.     power.d1003 USING power.meters TAGS('California.LosAngeles', 2) 
  22.      VALUES ('2018-10-03 14:38:05.500', 11.80000, 221, 0.28000) ('2018-10-03 14:38:16.600', 13.40000, 223, 0.29000)
  23.     power.d1004 USING power.meters TAGS('California.LosAngeles', 3) 
  24.      VALUES ('2018-10-03 14:38:05.000', 10.80000, 223, 0.29000) ('2018-10-03 14:38:06.500', 11.50000, 221, 0.35000)").await?;
  26.     assert_eq!(inserted, 8);
  27.     Ok(())
  28. }

查看源码

  1. const taos = require("@tdengine/client");
  3. const conn = taos.connect({
  4.   host: "localhost",
  5. });
  7. const cursor = conn.cursor();
  8. try {
  9.   cursor.execute("CREATE DATABASE power");
  10.   cursor.execute("USE power");
  11.   cursor.execute(
  12.     "CREATE STABLE meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)"
  13.   );
  14.   var sql = `INSERT INTO power.d1001 USING power.meters TAGS('California.SanFrancisco', 2) VALUES ('2018-10-03 14:38:05.000', 10.30000, 219, 0.31000) ('2018-10-03 14:38:15.000', 12.60000, 218, 0.33000) ('2018-10-03 14:38:16.800', 12.30000, 221, 0.31000)
  15. power.d1002 USING power.meters TAGS('California.SanFrancisco', 3) VALUES ('2018-10-03 14:38:16.650', 10.30000, 218, 0.25000)
  16. power.d1003 USING power.meters TAGS('California.LosAngeles', 2) VALUES ('2018-10-03 14:38:05.500', 11.80000, 221, 0.28000) ('2018-10-03 14:38:16.600', 13.40000, 223, 0.29000)
  17. power.d1004 USING power.meters TAGS('California.LosAngeles', 3) VALUES ('2018-10-03 14:38:05.000', 10.80000, 223, 0.29000) ('2018-10-03 14:38:06.500', 11.50000, 221, 0.35000)`;
  18.   cursor.execute(sql,{'quiet':false});
  19. } finally {
  20.   cursor.close();
  21.   conn.close();
  22. }
  24. // run with: node insert_example.js
  25. // output:
  26. // Successfully connected to TDengine
  27. // Query OK, 0 row(s) affected (0.00509570s)
  28. // Query OK, 0 row(s) affected (0.00130880s)
  29. // Query OK, 0 row(s) affected (0.00467900s)
  30. // Query OK, 8 row(s) affected (0.04043550s)
  31. // Connection is closed

查看源码

  1. using TDengineDriver;
  4. namespace TDengineExample
  5. {
  6.     internal class SQLInsertExample
  7.     {
  9.         static void Main()
  10.         {
  11.             IntPtr conn = GetConnection();
  12.             try
  13.             {
  14.                 IntPtr res = TDengine.Query(conn, "CREATE DATABASE power");
  15.                 CheckRes(conn, res, "failed to create database");
  16.                 res = TDengine.Query(conn, "USE power");
  17.                 CheckRes(conn, res, "failed to change database");
  18.                 res = TDengine.Query(conn, "CREATE STABLE power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)");
  19.                 CheckRes(conn, res, "failed to create stable");
  20.                 var sql = "INSERT INTO d1001 USING meters TAGS('California.SanFrancisco', 2) VALUES ('2018-10-03 14:38:05.000', 10.30000, 219, 0.31000) ('2018-10-03 14:38:15.000', 12.60000, 218, 0.33000) ('2018-10-03 14:38:16.800', 12.30000, 221, 0.31000) " +
  21.                             "d1002 USING power.meters TAGS('California.SanFrancisco', 3) VALUES('2018-10-03 14:38:16.650', 10.30000, 218, 0.25000) " +
  22.                             "d1003 USING power.meters TAGS('California.LosAngeles', 2) VALUES('2018-10-03 14:38:05.500', 11.80000, 221, 0.28000)('2018-10-03 14:38:16.600', 13.40000, 223, 0.29000) " +
  23.                             "d1004 USING power.meters TAGS('California.LosAngeles', 3) VALUES('2018-10-03 14:38:05.000', 10.80000, 223, 0.29000)('2018-10-03 14:38:06.500', 11.50000, 221, 0.35000)";
  24.                 res = TDengine.Query(conn, sql);
  25.                 CheckRes(conn, res, "failed to insert data");
  26.                 int affectedRows = TDengine.AffectRows(res);
  27.                 Console.WriteLine("affectedRows " + affectedRows);
  28.                 TDengine.FreeResult(res);
  29.             }
  30.             finally
  31.             {
  32.                 TDengine.Close(conn);
  33.             }
  35.         }
  37.         static IntPtr GetConnection()
  38.         {
  39.             string host = "localhost";
  40.             short port = 6030;
  41.             string username = "root";
  42.             string password = "taosdata";
  43.             string dbname = "";
  44.             var conn = TDengine.Connect(host, username, password, dbname, port);
  45.             if (conn == IntPtr.Zero)
  46.             {
  47.                 throw new Exception("Connect to TDengine failed");
  48.             }
  49.             else
  50.             {
  51.                 Console.WriteLine("Connect to TDengine success");
  52.             }
  53.             return conn;
  54.         }
  56.         static void CheckRes(IntPtr conn, IntPtr res, String errorMsg)
  57.         {
  58.             if (TDengine.ErrorNo(res) != 0)
  59.             {
  60.                 throw new Exception($"{errorMsg} since: {TDengine.Error(res)}");
  61.             }
  62.         }
  64.     }
  65. }
  67. // output:
  68. // Connect to TDengine success
  69. // affectedRows 8

查看源码

  1. // compile with
  2. // gcc -o insert_example insert_example.c -ltaos
  3. #include <stdio.h>
  4. #include <stdlib.h>
  5. #include "taos.h"
  8. /**
  9.  * @brief execute sql and print affected rows.
  10.  * 
  11.  * @param taos 
  12.  * @param sql 
  13.  */
  14. void executeSQL(TAOS *taos, const char *sql) {
  15.   TAOS_RES *res = taos_query(taos, sql);
  16.   int       code = taos_errno(res);
  17.   if (code != 0) {
  18.     printf("Error code: %d; Message: %s\n", code, taos_errstr(res));
  19.     taos_free_result(res);
  20.     taos_close(taos);
  21.     exit(EXIT_FAILURE);
  22.   }
  23.   int affectedRows = taos_affected_rows(res);
  24.   printf("affected rows %d\n", affectedRows);
  25.   taos_free_result(res);
  26. }
  30. int main() {
  31.    TAOS *taos = taos_connect("localhost", "root", "taosdata", NULL, 6030);
  32.   if (taos == NULL) {
  33.     printf("failed to connect to server\n");
  34.     exit(EXIT_FAILURE);
  35.   }
  36.   executeSQL(taos, "CREATE DATABASE power");
  37.   executeSQL(taos, "USE power");
  38.   executeSQL(taos, "CREATE STABLE meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)");
  39.   executeSQL(taos, "INSERT INTO d1001 USING meters TAGS('California.SanFrancisco', 2) VALUES ('2018-10-03 14:38:05.000', 10.30000, 219, 0.31000) ('2018-10-03 14:38:15.000', 12.60000, 218, 0.33000) ('2018-10-03 14:38:16.800', 12.30000, 221, 0.31000)"
  40.                 "d1002 USING meters TAGS('California.SanFrancisco', 3) VALUES ('2018-10-03 14:38:16.650', 10.30000, 218, 0.25000)"
  41.                 "d1003 USING meters TAGS('California.LosAngeles', 2) VALUES ('2018-10-03 14:38:05.500', 11.80000, 221, 0.28000) ('2018-10-03 14:38:16.600', 13.40000, 223, 0.29000)"
  42.                 "d1004 USING meters TAGS('California.LosAngeles', 3) VALUES ('2018-10-03 14:38:05.000', 10.80000, 223, 0.29000) ('2018-10-03 14:38:06.500', 11.50000, 221, 0.35000)");
  43.   taos_close(taos);
  44.   taos_cleanup();
  45. }
  47. // output:
  48. // affected rows 0
  49. // affected rows 0
  50. // affected rows 0
  51. // affected rows 8

查看源码

  1. <?php
  3. use TDengine\Connection;
  4. use TDengine\Exception\TDengineException;
  6. try {
  7.     // instantiate
  8.     $host = 'localhost';
  9.     $port = 6030;
  10.     $username = 'root';
  11.     $password = 'taosdata';
  12.     $dbname = 'power';
  13.     $connection = new Connection($host, $port, $username, $password, $dbname);
  15.     // connect
  16.     $connection->connect();
  18.     // insert
  19.     $connection->query('CREATE DATABASE if not exists power');
  20.     $connection->query('CREATE STABLE if not exists meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)');
  21.     $resource = $connection->query(<<<'SQL'
  22.     INSERT INTO power.d1001 USING power.meters TAGS(California.SanFrancisco, 2) VALUES ('2018-10-03 14:38:05.000', 10.30000, 219, 0.31000) ('2018-10-03 14:38:15.000', 12.60000, 218, 0.33000) ('2018-10-03 14:38:16.800', 12.30000, 221, 0.31000)
  23.     power.d1002 USING power.meters TAGS(California.SanFrancisco, 3) VALUES ('2018-10-03 14:38:16.650', 10.30000, 218, 0.25000)
  24.     power.d1003 USING power.meters TAGS(California.LosAngeles, 2) VALUES ('2018-10-03 14:38:05.500', 11.80000, 221, 0.28000) ('2018-10-03 14:38:16.600', 13.40000, 223, 0.29000)
  25.     power.d1004 USING power.meters TAGS(California.LosAngeles, 3) VALUES ('2018-10-03 14:38:05.000', 10.80000, 223, 0.29000) ('2018-10-03 14:38:06.500', 11.50000, 221, 0.35000)
  26.     SQL);
  28.     // get affected rows
  29.     var_dump($resource->affectedRows());
  30. } catch (TDengineException $e) {
  31.     // throw exception
  32.     throw $e;
  33. }

查看源码

SQL 写入 - 图3note
  1. 无论 RESTful 方式建立连接还是本地驱动方式建立连接，以上示例代码都能正常工作。
  2. 唯一需要注意的是：由于 RESTful 接口无状态， 不能使用 USE db; 语句来切换数据库, 所以在上面示例中使用了dbName.tbName指定表名。

参数绑定写入

TDengine 也提供了支持参数绑定的 Prepare API，与 MySQL 类似，这些 API 目前也仅支持用问号 ? 来代表待绑定的参数。在通过参数绑定接口写入数据时，就避免了 SQL 语法解析的资源消耗，从而在绝大多数情况下显著提升写入性能。

需要注意的是，只有使用原生连接的连接器，才能使用参数绑定功能。

  • Java
  • Python
  • Go
  • Rust
  • Node.js
  • C#
  • C
  • PHP
  1. package com.taos.example;
  3. import com.taosdata.jdbc.TSDBPreparedStatement;
  5. import java.sql.Connection;
  6. import java.sql.DriverManager;
  7. import java.sql.SQLException;
  8. import java.sql.Statement;
  9. import java.time.LocalDateTime;
  10. import java.time.ZoneOffset;
  11. import java.time.format.DateTimeFormatter;
  12. import java.util.ArrayList;
  13. import java.util.Arrays;
  14. import java.util.List;
  16. public class StmtInsertExample {
  17.     private static ArrayList<Long> tsToLongArray(String ts) {
  18.         ArrayList<Long> result = new ArrayList<>();
  19.         DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS");
  20.         LocalDateTime localDateTime = LocalDateTime.parse(ts, formatter);
  21.         result.add(localDateTime.toInstant(ZoneOffset.of("+8")).toEpochMilli());
  22.         return result;
  23.     }
  25.     private static <T> ArrayList<T> toArray(T v) {
  26.         ArrayList<T> result = new ArrayList<>();
  27.         result.add(v);
  28.         return result;
  29.     }
  31.     private static List<String> getRawData() {
  32.         return Arrays.asList(
  33.                 "d1001,2018-10-03 14:38:05.000,10.30000,219,0.31000,California.SanFrancisco,2",
  34.                 "d1001,2018-10-03 14:38:15.000,12.60000,218,0.33000,California.SanFrancisco,2",
  35.                 "d1001,2018-10-03 14:38:16.800,12.30000,221,0.31000,California.SanFrancisco,2",
  36.                 "d1002,2018-10-03 14:38:16.650,10.30000,218,0.25000,California.SanFrancisco,3",
  37.                 "d1003,2018-10-03 14:38:05.500,11.80000,221,0.28000,California.LosAngeles,2",
  38.                 "d1003,2018-10-03 14:38:16.600,13.40000,223,0.29000,California.LosAngeles,2",
  39.                 "d1004,2018-10-03 14:38:05.000,10.80000,223,0.29000,California.LosAngeles,3",
  40.                 "d1004,2018-10-03 14:38:06.500,11.50000,221,0.35000,California.LosAngeles,3"
  41.         );
  42.     }
  44.     private static Connection getConnection() throws SQLException {
  45.         String jdbcUrl = "jdbc:TAOS://localhost:6030?user=root&password=taosdata";
  46.         return DriverManager.getConnection(jdbcUrl);
  47.     }
  49.     private static void createTable(Connection conn) throws SQLException {
  50.         try (Statement stmt = conn.createStatement()) {
  51.             stmt.execute("CREATE DATABASE power KEEP 3650");
  52.             stmt.executeUpdate("USE power");
  53.             stmt.execute("CREATE STABLE meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) " +
  54.                     "TAGS (location BINARY(64), groupId INT)");
  55.         }
  56.     }
  58.     private static void insertData() throws SQLException {
  59.         try (Connection conn = getConnection()) {
  60.             createTable(conn);
  61.             String psql = "INSERT INTO ? USING meters TAGS(?, ?) VALUES(?, ?, ?, ?)";
  62.             try (TSDBPreparedStatement pst = (TSDBPreparedStatement) conn.prepareStatement(psql)) {
  63.                 for (String line : getRawData()) {
  64.                     String[] ps = line.split(",");
  65.                     // bind table name and tags
  66.                     pst.setTableName(ps[0]);
  67.                     pst.setTagString(0, ps[5]);
  68.                     pst.setTagInt(1, Integer.valueOf(ps[6]));
  69.                     // bind values
  70.                     pst.setTimestamp(0, tsToLongArray(ps[1])); //ps[1] looks like: 2018-10-03 14:38:05.000
  71.                     pst.setFloat(1, toArray(Float.valueOf(ps[2])));
  72.                     pst.setInt(2, toArray(Integer.valueOf(ps[3])));
  73.                     pst.setFloat(3, toArray(Float.valueOf(ps[4])));
  74.                     pst.columnDataAddBatch();
  75.                 }
  76.                 pst.columnDataExecuteBatch();
  77.             }
  78.         }
  79.     }
  81.     public static void main(String[] args) throws SQLException {
  82.         insertData();
  83.     }
  84. }

查看源码

一次绑定一行

  1. import taos
  2. from datetime import datetime
  4. # note: lines have already been sorted by table name
  5. lines = [('d1001', '2018-10-03 14:38:05.000', 10.30000, 219, 0.31000, 'California.SanFrancisco', 2),
  6.          ('d1001', '2018-10-03 14:38:15.000', 12.60000, 218, 0.33000, 'California.SanFrancisco', 2),
  7.          ('d1001', '2018-10-03 14:38:16.800', 12.30000, 221, 0.31000, 'California.SanFrancisco', 2),
  8.          ('d1002', '2018-10-03 14:38:16.650', 10.30000, 218, 0.25000, 'California.SanFrancisco', 3),
  9.          ('d1003', '2018-10-03 14:38:05.500', 11.80000, 221, 0.28000, 'California.LosAngeles', 2),
  10.          ('d1003', '2018-10-03 14:38:16.600', 13.40000, 223, 0.29000, 'California.LosAngeles', 2),
  11.          ('d1004', '2018-10-03 14:38:05.000', 10.80000, 223, 0.29000, 'California.LosAngeles', 3),
  12.          ('d1004', '2018-10-03 14:38:06.500', 11.50000, 221, 0.35000, 'California.LosAngeles', 3)]
  15. def get_ts(ts: str):
  16.     dt = datetime.strptime(ts, '%Y-%m-%d %H:%M:%S.%f')
  17.     return int(dt.timestamp() * 1000)
  20. def create_stable():
  21.     conn = taos.connect()
  22.     try:
  23.         conn.execute("CREATE DATABASE power")
  24.         conn.execute("CREATE STABLE power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) "
  25.                      "TAGS (location BINARY(64), groupId INT)")
  26.     finally:
  27.         conn.close()
  30. def bind_row_by_row(stmt: taos.TaosStmt):
  31.     tb_name = None
  32.     for row in lines:
  33.         if tb_name != row[0]:
  34.             tb_name = row[0]
  35.             tags: taos.TaosBind = taos.new_bind_params(2)  # 2 is count of tags
  36.             tags[0].binary(row[5])  # location
  37.             tags[1].int(row[6])  # groupId
  38.             stmt.set_tbname_tags(tb_name, tags)
  39.         values: taos.TaosBind = taos.new_bind_params(4)  # 4 is count of columns
  40.         values[0].timestamp(get_ts(row[1]))
  41.         values[1].float(row[2])
  42.         values[2].int(row[3])
  43.         values[3].float(row[4])
  44.         stmt.bind_param(values)
  47. def insert_data():
  48.     conn = taos.connect(database="power")
  49.     try:
  50.         stmt = conn.statement("INSERT INTO ? USING meters TAGS(?, ?) VALUES(?, ?, ?, ?)")
  51.         bind_row_by_row(stmt)
  52.         stmt.execute()
  53.         stmt.close()
  54.     finally:
  55.         conn.close()
  58. if __name__ == '__main__':
  59.     create_stable()
  60.     insert_data()

查看源码

一次绑定多行

  1. table_tags = {
  2.     "d1001": ('California.SanFrancisco', 2),
  3.     "d1002": ('California.SanFrancisco', 3),
  4.     "d1003": ('California.LosAngeles', 2),
  5.     "d1004": ('California.LosAngeles', 3)
  6. }
  8. table_values = {
  9.     "d1001": [
  10.         ['2018-10-03 14:38:05.000', '2018-10-03 14:38:15.000', '2018-10-03 14:38:16.800'],
  11.         [10.3, 12.6, 12.3],
  12.         [219, 218, 221],
  13.         [0.31, 0.33, 0.32]
  14.     ],
  15.     "d1002": [
  16.         ['2018-10-03 14:38:16.650'], [10.3], [218], [0.25]
  17.     ],
  18.     "d1003": [
  19.         ['2018-10-03 14:38:05.500', '2018-10-03 14:38:16.600'],
  20.         [11.8, 13.4],
  21.         [221, 223],
  22.         [0.28, 0.29]
  23.     ],
  24.     "d1004": [
  25.         ['2018-10-03 14:38:05.500', '2018-10-03 14:38:06.500'],
  26.         [10.8, 11.5],
  27.         [223, 221],
  28.         [0.29, 0.35]
  29.     ]
  30. }
  33. def bind_multi_rows(stmt: taos.TaosStmt):
  34.     """
  35.     batch bind example
  36.     """
  37.     for tb_name in table_values.keys():
  38.         tags = table_tags[tb_name]
  39.         tag_params = taos.new_bind_params(2)
  40.         tag_params[0].binary(tags[0])
  41.         tag_params[1].int(tags[1])
  42.         stmt.set_tbname_tags(tb_name, tag_params)
  44.         values = table_values[tb_name]
  45.         value_params = taos.new_multi_binds(4)
  46.         value_params[0].timestamp([get_ts(t) for t in values[0]])
  47.         value_params[1].float(values[1])
  48.         value_params[2].int(values[2])
  49.         value_params[3].float(values[3])
  50.         stmt.bind_param_batch(value_params)
  53. def insert_data():
  54.     conn = taos.connect(database="power")
  55.     try:
  56.         stmt = conn.statement("INSERT INTO ? USING meters TAGS(?, ?) VALUES(?, ?, ?, ?)")
  57.         bind_multi_rows(stmt)
  58.         stmt.execute()
  59.         stmt.close()
  60.     finally:
  61.         conn.close()

查看源码

SQL 写入 - 图4info

一次绑定一行效率不如一次绑定多行，但支持非 INSERT 语句。一次绑定多行效率更高，但仅支持 INSERT 语句。

  1. package main
  3. import (
  4.     "fmt"
  5.     "time"
  7.     "github.com/taosdata/driver-go/v3/af"
  8.     "github.com/taosdata/driver-go/v3/common"
  9.     "github.com/taosdata/driver-go/v3/common/param"
  10. )
  12. func checkErr(err error, prompt string) {
  13.     if err != nil {
  14.         fmt.Printf("%s\n", prompt)
  15.         panic(err)
  16.     }
  17. }
  19. func prepareStable(conn *af.Connector) {
  20.     _, err := conn.Exec("CREATE DATABASE power")
  21.     checkErr(err, "failed to create database")
  22.     _, err = conn.Exec("CREATE STABLE power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)")
  23.     checkErr(err, "failed to create stable")
  24.     _, err = conn.Exec("USE power")
  25.     checkErr(err, "failed to change database")
  26. }
  28. func main() {
  29.     conn, err := af.Open("localhost", "root", "taosdata", "", 6030)
  30.     checkErr(err, "fail to connect")
  31.     defer conn.Close()
  32.     prepareStable(conn)
  33.     // create stmt
  34.     stmt := conn.InsertStmt()
  35.     defer stmt.Close()
  36.     err = stmt.Prepare("INSERT INTO ? USING meters TAGS(?, ?) VALUES(?, ?, ?, ?)")
  37.     checkErr(err, "failed to create prepare statement")
  39.     // bind table name and tags
  40.     tagParams := param.NewParam(2).AddBinary([]byte("California.SanFrancisco")).AddInt(2)
  41.     err = stmt.SetTableNameWithTags("d1001", tagParams)
  42.     checkErr(err, "failed to execute SetTableNameWithTags")
  44.     // specify ColumnType
  45.     var bindType *param.ColumnType = param.NewColumnType(4).AddTimestamp().AddFloat().AddInt().AddFloat()
  47.     // bind values. note: can only bind one row each time.
  48.     valueParams := []*param.Param{
  49.         param.NewParam(1).AddTimestamp(time.Unix(1648432611, 249300000), common.PrecisionMilliSecond),
  50.         param.NewParam(1).AddFloat(10.3),
  51.         param.NewParam(1).AddInt(219),
  52.         param.NewParam(1).AddFloat(0.31),
  53.     }
  54.     err = stmt.BindParam(valueParams, bindType)
  55.     checkErr(err, "BindParam error")
  56.     err = stmt.AddBatch()
  57.     checkErr(err, "AddBatch error")
  59.     // bind one more row
  60.     valueParams = []*param.Param{
  61.         param.NewParam(1).AddTimestamp(time.Unix(1648432611, 749300000), common.PrecisionMilliSecond),
  62.         param.NewParam(1).AddFloat(12.6),
  63.         param.NewParam(1).AddInt(218),
  64.         param.NewParam(1).AddFloat(0.33),
  65.     }
  66.     err = stmt.BindParam(valueParams, bindType)
  67.     checkErr(err, "BindParam error")
  68.     err = stmt.AddBatch()
  69.     checkErr(err, "AddBatch error")
  70.     // execute
  71.     err = stmt.Execute()
  72.     checkErr(err, "Execute batch error")
  73. }

查看源码

SQL 写入 - 图5tip

driver-go 的模块 github.com/taosdata/driver-go/v3/wrapper 是 C 接口的底层封装。使用这个模块也可以实现参数绑定写入。

  1. use taos::*;
  3. #[tokio::main]
  4. async fn main() -> anyhow::Result<()> {
  5.     let taos = TaosBuilder::from_dsn("taos://")?.build()?;
  6.     taos.create_database("power").await?;
  7.     taos.use_database("power").await?;
  8.     taos.exec("CREATE STABLE IF NOT EXISTS meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)").await?;
  10.     let mut stmt = Stmt::init(&taos)?;
  11.     stmt.prepare("INSERT INTO ? USING meters TAGS(?, ?) VALUES(?, ?, ?, ?)")?;
  12.     // bind table name and tags
  13.     stmt.set_tbname_tags(
  14.         "d1001",
  15.         &[
  16.             Value::VarChar("California.SanFransico".into()),
  17.             Value::Int(2),
  18.         ],
  19.     )?;
  20.     // bind values.
  21.     let values = vec![
  22.         ColumnView::from_millis_timestamp(vec![1648432611249]),
  23.         ColumnView::from_floats(vec![10.3]),
  24.         ColumnView::from_ints(vec![219]),
  25.         ColumnView::from_floats(vec![0.31]),
  26.     ];
  27.     stmt.bind(&values)?;
  28.     // bind one more row
  29.     let values2 = vec![
  30.         ColumnView::from_millis_timestamp(vec![1648432611749]),
  31.         ColumnView::from_floats(vec![12.6]),
  32.         ColumnView::from_ints(vec![218]),
  33.         ColumnView::from_floats(vec![0.33]),
  34.     ];
  35.     stmt.bind(&values2)?;
  37.     stmt.add_batch()?;
  39.     // execute.
  40.     let rows = stmt.execute()?;
  41.     assert_eq!(rows, 2);
  42.     Ok(())
  43. }

查看源码

一次绑定一行

  1. const taos = require("@tdengine/client");
  3. const conn = taos.connect({
  4.   host: "localhost",
  5. });
  7. const cursor = conn.cursor();
  9. function prepareSTable() {
  10.   cursor.execute("CREATE DATABASE power");
  11.   cursor.execute("USE power");
  12.   cursor.execute(
  13.     "CREATE STABLE meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)"
  14.   );
  15. }
  17. function insertData() {
  18.   // init
  19.   cursor.stmtInit();
  20.   // prepare
  21.   cursor.stmtPrepare(
  22.     "INSERT INTO ? USING meters TAGS(?, ?) VALUES(?, ?, ?, ?)"
  23.   );
  25.   // bind table name and tags
  26.   let tagBind = new taos.TaosMultiBindArr(2);
  27.   tagBind.multiBindBinary(["California.SanFrancisco"]);
  28.   tagBind.multiBindInt([2]);
  29.   cursor.stmtSetTbnameTags("d1001", tagBind.getMultiBindArr());
  31.   // bind values
  32.   let rows = [[1648432611249, 1648432611749], [10.3, 12.6], [219, 218], [0.31, 0.33]];
  34.   let valueBind = new taos.TaosMultiBindArr(4);
  35.   valueBind.multiBindTimestamp(rows[0]);
  36.   valueBind.multiBindFloat(rows[1]);
  37.   valueBind.multiBindInt(rows[2]);
  38.   valueBind.multiBindFloat(rows[3]);
  39.   cursor.stmtBindParamBatch(valueBind.getMultiBindArr());
  40.   cursor.stmtAddBatch();
  43.   // execute
  44.   cursor.stmtExecute();
  45.   cursor.stmtClose();
  46. }
  48. try {
  49.   prepareSTable();
  50.   insertData();
  51. } finally {
  52.   cursor.close();
  53.   conn.close();
  54. }

查看源码

一次绑定多行

  1. function insertData() {
  2.   // init
  3.   cursor.stmtInit();
  4.   // prepare
  5.   cursor.stmtPrepare(
  6.     "INSERT INTO ? USING meters TAGS(?, ?) VALUES(?, ?, ?, ?)"
  7.   );
  9.   // bind table name and tags
  10.   let tagBind = new taos.TaosMultiBindArr(2);
  11.   tagBind.multiBindBinary(["California.SanFrancisco"]);
  12.   tagBind.multiBindInt([2]);
  13.   cursor.stmtSetTbnameTags("d1001", tagBind.getMultiBindArr());
  15.   // bind values
  16.   let valueBind = new taos.TaosMultiBindArr(4);
  17.   valueBind.multiBindTimestamp([1648432611249, 1648432611749]);
  18.   valueBind.multiBindFloat([10.3, 12.6]);
  19.   valueBind.multiBindInt([219, 218]);
  20.   valueBind.multiBindFloat([0.31, 0.33]);
  21.   cursor.stmtBindParamBatch(valueBind.getMultiBindArr());
  22.   cursor.stmtAddBatch();
  24.   // execute
  25.   cursor.stmtExecute();
  26.   cursor.stmtClose();
  27. }

查看源码

SQL 写入 - 图6info

一次绑定一行效率不如一次绑定多行，但支持非 INSERT 语句。一次绑定多行效率更高，但仅支持 INSERT 语句。

  1. using TDengineDriver;
  3. namespace TDengineExample
  4. {
  5.     internal class StmtInsertExample
  6.     {
  7.         private static IntPtr conn;
  8.         private static IntPtr stmt;
  9.         static void Main()
  10.         {
  11.             conn = GetConnection();
  12.             try
  13.             {
  14.                 PrepareSTable();
  15.                 // 1. init and prepare
  16.                 stmt = TDengine.StmtInit(conn);
  17.                 if (stmt == IntPtr.Zero)
  18.                 {
  19.                     throw new Exception("failed to init stmt.");
  20.                 }
  21.                 int res = TDengine.StmtPrepare(stmt, "INSERT INTO ? USING meters TAGS(?, ?) VALUES(?, ?, ?, ?)");
  22.                 CheckStmtRes(res, "failed to prepare stmt");
  24.                 // 2. bind table name and tags
  25.                 TAOS_MULTI_BIND[] tags = new TAOS_MULTI_BIND[2] { TaosMultiBind.MultiBindBinary(new string[] { "California.SanFrancisco" }), TaosMultiBind.MultiBindInt(new int?[] { 2 }) };
  26.                 res = TDengine.StmtSetTbnameTags(stmt, "d1001", tags);
  27.                 CheckStmtRes(res, "failed to bind table name and tags");
  29.                 // 3. bind values
  30.                 TAOS_MULTI_BIND[] values = new TAOS_MULTI_BIND[4] {
  31.                 TaosMultiBind.MultiBindTimestamp(new long[2] { 1648432611249, 1648432611749}),
  32.                 TaosMultiBind.MultiBindFloat(new float?[2] { 10.3f, 12.6f}),
  33.                 TaosMultiBind.MultiBindInt(new int?[2] { 219, 218}),
  34.                 TaosMultiBind.MultiBindFloat(new float?[2]{ 0.31f, 0.33f})
  35.             };
  36.                 res = TDengine.StmtBindParamBatch(stmt, values);
  37.                 CheckStmtRes(res, "failed to bind params");
  39.                 // 4. add batch
  40.                 res = TDengine.StmtAddBatch(stmt);
  41.                 CheckStmtRes(res, "failed to add batch");
  43.                 // 5. execute
  44.                 res = TDengine.StmtExecute(stmt);
  45.                 CheckStmtRes(res, "failed to execute");
  47.                 // 6. free 
  48.                 TaosMultiBind.FreeTaosBind(tags);
  49.                 TaosMultiBind.FreeTaosBind(values);
  50.             }
  51.             finally
  52.             {
  53.                 TDengine.Close(conn);
  54.             }
  56.         }
  58.         static IntPtr GetConnection()
  59.         {
  60.             string host = "localhost";
  61.             short port = 6030;
  62.             string username = "root";
  63.             string password = "taosdata";
  64.             string dbname = "";
  65.             var conn = TDengine.Connect(host, username, password, dbname, port);
  66.             if (conn == IntPtr.Zero)
  67.             {
  68.                 throw new Exception("Connect to TDengine failed");
  69.             }
  70.             else
  71.             {
  72.                 Console.WriteLine("Connect to TDengine success");
  73.             }
  74.             return conn;
  75.         }
  77.         static void PrepareSTable()
  78.         {
  79.             IntPtr res = TDengine.Query(conn, "CREATE DATABASE power");
  80.             CheckResPtr(res, "failed to create database");
  81.             res = TDengine.Query(conn, "USE power");
  82.             CheckResPtr(res, "failed to change database");
  83.             res = TDengine.Query(conn, "CREATE STABLE power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)");
  84.             CheckResPtr(res, "failed to create stable");
  85.         }
  87.         static void CheckStmtRes(int res, string errorMsg)
  88.         {
  89.             if (res != 0)
  90.             {
  91.                 Console.WriteLine(errorMsg + ", " + TDengine.StmtErrorStr(stmt));
  92.                 int code = TDengine.StmtClose(stmt);
  93.                 if (code != 0)
  94.                 {
  95.                     throw new Exception($"failed to close stmt, {code} reason: {TDengine.StmtErrorStr(stmt)} ");
  96.                 }
  97.             }
  98.         }
  100.         static void CheckResPtr(IntPtr res, string errorMsg)
  101.         {
  102.             if (TDengine.ErrorNo(res) != 0)
  103.             {
  104.                 throw new Exception(errorMsg + " since:" + TDengine.Error(res));
  105.             }
  106.         }
  108.     }
  109. }

查看源码

一次绑定一行

  1. // compile with
  2. // gcc -o stmt_example stmt_example.c -ltaos
  3. #include <stdio.h>
  4. #include <stdlib.h>
  5. #include <string.h>
  6. #include "taos.h"
  8. /**
  9.  * @brief execute sql only.
  10.  * 
  11.  * @param taos 
  12.  * @param sql 
  13.  */
  14. void executeSQL(TAOS *taos, const char *sql) {
  15.   TAOS_RES *res = taos_query(taos, sql);
  16.   int       code = taos_errno(res);
  17.   if (code != 0) {
  18.     printf("%s\n", taos_errstr(res));
  19.     taos_free_result(res);
  20.     taos_close(taos);
  21.     exit(EXIT_FAILURE);
  22.   }
  23.   taos_free_result(res);
  24. }
  26. /**
  27.  * @brief check return status and exit program when error occur.
  28.  * 
  29.  * @param stmt 
  30.  * @param code 
  31.  * @param msg 
  32.  */
  33. void checkErrorCode(TAOS_STMT *stmt, int code, const char* msg) {
  34.   if (code != 0) {
  35.     printf("%s. error: %s\n", msg, taos_stmt_errstr(stmt));
  36.     taos_stmt_close(stmt);
  37.     exit(EXIT_FAILURE);
  38.   }
  39. }
  41. typedef struct {
  42.   int64_t ts;
  43.   float current;
  44.   int voltage;
  45.   float phase;
  46. } Row;
  48. /**
  49.  * @brief insert data using stmt API
  50.  * 
  51.  * @param taos 
  52.  */
  53. void insertData(TAOS *taos) {
  54.   // init
  55.   TAOS_STMT *stmt = taos_stmt_init(taos);
  56.   // prepare
  57.   const char *sql = "INSERT INTO ? USING meters TAGS(?, ?) VALUES(?, ?, ?, ?)";
  58.   int code = taos_stmt_prepare(stmt, sql, 0);
  59.   checkErrorCode(stmt, code, "failed to execute taos_stmt_prepare");
  60.   // bind table name and tags
  61.   TAOS_BIND tags[2];
  62.   char* location = "California.SanFrancisco";
  63.   int groupId = 2;
  64.   tags[0].buffer_type = TSDB_DATA_TYPE_BINARY;
  65.   tags[0].buffer_length = strlen(location);
  66.   tags[0].length = &tags[0].buffer_length;
  67.   tags[0].buffer = location;
  68.   tags[0].is_null = NULL;
  70.   tags[1].buffer_type = TSDB_DATA_TYPE_INT;
  71.   tags[1].buffer_length = sizeof(int);
  72.   tags[1].length = &tags[1].buffer_length;
  73.   tags[1].buffer = &groupId;
  74.   tags[1].is_null = NULL;
  76.   code = taos_stmt_set_tbname_tags(stmt, "d1001", tags);
  77.   checkErrorCode(stmt, code, "failed to execute taos_stmt_set_tbname_tags");
  79.   // insert two rows
  80.   Row rows[2] = {
  81.     {1648432611249, 10.3, 219, 0.31},
  82.     {1648432611749, 12.6, 218, 0.33},
  83.   };
  85.   TAOS_BIND values[4];
  86.   values[0].buffer_type = TSDB_DATA_TYPE_TIMESTAMP;
  87.   values[0].buffer_length = sizeof(int64_t);
  88.   values[0].length = &values[0].buffer_length;
  89.   values[0].is_null = NULL;
  91.   values[1].buffer_type = TSDB_DATA_TYPE_FLOAT;
  92.   values[1].buffer_length = sizeof(float);
  93.   values[1].length = &values[1].buffer_length;
  94.   values[1].is_null = NULL;
  96.   values[2].buffer_type = TSDB_DATA_TYPE_INT;
  97.   values[2].buffer_length = sizeof(int);
  98.   values[2].length = &values[2].buffer_length;
  99.   values[2].is_null = NULL;
  101.   values[3].buffer_type = TSDB_DATA_TYPE_FLOAT;
  102.   values[3].buffer_length = sizeof(float);
  103.   values[3].length = &values[3].buffer_length;
  104.   values[3].is_null = NULL;
  106.   for (int i = 0; i < 2; ++i) {
  107.     values[0].buffer = &rows[i].ts;
  108.     values[1].buffer = &rows[i].current;
  109.     values[2].buffer = &rows[i].voltage;
  110.     values[3].buffer = &rows[i].phase;
  111.     code = taos_stmt_bind_param(stmt, values); // bind param
  112.     checkErrorCode(stmt, code, "failed to execute taos_stmt_bind_param");
  113.     code = taos_stmt_add_batch(stmt); // add batch
  114.     checkErrorCode(stmt, code, "failed to execute taos_stmt_add_batch");
  115.   }
  116.   // execute
  117.   code = taos_stmt_execute(stmt);
  118.   checkErrorCode(stmt, code, "failed to execute taos_stmt_execute");
  119.   int affectedRows = taos_stmt_affected_rows(stmt);
  120.   printf("successfully inserted %d rows\n", affectedRows);
  121.   // close
  122.   taos_stmt_close(stmt);
  123. }
  125. int main() {
  126.   TAOS *taos = taos_connect("localhost", "root", "taosdata", NULL, 6030);
  127.   if (taos == NULL) {
  128.     printf("failed to connect to server\n");
  129.     exit(EXIT_FAILURE);
  130.   }
  131.   executeSQL(taos, "CREATE DATABASE power");
  132.   executeSQL(taos, "USE power");
  133.   executeSQL(taos, "CREATE STABLE meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)");
  134.   insertData(taos);
  135.   taos_close(taos);
  136.   taos_cleanup();
  137. }
  140. // output:
  141. // successfully inserted 2 rows

查看源码

一次绑定多行

  1. // compile with
  2. // gcc -o multi_bind_example multi_bind_example.c -ltaos
  3. #include <stdio.h>
  4. #include <stdlib.h>
  5. #include <string.h>
  6. #include "taos.h"
  8. /**
  9.  * @brief execute sql only and ignore result set
  10.  *
  11.  * @param taos
  12.  * @param sql
  13.  */
  14. void executeSQL(TAOS *taos, const char *sql) {
  15.   TAOS_RES *res = taos_query(taos, sql);
  16.   int       code = taos_errno(res);
  17.   if (code != 0) {
  18.     printf("%s\n", taos_errstr(res));
  19.     taos_free_result(res);
  20.     taos_close(taos);
  21.     exit(EXIT_FAILURE);
  22.   }
  23.   taos_free_result(res);
  24. }
  26. /**
  27.  * @brief exit program when error occur.
  28.  *
  29.  * @param stmt
  30.  * @param code
  31.  * @param msg
  32.  */
  33. void checkErrorCode(TAOS_STMT *stmt, int code, const char *msg) {
  34.   if (code != 0) {
  35.     printf("%s. error: %s\n", msg, taos_stmt_errstr(stmt));
  36.     taos_stmt_close(stmt);
  37.     exit(EXIT_FAILURE);
  38.   }
  39. }
  41. /**
  42.  * @brief insert data using stmt API
  43.  *
  44.  * @param taos
  45.  */
  46. void insertData(TAOS *taos) {
  47.   // init
  48.   TAOS_STMT *stmt = taos_stmt_init(taos);
  49.   // prepare
  50.   const char *sql = "INSERT INTO ? USING meters TAGS(?, ?) values(?, ?, ?, ?)";
  51.   int         code = taos_stmt_prepare(stmt, sql, 0);
  52.   checkErrorCode(stmt, code, "failed to execute taos_stmt_prepare");
  53.   // bind table name and tags
  54.   TAOS_BIND tags[2];
  55.   char     *location = "California.SanFrancisco";
  56.   int       groupId = 2;
  57.   tags[0].buffer_type = TSDB_DATA_TYPE_BINARY;
  58.   tags[0].buffer_length = strlen(location);
  59.   tags[0].length = &tags[0].buffer_length;
  60.   tags[0].buffer = location;
  61.   tags[0].is_null = NULL;
  63.   tags[1].buffer_type = TSDB_DATA_TYPE_INT;
  64.   tags[1].buffer_length = sizeof(int);
  65.   tags[1].length = &tags[1].buffer_length;
  66.   tags[1].buffer = &groupId;
  67.   tags[1].is_null = NULL;
  69.   code = taos_stmt_set_tbname_tags(stmt, "d1001", tags);
  70.   checkErrorCode(stmt, code, "failed to execute taos_stmt_set_tbname_tags");
  72.   // insert two rows with multi binds
  73.   TAOS_MULTI_BIND params[4];
  74.   // values to bind
  75.   int64_t ts[] = {1648432611249, 1648432611749};
  76.   float   current[] = {10.3, 12.6};
  77.   int     voltage[] = {219, 218};
  78.   float   phase[] = {0.31, 0.33};
  79.   // is_null array
  80.   char is_null[2] = {0};
  81.   // length array
  82.   int32_t int64Len[2] = {sizeof(int64_t)};
  83.   int32_t floatLen[2] = {sizeof(float)};
  84.   int32_t intLen[2] = {sizeof(int)};
  86.   params[0].buffer_type = TSDB_DATA_TYPE_TIMESTAMP;
  87.   params[0].buffer_length = sizeof(int64_t);
  88.   params[0].buffer = ts;
  89.   params[0].length = int64Len;
  90.   params[0].is_null = is_null;
  91.   params[0].num = 2;
  93.   params[1].buffer_type = TSDB_DATA_TYPE_FLOAT;
  94.   params[1].buffer_length = sizeof(float);
  95.   params[1].buffer = current;
  96.   params[1].length = floatLen;
  97.   params[1].is_null = is_null;
  98.   params[1].num = 2;
  100.   params[2].buffer_type = TSDB_DATA_TYPE_INT;
  101.   params[2].buffer_length = sizeof(int);
  102.   params[2].buffer = voltage;
  103.   params[2].length = intLen;
  104.   params[2].is_null = is_null;
  105.   params[2].num = 2;
  107.   params[3].buffer_type = TSDB_DATA_TYPE_FLOAT;
  108.   params[3].buffer_length = sizeof(float);
  109.   params[3].buffer = phase;
  110.   params[3].length = floatLen;
  111.   params[3].is_null = is_null;
  112.   params[3].num = 2;
  114.   code = taos_stmt_bind_param_batch(stmt, params); // bind batch
  115.   checkErrorCode(stmt, code, "failed to execute taos_stmt_bind_param_batch");
  116.   code = taos_stmt_add_batch(stmt);  // add batch
  117.   checkErrorCode(stmt, code, "failed to execute taos_stmt_add_batch");
  118.   // execute
  119.   code = taos_stmt_execute(stmt);
  120.   checkErrorCode(stmt, code, "failed to execute taos_stmt_execute");
  121.   int affectedRows = taos_stmt_affected_rows(stmt);
  122.   printf("successfully inserted %d rows\n", affectedRows);
  123.   // close
  124.   taos_stmt_close(stmt);
  125. }
  127. int main() {
  128.   TAOS *taos = taos_connect("localhost", "root", "taosdata", NULL, 6030);
  129.   if (taos == NULL) {
  130.     printf("failed to connect to server\n");
  131.     exit(EXIT_FAILURE);
  132.   }
  133.   executeSQL(taos, "DROP DATABASE IF EXISTS power");
  134.   executeSQL(taos, "CREATE DATABASE power");
  135.   executeSQL(taos, "USE power");
  136.   executeSQL(taos,
  137.              "CREATE STABLE meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), "
  138.              "groupId INT)");
  139.   insertData(taos);
  140.   taos_close(taos);
  141.   taos_cleanup();
  142. }
  144. // output:
  145. // successfully inserted 2 rows

查看源码

  1. <?php
  3. use TDengine\Connection;
  4. use TDengine\Exception\TDengineException;
  6. try {
  7.     // instantiate
  8.     $host = 'localhost';
  9.     $port = 6030;
  10.     $username = 'root';
  11.     $password = 'taosdata';
  12.     $dbname = 'power';
  13.     $connection = new Connection($host, $port, $username, $password, $dbname);
  15.     // connect
  16.     $connection->connect();
  18.     // insert
  19.     $connection->query('CREATE DATABASE if not exists power');
  20.     $connection->query('CREATE STABLE if not exists meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)');
  21.     $stmt = $connection->prepare('INSERT INTO ? USING meters TAGS(?, ?) VALUES(?, ?, ?, ?)');
  23.     // set table name and tags
  24.     $stmt->setTableNameTags('d1001', [
  25.         // same format as parameter binding
  26.         [TDengine\TSDB_DATA_TYPE_BINARY, 'California.SanFrancisco'],
  27.         [TDengine\TSDB_DATA_TYPE_INT, 2],
  28.     ]);
  30.     $stmt->bindParams([
  31.         [TDengine\TSDB_DATA_TYPE_TIMESTAMP, 1648432611249],
  32.         [TDengine\TSDB_DATA_TYPE_FLOAT, 10.3],
  33.         [TDengine\TSDB_DATA_TYPE_INT, 219],
  34.         [TDengine\TSDB_DATA_TYPE_FLOAT, 0.31],
  35.     ]);
  36.     $stmt->bindParams([
  37.         [TDengine\TSDB_DATA_TYPE_TIMESTAMP, 1648432611749],
  38.         [TDengine\TSDB_DATA_TYPE_FLOAT, 12.6],
  39.         [TDengine\TSDB_DATA_TYPE_INT, 218],
  40.         [TDengine\TSDB_DATA_TYPE_FLOAT, 0.33],
  41.     ]);
  42.     $resource = $stmt->execute();
  44.     // get affected rows
  45.     var_dump($resource->affectedRows());
  46. } catch (TDengineException $e) {
  47.     // throw exception
  48.     throw $e;
  49. }

查看源码