Skip to content

Latest commit

 

History

History
600 lines (492 loc) · 17.9 KB

data_definition_statements.md

File metadata and controls

600 lines (492 loc) · 17.9 KB

Table of Contents

Data Definition Statements

ALTER DATABASE Statement

Syntax

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}
}

Instructions

  • RadonDB sends the corresponding backend execution engine changes based on the routing information
  • Cross-partition non-atomic operations
  • radon completely support syntax with MySQL 8.0 and abandon "alter ... UPGRADE DATA DIRECTORY NAME" feature in 5.7.

Example:

  1. alter with specify database.
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)
  1. Alter without specify database, use the current default session database.
mysql> alter /*current session has no db, no database selected*/ database default character set = utf8 collate = utf8_unicode_ci;
ERROR 1046 (3D000): No database selected
mysql> use testdb;
Database changed
mysql> alter /*use current session testdb*/ database default character set = 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)

ALTER TABLE Statement

Syntax

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
}

Instructions

  • RadonDB sends the corresponding backend execution engine changes based on the routing information
  • Cross-partition non-atomic operations

Add Column

Example: 

mysql> ALTER TABLE t1 ADD COLUMN (b int, c varchar(100));
Query OK, 0 rows affected (2.94 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,
  `c` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (id) */
1 row in set (0.048 sec)

Change Table Engine

Example: 

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)

Change The Table Character Set

In RadonDB, the default character set is UTF-8.

Example: 

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)

Drop Column

Instructions

  • Cannot delete the column where the partition key is located

Example: 

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

Modify Column

Instructions

  • Cannot modify the column where the partition key is located

Example: 

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

CREATE DATABASE Statement

Syntax

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

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

Instructions

  • RadonDB will sends this statement directly to all backends to execute and return results.
  • Cross-partition non-atomic operations
  • The ENCRYPTION option, introduced in MySQL 8.0.16, defines the default database encryption. Inherited by tables created in the database.

Example:

mysql> CREATE DATABASE db_test1;
Query OK, 1 row affected (0.00 sec)

CREATE INDEX

Syntax

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}

Instructions

  • RadonDB sends the index to the corresponding backend based on the routing information.
  • Cross-partition non-atomic operations

Example: 

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

CREATE TABLE

Syntax

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) [PARTITIONS num]
    | PARTITION BY LIST(shard-key)(PARTITION backend VALUES IN (value_list),...)
    | SINGLE
    | GLOBAL
    | DISTRIBUTED BY (backend-name)

With MySQL compatibility:

  • radon not support CREATE TEMPORARY TABLE syntax
  • not support CREATE TABLE [AS] query_expression and CREATE TABLE LIKE
  • not support check_constraint_definition and reference_definition
  • not support FOREIGN KEY

Instructions

  • Create partition information and generate partition tables on each partition
  • AUTO_INCREMENT table_option currently only supported at the grammatical level, the value will not take effect.
  • With GLOBAL will create a global table. The global table has full data at every backend. The global tables are generally used for tables with fewer changes and smaller capacity, requiring frequent association with other tables.
  • With SINGLE will create a single table. The single table only on the first backend.
  • With DISTRIBUTED BY (backend-name) will create a single table. The single table is distributed on the specified backend backend-name.
  • With PARTITION BY HASH(shard-key) will create a hash partition table. The partition mode is HASH, which is evenly distributed across the partitions according to the partition key HASH value, PARTITIONS num can specify the partition number.
  • Without PARTITION BY HASH(shard-key)|LIST(shard-key)|SINGLE|GLOBAL will create a hash partition table. The table's PRIMARY|UNIQUE KEY is the partition key, only support one primary|unique key.
  • With PARTITION BY LIST(shard-key) will create a list partition table. PARTITION backend VALUES IN (value_list) is one partition, The variable backend is one backend name, The variable value_list is values with ,.
    • all expected values for the partitioning expression should be covered in PARTITION ... VALUES IN (...) clauses. An INSERT statement containing an unmatched partitioning column value fails with an error, as shown in this example:
	mysql> CREATE TABLE h2 (
	    ->   c1 INT,
	    ->   c2 INT
	    -> )
	    -> PARTITION BY LIST(c1) (
	    ->   PARTITION backend_name1 VALUES IN (1, 4, 7),
	    ->   PARTITION backend_name2 VALUES IN (2, 5, 8)
	    -> );
	Query OK, 0 rows affected (0.11 sec)
		
	mysql> INSERT INTO h2 VALUES (3, 5);
	ERROR 1525 (HY000): Table has no partition for value 3

  mysql> CREATE TABLE t5(id int, age int) DISTRIBUTED BY (backend1);
  Query OK, 0 rows affected (0.11 sec)
  • The partitioning key only supports specifying one column, the data type of this column is not limited( except for TYPE BINARY/NULL)
  • The default engine for partition table is InnoDB
  • The default character set for partition table UTF-8
  • Does not support PRIMARY/UNIQUE constraints for non-partitioned keys, returning errors directly
  • Cross-partition non-atomic operations

Example:

mysql> CREATE DATABASE db_test1;
Query OK, 1 row affected (0.00 sec)

mysql> USE db_test1;

Database changed
mysql> CREATE TABLE t1(id int, age int) PARTITION BY HASH(id);
Query OK, 0 rows affected (1.80 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.051 sec)

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

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!GLOBAL*/
1 row in set (0.047 sec)

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

mysql> show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 'HELLO RADON'
/*!SINGLE*/
1 row in set (0.093 sec)

mysql> CREATE TABLE t4(id int, age int);
ERROR 1105 (HY000): The unique/primary constraint shoule be defined or add 'PARTITION BY HASH' to mandatory indication
mysql> CREATE TABLE t4(id int, age int,primary key(id));
Query OK, 0 rows affected (1.110 sec)

mysql> show create table t4\G
*************************** 1. row ***************************
       Table: t4
Create Table: CREATE TABLE `t4` (
  `id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (id) */
1 row in set (0.094 sec)

DROP DATABASE

Syntax

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

Instructions

  • RadonDB will sends this statement directly to all backends to execute and return results.
  • Cross-partition non-atomic operations

Example 

mysql> DROP DATABASE db_test1;
Query OK, 0 rows affected (0.01 sec)

DROP INDEX

Syntax

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}

Instructions

  • RadonDB sends an drop index operation to the appropriate backend based on routing information
  • Cross-partition non-atomic operations

Example: 

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

DROP TABLE

Syntax

DROP {TABLE | TABLES} [IF EXISTS] table_name

Instructions

  • Delete partition information and backend`s partition table
  • Cross-partition non-atomic operations

Example: 

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

TRUNCATE TABLE Statement

Syntax

TRUNCATE TABLE table_name

Instructions

Cross-partition non-atomic operations

Example: 

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

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

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

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