In MySQL mode, OceanBase Database allows you to change primary keys, partitioning types, data types of columns, and character sets through DDL operations.
Change primary keys
In MySQL mode, OceanBase Database allows you to add, modify, and drop primary keys.
Add a primary key
Syntax:
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
Example:
obclient> CREATE TABLE tbl1(c1 INT,c2 VARCHAR(50));
Query OK, 0 rows affected
obclient> SHOW CREATE TABLE tbl1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl1 | CREATE TABLE `tbl1` (
`c1` int(11) DEFAULT NULL,
`c2` varchar(50) DEFAULT NULL
) 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 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
obclient> ALTER TABLE tbl1 ADD PRIMARY KEY(c1);
Query OK, 0 rows affected
obclient> SHOW CREATE TABLE tbl1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl1 | CREATE TABLE `tbl1` (
`c1` int(11) NOT NULL,
`c2` varchar(50) 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
Modify a primary key
Syntax:
ALTER TABLE table_name DROP PRIMARY KEY,ADD PRIMARY KEY (column_name_list);
Example:
obclient> ALTER TABLE tbl1 DROP PRIMARY KEY,ADD PRIMARY KEY(c2);
Query OK, 0 rows affected
obclient [test]> SHOW CREATE TABLE tbl1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl1 | CREATE TABLE `tbl1` (
`c1` int(11) NOT NULL,
`c2` varchar(50) NOT NULL,
PRIMARY KEY (`c2`)
) 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
Drop a primary key
Syntax:
ALTER TABLE table_name DROP PRIMARY KEY;
Example:
obclient> ALTER TABLE tbl1 DROP PRIMARY KEY;
Query OK, 0 rows affected
obclient> SHOW CREATE TABLE tbl1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl1 | CREATE TABLE `tbl1` (
`c1` int(11) NOT NULL,
`c2` varchar(50) NOT NULL
) 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
Change partitioning types
In MySQL mode, OceanBase Database allows you to change partitioning types. Syntax:
ALTER TABLE table_name PARTITION BY (partition_definitions);
Examples
Example 1: Change HASH partitioning to KEY partitioning.
obclient> CREATE TABLE tbl2(c1 INT, c2 DATETIME, PRIMARY KEY(c1, c2))
PARTITION BY HASH(c1) PARTITIONS 4;
Query OK, 0 rows affected
obclient [test]> SHOW CREATE TABLE tbl2;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl2 | CREATE TABLE `tbl2` (
`c1` int(11) NOT NULL,
`c2` datetime NOT NULL,
PRIMARY KEY (`c1`, `c2`)
) 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
partition by hash(c1)
(partition p0,
partition p1,
partition p2,
partition p3) |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
obclient> ALTER TABLE tbl2 PARTITION BY KEY(c1) PARTITIONS 10;
Query OK, 0 rows affected
obclient [test]> SHOW CREATE TABLE tbl2;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl2 | CREATE TABLE `tbl2` (
`c1` int(11) NOT NULL,
`c2` datetime NOT NULL,
PRIMARY KEY (`c1`, `c2`)
) 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
partition by key(c1)
(partition p0,
partition p1,
partition p2,
partition p3,
partition p4,
partition p5,
partition p6,
partition p7,
partition p8,
partition p9) |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
Example 2: Change a HASH-partitioned table to a template-based HASH-RANGE-subpartitioned table.
obclient> ALTER TABLE tbl2
PARTITION BY HASH(c1)
SUBPARTITION BY RANGE COLUMNS(c2)
SUBPARTITION TEMPLATE(
SUBPARTITION p1 VALUES LESS THAN ('2016-10-10'),
SUBPARTITION p2 VALUES LESS THAN ('2116-3-30')) PARTITIONS 2;
Query OK, 0 rows affected
obclient> SHOW CREATE TABLE tbl2;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl2 | CREATE TABLE `tbl2` (
`c1` int(11) NOT NULL,
`c2` datetime NOT NULL,
PRIMARY KEY (`c1`, `c2`)
) 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
partition by hash(c1) subpartition by range columns(c2) subpartition template (
subpartition p1 values less than ('2016-10-10 00:00:00'),
subpartition p2 values less than ('2116-03-30 00:00:00'))
(partition p0,
partition p1) |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
Example 3: Change a HASH-RANGE-subpartitioned table to a RANGE COLUMNS-RANGE COLUMNS-subpartitioned table.
obclient> ALTER TABLE tbl2
PARTITION BY RANGE COLUMNS(c1)
SUBPARTITION BY RANGE COLUMNS(c2)
(
PARTITION p0 VALUES LESS THAN (100)
(SUBPARTITION sp0 VALUES LESS THAN ('2020-01-01')
,SUBPARTITION sp1 VALUES LESS THAN ('2021-01-01')
,SUBPARTITION sp2 VALUES LESS THAN ('2022-01-01')
,SUBPARTITION sp3 VALUES LESS THAN ('2023-01-01')
),
PARTITION p1 VALUES LESS THAN (200)
(SUBPARTITION sp4 VALUES LESS THAN ('2020-01-01')
,SUBPARTITION sp5 VALUES LESS THAN ('2021-01-01')
,SUBPARTITION sp6 VALUES LESS THAN ('2022-01-01')
,SUBPARTITION sp7 VALUES LESS THAN ('2023-01-01'))
);
Query OK, 0 rows affected
obclient [test]> SHOW CREATE TABLE tbl2;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl2 | CREATE TABLE `tbl2` (
`c1` int(11) NOT NULL,
`c2` datetime NOT NULL,
PRIMARY KEY (`c1`, `c2`)
) 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
partition by range columns(c1) subpartition by range columns(c2)
(partition p0 values less than (100) (
subpartition sp0 values less than ('2020-01-01 00:00:00'),
subpartition sp1 values less than ('2021-01-01 00:00:00'),
subpartition sp2 values less than ('2022-01-01 00:00:00'),
subpartition sp3 values less than ('2023-01-01 00:00:00')),
partition p1 values less than (200) (
subpartition sp4 values less than ('2020-01-01 00:00:00'),
subpartition sp5 values less than ('2021-01-01 00:00:00'),
subpartition sp6 values less than ('2022-01-01 00:00:00'),
subpartition sp7 values less than ('2023-01-01 00:00:00'))) |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
Change the data type of columns
In MySQL mode, OceanBase Database allows you to convert the data types of columns:
- Conversion for the following character data types:
CHAR,VARCHAR,TINYTEXT,TEXT, andLONGTEXT. - Conversion for the following numeric data types:
TINYINT,SMALLINT,MEDIUMINT,INT, andBIGINT. - Conversion for the following binary data types:
BINARY,VARBINARY,BLOB,TINYBLOB,MEDIUMBLOB, andLONGBLOB. - Precision changes for the following data types with a specified precision:
VARCHAR,FLOAT,DOUBLE, andDECIMAL. - Conversion between the following data types with a specified precision:
FLOAT,DOUBLE, andDECIMAL. - Conversion between the following data types:
INT,VARCHAR,DOUBLE,FLOAT, andDECIMAL.
Syntax:
ALTER TABLE table_name MODEFY column_name data_type;
Examples of column type changes
Conversions between character data types
Change the data type of a character column and increase the length.
obclient> CREATE TABLE test01 (c1 INT PRIMARY KEY, c2 CHAR(10), c3 CHAR(10));
Query OK, 0 rows affected
obclient> ALTER TABLE test01 MODIFY C2 VARCHAR(20);
Query OK, 0 rows affected
obclient> ALTER TABLE test01 MODIFY C2 VARCHAR(40);
Query OK, 0 rows affected
obclient> SHOW CREATE TABLE test01;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test01 | CREATE TABLE `test01` (
`c1` int(11) NOT NULL,
`C2` varchar(40) DEFAULT NULL,
`c3` char(10) 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
obclient> ALTER TABLE test01 MODIFY C2 TINYTEXT;
Query OK, 0 rows affected
obclient> ALTER TABLE test01 MODIFY C2 LONGTEXT;
Query OK, 0 rows affected
obclient> ALTER TABLE test01 MODIFY C3 CHAR(20);
Query OK, 0 rows affected
obclient> ALTER TABLE test01 MODIFY C3 VARCHAR(30);
Query OK, 0 rows affected
obclient> SHOW CREATE TABLE test01;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test01 | CREATE TABLE `test01` (
`c1` int(11) NOT NULL,
`C2` longtext DEFAULT NULL,
`C3` varchar(30) 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
Conversion between numeric data types
Example 1: Change the data type and increase the length of an integer column.
obclient> CREATE TABLE test02 (id INT PRIMARY KEY, name VARCHAR(10),age TINYINT, description VARCHAR(65525));
Query OK, 0 rows affected
obclient> ALTER TABLE test02 MODIFY age SMALLINT;
Query OK, 0 rows affected
obclient> SHOW CREATE TABLE test02;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test02 | CREATE TABLE `test02` (
`id` int(11) NOT NULL,
`name` varchar(10) DEFAULT NULL,
`age` smallint(6) DEFAULT NULL,
`description` varchar(65525) DEFAULT NULL,
PRIMARY KEY (`id`)
) DEFAULT CHARSET = gbk 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
obclient> ALTER TABLE test02 MODIFY age INT;
Query OK, 0 rows affected
obclient> SHOW CREATE TABLE test02;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test02 | CREATE TABLE `test02` (
`id` int(11) NOT NULL,
`name` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`description` varchar(65525) DEFAULT NULL,
PRIMARY KEY (`id`)
) DEFAULT CHARSET = gbk 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
obclient> ALTER TABLE test02 MODIFY age BIGINT;
Query OK, 0 rows affected
obclient> SHOW CREATE TABLE test02;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test02 | CREATE TABLE `test02` (
`id` int(11) NOT NULL,
`name` varchar(10) DEFAULT NULL,
`age` bigint(20) DEFAULT NULL,
`description` varchar(65525) DEFAULT NULL,
PRIMARY KEY (`id`)
) DEFAULT CHARSET = gbk 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
Example 2: Change the data type and length of a column with a specified precision.
obclient> CREATE TABLE test03(c1 INT, c2 FLOAT(8,0), c3 FLOAT(8,0), UNIQUE(c2, c3));
Query OK, 0 rows affected
obclient> ALTER TABLE test03 MODIFY c2 FLOAT(5,0);
Query OK, 0 rows affected
obclient> ALTER TABLE test03 MODIFY c2 DOUBLE(10,0);
Query OK, 0 rows affected
obclient> ALTER TABLE test03 MODIFY c2 DOUBLE(5,0);
Query OK, 0 rows affected
obclient> ALTER TABLE test03 MODIFY c2 DECIMAL(20, 4);
Query OK, 0 rows affected
obclient> SHOW CREATE TABLE test03;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test03 | CREATE TABLE `test03` (
`c1` int(11) DEFAULT NULL,
`c2` decimal(20,4) DEFAULT NULL,
`c3` float(8,0) DEFAULT NULL,
UNIQUE KEY `c2` (`c2`, `c3`) BLOCK_SIZE 16384 LOCAL
) 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
Conversion for binary data types
obclient> CREATE TABLE test04 (c1 TINYBLOB, c2 BINARY(64));
Query OK, 0 rows affected
obclient> ALTER TABLE test04 MODIFY c1 BLOB;
Query OK, 0 rows affected
obclient> ALTER TABLE test04 MODIFY c1 BINARY(256);
Query OK, 0 rows affected
obclient> SHOW CREATE TABLE test04;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test04 | CREATE TABLE `test04` (
`c1` binary(256) DEFAULT NULL,
`c2` binary(64) DEFAULT NULL
) 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
obclient> CREATE TABLE test05 (id INT PRIMARY KEY, name TINYTEXT,age INT, description VARCHAR(65535));
Query OK, 0 rows affected
obclient> ALTER TABLE test05 MODIFY name VARCHAR(256);
Query OK, 0 rows affected
obclient> SHOW CREATE TABLE test05;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test05 | CREATE TABLE `test05` (
`id` int(11) NOT NULL,
`name` varchar(256) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`description` varchar(65535) DEFAULT NULL,
PRIMARY KEY (`id`)
) 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
Conversion between an integer data type and a character data type
obclient> CREATE TABLE test06 (c1 INT);
Query OK, 0 rows affected
obclient> ALTER TABLE test06 MODIFY c1 VARCHAR(64);
Query OK, 0 rows affected
obclient [test]> SHOW CREATE TABLE test06;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test06 | CREATE TABLE `test06` (
`c1` varchar(64) DEFAULT NULL
) 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
obclient> CREATE TABLE test07 (c1 VARCHAR(32));
Query OK, 0 rows affected
obclient> ALTER TABLE test07 MODIFY c1 INT;
Query OK, 0 rows affected
obclient [test]> SHOW CREATE TABLE test07;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test07 | CREATE TABLE `test07` (
`c1` int(11) DEFAULT NULL
) 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
Change the character set and collation of a table or column
Change the character set and collation of a table
In MySQL mode, OceanBase Database allows you to change the character set and collation of a table. Syntax:
ALTER TABLE table_name CHARACTER SET = charset_name COLLATE =collate_name;
Note
The syntax changes only the character set and collation of the schema to which the table belongs. The change applies only to the tables generated later.
Example:
obclient> CREATE TABLE test_collation (c1 INT PRIMARY KEY, c2 VARCHAR(32), c3 VARCHAR(32), UNIQUE KEY idx_test_collation_c2(c2));
Query OK, 0 rows affected
obclient> SHOW CREATE TABLE test_collation;
+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_collation | CREATE TABLE `test_collation` (
`c1` int(11) NOT NULL,
`c2` varchar(32) DEFAULT NULL,
`c3` varchar(32) DEFAULT NULL,
PRIMARY KEY (`c1`),
UNIQUE KEY `idx_test_collation_c2` (`c2`) BLOCK_SIZE 16384 LOCAL
) 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
obclient [test]> ALTER TABLE test_collation CHARACTER SET = utf16 COLLATE = utf16_general_ci;
Query OK, 0 rows affected
obclient [test]> SHOW CREATE TABLE test_collation;
+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_collation | CREATE TABLE `test_collation` (
`c1` int(11) NOT NULL,
`c2` varchar(32) CHARACTER SET utf8mb4 DEFAULT NULL,
`c3` varchar(32) CHARACTER SET utf8mb4 DEFAULT NULL,
PRIMARY KEY (`c1`),
UNIQUE KEY `idx_test_collation_c2` (`c2`) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf16 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
Change the character set and collation of existing data in a table
In MySQL mode, OceanBase Database allows you to change the character set and collation of existing data in a table. Syntax:
ALTER TABLE table_name CONVERT TO CHARACTER SET charset_name COLLATE collate_name;
Note
The syntax changes the character set and collation not only for existing data in the table but also for the schema to which the table belongs.
Example:
obclient> ALTER TABLE test_collation CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
Query OK, 0 rows affected
Change the character set and collation of a column
In MySQL mode, OceanBase Database allows you to change the character set and collation of a column. Syntax:
ALTER TABLE table_name MODIFY COLUMN column_name data_type COLLATE collate_name;
Example:
obclient> ALTER TABLE test_collation MODIFY COLUMN c2 VARCHAR(32) COLLATE utf8mb4_bin;
Query OK, 0 rows affected
obclient> SHOW CREATE TABLE test_collation;
+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_collation | CREATE TABLE `test_collation` (
`c1` int(11) NOT NULL,
`c2` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
`c3` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`c1`),
UNIQUE KEY `idx_test_collation_c2` (`c2`) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin 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