SQL 写入

SQL 写入简介

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

一次写入一条

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

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

一次写入多条

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

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

一次写入多表

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

  1. INSERT INTO d1001 VALUES (1538548685000, 10.3, 219, 0.31) (1538548695000, 12.6, 218, 0.33) d1002 VALUES (1538548696800, 12.3, 221, 0.31);

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

SQL 写入 - 图1info
  • 要提高写入效率，需要批量写入。一批写入的记录条数越多，插入效率就越高。但一条记录不能超过 48K，一条 SQL 语句总长度不能超过 1M 。
  • TDengine 支持多线程同时写入，要进一步提高写入速度，一个客户端需要打开 20 个以上的线程同时写。但线程数达到一定数量后，无法再提高，甚至还会下降，因为线程频繁切换，带来额外开销。
SQL 写入 - 图2warning
  • 对同一张表，如果新插入记录的时间戳已经存在，默认情形下（UPDATE=0）新记录将被直接抛弃，也就是说，在一张表里，时间戳必须是唯一的。如果应用自动生成记录，很有可能生成的时间戳是一样的，这样，成功插入的记录条数会小于应用插入的记录条数。如果在创建数据库时使用了 UPDATE 1 选项，插入相同时间戳的新记录将覆盖原有记录。
  • 写入的数据的时间戳必须大于当前时间减去配置参数 keep 的时间。如果 keep 配置为 3650 天，那么无法写入比 3650 天还早的数据。写入数据的时间戳也不能大于当前时间加配置参数 days。如果 days 为 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"
  7.     _ "github.com/taosdata/driver-go/v2/taosRestful"
  8. )
  10. func createStable(taos *sql.DB) {
  11.     _, err := taos.Exec("CREATE DATABASE power")
  12.     if err != nil {
  13.         fmt.Println("failed to create database, err:", err)
  14.     }
  15.     _, err = taos.Exec("CREATE STABLE power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)")
  16.     if err != nil {
  17.         fmt.Println("failed to create stable, err:", err)
  18.     }
  19. }
  21. func insertData(taos *sql.DB) {
  22.     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)
  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.     result, err := taos.Exec(sql)
  27.     if err != nil {
  28.         fmt.Println("failed to insert, err:", err)
  29.         return
  30.     }
  31.     rowsAffected, err := result.RowsAffected()
  32.     if err != nil {
  33.         fmt.Println("failed to get affected rows, err:", err)
  34.         return
  35.     }
  36.     fmt.Println("RowsAffected", rowsAffected)
  37. }
  39. func main() {
  40.     var taosDSN = "root:taosdata@http(localhost:6041)/"
  41.     taos, err := sql.Open("taosRestful", taosDSN)
  42.     if err != nil {
  43.         fmt.Println("failed to connect TDengine, err:", err)
  44.         return
  45.     }
  46.     defer taos.Close()
  47.     createStable(taos)
  48.     insertData(taos)
  49. }

查看源码

  1. use libtaos::*;
  3. #[tokio::main]
  4. async fn main() -> Result<(), Error> {
  5.     let taos = TaosCfg::default().connect().expect("fail to connect");
  6.     taos.create_database("power").await?;
  7.     taos.exec("CREATE STABLE power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)").await?;
  8.     let 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)
  9.     power.d1002 USING power.meters TAGS(California.SanFrancisco, 3) VALUES ('2018-10-03 14:38:16.650', 10.30000, 218, 0.25000)
  10.     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)
  11.     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)";
  12.     let result = taos.query(sql).await?;
  13.     println!("{:?}", result);
  14.     Ok(())
  15. }
  17. // output:
  18. // TaosQueryData { column_meta: [ColumnMeta { name: "affected_rows", type_: Int, bytes: 4 }], rows: [[Int(8)]] }

查看源码

  1. const taos = require("td2.0-connector");
  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);
  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.             IntPtr res = TDengine.Query(conn, "CREATE DATABASE power");
  13.             CheckRes(conn, res, "failed to create database");
  14.             res = TDengine.Query(conn, "USE power");
  15.             CheckRes(conn, res, "failed to change database");
  16.             res = TDengine.Query(conn, "CREATE STABLE power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)");
  17.             CheckRes(conn, res, "failed to create stable");
  18.             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) " +
  19.                         "d1002 USING power.meters TAGS(California.SanFrancisco, 3) VALUES('2018-10-03 14:38:16.650', 10.30000, 218, 0.25000) " +
  20.                         "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) " +
  21.                         "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)";
  22.             res = TDengine.Query(conn, sql);
  23.             CheckRes(conn, res, "failed to insert data");
  24.             int affectedRows = TDengine.AffectRows(res);
  25.             Console.WriteLine("affectedRows " + affectedRows);
  26.             ExitProgram(conn, 0);
  27.         }
  29.         static IntPtr GetConnection()
  30.         {
  31.             string host = "localhost";
  32.             short port = 6030;
  33.             string username = "root";
  34.             string password = "taosdata";
  35.             string dbname = "";
  36.             var conn = TDengine.Connect(host, username, password, dbname, port);
  37.             if (conn == IntPtr.Zero)
  38.             {
  39.                 Console.WriteLine("Connect to TDengine failed");
  40.                 Environment.Exit(0);
  41.             }
  42.             else
  43.             {
  44.                 Console.WriteLine("Connect to TDengine success");
  45.             }
  46.             return conn;
  47.         }
  49.         static void CheckRes(IntPtr conn, IntPtr res, String errorMsg)
  50.         {
  51.             if (TDengine.ErrorNo(res) != 0)
  52.             {
  53.                 Console.Write(errorMsg + " since: " + TDengine.Error(res));
  54.                 ExitProgram(conn, 1);
  55.             }
  56.         }
  58.         static void ExitProgram(IntPtr conn, int exitCode)
  59.         {
  60.             TDengine.Close(conn);
  61.             TDengine.Cleanup();
  62.             Environment.Exit(exitCode);
  63.         }
  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 目前也仅支持用问号 ? 来代表待绑定的参数。从 2.1.1.0 和 2.1.2.0 版本开始，TDengine 大幅改进了参数绑定接口对数据写入（INSERT）场景的支持。这样在通过参数绑定接口写入数据时，就避免了 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/v2/af"
  8.     "github.com/taosdata/driver-go/v2/af/param"
  9.     "github.com/taosdata/driver-go/v2/common"
  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/v2/wrapper 是 C 接口的底层封装。使用这个模块也可以实现参数绑定写入。

  1. use bstr::BString;
  2. use libtaos::*;
  4. #[tokio::main]
  5. async fn main() -> Result<(), Error> {
  6.     let taos = TaosCfg::default().connect().expect("fail to connect");
  7.     taos.create_database("power").await?;
  8.     taos.use_database("power").await?;
  9.     taos.exec("CREATE STABLE meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)").await?;
  10.     let mut stmt = taos.stmt("INSERT INTO ? USING meters TAGS(?, ?) VALUES(?, ?, ?, ?)")?;
  11.     // bind table name and tags
  12.     stmt.set_tbname_tags(
  13.         "d1001",
  14.         [
  15.             Field::Binary(BString::from("California.SanFrancisco")),
  16.             Field::Int(2),
  17.         ],
  18.     )?;
  19.     // bind values.
  20.     let values = vec![
  21.         Field::Timestamp(Timestamp::new(1648432611249, TimestampPrecision::Milli)),
  22.         Field::Float(10.3),
  23.         Field::Int(219),
  24.         Field::Float(0.31),
  25.     ];
  26.     stmt.bind(&values)?;
  27.     // bind one more row
  28.     let values2 = vec![
  29.         Field::Timestamp(Timestamp::new(1648432611749, TimestampPrecision::Milli)),
  30.         Field::Float(12.6),
  31.         Field::Int(218),
  32.         Field::Float(0.33),
  33.     ];
  34.     stmt.bind(&values2)?;
  35.     // execute
  36.     stmt.execute()?;
  37.     Ok(())
  38. }

查看源码

一次绑定一行

  1. const taos = require("td2.0-connector");
  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.TaosBind(2);
  27.   tagBind.bindBinary("California.SanFrancisco");
  28.   tagBind.bindInt(2);
  29.   cursor.stmtSetTbnameTags("d1001", tagBind.getBind());
  31.   // bind values
  32.   let rows = [
  33.     [1648432611249, 10.3, 219, 0.31],
  34.     [1648432611749, 12.6, 218, 0.33],
  35.   ];
  36.   for (let row of rows) {
  37.     let valueBind = new taos.TaosBind(4);
  38.     valueBind.bindTimestamp(row[0]);
  39.     valueBind.bindFloat(row[1]);
  40.     valueBind.bindInt(row[2]);
  41.     valueBind.bindFloat(row[3]);
  42.     cursor.stmtBindParam(valueBind.getBind());
  43.     cursor.stmtAddBatch();
  44.   }
  46.   // execute
  47.   cursor.stmtExecute();
  48.   cursor.stmtClose();
  49. }
  51. try {
  52.   prepareSTable();
  53.   insertData();
  54. } finally {
  55.   cursor.close();
  56.   conn.close();
  57. }

查看源码

一次绑定多行

  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.TaosBind(2);
  11.   tagBind.bindBinary("California.SanFrancisco");
  12.   tagBind.bindInt(2);
  13.   cursor.stmtSetTbnameTags("d1001", tagBind.getBind());
  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.             PrepareSTable();
  13.             // 1. init and prepare
  14.             stmt = TDengine.StmtInit(conn);
  15.             if (stmt == IntPtr.Zero)
  16.             {
  17.                 Console.WriteLine("failed to init stmt, " + TDengine.Error(stmt));
  18.                 ExitProgram();
  19.             }
  20.             int res = TDengine.StmtPrepare(stmt, "INSERT INTO ? USING meters TAGS(?, ?) VALUES(?, ?, ?, ?)");
  21.             CheckStmtRes(res, "failed to prepare stmt");
  23.             // 2. bind table name and tags
  24.             TAOS_BIND[] tags = new TAOS_BIND[2] { TaosBind.BindBinary("California.SanFrancisco"), TaosBind.BindInt(2) };
  25.             res = TDengine.StmtSetTbnameTags(stmt, "d1001", tags);
  26.             CheckStmtRes(res, "failed to bind table name and tags");
  28.             // 3. bind values
  29.             TAOS_MULTI_BIND[] values = new TAOS_MULTI_BIND[4] {
  30.                 TaosMultiBind.MultiBindTimestamp(new long[2] { 1648432611249, 1648432611749}),
  31.                 TaosMultiBind.MultiBindFloat(new float?[2] { 10.3f, 12.6f}),
  32.                 TaosMultiBind.MultiBindInt(new int?[2] { 219, 218}),
  33.                 TaosMultiBind.MultiBindFloat(new float?[2]{ 0.31f, 0.33f})
  34.             };
  35.             res = TDengine.StmtBindParamBatch(stmt, values);
  36.             CheckStmtRes(res, "failed to bind params");
  38.             // 4. add batch
  39.             res = TDengine.StmtAddBatch(stmt);
  40.             CheckStmtRes(res, "failed to add batch");
  42.             // 5. execute
  43.             res = TDengine.StmtExecute(stmt);
  44.             CheckStmtRes(res, "faild to execute");
  46.             // 6. free 
  47.             TaosBind.FreeTaosBind(tags);
  48.             TaosMultiBind.FreeTaosBind(values);
  49.             TDengine.Close(conn);
  50.             TDengine.Cleanup();
  51.         }
  53.         static IntPtr GetConnection()
  54.         {
  55.             string host = "localhost";
  56.             short port = 6030;
  57.             string username = "root";
  58.             string password = "taosdata";
  59.             string dbname = "";
  60.             var conn = TDengine.Connect(host, username, password, dbname, port);
  61.             if (conn == IntPtr.Zero)
  62.             {
  63.                 Console.WriteLine("Connect to TDengine failed");
  64.                 Environment.Exit(0);
  65.             }
  66.             else
  67.             {
  68.                 Console.WriteLine("Connect to TDengine success");
  69.             }
  70.             return conn;
  71.         }
  75.         static void PrepareSTable()
  76.         {
  77.             IntPtr res = TDengine.Query(conn, "CREATE DATABASE power");
  78.             CheckResPtr(res, "failed to create database");
  79.             res = TDengine.Query(conn, "USE power");
  80.             CheckResPtr(res, "failed to change database");
  81.             res = TDengine.Query(conn, "CREATE STABLE power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)");
  82.             CheckResPtr(res, "failed to create stable");
  83.         }
  85.         static void CheckStmtRes(int res, string errorMsg)
  86.         {
  87.             if (res != 0)
  88.             {
  89.                 Console.WriteLine(errorMsg + ", " + TDengine.StmtErrorStr(stmt));
  90.                 int code = TDengine.StmtClose(stmt);
  91.                 if (code != 0)
  92.                 {
  93.                     Console.WriteLine($"falied to close stmt, {code} reason: {TDengine.StmtErrorStr(stmt)} ");
  94.                 }
  95.                 ExitProgram();
  96.             }
  97.         }
  99.         static void CheckResPtr(IntPtr res, string errorMsg)
  100.         {
  101.             if (TDengine.ErrorNo(res) != 0)
  102.             {
  103.                 Console.WriteLine(errorMsg + " since:" + TDengine.Error(res));
  104.                 ExitProgram();
  105.             }
  106.         }
  108.         static void ExitProgram()
  109.         {
  110.             TDengine.Close(conn);
  111.             TDengine.Cleanup();
  112.             Environment.Exit(1);
  113.         }
  114.     }
  115. }

查看源码

一次绑定一行

  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. }

查看源码