RadonDB 支持 SQL 集

RadonDB 支持 SQL 集

背景

在 SQL 语法上， RadonDB 与 MySQL 完全兼容。

在满足大部分需求场景下， RadonDB 的 SQL 实现只是 MySQL 一个子集，从而更好的使用和规范。

Data Definition Statements

1. ALTER DATABASE

语法:

ALTER {DATABASE | SCHEMA} [db_name]
    alter_option ...

alter_option: {
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name
  | [DEFAULT] ENCRYPTION [=] {'Y' | 'N'}
  | READ ONLY [=] {DEFAULT | 0 | 1}
}

说明:

RadonDB 根据路由信息发送 sql 到相应的后端执行引擎进行更改。
跨分区非原子操作。

示例:

mysql> create database testdb DEFAULT CHARSET=utf8 collate utf8_unicode_ci;
Query OK, 2 rows affected (0.02 sec)

mysql> show create database testdb;
+----------+-----------------------------------------------------------------------------------------+
| Database | Create Database                                                                         |
+----------+-----------------------------------------------------------------------------------------+
| testdb   | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */ |
+----------+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter database testdb default character set = utf8mb4 collate = utf8mb4_bin;
Query OK, 2 rows affected (0.01 sec)

mysql> show create database testdb;
+----------+----------------------------------------------------------------------------------------+
| Database | Create Database                                                                        |
+----------+----------------------------------------------------------------------------------------+
| testdb   | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ |
+----------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2. ALTER TABLE

语法:

ALTER TABLE tbl_name alter_option

alter_option: {
    table_option
  | ADD COLUMN (col_name column_definition,...)
  | DROP COLUMN col_name
  | MODIFY COLUMN col_name column_definition
}

table_option: {
  ENGINE [=] engine_name
| CONVERT TO CHARACTER SET charset_name
}

说明:

RadonDB 根据路由信息发送 sql 到相应的后端执行引擎进行更改。
跨分区非原子操作。

2.1. 增加列

示例:

mysql> CREATE TABLE t1(a int primary key) PARTITION BY HASHa);
Query OK, 0 rows affected (0.16 sec)

mysql> ALTER TABLE t1 ADD COLUMN (b int, c varchar(100));
Query OK, 0 rows affected (0.10 sec)

mysql> SHOW CREATE TABLE t1\G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  `c` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

2.2. 更改表引擎

示例:

mysql> CREATE TABLE t1(id int, age int) PARTITION BY HASH(id);
Query OK, 0 rows affected (1.76 sec)

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (id) */
1 row in set (0.046 sec)

mysql> ALTER TABLE t1 ENGINE=TokuDB;
Query OK, 0 rows affected (0.15 sec)

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=TokuDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (id) */
1 row in set (0.095 sec)

2.3. 更改表字符集

说明:

RadonDB 的表字符集默认为 utf8。

示例:

mysql> create table t1(id int, b int) partition by hash(id);
Query OK, 0 rows affected (0.15 sec)

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (id) */
1 row in set (0.097 sec)

mysql> alter table t1 convert to character set utf8mb4;
Query OK, 0 rows affected (0.07 sec)

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY HASH (id) */
1 row in set (0.045 sec)

2.4. 删除列

说明:

无法删除分区键所在的列。

示例:

mysql>  ALTER TABLE t1 DROP COLUMN c;
Query OK, 0 rows affected (2.92 sec)

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (id) */
1 row in set (0.092 sec)

mysql>  ALTER TABLE t1 DROP COLUMN id;
ERROR 1105 (HY000): unsupported: cannot.drop.the.column.on.shard.key

2.5. 更改列定义

说明:

无法修改分区键所在的列。

示例:

mysql> ALTER TABLE t1 MODIFY COLUMN b bigint;
Query OK, 0 rows affected (4.09 sec)

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `b` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (id) */
1 row in set (0.049 sec)

mysql>  ALTER TABLE t1 MODIFY COLUMN id bigint;
ERROR 1105 (HY000): unsupported: cannot.modify.the.column.on.shard.key

3. CREATE DATABASE

语法:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_option] ...

create_option: [DEFAULT] {
    CHARACTER SET [=] charset_name
  | COLLATE [=] collation_name
  | ENCRYPTION [=] {'Y' | 'N'}
}

说明:

RadonDB 会把此语句直接发到所有后端执行并返回。
跨分区非原子操作。

示例:

mysql> CREATE DATABASE sbtest1;
Query OK, 4 rows affected (0.01 sec)

4. CREATE INDEX

语法:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    ON tbl_name (key_part,...)
    [index_option]
    [index_lock_and_algorithm_opt]

key_part:
    col_name [(length)]

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER NGRAM
  | COMMENT 'string'

index_type:
    USING {BTREE | HASH}

index_lock_and_algorithm_opt:
    algorithm_option
|   lock_option
|   algorithm_option lock_option
|   lock_option algorithm_option

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

说明:

RadonDB 根据路由信息，发到相应的后端执行索引添加。
跨分区非原子操作。

示例:

mysql> CREATE INDEX idx_id_age ON t1(id, age);
Query OK, 0 rows affected (0.17 sec)

5. CREATE TABLE

语法:

CREATE TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

create_definition: {
    col_name column_definition
  | {INDEX | KEY} [index_name] [index_type] (key_part,...)
      [index_option] ...
  | {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] PRIMARY KEY
      [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
      [index_name] [index_type] (key_part,...)
      [index_option] ...
}

column_definition: {
    data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
      [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [COLLATE collation_name]
      [COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
      [STORAGE {DISK | MEMORY}]
}

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

index_type:
    USING {BTREE | HASH}

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
}

table_options:
    table_option [[,] table_option] ...

table_option: {
    AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
  | CONNECTION [=] 'connect_string'
  | {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | ENCRYPTION [=] {'Y' | 'N'}
  | ENGINE [=] {InnoDB | TokuDB}
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
  | STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
  | STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
  | STATS_SAMPLE_PAGES [=] value
  | TABLESPACE tablespace_name [STORAGE {DISK | MEMORY}]
}

partition_options:
      PARTITION BY HASH(shard-key)
    | PARTITION BY LIST(shard-key)(PARTITION backend VALUES IN (value_list),...)
    | SINGLE
    | GLOBAL
    | DISTRIBUTED BY (backend-name)

说明:

创建分区信息并在各个分区生成分区表。
使用 GLOBAL 语法创建全局表，全局表在每个存储节点上都有一份完整的数据。可以把数据量小、变更较少的表设置为全局表，通常用来与分区表做关联查询。
使用 SINGLE 语法创建单表，单表只分布在第一个存储节点上。
使用 DISTRIBUTED BY (backend-name) 语法创建一个可以指定存储节点的单表。
使用 PARTITION BY HASH(shard-key) PARTITIONS num 语法创建 hash 分区表, 分区方式为 HASH，分表个数为 num （选填），根据分区键 HASH 值均匀分散在各个分区。
使用 PARTITION BY LIST(shard-key) 语法创建 list 分区表，分区方式为 LIST, PARTITION ... VALUES IN (...) 是一个分区。
LIST 分区表达式的所有期望值都应包含在 PARTITION ... VALUES IN（...） 子句中。INSERT 语句如果包含不匹配分区列值将会报错。
建表语句不包含 PARTITION BY HASH(shard-key)|LIST(shard-key)|SINGLE|GLOBAL|DISTRIBUTED BY (backend-name) 时，将会创建一个 hash 分区表，选择表的主键索引或唯一索引作为分区键（不支持复合索引）。
分区键仅支持指定一个列，该列数据类型没有限制 (BINARY/NULL 类型除外)。
table_options 只支持 ENGINE、COMMENT 和 CHARSET，其他自动被忽略。
分区表默认引擎为 InnoDB。
表字符集默认为 utf8。
不支持非分区键的 PRIMARY/UNIQUE 约束，直接返回错误。
仅在语法级别上支持 AUTO_INCREMENT，该值不会生效。
不支持外键 FOREIGN KEY。
跨分区非原子操作。

示例:

mysql> CREATE TABLE t1(id int, age int) PARTITION BY HASH(id);
Query OK, 0 rows affected (1.80 sec)

mysql> CREATE TABLE t2(id int, age int) GLOBAL;
Query OK, 0 rows affected (1.80 sec)

mysql> CREATE TABLE t3(id int, age int) SINGLE;
Query OK, 0 rows affected (1.80 sec)

mysql> CREATE TABLE t4(id int, age int,primary key(id));
Query OK, 0 rows affected (1.110 sec)

mysql> CREATE TABLE t5(id int, age int) DISTRIBUTED BY (Bgd2aclf2peds630203362);
Query OK, 0 rows affected (1.110 sec)

mysql> CREATE TABLE h2 (c1 INT, c2 INT) PARTITION BY LIST(c1) (
       PARTITION p0 VALUES IN (1, 4, 7),
       PARTITION p1 VALUES IN (2, 5, 8)
       );
Query OK, 0 rows affected (1.110 sec)

mysql> CREATE TABLE t6(id int, age int) PARTITION BY HASH(id) PARTITIONS 16;
Query OK, 0 rows affected (1.80 sec)

6. DROP DATABASE

语法:

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

说明:

RadonDB 会把此语句直接发到所有后端并返回。
跨分区非原子操作。

示例:

mysql> DROP DATABASE sbtest1;
Query OK, 0 rows affected (0.02 sec)

7. DROP INDEX

语法:

DROP INDEX index_name ON tbl_name index_lock_and_algorithm_opt

index_lock_and_algorithm_opt:
    algorithm_option
|   lock_option
|   algorithm_option lock_option
|   lock_option algorithm_option

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

说明:

RadonDB 根据路由信息，发到相应的后端执行索引删除。
跨分区非原子操作。

示例:

mysql> DROP INDEX idx_id_age ON t1;
Query OK, 0 rows affected (0.09 sec)

8. DROP TABLE

语法:

DROP {TABLE | TABLES} [IF EXISTS] table_name

说明:

删除分区信息及后端分区表。
跨分区非原子操作。

示例:

mysql> DROP TABLE t1;
Query OK, 0 rows affected (0.05 sec)

9. TRUNCATE TABLE

语法:

TRUNCATE TABLE table_name

说明:

跨分区非原子操作。

示例:

mysql> insert into t1(a) values(1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

mysql> truncate table t1;
Query OK, 0 rows affected (0.17 sec)

mysql> select * from t1;
Empty set (0.01 sec)

Data Manipulation Statements

1. DELETE

语法:

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

说明:

支持分布式事务，保证跨分区删除原子性。
不支持无 WHERE 条件删除。
不支持子句。

示例:

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

2. DO

语法:

DO expr [, expr] ...

示例:

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

3. INSERT

语法:

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] ...

说明:

支持分布式事务，保证跨分区写入原子性。
支持 insert 多个值，这些值可以在不同分区。
建议指定写入列。
仅在语法层面上支持 PARTITION，不生效。
不支持子句。

示例:

mysql> INSERT INTO t1(id, age) VALUES(1, 24), (2, 28), (3, 29);
Query OK, 3 rows affected (0.01 sec)

4. REPLACE

语法:

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] ...

说明:

支持分布式事务，保证跨分区写入原子性。
支持 replace 多个值，这些值可以在不同分区。
仅在语法层面上支持 PARTITION，不生效。
建议指定写入列。
不支持子句。

示例:

mysql> REPLACE INTO t1 (id, age) VALUES(3,34),(5, 55);
Query OK, 2 rows affected (0.01 sec)

5. SELECT

语法:

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 ...]

说明:

支持跨分区的 count, sum, avg, max, min 等聚合函数，支持含有 distinct 的聚合函数，聚合函数只对数值型有效。
支持跨分区的 order by, group by, limit 等操作， group by 字段必须在 select_expr 中。
支持 left | right outer 和 inner | cross join。不可下推的 join 场景，select_expr 不能有 *
支持 sort-merge join 和 nested-loop join。默认使用 sort-merge join。
支持UNION [ALL | DISTINCT]。
不支持 having 子句中包含聚合函数的场景
不支持子查询

示例:

mysql> SELECT id, age, sum(id), avg(age) FROM t1 GROUP BY id ORDER BY id DESC LIMIT 10;
+------+------+---------+----------+
| id   | age  | sum(id) | avg(age) |
+------+------+---------+----------+
|    1 |   25 |       2 |       26 |
|    3 |   32 |       3 |       32 |
+------+------+---------+----------+
2 rows in set (0.01 sec)

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

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)

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)

6. UPDATE

语法:

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

说明:

支持分布式事务，保证跨分区更新原子性。
不支持无 WHERE 条件更新。
不支持更新分区键。
不支持子句。

示例:

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

Database Administration Statements

1. SET

说明:

为了兼容 JDBC/mydumper 。
SET 是一个空操作，除了 autocommit 和 radon_streaming_fetch，其他操作并不会生效，请勿直接使用。

2. SHOW

说明:

SHOW 具有许多形式，可提供有关 database, table, columns 或有关服务器状态的信息。

2.1 SHOW CHARSET

语法:

SHOW CHARSET

说明:

该语句列出所有可用的字符集。

示例:

mysql> show charset;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
... ...
41 rows in set (0.02 sec)

2.2 SHOW COLLATION

语法:

SHOW COLLATION

说明:

该语句列出所有支持的校验字符集。

示例:

mysql> SHOW COLLATION;
+--------------------------+----------+-----+---------+----------+---------+
| Collation                | Charset  | Id  | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci          | big5     |   1 | Yes     | Yes      |       1 |
| big5_bin                 | big5     |  84 |         | Yes      |       1 |
| dec8_swedish_ci          | dec8     |   3 | Yes     | Yes      |       1 |
| dec8_bin                 | dec8     |  69 |         | Yes      |       1 |
| cp850_general_ci         | cp850    |   4 | Yes     | Yes      |       1 |
| cp850_bin                | cp850    |  80 |         | Yes      |       1 |
| hp8_english_ci           | hp8      |   6 | Yes     | Yes      |       1 |
| hp8_bin                  | hp8      |  72 |         | Yes      |       1 |
| koi8r_general_ci         | koi8r    |   7 | Yes     | Yes      |       1 |
... ...
222 rows in set (0.05 sec)

2.3. SHOW ENGINES

语法:

SHOW ENGINES

说明:

后端分区 MySQL 支持的引擎列表。

示例:

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                                      | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                                      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)             | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                         | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                                     | NO           | NO   | NO         |
| TokuDB             | YES     | Percona TokuDB Storage Engine with Fractal Tree(tm) Technology             | YES          | YES  | YES        |
| FEDERATED          | NO      | Federated MySQL storage engine                                             | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                  | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)

2.4. SHOW DATABASES

语法:

SHOW DATABASES

说明:

包含系统 DB，比如 mysql, information_schema

示例:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sbtest1            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

2.5. SHOW TABLES

语法:

SHOW [FULL] TABLES
[FROM db_name]
[LIKE 'pattern' | WHERE expr]

说明:

如果未指定 db_name, 则返回当前 DB 下的表。

示例:

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
+----------------+
2 rows in set (0.00 sec)

2.6. SHOW TABLE STATUS

语法：

SHOW TABLE STATUS
[FROM db_name]

说明:

如果未指定 db_name, 则返回当前 DB 下的表。

示例:

mysql> show table status;
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name   | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| b      | InnoDB |      10 | Dynamic    |    6 |          16384 |       16384 |               0 |            0 |         0 |           NULL | 2018-12-24 08:26:24 | 2019-01-22 08:31:47 | NULL       | utf8_general_ci |     NULL |                |         |
| g      | InnoDB |      10 | Dynamic    |    1 |          16384 |       16384 |               0 |            0 |         0 |           NULL | 2018-12-24 08:26:24 | 2019-02-28 03:20:46 | NULL       | utf8_general_ci |     NULL |                          |         |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
2 rows in set (0.08 sec)

2.7. SHOW COLUMNS

语法：

SHOW [FULL] {COLUMNS | FIELDS}
    {FROM | IN} tbl_name
    [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

说明:

获取表的列定义。

示例:

mysql> CREATE TABLE T1(A INT, B VARCHAR(10)) PARTITION BY HASH(A);
Query OK, 0 rows affected (0.52 sec)

mysql> SHOW COLUMNS FROM T1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| A     | int(11)     | YES  |     | NULL    |       |
| B     | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

2.8. SHOW CREATE TABLE

语法:

SHOW CREATE TABLE table_name

说明:

示例:

mysql> SHOW CREATE TABLE t1\G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

2.9. SHOW INDEX

语法:

SHOW {INDEX | INDEXES | KEYS}
    {FROM | IN} tbl_name
    [{FROM | IN} db_name]
    [WHERE expr]

说明:

获取表的索引信息。

示例:

mysql> CREATE TABLE t1(A INT PRIMARY KEY, B VARCHAR(10)) PARTITION BY HASH(A);
Query OK, 0 rows affected (2.20 sec)

mysql> show index from t1\G
*************************** 1. row ***************************
        Table: t1
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: A
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.05 sec)

2.10. SHOW PROCESSLIST

语法:

SHOW PROCESSLIST

说明:

显示的为 client 到 RadonDB 的连接情况，并非后端分区 MySQL。

示例:

mysql> SHOW PROCESSLIST;
+------+------+-------------------+------+---------+------+-------+------+-----------+---------------+
| Id   | User | Host              | db   | Command | Time | State | Info | Rows_sent | Rows_examined |
+------+------+-------------------+------+---------+------+-------+------+-----------+---------------+
|    1 | mock | 192.168.0.3:35346 | test | Sleep   |  379 |       |      |         0 |             0 |
+------+------+-------------------+------+---------+------+-------+------+-----------+---------------+
1 row in set (0.00 sec)

2.11. SHOW VARIABLES

语法:

SHOW VARIABLES
    [LIKE 'pattern' | WHERE expr]

说明:

为了兼容 JDBC/mydumper 。
SHOW VARIABLES 命令会发往后端分区 MySQL (随机分区)获取并返回。

3. Table Maintenance Statements

3.1. CHECK TABLE

语法:

CHECK {TABLE | TABLES} tbl_name [, tbl_name] ... [option] ...

option: {
    FOR UPGRADE
  | QUICK
  | FAST
  | MEDIUM
  | EXTENDED
  | CHANGED
}

示例:

mysql> check tables t_part for quick;
+------------------+-------+----------+----------+
| Table            | Op    | Msg_type | Msg_text |
+------------------+-------+----------+----------+
| test.t_part_0000 | check | status   | OK       |
| test.t_part_0001 | check | status   | OK       |
| test.t_part_0002 | check | status   | OK       |
| test.t_part_0003 | check | status   | OK       |
...
...
| test.t_part_0062 | check | status   | OK       |
| test.t_part_0063 | check | status   | OK       |
+------------------+-------+----------+----------+
64 rows in set (0.02 sec)

3.2. CHECKSUM TABLE

语法:

CHECKSUM {TABLE | TABLES} tbl_name [, tbl_name] ... [QUICK | EXTENDED]

说明:

校验表的数据一致性。

示例:

mysql> checksum table test.t1;
+----------+------------+
| Table    | Checksum   |
+----------+------------+
| test.t1  | 2464930879 |
+----------+------------+
1 row in set (0.00 sec)

3.3. OPTIMIZE TABLE

语法:

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL]
    {TABLE | TABLES} tbl_name [, tbl_name] ...

说明:

重新组织表、索引的物理存储，减少存储空间，提高访问的 I/O 效率。

示例:

mysql> create table t_part(a int key, b char);
Query OK, 0 rows affected (0.05 sec)

mysql> optimize local tables t_part;
+------------------+----------+----------+-------------------------------------------------------------------+
| Table            | Op       | Msg_type | Msg_text                                                          |
+------------------+----------+----------+-------------------------------------------------------------------+
| test.t_part_0000 | optimize | status   | OK                                                                |
| test.t_part_0000 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.t_part_0001 | optimize | status   | OK                                                                |
| test.t_part_0001 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.t_part_0002 | optimize | status   | OK                                                                |
....
....
| test.t_part_0062 | optimize | status   | OK                                                                |
| test.t_part_0062 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.t_part_0063 | optimize | status   | OK                                                                |
| test.t_part_0063 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
+------------------+----------+----------+-------------------------------------------------------------------+
128 rows in set (1.65 sec)

4. KILL

语法:

KILL processlist_id

说明:

kill 某个链接(包含终止链接正在执行的语句)。

示例:

mysql> show processlist;
+------+------+-----------------+------+---------+------+-------+------+-----------+---------------+
| Id   | User | Host            | db   | Command | Time | State | Info | Rows_sent | Rows_examined |
+------+------+-----------------+------+---------+------+-------+------+-----------+---------------+
|   11 | mock | 127.0.0.1:43028 | test | Sleep   |  291 |       |      |         0 |             0 |
+------+------+-----------------+------+---------+------+-------+------+-----------+---------------+
1 row in set (0.00 sec)

mysql> kill 11;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Utility Statements

1. DESCRIBE

说明:

DESCRIBE 和 EXPLAIN 语句是同义词，用于获取有关表结构的信息或查询执行计划。
参考 EXPLAIN。

2. EXPLAIN

语法:

{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    {explainable_stmt | FOR CONNECTION connection_id}

{EXPLAIN | DESCRIBE | DESC} ANALYZE [FORMAT = TREE] select_statement

explain_type: {
    EXTENDED
  | PARTITIONS
  | FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
  | TREE
}

explainable_stmt: {
    SELECT statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

说明:

EXPLAIN 输出 RadonDB 的执行计划。

示例:

mysql> create table t(c1 int key, c2 char(10), c3 varchar(100));
Query OK, 0 rows affected (1.31 sec)

mysql> desc t;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| c1    | int(11)      | NO   | PRI | NULL    |       |
| c2    | char(10)     | YES  |     | NULL    |       |
| c3    | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> explain select * from t\G
*************************** 1. row ***************************
EXPLAIN: {
  "RawQuery": "explain select * from t",
  "Project": "*",
  "Partitions": [
    {
      "Query": "select * from testdb.t_0000 as t",
      "Backend": "backend1",
      "Range": "[0-64)"
    },
    {
      "Query": "select * from testdb.t_0001 as t",
      "Backend": "backend1",
      "Range": "[64-128)"
    },
    .........
    .........
    .........

3. USE DATABASE

语法:

USE db_name

说明:

切换当前 session 的 database

示例:

mysql> use test;
Database changed

Transactional and Locking Statements

1. Transaction

语法:

BEGIN
COMMIT
ROLLBACK

说明:

支持多语句事务。
必须开启 RadonDB 参数 twopc-enable。
支持单条事务 autocommit (twopc-enable ON)。

示例:

mysql> create table txntbl(a int);
Query OK, 0 rows affected (0.01 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into txntbl(a) values(1),(2);
Query OK, 4 rows affected (0.00 sec)

mysql> select * from txntbl;
+------+
| a    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.01 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from txntbl;
Empty set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into txntbl(a) values(1),(2);
Query OK, 4 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from txntbl;
+------+
| a    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

Radon

1. RADON ATTACH

语法:

RADON ATTACH($address,$user,$password)

说明:

附加一个 MySQL 到 RadonDB 的 backend。
在 backend 中类型为 attach.

示例:

mysql> radon attach('127.0.0.1:3306','root','123456');
Query OK, 0 rows affected (0.94 sec)

2. RADON ATTACHLIST

说明:

列出类型为 attach 的 backend.

示例:

mysql> radon attachlist;
+----------------+----------------+------+
| Name           | Address        | User |
+----------------+----------------+------+
| 127.0.0.1:3306 | 127.0.0.1:3306 | root |
+----------------+----------------+------+
1 row in set (0.00 sec)

3. RADON DETACH

语法:

RADON DETACH($address)

说明:

将一个类型为 attach 的 backend 从 RadonDB 中剥离。

示例:

mysql> radon detach('127.0.0.1:3306');
Query OK, 0 rows affected (0.22 sec)

mysql> radon attachlist;
Empty set (0.00 sec)

4. RADON RESHARD

语法:

RADON RESHARD tbl_name TO new_tbl_name

说明:

将数据从一个 single 表转移到另一个 hash 分区表。
执行后立即返回，迁移将在后台运行。
single 表必须带有主键。

示例:

mysql> show tables;
Empty set (0.10 sec)

mysql> create table t1(a int primary key, b varchar(255)) single;
Query OK, 0 rows affected (0.13 sec)

mysql> insert into t1(a,b) values(1,'a'),(2,'b');
Query OK, 2 rows affected (0.10 sec)

mysql> radon reshard t1 to new_tb;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+---------------+
| Tables_in_zzq |
+---------------+
| t1            |
| new_tb        |
+---------------+
2 rows in set (0.10 sec)

mysql> show create table new_tb;
+--------+----------------------------------------------------------------+
| Table  | Create Table                                                   |
+--------+----------------------------------------------------------------+
| new_tb | CREATE TABLE `new_tb` (
  `a` int(11) NOT NULL,
  `b` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH(a) */ |
+--------+----------------------------------------------------------------+
1 row in set (0.05 sec)

mysql> select * from new_tb;
+---+------+
| a | b    |
+---+------+
| 1 | a    |
| 2 | b    |
+---+------+
2 rows in set (1.09 sec)

5. RADON CLEANUP

说明:

用于数据重分布之后，旧数据的清理。

示例:

mysql> radon cleanup;
Empty set (0.00 sec)

6.RADON REBALANCE

说明:

这条指令旨在让节点之间的数据重新均衡分布，每次从一个后端节点只迁移一张分区表到另一个后端节点。可重复执行，直至数据分布均衡。
指令执行时，客户端会阻塞；若执行 ctrl+c 或者退出客户端，操作将会在后台继续进行。

示例:

mysql> radon rebalance;
Query OK, 0 rows affected (39.09 sec)

Hint

1. 流式读取

说明：

查询结果集比较大时，可通过流式读取方式获取数据。
方式一：执行 set @@SESSION.radon_streaming_fetch='ON' 开启流式读取，查询语句执行完成后执行 set @@SESSION.radon_streaming_fetch='OFF' 关闭流式读取。
方式二：通过在查询语句中加 hint /*+ streaming */ 的方式流式读取。
不支持复杂查询

示例:

mysql> select /*+ streaming */ * from t1;
Empty set (0.00 sec)

2. 读写分离

说明：

配置参数 load-balance 为 1 时，默认查询会从存储节点的高可用读 ip 获取数据，有可能存在因从节点延时而无法获取实时数据。
因此支持通过加 hint /*+ loadbalance=0 */ 方式强制从存储节点高可用写 ip 查询数据；若为 /*+ loadbalance=1 */ 则强制从高可用读 ip 查询。

示例：

mysql> select /*+ loadbalance=0 */ * from t1;
Empty set (0.00 sec)

mysql> select /*+ loadbalance=1 */ * from t1;
Empty set (0.00 sec)

Others

1. AUTO INCREMENT

说明:

使用 golang 的 UnixNano() 作为唯一标识。
AUTO_INCREMENT 字段类型必须是 BIGINT。

示例:

mysql> CREATE TABLE animals (
    ->      id BIGINT NOT NULL AUTO_INCREMENT,
    ->      name CHAR(30) NOT NULL,
    ->      PRIMARY KEY (id)
    -> ) PARTITION BY HASH(id);
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO animals (name) VALUES
    ->     ('dog'),('cat'),('penguin'),
    ->     ('lax'),('whale'),('ostrich');
Query OK, 6 rows affected (0.01 sec)

mysql> SELECT * FROM animals;
+---------------------+---------+
| id                  | name    |
+---------------------+---------+
| 1553090617754346084 | lax     |
| 1553090617754346082 | cat     |
| 1553090617754346085 | whale   |
| 1553090617754346081 | dog     |
| 1553090617754346083 | penguin |
| 1553090617754346086 | ostrich |
+---------------------+---------+
6 rows in set (0.02 sec)

2. N-Gram Full Text Parser

说明:

支持 Full-Text Search，提供支持中文，日文和韩文 (CJK) 的 n-gram full-text parser。
RadonDB 的 Full-Text 分片表查询时并行执行(而 MySQL 的分区表并不支持 Full-Text Indexes 或 Searches)。

示例:

mysql>CREATE TABLE `articles` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) DEFAULT NULL,
  `body` text,
  PRIMARY KEY (`id`),
  FULLTEXT INDEX `ngram_idx` (`title`,`body`) WITH PARSER ngram
) ENGINE=InnoDB PARTITION BY HASH(id);
1 row in set (0.01 sec)

mysql>INSERT INTO articles (title,body) VALUES
    ('数据库管理','在本教程中我将向你展示如何管理数据库'),
    ('数据库应用开发','学习开发数据库应用程序');
1 row in set (0.02 sec)

mysql>SELECT title from articles  WHERE MATCH (title, body) AGAINST ('数据库' IN BOOLEAN MODE);
+-----------------------+
| title                 |
+-----------------------+
| 数据库应用开发        |
| 数据库管理            |
+-----------------------+
2 rows in set (0.04 sec)

混合云解决方案

大数据解决方案

托管云解决方案

安全解决方案

混合云存储解决方案

多活灾备解决方案

电商行业解决方案

航空业解决方案

医疗解决方案

金融行业解决方案

媒体业解决方案

教育行业解决方案

移动互联网解决方案

视频行业解决方案

物联网解决方案

混合云解决方案

大数据解决方案

托管云解决方案

安全解决方案

混合云存储解决方案

多活灾备解决方案

电商行业解决方案

航空业解决方案

医疗解决方案

金融行业解决方案

媒体业解决方案

教育行业解决方案

移动互联网解决方案

视频行业解决方案

物联网解决方案

混合云解决方案

大数据解决方案

托管云解决方案

安全解决方案

混合云存储解决方案

多活灾备解决方案

电商行业解决方案

航空业解决方案

医疗解决方案

金融行业解决方案

媒体业解决方案

教育行业解决方案

移动互联网解决方案

视频行业解决方案

物联网解决方案

混合云解决方案

大数据解决方案

托管云解决方案

安全解决方案

混合云存储解决方案

多活灾备解决方案

电商行业解决方案

航空业解决方案

医疗解决方案

金融行业解决方案

媒体业解决方案

教育行业解决方案

移动互联网解决方案

视频行业解决方案

物联网解决方案

RadonDB 支持 SQL 集

背景

Data Definition Statements

1. ALTER DATABASE

2. ALTER TABLE

2.1. 增加列

2.2. 更改表引擎

2.3. 更改表字符集

2.4. 删除列

2.5. 更改列定义

3. CREATE DATABASE

4. CREATE INDEX

5. CREATE TABLE

6. DROP DATABASE

7. DROP INDEX

8. DROP TABLE

9. TRUNCATE TABLE

Data Manipulation Statements

1. DELETE

2. DO