盐析白兔

这个屌丝很懒，什么也没留下！

热门标签

深入OceanBase分布式数据库：MySQL 模式下的 SQL 基本操作

作者：盐析白兔 | 2024-05-02 23:44:40

踩

码到三十五： 个人主页

OceanBase与MySQL模式下兼容性序

在当今的大数据时代，数据库技术的选择对于企业的信息化发展至关重要。OceanBase作为一种高性能、高可用的分布式关系数据库，在与MySQL模式的兼容性方面展现出了显著的优势，为企业数据迁移、整合与升级提供了极大的便利。

OceanBase与MySQL的兼容性不仅体现在数据类型、SQL语法等基本面，更深入到了存储引擎、优化器等多个层次。这种高度的兼容性使得企业在不改变原有业务逻辑的情况下，能够轻松地将数据和应用从MySQL迁移到OceanBase，从而享受到OceanBase带来的高性能和可扩展性。

在这里插入图片描述

本文将探讨OceanBase与MySQL模式下的兼容性和OceanBase的MySQL 模式下的 SQL 基本操作。

前言：OceanBase与 MySQL 兼容性对比

OceanBase与MySQL模式下的兼容性可以从以下几个方面进行简述：

数据类型兼容性

OceanBase的MySQL模式兼容MySQL 5.7的绝大部分数据类型，包括数值类型（如INT、BIGINT、FLOAT、DOUBLE等）、日期和时间类型（如DATETIME、TIMESTAMP等）、字符串类型（如CHAR、VARCHAR等）以及其他复杂数据类型（如ENUM、SET、JSON等）。此外，OceanBase还支持空间数据类型，这在某些特定应用场景中非常有用。

OceanBase 数据库支持的数据类型有：
数值类型
整数类型：BOOL/BOOLEAN/TINYINT、SMALLINT、MEDIUMINT、INT/INTEGER 和 BIGINT。
定点类型：DECIMAL 和 NUMERIC。
浮点类型：FLOAT 和 DOUBLE。
Bit-Value 类型：BIT。
日期时间类型：DATETIME、TIMESTAMP、DATE、TIME 和 YEAR。
字符类型：CHAR、VARCHAR、BINARY 和 VARBINARY。
大对象类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。
文本类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。
枚举类型：ENUM。
集合类型：SET。
JSON 数据类型
空间数据类型

SQL语法兼容性

OceanBase支持MySQL 5.7版本的大部分SQL语法，包括SELECT、INSERT、UPDATE、DELETE等基本操作，以及复杂的SQL查询，如子查询、联接操作、聚合函数等。此外，OceanBase还支持对JSON数据进行查询和操作，提供了丰富的JSON函数。

SELECT
支持大部分查询功能，包括支持单、多表查询；支持子查询；支持内联接、半联接以及外联接；支持分组、聚合；支持常见的概率、线性回归等数据挖掘函数等。
支持对多个 SELECT 查询的结果进行 UNION、UNION ALL、MINUS、EXCEPT 或 INTERSECT 等集合操作。

支持使用 EXPLAIN 语法查看执行计划。

INSERT
支持单行和多行插入数据，同时支持指定分区插入数据。
支持 INSERT INTO … SELECT … 语句。

UPDATE
支持单列和多列更新数据。
支持使用子查询更新数据。
支持集合更新数据。

DELETE
支持单表和多表删除。

TRUNCATE
支持完全清空指定表。

然而，需要注意的是，OceanBase在某些方面与MySQL存在不兼容的情况。例如，OceanBase不支持SELECT…FOR SHARE语法，也不支持部分JSON类型和空间数据类型。此外，在函数支持方面，OceanBase也有一些与MySQL不同的地方。

存储引擎和分区功能

OceanBase本质上是一个基线加增量的存储引擎，采用LSM树（Log-Structured Merge Tree）作为存储机制，这与传统的关系数据库存储引擎有所不同。尽管如此，OceanBase仍然提供了与MySQL相似的分区功能，支持分区表和二级分区，可以完全取代MySQL常用的分库分表方案。

优化器和执行计划

OceanBase的优化器在执行查询时会生成与MySQL相似的执行计划。然而，两者在执行计划的表示和细节上可能存在差异。此外，OceanBase还支持使用EXPLAIN语法查看执行计划，这有助于用户理解和优化查询性能。

备份与恢复

与MySQL相比，OceanBase在备份与恢复方面有所不同。例如，OceanBase不支持冷备份、数据库和表级的备份恢复以及备份数据的有效性验证。因此，在使用OceanBase时需要考虑这些差异并采取相应的措施来确保数据的完整性和可用性。

总的来说，OceanBase在MySQL模式下与MySQL具有较高的兼容性，但仍然存在一些差异和限制。在使用OceanBase时，建议仔细了解其与MySQL的兼容性和差异，并根据实际需求进行相应的调整和优化。
OceanBase 数据库的 MySQL 模式兼容 MySQL 5.7/8.0 的绝大部分功能和语法。

1. 创建数据库

1.1 语法

CREATE TABLE [IF NOT EXISTS] table_name
      (table_definition_list) [table_option_list] [partition_option] [AS] select;

CREATE TABLE [IF NOT EXISTS] table_name
      LIKE table_name;

table_definition_list:
    table_definition [, table_definition ...]

table_definition:
      column_definition
    | [CONSTRAINT [constraint_name]] PRIMARY KEY index_desc
    | [CONSTRAINT [constraint_name]] UNIQUE {INDEX | KEY} 
            [index_name] index_desc
    | [CONSTRAINT [constraint_name]] FOREIGN KEY 
            [index_name] index_desc 
            REFERENCES reference_definition 
            [match_action][opt_reference_option_list]
    | [SPATIAL] {INDEX | KEY} [index_name] [index_type] (key_part,...)
      [index_optionn_list] 
    | [CONSTRAINT [constraint_name]] CHECK(expression) constranit_state

column_definition_list:
    column_definition [, column_definition ...]

column_definition:
     column_name data_type
         [DEFAULT const_value] [AUTO_INCREMENT]
         [NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] comment
   | column_name data_type
         [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED]
         [opt_generated_column_attribute] 

index_desc:
   (column_desc_list) [index_type] [index_option_list]

match_action:
   MATCH {SIMPLE | FULL | PARTIAL}

opt_reference_option_list:
   reference_option [,reference_option ...]

reference_option:
   ON {DELETE | UPDATE} {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}


key_part: 
    {index_col_name [(length)] | (expr)} [ASC | DESC]

index_type:
    USING BTREE

index_option_list:
    index_option [ index_option ...]

index_option:
      [GLOBAL | LOCAL]
    | block_size
    | compression
    | STORING(column_name_list)
    | comment

table_option_list:
    table_option [ table_option ...]

table_option:
      [DEFAULT] {CHARSET | CHARACTER SET} [=] charset_name
    | [DEFAULT] COLLATE [=] collation_name
    | table_tablegroup
    | block_size
    | compression
    | AUTO_INCREMENT [=] INT_VALUE
    | comment
    | ROW_FORMAT [=] REDUNDANT|COMPACT|DYNAMIC|COMPRESSED|DEFAULT
    | PCTFREE [=] num
    | parallel_clause
    | DUPLICATE_SCOPE [=] 'none|cluster'

parallel_clause:
    {NOPARALLEL | PARALLEL integer}

partition_option:
      PARTITION BY HASH(expression)
      [subpartition_option] PARTITIONS partition_count
    | PARTITION BY KEY([column_name_list])
      [subpartition_option] PARTITIONS partition_count
    | PARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
      [subpartition_option] (range_partition_list)
    | PARTITION BY LIST {(expression) | COLUMNS (column_name_list)}
      [subpartition_option] PARTITIONS partition_count

subpartition_option:
      SUBPARTITION BY HASH(expression)
      SUBPARTITIONS subpartition_count
    | SUBPARTITION BY KEY(column_name_list)
      SUBPARTITIONS subpartition_count
    | SUBPARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
      (range_subpartition_list)
    | SUBPARTITION BY LIST(expression)

range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION partition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE}

range_subpartition_list:
    range_subpartition [, range_subpartition ...]

range_subpartition:
    SUBPARTITION subpartition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE}

expression_list:
    expression [, expression ...]

column_name_list:
    column_name [, column_name ...]

partition_name_list:
    partition_name [, partition_name ...]

partition_count | subpartition_count:
    INT_VALUE
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125

1.2 参数说明

参数	描述
PRIMARY KEY	为创建的表指定主键。如果不指定，则使用隐藏主键。特别地，OceanBase 数据库不支持修改表的主键或通过 ALTER TABLE 语句为表添加主键，因此推荐您在创建表时指定好表的主键。
FOREIGN KEY	为创建的表指定外键。如果不指定外键名，则会使用表名 + OBFK + 创建时间命名。（例如，在 2021 年 8 月 1 日 00:00:00 为 t1 表创建的外键名称为 t1_OBFK_1627747200000000）。
KEY , INDEX	为创建的表指定键或索引。如果不指定索引名，则会使用索引引用的第一列作为索引名，如果命名存在重复，则会使用下划线（_）+ 序号的方式命名。（例如，使用 c1 列创建的索引如果命名重复，则会将索引命名为 c1_2。）您可以通过 SHOW INDEX
key_part	定义所创建的索引。
index_col_name	指定索引的列名，每个列名后都支持 ASC（升序），不支持 DESC（降序）。默认为升序。建立索引的排序方式为：首先以 index_col_name 中第一个列的值排序；该列值相同的记录，按下一列名的值排序；以此类推。
expr	表示合法的函数索引表达式，且允许是布尔表达式，例如 c1=c1。OceanBase 数据库当前版本禁止创建生成列上的函数索引。
ROW_FORMAT	指定表是否开启 Encoding 存储格式。redundant：不开启 Encoding 存储格式。compact：不开启 Encoding 存储格式。dynamic：Encoding 存储格式。compressed：Encoding 存储格式。default：等价 dynamic 模式。
[GENERATED ALWAYS] AS (expr) [VIRTUAL . STORED]	创建生成列，expr 为用于计算列值的表达式。VIRTUAL：列值不会被存储，而是在读取行时，在任何 BEFORE 触发器之后立即计算。虚拟列不占用存储空间。STORED：在插入或更新行时评估和存储列值。存储列确实需要存储空间并且可以被索引。
BLOCK_SIZE	指定表的微块大小。
COMPRESSION	指定表的压缩算法，取值如下：none：不使用压缩算法。lz4_1.0: 使用 lz4 压缩算法。zstd_1.0: 使用 zstd 压缩算法。snappy_1.0: 使用 snappy 压缩算法。
CHARSET , CHARACTER SET	指定表中列的默认字符集，可使用的字符集请参见字符集。
COLLATE	指定表中列的默认字符序，可使用的字符序请参见字符序。
table_tablegroup	指定表所属的 tablegroup。
AUTO_INCREMENT	指定表中自增列的初始值。OceanBase 数据库支持使用自增列作为分区键。
comment	注释。
PCTFREE	指定宏块保留空间百分比。
parallel_clause	指定表级别的并行度：NOPARALLEL：并行度为 1，默认配置PARALLEL integer：指定并行度，integer 取值大于等于 1。DUPLICATE_SCOPE 指定复制表的属性，取值如下：

使用 CREATE DATABASE 语句创建数据库。

示例：创建数据库 db1，指定字符集为 utf8mb4，并创建读写属性。

obclient> CREATE DATABASE db1 DEFAULT CHARACTER SET utf8mb4 READ WRITE;
Query OK, 1 row affected
1
2

创建完成后，可以通过 SHOW DATABASES 命令查看当前数据库服务器中所有的数据库。

obclient> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| oceanbase          |
| db1                |
| test               |
+--------------------+
3 rows in set
1
2
3
4
5
6
7
8
9

2. 表操作

在 OceanBase 数据库中，表是最基础的数据存储单元，包含了所有用户可以访问的数据，每个表包含多行记录，每个记录由多个列组成。本节主要提供数据库中表的创建、查看、修改和删除的语法和示例。

2.1 创建表

使用 CREATE TABLE 语句在数据库中创建新表。

示例：在数据库 db1 中创建表 test。

obclient> USE db1;
Database changed

obclient> CREATE TABLE test (c1 INT PRIMARY KEY, c2 VARCHAR(3));
Query OK, 0 rows affected
1
2
3
4
5

更多 CREATE TABLE 语句相关的语法说明，请参见 CREATE TABLE 章节。

2.2 查看表

使用 SHOW CREATE TABLE 语句查看建表语句。

示例：

查看表 test 的建表语句。

obclient> SHOW CREATE TABLE test\G
*************************** 1. row ***************************
      Table: test
Create Table: CREATE TABLE `test` (
  `c1` int(11) NOT NULL,
  `c2` varchar(3) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
1 row in set
1
2
3
4
5
6
7
8
9

使用 SHOW TABLES 语句查看 db1 数据库中的所有表。

obclient> SHOW TABLES FROM db1;
+---------------+
| Tables_in_db1 |
+---------------+
| test          |
+---------------+
1 row in set
1
2
3
4
5
6
7

2.3 修改表

使用 ALTER TABLE 语句来修改已存在的表的结构，包括修改表及表属性、新增列、修改列及属性、删除列等。

示例：

将表 test 的字段 c2 改名为 c3，并同时修改其字段类型。

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

obclient> ALTER TABLE test CHANGE COLUMN c2 c3 CHAR(10);
Query OK, 0 rows affected

obclient> DESCRIBE test;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c1    | int(11)  | NO   | PRI | NULL    |       |
| c3    | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

在表 test 中增加、删除列。

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

obclient> ALTER TABLE test ADD c4 int;
Query OK, 0 rows affected

obclient> DESCRIBE test;
+-------+----------+------+-----+---------+-------+
| 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

obclient> ALTER TABLE test DROP c3;
Query OK, 0 rows affected

obclient> DESCRIBE test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | int(11) | NO   | PRI | NULL    |       |
| c4    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

2.4 删除表

使用 DROP TABLE 语句删除表。

示例：删除表 test。

obclient> DROP TABLE test;
Query OK, 0 rows affected
1
2

3. 索引操作

索引是创建在表上并对数据库表中一列或多列的值进行排序的一种结构。其作用主要在于提高查询的速度，降低数据库系统的性能开销。本节主要介绍数据库中索引的创建、查看、删除的语法和示例。

3.1 创建索引

使用 CREATE INDEX 语句创建表的索引。

示例：在表 test 中创建索引。

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

obclient> CREATE INDEX test_index ON test (c1, c2);
Query OK, 0 rows affected
1
2
3
4
5
6
7
8
9
10
11

3.2 查看索引

使用 SHOW INDEX 语句查看表的索引。

示例：查看表 test 中的索引信息。

obclient> SHOW INDEX FROM test\G
*************************** 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
*************************** 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
*************************** 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
3 rows in set
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

3.3 删除索引

使用 DROP INDEX 语句删除表的索引。

示例：删除表 test 中的索引。

obclient> DROP INDEX test_index ON test;
Query OK, 0 rows affected
1
2

4. 插入数据

使用 INSERT 语句在已经存在的表中插入数据。

示例：

创建表 t1 并插入一行数据。

obclient> CREATE TABLE t1(c1 INT PRIMARY KEY, c2 int) PARTITION BY KEY(c1) PARTITIONS 4;
Query OK, 0 rows affected

obclient> SELECT * FROM t1;
Empty set

obclient> INSERT t1 VALUES(1,1);
Query OK, 1 row affected

obclient> SELECT * FROM t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
+----+------+
1 row in set
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

向表 t1 中插入多行数据。

obclient> INSERT t1 VALUES(2,2),(3,default),(2+2,3*4);
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

obclient> SELECT * FROM t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 | NULL |
|  4 |   12 |
+----+------+
4 rows in set
1
2
3
4
5
6
7
8
9
10
11
12
13
14

更多 INSERT 语句相关的语法，请参见 INSERT 章节。

5. 删除数据

使用 DELETE 语句删除数据，支持单表删除和多表删除数据。

示例：

通过 CREATE TABLE 创建表 t2 和 t3。删除 c1=2 的行，其中 c1 列为表 t2 中的 PRIMARY KEY。

/表 t3 为 KEY 分区表，且分区名由系统根据分区命令规则自动生成，即分区名为 p0、p1、p2、p3/

obclient> CREATE TABLE t2(c1 INT PRIMARY KEY, c2 INT);
Query OK, 0 rows affected

obclient> INSERT t2 VALUES(1,1),(2,2),(3,3),(5,5);
Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

obclient> SELECT * FROM t2;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  5 |    5 |
+----+------+
4 rows in set

obclient> CREATE TABLE t3(c1 INT PRIMARY KEY, c2 INT) PARTITION BY KEY(c1) PARTITIONS 4;
Query OK, 0 rows affected

obclient> INSERT INTO t3 VALUES(5,5),(1,1),(2,2),(3,3);
Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

obclient> SELECT * FROM t3;
+----+------+
| c1 | c2   |
+----+------+
|  5 |    5 |
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
4 rows in set

obclient> DELETE FROM t2 WHERE c1 = 2;
Query OK, 1 row affected

obclient> SELECT * FROM t2;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  3 |    3 |
|  5 |    5 |
+----+------+
3 rows in set
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

删除表 t2 中按照 c2 列排序之后的第一行数据。

obclient> DELETE FROM t2 ORDER BY c2 LIMIT 1;
Query OK, 1 row affected

obclient> SELECT * FROM t2;
+----+------+
| c1 | c2   |
+----+------+
|  3 |    3 |
|  5 |    5 |
+----+------+
2 rows in set
1
2
3
4
5
6
7
8
9
10
11

删除表 t3 的 p2 分区的数据。

obclient> SELECT * FROM t3 PARTITION(p2); 
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set

obclient> DELETE FROM t3 PARTITION(p2); 
Query OK, 3 rows affected

obclient> SELECT * FROM t3;
+----+------+
| c1 | c2   | 
+----+------+
|  5 |    5 |
+----+------+
1 row in set
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

删除 t2、t3 表中 t2.c1 = t3.c1 的数据。

obclient> SELECT * FROM t2;
+----+------+
| c1 | c2   |
+----+------+
|  3 |    3 |
|  5 |    5 |
+----+------+
2 rows in set

obclient> SELECT * FROM t3;
+----+------+
| c1 | c2   | 
+----+------+
|  5 |    5 |
+----+------+

obclient> DELETE t2, t3 FROM t2, t3 WHERE t2.c1 = t3.c1;
Query OK, 3 rows affected


/*等价于
obclient> DELETE FROM t2, t3 USING t2, t3 WHERE t2.c1 = t3.c1;
*/

obclient> SELECT * FROM t2;
+----+------+
| c1 | c2   |
+----+------+
|  3 |    3 |
+----+------+
1 row in set

obclient> SELECT * FROM t3;
Empty set

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

6. 更新数据

使用 UPDATE 语句修改表中的字段值。

示例：

通过 CREATE TABLE 创建表 t4 和 t5，将表 t4 中 t4.c1=10 对应的那一行数据的 c2 列值修改为 100。

obclient> CREATE TABLE t4(c1 INT PRIMARY KEY, c2 INT);
Query OK, 0 rows affected

obclient> INSERT t4 VALUES(10,10),(20,20),(30,30),(40,40);
Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

obclient> SELECT * FROM t4;
+----+------+
| c1 | c2   |
+----+------+
| 10 |   10 |
| 20 |   20 |
| 30 |   30 |
| 40 |   40 |
+----+------+
4 rows in set

obclient> CREATE TABLE t5(c1 INT PRIMARY KEY, c2 INT) PARTITION BY KEY(c1) PARTITIONS 4;
Query OK, 0 rows affected

obclient> INSERT t5 VALUES(50,50),(10,10),(20,20),(30,30);
Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

obclient> SELECT * FROM t5;
+----+------+
| c1 | c2   |
+----+------+
| 20 |   20 |
| 10 |   10 |
| 50 |   50 |
| 30 |   30 |
+----+------+
4 rows in set

obclient> UPDATE t4 SET t4.c2 = 100 WHERE t4.c1 = 10;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

obclient> SELECT * FROM t4;
+----+------+
| c1 | c2   |
+----+------+
| 10 |  100 |
| 20 |   20 |
| 30 |   30 |
| 40 |   40 |
+----+------+
4 rows in set
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

将表 t4 中按照 c2 列排序的前两行数据的 c2 列值修改为 100。

obclient> UPDATE t4 set t4.c2 = 100 ORDER BY c2 LIMIT 2;
Query OK, 2 rows affected
Rows matched: 2  Changed: 2  Warnings: 0

obclient> SELECT * FROM t4;
+----+------+
| c1 | c2   |
+----+------+
| 10 |  100 |
| 20 |  100 |
| 30 |  100 |
| 40 |   40 |
+----+------+
4 rows in set
1
2
3
4
5
6
7
8
9
10
11
12
13
14

将表 t5 中 p1 分区的数据中 t5.c1 > 20 的对应行数据的 c2 列值修改为 100。

obclient> SELECT * FROM t5 PARTITION (p1);
+----+------+
| c1 | c2   |
+----+------+
| 10 |   10 |
| 50 |   50 |
+----+------+
2 rows in set

obclient> UPDATE t5 PARTITION(p1) SET t5.c2 = 100 WHERE t5.c1 > 20;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

obclient> SELECT * FROM t5 PARTITION(p1);
+----+------+
| c1 | c2   |
+----+------+
| 10 |   10 |
| 50 |  100 |
+----+------+
2 rows in set
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

对于表 t4 和表 t5 中满足 t4.c2 = t5.c2 对应行的数据，将表 t4 中的 c2 列值修改为 100，表 t5 中的 c2 列值修改为 200。

obclient> UPDATE t4,t5 SET t4.c2 = 100, t5.c2 = 200 WHERE t4.c2 = t5.c2;
Query OK, 1 row affected 
Rows matched: 4  Changed: 1  Warnings: 0

obclient> SELECT * FROM t4;
+----+------+
| c1 | c2   |
+----+------+
| 10 |  100 |
| 20 |  100 |
| 30 |  100 |
| 40 |   40 |
+----+------+
4 rows in set

obclient> SELECT * FROM t5;
+----+------+
| c1 | c2   |
+----+------+
| 20 |   20 |
| 10 |   10 |
| 50 |  200 |
| 30 |   30 |
+----+------+
4 rows in set
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

7. 查询数据

使用 SELECT 语句查询表中的内容。

示例：

通过 CREATE TABLE 创建表 t6。从表 t6 中读取 name 的数据。

obclient> CREATE TABLE t6 (id INT, name VARCHAR(50), num INT);
Query OK, 0 rows affected

obclient> INSERT INTO t6 VALUES(1,'a',100),(2,'b',200),(3,'a',50);
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

obclient> SELECT * FROM t6;
+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    1 | a    |  100 |
|    2 | b    |  200 |
|    3 | a    |   50 |
+------+------+------+
3 rows in set

obclient> SELECT name FROM t6;
+------+
| NAME |
+------+
| a    |
| b    |
| a    |
+------+
3 rows in set
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

在查询结果中对 name 进行去重处理。

obclient> SELECT DISTINCT name FROM t6;
+------+
| NAME |
+------+
| a    |
| b    |
+------+
2 rows in set
1
2
3
4
5
6
7
8

从表 t6 中根据筛选条件 name = ‘a’ ，输出对应的 id 、name 和 num。

obclient> SELECT id, name, num FROM t6 WHERE name = 'a';
+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    1 | a    |  100 |
|    3 | a    |   50 |
+------+------+------+
2 rows in set
1
2
3
4
5
6
7
8

8. 提交事务

使用 COMMIT 语句提交事务。

在提交事务（COMMIT）之前：

您的修改只对当前会话可见，对其他数据库会话均不可见。
您的修改没有持久化，您可以通过 ROLLBACK 语句撤销修改。

在提交事务（COMMIT）之后：

您的修改对所有数据库会话可见。
您的修改持久化成功，不能通过 ROLLBACK 语句回滚修改。

示例：通过 CREATE TABLE 创建表 t_insert。使用 COMMIT 语句提交事务。

obclient> BEGIN;
Query OK, 0 rows affected

obclient> 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
 );
Query OK, 0 rows affected

obclient> 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);
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

obclient> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10001 | 2022-08-22 16:19:26 |
|  2 | US   | 10002 | 2022-08-22 16:19:26 |
|  3 | EN   | 10003 | 2022-08-22 16:19:26 |
+----+------+-------+---------------------+
3 rows in set

obclient> INSERT INTO t_insert(id,name) VALUES(4,'JP');
Query OK, 1 row affected

obclient> COMMIT;
Query OK, 0 rows affected

obclient> exit;
Bye

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

obclient> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10001 | 2022-08-22 16:19:26 |
|  2 | US   | 10002 | 2022-08-22 16:19:26 |
|  3 | EN   | 10003 | 2022-08-22 16:19:26 |
|  4 | JP   |  NULL | 2022-08-22 16:21:39 |
+----+------+-------+---------------------+
4 rows in set
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

9. 回滚事务

使用 ROLLBACK 语句回滚事务。

回滚一个事务指将事务的修改全部撤销。可以回滚当前整个未提交的事务，也可以回滚到事务中任意一个保存点。如果要回滚到某个保存点，必须结合使用 ROLLBACK 和 TO SAVEPOINT 语句。其中：

如果回滚整个事务，则：

事务会结束
所有的修改会被丢弃
清除所有保存点
释放事务持有的所有锁
如果回滚到某个保存点，则：
事务不会结束
保存点之前的修改被保留，保存点之后的修改被丢弃
清除保存点之后的保存点（不包括保存点自身）
释放保存点之后事务持有的所有锁

示例：回滚事务的全部修改。

obclient> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10001 | 2022-08-22 16:19:26 |
|  2 | US   | 10002 | 2022-08-22 16:19:26 |
|  3 | EN   | 10003 | 2022-08-22 16:19:26 |
+----+------+-------+---------------------+
3 rows in set

obclient> BEGIN;
Query OK, 0 rows affected

obclient> INSERT INTO t_insert(id, name, value) VALUES(4,'JP',10004),(5,'FR',10005),(6,'RU',10006);
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

obclient> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10001 | 2022-08-22 16:19:26 |
|  2 | US   | 10002 | 2022-08-22 16:19:26 |
|  3 | EN   | 10003 | 2022-08-22 16:19:26 |
|  4 | JP   | 10004 | 2022-08-22 16:26:23 |
|  5 | FR   | 10005 | 2022-08-22 16:26:23 |
|  6 | RU   | 10006 | 2022-08-22 16:26:23 |
+----+------+-------+---------------------+
6 rows in set

obclient> ROLLBACK;
Query OK, 0 rows affected

+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10001 | 2022-08-22 16:19:26 |
|  2 | US   | 10002 | 2022-08-22 16:19:26 |
|  3 | EN   | 10003 | 2022-08-22 16:19:26 |
+----+------+-------+---------------------+
3 rows in set
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

听说...关注下面公众号的人都变牛了，纯技术，纯干货 !

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/盐析白兔/article/detail/526706

深入OceanBase分布式数据库：MySQL 模式下的 SQL 基本操作

目录

前言：OceanBase与 MySQL 兼容性对比

数据类型兼容性

SQL语法兼容性

存储引擎和分区功能

优化器和执行计划

备份与恢复

1. 创建数据库

1.1 语法

1.2 参数说明

2. 表操作

2.1 创建表

2.2 查看表

2.3 修改表

2.4 删除表

3. 索引操作

3.1 创建索引

3.2 查看索引

3.3 删除索引

4. 插入数据

5. 删除数据

6. 更新数据

7. 查询数据

8. 提交事务

9. 回滚事务