SQL 写入数据

SQL 样例

这里有一些 INSERT 语句的基本样例。用户可以通过 TDengine CLI，TDengine Cloud 的数据浏览器或者通过 TDengine 连接器开发等执行这些语句。

一次写入一条

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

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

一次写入多条

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

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

一次写入多表

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

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

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

连接器样例

下面以智能电表为例，展示如何使用各语言连接器在名为 power 的数据库中，创建一个名为 meters 的超级表（STABLE），其表结构包含时间戳、电流、电压、相位等列，以及分组 ID 和位置作为标签。

IMPORTANT

在执行下面样例代码的之前，您必须先在 TDengine Cloud - 数据浏览器页面创建一个名为 power 的数据库
如何在代码中建立和 TDengine Cloud 的连接，请参考开发指南-建立连接。

这个例子中，我们使用 execute 方法来执行 SQL 和得到插入的行数。参数 conn 是类taosrest.TaosRestConnection 的一个实例，请参考建立连接-Python.

# create super table
conn.execute("CREATE STABLE IF NOT EXISTS power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)")
# insert multiple rows into multiple tables at once. subtables will be created automatically.
affected_row = conn.execute("""
    INSERT INTO 
    power.d1001 USING power.meters TAGS('California.SanFrancisco',1) VALUES 
                            ('2018-10-03 14:38:10.000', 10.30000, 219, 0.31000) 
                            ('2018-10-03 14:38:20.000', 12.60000, 218, 0.33000) 
                            ('2018-10-03 14:38:30.800', 12.30000, 221, 0.31000)
    power.d1002 USING power.meters TAGS('California.SanDiego',2) VALUES 
                            ('2018-10-03 14:38:16.650', 10.30000, 218, 0.25000)
        """)
print("affected_row", affected_row)  # 4

查看源码

stmt.execute("DROP DATABASE IF EXISTS power");
stmt.execute("CREATE DATABASE power");
stmt.execute("CREATE STABLE power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)");
stmt.execute("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) power.d1002 USING power.meters TAGS(California.SanFrancisco, 3) VALUES ('2018-10-03 14:38:16.650', 10.30000, 218, 0.25000)");

查看源码

_, err = taos.Exec("DROP DATABASE IF EXISTS power")
if err != nil {
	fmt.Println("failed to drop database, err:", err)
	return
}
_, err = taos.Exec("CREATE DATABASE power")
if err != nil {
	fmt.Println("failed to create database, err:", err)
	return
}
_, err = taos.Exec("CREATE STABLE power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)")
if err != nil {
	fmt.Println("failed to create stable, err:", err)
	return
}
result, err := taos.Exec("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) power.d1002 USING power.meters TAGS(California.SanFrancisco, 3) VALUES ('2018-10-03 14:38:16.650', 10.30000, 218, 0.25000)")
if err != nil {
	fmt.Println("failed to insert, err:", err)
	return
}
rowsAffected, err := result.RowsAffected()
if err != nil {
	fmt.Println("failed to get affected rows, err:", err)
	return
}
fmt.Println("RowsAffected", rowsAffected) // RowsAffected 4

查看源码

在这个例子中，我们使用 exec 方法来执行 SQL 。exec 是为非查询的 SQL 语句设计的，所有返回的数据都会被忽略。

taos.exec("CREATE STABLE IF NOT EXISTS power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)").await?;
taos.exec("INSERT INTO
    power.d1001 USING power.meters
        TAGS('California.SanFrancisco', 2)
        VALUES (NOW, 12.3, 219, 0.31000) (NOW - 1s, 12.60000, 218, 0.33000) (NOW - 3s, 12.30000, 221, 0.31000)
    power.d1002 USING power.meters
        TAGS('California.SanFrancisco', 3)
        VALUES ('2018-10-03 14:39:16.650', 23.4, 218, 0.25000)
    ").await?;

查看源码

const taos = require('@tdengine/websocket');

var url = process.env.TDENGINE_CLOUD_URL;
async function insertData() {
  let conn = null;
  try {
    let conf = new taos.WSConfig(url);
    conf.setDb('test');
    conn = await taos.sqlConnect(conf);
    await conn.exec(
      "insert into cloud using meters tags (1, 'new york') values (now, 1.1, 1, 1.1)"
    );
  } catch (err) {
    throw err;
  } finally {
    if (conn) {
      await conn.close();
    }
  }
}

insertData();

查看源码

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net5.0</TargetFramework>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="TDengine.Connector" Version="3.0.*" GeneratePathProperty="true" />
  </ItemGroup>
  <Target Name="copyDLLDependency" BeforeTargets="BeforeBuild">
    <ItemGroup>
      <DepDLLFiles Include="$(PkgTDengine_Connector)\runtimes\**\*.*" />
    </ItemGroup>
    <Copy SourceFiles="@(DepDLLFiles)" DestinationFolder="$(OutDir)" />
  </Target>

</Project>

查看源码

string createTable = "CREATE STABLE if not exists test.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)";
string insertData = "INSERT INTO test.d1001 USING test.meters TAGS('California.SanFrancisco', 1) VALUES ('2018-10-03 14:38:05.000', 10.30000, 219, 0.31000)" +
                    "test.d1002 USING test.meters TAGS('California.SanFrancisco', 2) VALUES ('2018-10-03 14:38:16.650', 10.30000, 218, 0.25000)" +
                    "test.d1003 USING test.meters TAGS('California.LosAngeles', 3) VALUES ('2018-10-03 14:38:05.500', 11.80000, 221, 0.28000)" +
                    "test.d1004 USING test.meters TAGS('California.LosAngeles', 4) VALUES ('2018-10-03 14:38:05.000', 10.80000, 223, 0.29000) ";

// create database under database named 'test'
IntPtr res = LibTaosWS.WSQuery(conn, createTable);
ValidUpdateExecution(res);
// Free the query result every time when used up it.
LibTaosWS.WSFreeResult(res);

// insert data into the table created in previous step.
res = LibTaosWS.WSQuery(conn, insertData);
ValidUpdateExecution(res);
// Free the query result every time when used up it.
LibTaosWS.WSFreeResult(res);
                    
void ValidUpdateExecution(IntPtr res)
{
    int code = LibTaosWS.WSErrorNo(res);
    if (code != 0)
    {
        throw new Exception($"execute SQL failed: reason: {LibTaosWS.WSErrorStr(res)}, code:{code}");
    }
}