Basic SQL operations (MySQL-compatible mode)|V4.4.2| docs|Distributed Database

Basic SQL operations (MySQL-compatible mode)

Last Updated：2026-03-06 07:02:40 Updated

This topic describes the basic SQL operations in the MySQL-compatible mode of OceanBase Database.

Create a database

Use the CREATE DATABASE statement to create a database.

Example: Create a database named db1, set the character set to utf8mb4, and specify the read and write attributes.

obclient(root@mysqltenant)[(none)]> CREATE DATABASE db1 DEFAULT CHARACTER SET utf8mb4 READ WRITE;

For more information about the syntax of the CREATE DATABASE statement, see CREATE DATABASE.

After a database is created, you can execute the SHOW DATABASES statement to list all databases on the current database server.

obclient(root@mysqltenant)[(none)]> SHOW DATABASES;

The result is as follows:

+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| oceanbase          |
| test               |
| test_db            |
+--------------------+
6 rows in set

Table operations

In OceanBase Database, tables are the fundamental data storage units that hold all user-accessible data. Each table contains multiple rows, and each row consists of multiple columns. This topic describes the syntax and examples for creating, viewing, modifying, and dropping tables in a database.

Create a table

Use the CREATE TABLE statement to create a table in a database.

Example: Create a table named test in the db1 database.

Switch to the db1 database.

obclient(root@mysqltenant)[(none)]> USE db1;

Create the test table.

obclient(root@mysqltenant)[db1]> CREATE TABLE test (c1 INT PRIMARY KEY, c2 VARCHAR(3));

For more information about the syntax of the CREATE TABLE statement, see CREATE TABLE.

View a table

Use the SHOW CREATE TABLE statement to view the table creation statement.

Example: View the table creation statement for the test table.

obclient(root@mysqltenant)[db1]> SHOW CREATE TABLE test\G

The result is as follows:

*************************** 1. row ***************************
        Table: test
  Create Table: CREATE TABLE `test` (
    `c1` int(11) NOT NULL,
    `c2` varchar(3) DEFAULT NULL,
    PRIMARY KEY (`c1`)
  ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
1 row in set

You can also use SHOW TABLES to view all tables in a specified database.

Example: View all tables in the db1 database.

obclient(root@mysqltenant)[db1]> SHOW TABLES FROM db1;

The result is as follows:

+---------------+
| Tables_in_db1 |
+---------------+
| test          |
+---------------+
1 row in set

For more information about the syntax of the SHOW statement, see SHOW.

Modify a table

Use the ALTER TABLE statement to modify the structure of an existing table, including modifying the table and table attributes, adding columns, modifying columns and their attributes, and dropping columns.

Example:

Rename the c2 field to c3 in the test table and modify its type.

View the column definition of the test table.

obclient(root@mysqltenant)[db1]> DESCRIBE test;

The result is as follows:

+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c1    | int(11)    | NO   | PRI | NULL    |       |
| c2    | varchar(3) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set

Rename the c2 field to c3 in the test table and change its type to char.

obclient(root@mysqltenant)[db1]> ALTER TABLE test CHANGE COLUMN c2 c3 CHAR(10);

View the column definition of the test table again to confirm the modification.

obclient(root@mysqltenant)[db1]> DESCRIBE test;

The result is as follows:

+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c1    | int(11)  | NO   | PRI | NULL    |       |
| c3    | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set

Add and drop columns in the test table.

View the current column definition of the test table.

obclient(root@mysqltenant)[db1]> DESCRIBE test;

The result is as follows:

+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c1    | int(11)  | NO   | PRI | NULL    |       |
| c3    | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set

Add column c4 to the test table.

obclient(root@mysqltenant)[db1]> ALTER TABLE test ADD c4 int;

View the column definition of the test table again to confirm that c4 was added.

obclient(root@mysqltenant)[db1]> DESCRIBE test;

The result is as follows:

+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c1    | int(11)  | NO   | PRI | NULL    |       |
| c3    | char(10) | YES  |     | NULL    |       |
| c4    | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set

Drop column c3 from the test table.

obclient(root@mysqltenant)[db1]> ALTER TABLE test DROP c3;

View the column definition of the test table again to confirm that c3 was dropped.

obclient(root@mysqltenant)[db1]> DESCRIBE test;

The result is as follows:

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | int(11) | NO   | PRI | NULL    |       |
| c4    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set

For more information about the syntax of the ALTER TABLE statement, see ALTER TABLE.

Drop a table

Use the DROP TABLE statement to drop a table.

Example: Drop the test table.

obclient(root@mysqltenant)[db1]> DROP TABLE test;

For more information about the syntax of the DROP TABLE statement, see DROP TABLE.

Index operations

An index is a structure created on a table to sort the values in one or more columns of the table. It improves query performance and reduces the performance overhead of the database system. This topic describes the syntax and examples for creating, viewing, and dropping indexes in a database.

Create an index

Use the CREATE INDEX statement to create an index on a table.

Example: Create an index on the test table.

View the column definition of the test table.

obclient(root@mysqltenant)[db1]> DESCRIBE test;

The result is as follows:

+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c1    | int(11)  | NO   | PRI | NULL    |       |
| c2    | char(3)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set

Create a composite index named test_index on columns c1 and c2 of the test table.

obclient(root@mysqltenant)[db1]> CREATE INDEX test_index ON test (c1, c2);

For more information about the syntax of the CREATE INDEX statement, see CREATE INDEX.

View indexes

Use the SHOW INDEX statement to view the indexes of a table.

Example: View the index information of the test table.

obclient(root@mysqltenant)[db1]> SHOW INDEX FROM test\G

The result is as follows:

*************************** 1. row ***************************
        Table: test
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: c1
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment: available
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: test
   Non_unique: 1
     Key_name: test_index
 Seq_in_index: 1
  Column_name: c1
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment: available
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 3. row ***************************
        Table: test
   Non_unique: 1
     Key_name: test_index
 Seq_in_index: 2
  Column_name: c2
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: available
Index_comment:
      Visible: YES
   Expression: NULL
3 rows in set

For more information about the syntax of the SHOW statement, see SHOW.

Drop an index

Use the DROP INDEX statement to drop an index from a table.

Example: Drop the index from the test table.

obclient(root@mysqltenant)[db1]> DROP INDEX test_index ON test;

For more information about the syntax of the DROP INDEX statement, see DROP INDEX.

Insert data

Use the INSERT statement to insert data into an existing table.

Example:

Create table t1 and insert a row.

Create table t1.

obclient(root@mysqltenant)[db1]> CREATE TABLE t1(c1 INT PRIMARY KEY, c2 int) PARTITION BY KEY(c1) PARTITIONS 4;

View the data in the table.
```
obclient(root@mysqltenant)[db1]> SELECT * FROM t1;
```
The query result is empty; the table has no data.

Insert a row into table t1.

obclient(root@mysqltenant)[db1]> INSERT t1 VALUES(1,1);

View the data in the table again to confirm the insertion.

obclient(root@mysqltenant)[db1]> SELECT * FROM t1;

The result is as follows:

+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
+----+------+
1 row in set

Insert multiple rows into table t1.

View the current data in the table.

obclient(root@mysqltenant)[db1]> SELECT * FROM t1;

The result is as follows:

+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
+----+------+
1 row in set

Insert multiple rows into table t1 at once.

obclient(root@mysqltenant)[db1]> INSERT t1 VALUES(2,2),(3,default),(2+2,3*4);

View the data in the table again to confirm the insertion.

obclient(root@mysqltenant)[db1]> SELECT * FROM t1;

The result is as follows:

+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 | NULL |
|  4 |   12 |
+----+------+
4 rows in set

For more information about the syntax of the INSERT statement, see INSERT.

Delete data

Use the DELETE statement to delete data. Single-table and multi-table deletion are supported.

To facilitate the examples, create sample tables t2 and t3 and insert data into them first.

Create sample table t2 and insert data.

Create a non-partitioned table t2.

obclient(root@mysqltenant)[db1]> CREATE TABLE t2(c1 INT PRIMARY KEY, c2 INT);

Insert multiple rows into table t2.

obclient(root@mysqltenant)[db1]> INSERT t2 VALUES(1,1),(2,2),(3,3),(5,5);

View the data in the table to confirm the insertion.

obclient(root@mysqltenant)[db1]> SELECT * FROM t2;

The result is as follows:

+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  5 |    5 |
+----+------+
4 rows in set

Create sample table t3 and insert data.

Create a KEY-partitioned table t3. The partition names are automatically generated by the system based on the partitioning rule, namely p0, p1, p2, and p3.
```
obclient(root@mysqltenant)[db1]> CREATE TABLE t3(c1 INT PRIMARY KEY, c2 INT) PARTITION BY KEY(c1) PARTITIONS 4;
```

Insert multiple rows into table t3.

obclient(root@mysqltenant)[db1]> INSERT INTO t3 VALUES(5,5),(1,1),(2,2),(3,3);

View the data in the table to confirm the insertion.

obclient(root@mysqltenant)[db1]> SELECT * FROM t3;

The result is as follows:

+----+------+
| c1 | c2   |
+----+------+
|  5 |    5 |
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
4 rows in set

Example:

Delete specified rows from a table.

View the data in table t2 before deletion.

obclient(root@mysqltenant)[db1]> SELECT * FROM t2;

The result is as follows:

+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  5 |    5 |
+----+------+
4 rows in set

Delete the row where c1=2 from table t2. Note that c1 is the PRIMARY KEY column of table t2.
```
obclient(root@mysqltenant)[db1]> DELETE FROM t2 WHERE c1 = 2;
```

View the data in table t2 after deletion to confirm.

obclient(root@mysqltenant)[db1]> SELECT * FROM t2;

The result is as follows:

+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  3 |    3 |
|  5 |    5 |
+----+------+
3 rows in set

Delete rows that meet a condition from a table.

View the data in table t2 before deletion.

obclient(root@mysqltenant)[db1]> SELECT * FROM t2;

The result is as follows:

+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  3 |    3 |
|  5 |    5 |
+----+------+
3 rows in set

Delete the data in the first row after sorting by the c2 column from table t2.
```
obclient(root@mysqltenant)[db1]> DELETE FROM t2 ORDER BY c2 LIMIT 1;
```

View the data in table t2 after deletion to confirm.

obclient(root@mysqltenant)[db1]> SELECT * FROM t2;

The result is as follows:

+----+------+
| c1 | c2   |
+----+------+
|  3 |    3 |
|  5 |    5 |
+----+------+
2 rows in set

Delete data from a specified partition of a table.

View the data in the p2 partition of table t3 before deletion.

obclient(root@mysqltenant)[db1]> SELECT * FROM t3 PARTITION(p2);

The result is as follows:

+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set

Delete the data in the p2 partition of table t3.

obclient(root@mysqltenant)[db1]> DELETE FROM t3 PARTITION(p2);

View the data in table t3 after deletion to confirm.

obclient(root@mysqltenant)[db1]> SELECT * FROM t3;

The result is as follows:

+----+------+
| c1 | c2   |
+----+------+
|  5 |    5 |
+----+------+
1 row in set

Delete data from multiple tables at once.

View the data in tables t2 and t3 before deletion.

View the data in table t2.

obclient(root@mysqltenant)[db1]> SELECT * FROM t2;

The result is as follows:

+----+------+
| c1 | c2   |
+----+------+
|  3 |    3 |
|  5 |    5 |
+----+------+
2 rows in set

View the data in table t3.

obclient(root@mysqltenant)[db1]> SELECT * FROM t3;

The result is as follows:

+----+------+
| c1 | c2   |
+----+------+
|  5 |    5 |
+----+------+
1 row in set

Delete the data where t2.c1 = t3.c1 from tables t2 and t3.

obclient(root@mysqltenant)[db1]> DELETE t2, t3 FROM t2, t3 WHERE t2.c1 = t3.c1;

This statement is equivalent to:

obclient(root@mysqltenant)[db1]> DELETE FROM t2, t3 USING t2, t3 WHERE t2.c1 = t3.c1;

View the data in tables t2 and t3 after deletion to confirm.

View the data in table t2.

obclient(root@mysqltenant)[db1]> SELECT * FROM t2;

The result is as follows:

+----+------+
| c1 | c2   |
+----+------+
|  3 |    3 |
+----+------+
1 row in set

View the data in table t3.

obclient(root@mysqltenant)[db1]> SELECT * FROM t3;

The result is empty; the data in table t3 has been deleted.

For more information about the syntax of the DELETE statement, see DELETE.

Update data

Use the UPDATE statement to modify the field values in a table.

To facilitate the examples, create sample tables t4 and t5 and insert data into them first.

Create sample table t4 and insert data.

Create a non-partitioned table t4.

obclient(root@mysqltenant)[db1]> CREATE TABLE t4(c1 INT PRIMARY KEY, c2 INT);

Insert multiple rows into table t4.

obclient(root@mysqltenant)[db1]> INSERT t4 VALUES(10,10),(20,20),(30,30),(40,40);

View the data in the table to confirm the insertion.

obclient(root@mysqltenant)[db1]> SELECT * FROM t4;

The result is as follows:

+----+------+
| c1 | c2   |
+----+------+
| 10 |   10 |
| 20 |   20 |
| 30 |   30 |
| 40 |   40 |
+----+------+
4 rows in set

Create sample table t5 and insert data.

Create a KEY-partitioned table t5. The partition names are automatically generated by the system based on the partitioning rule, namely p0, p1, p2, and p3.
```
obclient(root@mysqltenant)[db1]> CREATE TABLE t5(c1 INT PRIMARY KEY, c2 INT) PARTITION BY KEY(c1) PARTITIONS 4;
```

Insert multiple rows into table t5.

obclient(root@mysqltenant)[db1]> INSERT t5 VALUES(50,50),(10,10),(20,20),(30,30);

View the data in the table to confirm the insertion.

obclient(root@mysqltenant)[db1]> SELECT * FROM t5;

The result is as follows:

+----+------+
| c1 | c2   |
+----+------+
| 20 |   20 |
| 10 |   10 |
| 50 |   50 |
| 30 |   30 |
+----+------+
4 rows in set

Example:

Update the value of a specified column in specified rows.

View the data in table t4 before update.

obclient(root@mysqltenant)[db1]> SELECT * FROM t4;

The result is as follows:

+----+------+
| c1 | c2   |
+----+------+
| 10 |   10 |
| 20 |   20 |
| 30 |   30 |
| 40 |   40 |
+----+------+
4 rows in set

Update the value of the c2 column to 100 for the row where t4.c1=10.

obclient(root@mysqltenant)[db1]> UPDATE t4 SET t4.c2 = 100 WHERE t4.c1 = 10;

View the data in table t4 after update to confirm.

obclient(root@mysqltenant)[db1]> SELECT * FROM t4;

The result is as follows:

+----+------+
| c1 | c2   |
+----+------+
| 10 |  100 |
| 20 |   20 |
| 30 |   30 |
| 40 |   40 |
+----+------+
4 rows in set

Update the value of the c2 column to 100 for the first two rows sorted by the c2 column in table t4.

View the data in table t4 before update.

obclient(root@mysqltenant)[db1]> SELECT * FROM t4;

The result is as follows:

+----+------+
| c1 | c2   |
+----+------+
| 10 |  100 |
| 20 |   20 |
| 30 |   30 |
| 40 |   40 |
+----+------+
4 rows in set

Update the value of the c2 column to 100 for the first two rows sorted by the c2 column in table t4.
```
obclient(root@mysqltenant)[db1]> UPDATE t4 set t4.c2 = 100 ORDER BY c2 LIMIT 2;
```

View the data in table t4 after update to confirm.

obclient(root@mysqltenant)[db1]> SELECT * FROM t4;

The result is as follows:

+----+------+
| c1 | c2   |
+----+------+
| 10 |  100 |
| 20 |  100 |
| 30 |  100 |
| 40 |   40 |
+----+------+
4 rows in set

Update the value in a specified partition.

View the data in the p1 partition of table t5 before update.

obclient(root@mysqltenant)[db1]> SELECT * FROM t5 PARTITION (p1);

The result is as follows:

+----+------+
| c1 | c2   |
+----+------+
| 10 |   10 |
| 50 |   50 |
+----+------+
2 rows in set

Update the value of the c2 column to 100 for rows where t5.c1 > 20 in the p1 partition of table t5.

obclient(root@mysqltenant)[db1]> UPDATE t5 PARTITION(p1) SET t5.c2 = 100 WHERE t5.c1 > 20;

View the data in the p1 partition of table t5 after update to confirm.

obclient(root@mysqltenant)[db1]> SELECT * FROM t5 PARTITION (p1);

The result is as follows:

+----+------+
| c1 | c2   |
+----+------+
| 10 |   10 |
| 50 |  100 |
+----+------+
2 rows in set

For rows in tables t4 and t5 that meet the condition t4.c2 = t5.c2, update the value of the c2 column to 100 in table t4 and to 200 in table t5.

View the data in tables t4 and t5 before update.

View the data in table t4.

obclient(root@mysqltenant)[db1]> SELECT * FROM t4;

The result is as follows:

+----+------+
| c1 | c2   |
+----+------+
| 10 |  100 |
| 20 |  100 |
| 30 |  100 |
| 40 |   40 |
+----+------+
4 rows in set

View the data in table t5.

obclient(root@mysqltenant)[db1]> SELECT * FROM t5;

The result is as follows:

+----+------+
| c1 | c2   |
+----+------+
| 20 |   20 |
| 10 |   10 |
| 50 |  100 |
| 30 |   30 |
+----+------+
4 rows in set

For rows in tables t4 and t5 that meet the condition t4.c2 = t5.c2, update the value of the c2 column to 100 in table t4 and to 200 in table t5.
```
obclient(root@mysqltenant)[db1]> UPDATE t4,t5 SET t4.c2 = 100, t5.c2 = 200 WHERE t4.c2 = t5.c2;
```

View the data in tables t4 and t5 after update to confirm.

View the data in table t4.

obclient(root@mysqltenant)[db1]> SELECT * FROM t4;

The result is as follows:

+----+------+
| c1 | c2   |
+----+------+
| 10 |  100 |
| 20 |  100 |
| 30 |  100 |
| 40 |   40 |
+----+------+
4 rows in set

View the data in table t5.

obclient(root@mysqltenant)[db1]> SELECT * FROM t5;

The result is as follows:

+----+------+
| c1 | c2   |
+----+------+
| 20 |   20 |
| 10 |   10 |
| 50 |  200 |
| 30 |   30 |
+----+------+
4 rows in set

For more information about the syntax of the UPDATE statement, see UPDATE.

Query data

Use the SELECT statement to query data from a table.

Example:

Create table t6 using CREATE TABLE and read the name data from table t6.

Create table t6.

obclient(root@mysqltenant)[db1]> CREATE TABLE t6 (id INT, name VARCHAR(50), num INT);

Insert multiple rows into table t6.

obclient(root@mysqltenant)[db1]> INSERT INTO t6 VALUES(1,'a',100),(2,'b',200),(3,'a',50);

View the data in table t6 to confirm the insertion.

obclient(root@mysqltenant)[db1]> SELECT * FROM t6;

The result is as follows:

+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    1 | a    |  100 |
|    2 | b    |  200 |
|    3 | a    |   50 |
+------+------+------+
3 rows in set

Read the name data from table t6.

obclient(root@mysqltenant)[db1]> SELECT name FROM t6;

The result is as follows:

+------+
| NAME |
+------+
| a    |
| b    |
| a    |
+------+
3 rows in set

Deduplicate the name in the query result.

obclient(root@mysqltenant)[db1]> SELECT DISTINCT name FROM t6;

The result is as follows:

+------+
| NAME |
+------+
| a    |
| b    |
+------+
2 rows in set

Output the corresponding id, name, and num from table t6 based on the filter condition name = 'a'.

obclient(root@mysqltenant)[db1]> SELECT id, name, num FROM t6 WHERE name = 'a';

The result is as follows:

+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    1 | a    |  100 |
|    3 | a    |   50 |
+------+------+------+
2 rows in set

For more information about the syntax of the SELECT statement, see SELECT.

Commit a transaction

Use the COMMIT statement to commit a transaction.

Before you commit a transaction (COMMIT):

Your modifications are visible only to the current session and invisible to other database sessions.
Your modifications are not persisted. You can use the ROLLBACK statement to undo them.

After you commit a transaction (COMMIT):

Your modifications are visible to all database sessions.
Your modifications are persisted successfully. You cannot use the ROLLBACK statement to undo them.

Example: Create table t_insert using CREATE TABLE and use the COMMIT statement to commit a transaction.

Create table t_insert.

obclient(root@mysqltenant)[db1]> CREATE TABLE t_insert(
   id number NOT NULL PRIMARY KEY,
   name varchar(10) NOT NULL,
   value number,
   gmt_create DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Start a transaction.

obclient(root@mysqltenant)[db1]> BEGIN;

Insert multiple rows into table t_insert.

obclient(root@mysqltenant)[db1]> INSERT INTO t_insert(id, name, value, gmt_create) VALUES(1,'CN',10001, current_timestamp),(2,'US',10002, current_timestamp),(3,'EN',10003, current_timestamp);

View the data in table t_insert after modification. The data has been inserted successfully.

obclient(root@mysqltenant)[db1]> SELECT * FROM t_insert;

The result is as follows:

+------+------+-------+---------------------+
| id   | name | value | gmt_create          |
+------+------+-------+---------------------+
|    1 | CN   | 10001 | 2025-11-07 17:46:35 |
|    2 | US   | 10002 | 2025-11-07 17:46:35 |
|    3 | EN   | 10003 | 2025-11-07 17:46:35 |
+------+------+-------+---------------------+
3 rows in set

Insert another row into table t_insert.

obclient(root@mysqltenant)[db1]> INSERT INTO t_insert(id,name) VALUES(4,'JP');

Commit the transaction.

obclient(root@mysqltenant)[db1]> COMMIT;

Exit the current session.
```
obclient(root@mysqltenant)[db1]> exit;
```

obclient -h127.0.0.1 -ur**t@mysqltenant -P2881 -p****** -Ddb1

View the data in table t_insert again. The modifications to the data in table t_insert have been persisted successfully.

obclient(root@mysqltenant)[db1]> SELECT * FROM t_insert;

The result is as follows:

+------+------+-------+---------------------+
| id   | name | value | gmt_create          |
+------+------+-------+---------------------+
|    1 | CN   | 10001 | 2025-11-07 17:46:35 |
|    2 | US   | 10002 | 2025-11-07 17:46:35 |
|    3 | EN   | 10003 | 2025-11-07 17:46:35 |
|    4 | JP   |  NULL | 2025-11-07 17:46:53 |
+------+------+-------+---------------------+
4 rows in set

For more information about transaction control statements, see Overview of transaction management.

Roll back a transaction

Use the ROLLBACK statement to roll back a transaction.

A transaction rollback means undoing all modifications made in the transaction. You can roll back the entire uncommitted transaction or roll back to any savepoint in the transaction. To roll back to a savepoint, you must use both the ROLLBACK and TO SAVEPOINT statements. Note:

If you roll back the entire transaction:
- The transaction ends.
- All modifications are discarded.
- All savepoints are cleared.
- All locks held by the transaction are released.
If you roll back to a savepoint:
- The transaction does not end.
- Modifications made before the savepoint are retained; modifications made after the savepoint are discarded.
- Savepoints after the specified savepoint are cleared (excluding the savepoint itself).
- All locks held by the transaction after the savepoint are released.

Example: Roll back all modifications of a transaction.

View the data in table t_insert before modification.

obclient(root@mysqltenant)[db1]> SELECT * FROM t_insert;

The result is as follows:

+------+------+-------+---------------------+
| id   | name | value | gmt_create          |
+------+------+-------+---------------------+
|    1 | CN   | 10001 | 2025-11-07 17:46:35 |
|    2 | US   | 10002 | 2025-11-07 17:46:35 |
|    3 | EN   | 10003 | 2025-11-07 17:46:35 |
+------+------+-------+---------------------+
3 rows in set

Start a transaction.

obclient(root@mysqltenant)[db1]> BEGIN;

Insert multiple rows into table t_insert.

obclient(root@mysqltenant)[db1]> INSERT INTO t_insert(id, name, value) VALUES(4,'JP',10004),(5,'FR',10005),(6,'RU',10006);

View the data in table t_insert after modification. The data has been inserted successfully.

obclient(root@mysqltenant)[db1]> SELECT * FROM t_insert;

The result is as follows:

+------+------+-------+---------------------+
| id   | name | value | gmt_create          |
+------+------+-------+---------------------+
|    1 | CN   | 10001 | 2025-11-07 17:46:35 |
|    2 | US   | 10002 | 2025-11-07 17:46:35 |
|    3 | EN   | 10003 | 2025-11-07 17:46:35 |
|    4 | JP   | 10004 | 2025-11-07 17:48:01 |
|    5 | FR   | 10005 | 2025-11-07 17:48:01 |
|    6 | RU   | 10006 | 2025-11-07 17:48:01 |
+------+------+-------+---------------------+
6 rows in set

Roll back the transaction.

obclient(root@mysqltenant)[db1]> ROLLBACK;

View the data in table t_insert again after the rollback. The inserted data has been rolled back.

obclient(root@mysqltenant)[db1]> SELECT * FROM t_insert;

The result is as follows:

+------+------+-------+---------------------+
| id   | name | value | gmt_create          |
+------+------+-------+---------------------+
|    1 | CN   | 10001 | 2025-11-07 17:46:35 |
|    2 | US   | 10002 | 2025-11-07 17:46:35 |
|    3 | EN   | 10003 | 2025-11-07 17:46:35 |
+------+------+-------+---------------------+
3 rows in set

For more information about transaction control statements, see Overview of transaction management.