Data Manipulation Statements

DELETE Statement

Single-Table Syntax

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Multiple-Table Syntax

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

Instructions

Support distributed transactions to ensure that atomicity is removed across partitions
Does not support clauses
Does not support partition feature
Currently we support parse syntax 'delete with multitables',but the function does not implement yet

Example:

mysql> DELETE FROM t1 WHERE id=1 order by id limit 2;
Query OK, 2 rows affected (0.01 sec)

mysql> DELETE FROM t1;
Query OK, 2 rows affected (0.01 sec)

DO Statement

Syntax

DO expr [, expr] ...

Example:

mysql> do 1;
Query OK, 0 rows affected (0.01 sec)

mysql> do 2
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> do 1 > 2, 1&2;
Query OK, 0 rows affected (0.00 sec)

INSERT

Syntax

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    {VALUES | VALUE} (value_list) [, (value_list)] ...
    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    SET assignment_list
    [ON DUPLICATE KEY UPDATE assignment_list]

value:
    {expr | DEFAULT}

value_list:
    value [, value] ...

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

Instructions

Support distributed transactions to ensure cross-partition write atomicity
Support insert multiple values, these values can be in different partitions
Does not support clauses like INSERT ... SELECT Statement
Not support PARTITION we support parser PARTITION, but the function hasn't supported yet.
If we write data with specified columns, we'll get a better performance.
Not support all default values: "INSERT INTO t VALUES (),(),();"
Not support subquery clause: "INSERT INTO t select * from t1 ... "
Not support expr in values: "INSERT INTO t values (a+2)"

Example: Write data with columns(In this way we'll get a better performance.)

mysql> desc t;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | NO   | PRI | NULL    |       |
| b     | int(11) | YES  |     | NULL    |       |
| c     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> INSERT INTO t(id, age, c) VALUES(1, 24, 2), (2, 28, 3), (3, 29, 4);
Query OK, 3 rows affected (0.03 sec)

Write data without columns(In this way there will be some performance loss.)


mysql> insert into t values (11, 2, 3), (12, 4, 5), (13, 2, 3);
Query OK, 2 rows affected (0.03 sec)

REPLACE

Syntax

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    {VALUES | VALUE} (value_list) [, (value_list)] ...

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    SET assignment_list

value:
    {expr | DEFAULT}

value_list:
    value [, value] ...

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

Instructions

Support distributed transactions to ensure cross-partition write atomicity
Support replace multiple values, these values can be in different partitions
Does not support clauses like INSERT ... SELECT Statement
Not support PARTITION we support parser PARTITION, but the function hasn't supported yet.
If we write data with specified columns, we'll get a better performance.

Example:

mysql> replace into t values (23, 2, 3), (24, 4, 5), (25, 5, 6);
Query OK, 3 rows affected (0.01 sec)

mysql> replace into t(a,b,c) values (33, 2, 3), (34, 4, 5), (35, 5, 6);
Query OK, 3 rows affected (0.01 sec)

SELECT

Syntax

SELECT
    [DISTINCT]
    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name}
    [HAVING where_condition]
    [ORDER BY {col_name}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

JOIN

table_references:
    table_reference [, table_reference] ...
table_reference:
    table_factor
  | join_table
table_factor:
    [schema_name.]tbl_name [[AS] alias]
  | ( table_references )
join_table:
    table_reference [INNER | CROSS] JOIN table_factor [join_condition]
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
join_condition:
    ON conditional_expr

UNION

SELECT ...
UNION [ALL | DISTINCT]
SELECT ...
[UNION [ALL | DISTINCT]
SELECT ...]

Instructions

Support cross-partition count, sum, avg, max, min and other aggregate functions, Aggregate functions only support for numeric values
Support cross-partition order by, group by, limit and other operations, group by field must be in select_expr
Group by suggest to be used with aggregation function, avoid using group by alone when returning non-group by fields.
Support complex queries such as joins.
Support where and having clause, having doesn't support aggregate function temporarily.
Support retrieving rows computed without reference to any table or specify DUAL as a dummy table name in situations where no tables are referenced.
Support alias_name for column like SELECT columna [[AS] alias] FROM mytable;.
Support alias_name for table like SELECT columna FROM tbl_name [[AS] alias];.
Support LEFT|RIGHT OUTER and INNER|CROSS join.
select * is not recommended, especially in join statements.
Support UNION [ALL | DISTINCT].

Example:

mysql> CREATE TABLE t2(id int, age int) partition by HASH(id);
Query OK, 0 rows affected (1.78 sec)

mysql> INSERT INTO t2(id, age) values(1, 25);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t2(id, age) values(3, 22);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t2(id, age) values(13, 22);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO t2(id, age) values(23, 22);
Query OK, 1 row affected (0.00 sec)

mysql> select id, sum(id) from t2 group by id order by id desc limit 10;
+------+---------+
| id   | sum(id) |
+------+---------+
|   23 |      23 |
|   13 |      13 |
|    3 |       3 |
|    1 |       1 |
+------+---------+
4 rows in set (1.048 sec)

SELECT can be used to retrieve rows computed without reference to any table:

mysql> select 1 + 1;
+-------+
| 1 + 1 |
+-------+
|     2 |
+-------+
1 row in set (0.00 sec)

Specify DUAL as a dummy table name in situations where no tables are referenced:

mysql> select date_format(now(),'%y-%m-%d') FROM DUAL;
+-------------------------------+
| date_format(now(),'%y-%m-%d') |
+-------------------------------+
| 18-06-18                      |
+-------------------------------+
1 row in set (0.00 sec)

SELECT with alias, AS is optional:

mysql> select id ID from t2 testTbl;
+------+
| ID   |
+------+
|    3 |
|   23 |
|    1 |
|   13 |
+------+
4 rows in set (0.02 sec)

mysql> select testTbl.id as ID from t2 as testTbl;
+------+
| ID   |
+------+
|    3 |
|   23 |
|    1 |
|   13 |
+------+
4 rows in set (0.02 sec)

SELECT with JOIN, the join statement that cannot be pushed down cannot have * in the selectexpr:

mysql> CREATE TABLE t1(id int, age int) partition by HASH(id);
Query OK, 0 rows affected (1.127 sec)

mysql> INSERT INTO t1(id, age) values(1, 22),(2,25),(3,22),(4,25);
Query OK, 4 row affected (0.197 sec)

mysql> select id, sum(id) from t2 group by id order by id desc limit 10;
+------+---------+
| id   | sum(id) |
+------+---------+
|   23 |      23 |
|   13 |      13 |
|    3 |       3 |
|    1 |       1 |
+------+---------+
4 rows in set (1.048 sec)

mysql> select * from t1 join t2 on t1.id=t2.id where t2.age=22;
+------+------+------+------+
| id   | age  | id   | age  |
+------+------+------+------+
|    3 |   22 |    3 |   22 |
+------+------+------+------+
1 row in set (1.082 sec)

mysql> select t1.id, t1.age,t2.id from t1 join t2 on t1.age=t2.age where t2.id > 10 order by t1.id;
+------+------+------+
| id   | age  | id   |
+------+------+------+
|    1 |   22 |   23 |
|    1 |   22 |   13 |
|    3 |   22 |   23 |
|    3 |   22 |   13 |
+------+------+------+
4 rows in set (1.056 sec)

mysql> select * from t1 join t2 on t1.age=t2.age where t2.id > 10 order by t1.id;
ERROR 1105 (HY000): unsupported: '*'.expression.in.cross-shard.query

mysql> select * from t1 union select * from t2 order by id limit 1;
+------+------+
| id   | age  |
+------+------+
|    1 |   25 |
+------+------+
1 row in set (1.012 sec)

UPDATE

Syntax

UPDATE table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]

Instructions

Supports distributed transactions to ensure atomicity across partitions
Does not support WHERE-less condition updates
Does not support updating partition key
Does not support clauses

Example:

mysql> UPDATE t1 set age=age+1 WHERE id=1;
Query OK, 1 row affected (0.00 sec)

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

data_manipulation_statements.md

data_manipulation_statements.md

Table of Contents

Data Manipulation Statements

DELETE Statement

DO Statement

INSERT

REPLACE

SELECT

UPDATE

Files

data_manipulation_statements.md

Latest commit

History

data_manipulation_statements.md

File metadata and controls

Table of Contents

Data Manipulation Statements

DELETE Statement

DO Statement

INSERT

REPLACE

SELECT

UPDATE