Skip to content

Latest commit

 

History

History
173 lines (128 loc) · 3.07 KB

sql-statement-insert.md

File metadata and controls

173 lines (128 loc) · 3.07 KB
Raw
title summary aliases
INSERT
TiDB 数据库中 INSERT 的使用概况。
/docs-cn/dev/sql-statements/sql-statement-insert/
/docs-cn/dev/reference/sql/statements/insert/

INSERT

使用 INSERT 语句在表中插入新行。

语法图

InsertIntoStmt ::=
    'INSERT' TableOptimizerHints PriorityOpt IgnoreOptional IntoOpt TableName PartitionNameListOpt InsertValues OnDuplicateKeyUpdate

TableOptimizerHints ::=
    hintComment?

PriorityOpt ::=
    ( 'LOW_PRIORITY' | 'HIGH_PRIORITY' | 'DELAYED' )?

IgnoreOptional ::=
    'IGNORE'?

IntoOpt  ::= 'INTO'?

TableName ::=
    Identifier ( '.' Identifier )?

PartitionNameListOpt ::=
    ( 'PARTITION' '(' Identifier ( ',' Identifier )* ')' )?

InsertValues ::=
    '(' ( ColumnNameListOpt ')' ( ValueSym ValuesList | SelectStmt | '(' SelectStmt ')' | UnionStmt ) | SelectStmt ')' )
|   ValueSym ValuesList
|   SelectStmt
|   UnionStmt
|   'SET' ColumnSetValue? ( ',' ColumnSetValue )*

OnDuplicateKeyUpdate ::=
    ( 'ON' 'DUPLICATE' 'KEY' 'UPDATE' AssignmentList )?

注意：

TiDB 从 v6.6.0 版本开始支持使用资源管控 (Resource Control) 实现资源隔离功能。该功能可以将不同优先级的语句放在不同的资源组中执行，并为这些资源组分配不同的配额和优先级，可以达到更好的资源管控效果。在开启资源管控功能后，语句的调度主要受资源组的控制，PriorityOpt 将不再生效。建议在支持资源管控的版本优先使用资源管控功能。

示例

{{< copyable "sql" >}}

CREATE TABLE t1 (a INT);
Query OK, 0 rows affected (0.11 sec)

{{< copyable "sql" >}}

CREATE TABLE t2 LIKE t1;
Query OK, 0 rows affected (0.11 sec)

{{< copyable "sql" >}}

INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.02 sec)

{{< copyable "sql" >}}

INSERT INTO t1 (a) VALUES (1);
Query OK, 1 row affected (0.01 sec)

{{< copyable "sql" >}}

INSERT INTO t2 SELECT * FROM t1;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

{{< copyable "sql" >}}

SELECT * FROM t1;
+------+
| a    |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.00 sec)

{{< copyable "sql" >}}

SELECT * FROM t2;
+------+
| a    |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.00 sec)

{{< copyable "sql" >}}

INSERT INTO t2 VALUES (2),(3),(4);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

{{< copyable "sql" >}}

SELECT * FROM t2;
+------+
| a    |
+------+
|    1 |
|    1 |
|    2 |
|    3 |
|    4 |
+------+
5 rows in set (0.00 sec)

MySQL 兼容性

INSERT 语句与 MySQL 完全兼容。如发现任何兼容性差异，请尝试 TiDB 支持资源。

另请参阅