After a table is created, you can use the ALTER TABLE statement to modify it.
Change the collation and character set of a table
If you do not explicitly define the collation and character set for a table during creation, the database's default character set and collation are used. For detailed information about the database's collation and character set, see Database-level character set and collation.
After a table is created, you can modify its collation and character set settings using the following statement:
ALTER TABLE table_name [[DEFAULT] CHARACTER SET [=] charset_name] [COLLATE [=] collation_name];
For detailed information about the collation and character set of tables in OceanBase AI Database, see Table-level character set and collation.
Notice
Modifying the collation and character set of a table only affects the data storage method for columns of character types added subsequently; it does not modify the data storage method for existing columns of character types.
Example:
Create a table named
tbl1.obclient> CREATE TABLE tbl1 (c1 int, c2 varchar(32), c3 varchar(32), PRIMARY KEY(c1), UNIQUE KEY uk1(c2));Modify the collation and character set of the table.
obclient> ALTER TABLE tbl1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin;If the table already contains data, you can modify the collation and character set of the existing data and set the table's collation and character set to the corresponding values. Example:
obclient> ALTER TABLE tbl1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
Modify table structure
OceanBase AI Database supports adding columns, modifying columns and their attributes, and dropping columns.
Add a column
You can add a column to a table, but you cannot directly add a primary key column. If you need to add a primary key column, you can first add the column and then set it as the primary key. For more information about adding a primary key, see Define column constraints.
Assume there is a table named test with the following structure.
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c2 | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set
Add a column named
c3.obclient> ALTER TABLE test ADD c3 int;After the modification, execute the statement
DESCRIBE testto query the table structure. The result is as follows.+-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | c1 | int(11) | NO | PRI | NULL | | | c2 | varchar(50) | YES | | NULL | | | c3 | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in setAdd a column named
c4after thec1column.obclient> ALTER TABLE test ADD COLUMN c4 INT NULL AFTER c1;After the modification, execute the statement
DESCRIBE testto query the table structure. The result is as follows.+-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | c1 | int(11) | NO | PRI | NULL | | | c4 | int(11) | YES | | NULL | | | c2 | varchar(50) | YES | | NULL | | | c3 | int(11) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in setAdd a column named
c6before thec1column.obclient> ALTER TABLE test ADD COLUMN c6 INT NULL BEFORE c1;After the modification, execute the statement
DESCRIBE testto query the table structure. The result is as follows.+-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | c6 | int(11) | YES | | NULL | | | c1 | int(11) | NO | PRI | NULL | | | c4 | int(11) | YES | | NULL | | | c2 | varchar(50) | YES | | NULL | | | c3 | int(11) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 5 rows in setThe field definitions are described as follows:
Field: The name of the column.Type: The data type of the column.Null: Indicates whether the column allows NULL.NOindicates that the column does not allow NULL, whileYESindicates that the column allows NULL.Key: Indicates the column constraint. If the column constraint is a primary key, the value of theKeyfield isPRI.
Modify column attributes
You can rename a column, change the data type of a column, modify the default value, and modify the Skip Index attribute.
Rename a column
When you use the RENAME COLUMN statement to rename a column, note the following:
If the column to be renamed is indexed or is a foreign key column, the column name can be modified, and the indexes and foreign key constraints are automatically modified.
If the column to be renamed is referenced by a view or stored procedure, the column name can be modified, but you must manually modify the definition of the view or stored procedure.
You cannot rename and drop a column at the same time.
You cannot rename a column and perform a partition operation (such as adding or dropping a partition) at the same time.
Assume there is a table named test with the following structure.
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c2 | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set
The following example renames column c2 to c in table test.
obclient> ALTER TABLE test RENAME COLUMN c2 TO c;
After the modification, execute the statement DESCRIBE test to query the table structure. The result is as follows.
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set
Note that renaming a column will fail in the following scenarios:
The target column name already exists in the current table.
There is a special case where you can rename columns that reference each other in a cyclic manner. For example, the following statement renames the original
c1column toc2and the originalc2column toc1:ALTER TABLE test RENAME COLUMN c1 TO c2, RENAME COLUMN c2 TO c1;.The column to be renamed is referenced by a generated column expression.
The column to be renamed is referenced by a partitioning expression.
The column to be renamed is referenced by a CHECK constraint.
Modify column type
For more information about the column data type conversion rules in MySQL mode of OceanBase AI Database, see Column type change rules.
Assume there is a table named test with the following structure.
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c2 | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set
The following example changes column c2 to the CHAR type in table test.
obclient> ALTER TABLE test MODIFY c2 CHAR(60);
After the modification, execute the statement DESCRIBE test to query the table structure. The result is as follows.
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c2 | char(60) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set
Change column type while renaming the column
Assume there is a table named test with the following structure.
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c2 | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set
The following example shows how to rename column c2 to c and change its data type to CHAR in table test.
obclient> ALTER TABLE test CHANGE COLUMN c2 c CHAR(60);
After the modification, execute the statement DESCRIBE test to query the table structure. The result is as follows.
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c | char(60) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set
Modify the default value of a column
The following example shows how to set the default value of a column to 2.
obclient> ALTER TABLE test CHANGE COLUMN c c2 varchar(50) DEFAULT 2;
After the modification, execute the statement DESCRIBE test to query the table structure. The result is as follows.
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c2 | varchar(50) | YES | | 2 | |
+-------+-------------+------+-----+---------+-------+
2 rows in set
Alternatively, you can use the following statement to modify the default value of a column.
ALTER TABLE table_name ALTER [COLUMN] column_name {SET DEFAULT const_value | DROP DEFAULT}
Modify the Skip Index attribute of a column
OceanBase AI Database allows you to add, modify, and delete the Skip Index attribute of a column by using the ALTER TABLE statement.
For more information about Skip Index, see Column Skip Index attribute.
Example:
Use the following SQL statement to create table
test_skidx.CREATE TABLE test_skidx( col1 INT SKIP_INDEX(MIN_MAX, SUM), col2 FLOAT SKIP_INDEX(MIN_MAX), col3 VARCHAR(1024) SKIP_INDEX(MIN_MAX), col4 CHAR(10) );Change the Skip Index attribute of column
col2in tabletest_skidxto theSUMSkip Index type.ALTER TABLE test_skidx MODIFY COLUMN col2 FLOAT SKIP_INDEX(SUM);Add a
MIN_MAXSkip Index attribute to columncol4in tabletest_skidx.ALTER TABLE test_skidx MODIFY COLUMN col4 CHAR(10) SKIP_INDEX(MIN_MAX);Delete the Skip Index attribute of column
col1in tabletest_skidx.ALTER TABLE test_skidx MODIFY COLUMN col1 INT SKIP_INDEX();or
ALTER TABLE test_skidx MODIFY COLUMN col1 INT;
Modify the collation and character set of a column
If you do not specify the character set and collation of a column when you add it, the column uses the character set and collation of the table by default. You can modify the collation and character set of a column as needed.
Assume the tbl1 table is created with the following statement.
obclient> CREATE TABLE tbl1 (c1 int, c2 varchar(32), c3 varchar(32), PRIMARY KEY(c1), UNIQUE KEY uk1(c2));
The following example shows how to modify the collation of column c2 in table tbl1.
obclient> ALTER TABLE tbl1 MODIFY COLUMN c2 varchar(32) COLLATE utf8mb4_bin;
For more information about the collation and character set of a column in OceanBase AI Database, see Column-level character set and collation.
Drop a column
You can drop regular columns and indexed columns from a table, but you cannot drop a primary key column.
Assume there is a table named test with the following structure.
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c | char(60) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set
Drop a regular column from a table.
Assume there is a table named
testwith the following structure.+-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | c1 | int(11) | NO | PRI | NULL | | | c | char(60) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in setExecute the following statement to drop column
c.obclient> ALTER TABLE test DROP c;After dropping the column, execute the
DESCRIBE teststatement to query the table structure. The result is as follows.+-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | c1 | int(11) | NO | PRI | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in setDrop an indexed column from a table.
Assume there is a table named
testwith the following structure.+-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | c1 | int(11) | NO | PRI | NULL | | | c2 | varchar(32) | YES | MUL | NULL | | | c3 | varchar(32) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in setExecute the following statement to drop column
c2.obclient> ALTER TABLE test DROP c2;After the columns are dropped, execute the statement
DESCRIBE testto query the table structure. The result is as follows.+-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | c1 | int(11) | NO | PRI | NULL | | | c3 | varchar(32) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set
Drop multiple columns
The SQL syntax for dropping multiple columns is as follows:
ALTER TABLE table_name DROP COLUMN column_name1, DROP COLUMN column_name2, ...;
Example:
Create a test table named
test_tbl5.CREATE TABLE test_tbl5 ( col1 NUMBER(38), col2 VARCHAR(50), col3 NUMBER(38), col4 NUMBER(38), col5 NUMBER(38), col6 NUMBER(38), col7 NUMBER(38), PRIMARY KEY(col1));Drop columns
col4andcol5from thetest_tbl5table.ALTER TABLE test_tbl5 DROP COLUMN col4, DROP COLUMN col5;View the structure of the
test_tbl5table.DESC test_tbl5;The query result is as follows:
+-------+--------------+------+------+---------+-------+ | FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA | +-------+--------------+------+------+---------+-------+ | COL1 | NUMBER(38) | NO | PRI | NULL | NULL | | COL2 | VARCHAR(50) | YES | NULL | NULL | NULL | | COL3 | NUMBER(38) | YES | NULL | NULL | NULL | | COL6 | NUMBER(38) | YES | NULL | NULL | NULL | | COL7 | NUMBER(38) | YES | NULL | NULL | NULL | +-------+--------------+------+------+---------+-------+ 3 rows in set
Clear obsolete columns
When columns are dropped, they still occupy physical storage space even though they are no longer in use. To remove these obsolete columns and reclaim the associated space, you must clear them.
The SQL syntax for clearing obsolete columns is as follows:
ALTER TABLE table_name FORCE;
Example:
ALTER TABLE test_tbl5 FORCE;
Modify an index
OceanBase AI Database allows you to add unique indexes, normal indexes, and full-text indexes to a table. You can also modify the attributes of an index.
Add a unique index
OceanBase AI Database supports adding a unique index to a table after the table is created. If a primary key is set when creating the table, OceanBase AI Database creates a unique index on the primary key column by default.
Example:
Create a table named
test.obclient> CREATE TABLE test (c1 int PRIMARY KEY, c2 VARCHAR(50));Add a unique index to the table.
obclient> ALTER TABLE test ADD UNIQUE INDEX index_name(c2);
Add a normal index
OceanBase AI Database supports adding multiple indexes at once. You can use either
INDEXorKEYas the index keyword.Example:
Create a table named
test.obclient> CREATE TABLE test (c1 int PRIMARY KEY, c2 VARCHAR(50));Add an index named
myidxto both columnsc1andc2of the table.obclient> ALTER TABLE test ADD INDEX myidx(c1,c2);
Add a full-text index
OceanBase AI Database supports adding a full-text index to an existing table.
Example:
Create a table named
test.CREATE TABLE test(col1 INT, col2 VARCHAR(20), col3 VARCHAR(50));Add a full-text index named
ft_idx1_testto thetesttable, specifying the tokenizer for the full-text index asBENG.ALTER TABLE test ADD FULLTEXT INDEX ft_idx1_test(col2) WITH PARSER BENG;
Drop an index
OceanBase AI Database supports dropping indexes. When dropping multiple indexes, separate them with commas. You can use either
INDEXorKEYas the index keyword. Example:obclient> ALTER TABLE test DROP KEY index_name, DROP KEY index_name1;
Change the primary key, foreign key, and CHECK constraint of a table
OceanBase AI Database supports changing the primary key, foreign key, and CHECK constraint of a table. For specific operations and detailed instructions, see Define column constraints.
Change the number of table replicas
Set the number of table replicas to 2. Example:
obclient> ALTER TABLE test SET REPLICA_NUM=2;
Rename a table
After a table is created, you can rename it. OceanBase AI Database supports renaming tables.
Example:
obclient> ALTER TABLE test RENAME TO t1;
You can also use the following statement:
obclient> RENAME TABLE test TO t1;
Convert between row-based and columnar storage for tables
By default, when you create a table in OceanBase AI Database, it is a rowstore table. You can explicitly create it as a columnstore table or a hybrid rowstore-columnstore table by specifying the WITH COLUMN GROUP option.
After a table is created, you can use the ALTER TABLE statement to convert the table between rowstore and columnstore formats. The syntax is as follows:
To convert a table to a columnstore table:
ALTER TABLE table_name ADD COLUMN GROUP([all columns,] each column) [DELAYED];To remove the storage format of a table:
ALTER TABLE table_name DROP COLUMN GROUP([all columns,] each column);
The parameters are described as follows:
table_name: The name of the table.ADD COLUMN GROUP(all columns, each column): Converts the table to a hybrid rowstore-columnstore table.ADD COLUMN GROUP(each column): Converts the table to a columnstore table.DELAYED: Optional. Indicates that the command to convert the table to a columnstore table is executed asynchronously. After the command is executed, the storage format specified in the table definition is modified, but the actual conversion from rowstore to columnstore occurs during a major compaction task. This operation does not block current DML and is an online DDL operation. IfDELAYEDis not specified, the conversion is performed synchronously and is an offline DDL operation.Note
- Currently,
DELAYEDcan only be specified when converting a rowstore table to a columnstore table (each column) or to a hybrid rowstore-columnstore table (all columns, each column). - After executing the
DELAYEDcommand to convert a rowstore table to a columnstore table asynchronously, query performance may not meet expectations before the baseline data is fully compacted because the data storage format has not been actually converted.
- Currently,
DROP COLUMN GROUP(all columns, each column): Removes the hybrid rowstore-columnstore storage format from the table.DROP COLUMN GROUP(all columns): Removes the rowstore format from the table.DROP COLUMN GROUP(each column): Removes the columnstore format from the table.
Convert a rowstore table to a columnstore table
Note
When you convert a rowstore table created by using the WITH COLUMN GROUP(all columns) option to a columnstore table, you must execute the DROP COLUMN GROUP(all columns) statement to delete the column group after you set ADD COLUMN GROUP(each column).
Example:
Create a rowstore table by default.
Create a rowstore table named
tbl1.CREATE TABLE tbl1(col1 INT, col2 VARCHAR(30), col3 DATE);Convert the rowstore table
tbl1to a columnstore table.The modification for Offline DDL is as follows:
ALTER TABLE tbl1 ADD COLUMN GROUP(each column);The modification for Online DDL (asynchronously converting a rowstore table to a columnstore table) is as follows:
ALTER TABLE tbl1 ADD COLUMN GROUP(each column) DELAYED;
A rowstore table created by setting the
WITH COLUMN GROUP(all columns)option.Create a rowstore table named
tbl1_ac.CREATE TABLE tbl1_ac(col1 INT, col2 VARCHAR(30), col3 DATE) WITH COLUMN GROUP(all columns);Convert the rowstore table
tbl1_acto a columnstore table.The modification for Offline DDL is as follows:
ALTER TABLE tbl1_ac ADD COLUMN GROUP(each column);The modification for Online DDL (asynchronously converting a rowstore table to a columnstore table) is as follows:
ALTER TABLE tbl1_ac ADD COLUMN GROUP(each column) DELAYED;
Drop the rowstore format by setting
DROP COLUMN GROUP(all columns).ALTER TABLE tbl1_ac DROP COLUMN GROUP(all columns);
Convert a rowstore table to a hybrid rowstore-columnstore table
Example:
Create a rowstore table named
tbl2.CREATE TABLE tbl2(col1 INT, col2 VARCHAR(30), col3 DATE);Convert the rowstore table
tbl2to a hybrid rowstore-columnstore table.The modification for Offline DDL is as follows:
ALTER TABLE tbl2 ADD COLUMN GROUP(all columns, each column);The modification for Online DDL (asynchronously converting a rowstore table to a hybrid rowstore-columnstore table) is as follows:
ALTER TABLE tbl2 ADD COLUMN GROUP(all columns, each column) DELAYED;
Convert a hybrid rowstore-columnstore table to a columnstore table
Example:
Create a hybrid rowstore-columnstore table named
tbl3.CREATE TABLE tbl3(col1 INT, col2 VARCHAR(30), col3 DATE) WITH COLUMN GROUP(all columns, each column);Convert the hybrid rowstore-columnstore table
tbl3to a columnstore table.ALTER TABLE tbl3 DROP COLUMN GROUP(all columns);
Convert a hybrid rowstore-columnstore table to a rowstore table
Example:
Create a hybrid rowstore-columnstore table named
tbl4.CREATE TABLE tbl4(col1 INT, col2 VARCHAR(30), col3 DATE) WITH COLUMN GROUP(all columns, each column);Convert the hybrid rowstore-columnstore table
tbl4to a rowstore table.ALTER TABLE tbl4 DROP COLUMN GROUP(each column);or
ALTER TABLE tbl4 DROP COLUMN GROUP(all columns, each column);
Modify the clustered columns of an existing table
Notice
Modifying the clustered columns of an existing table triggers data reorganization, which may take a long time. Perform this operation during off-peak hours.
For an existing table, you can use the ALTER TABLE statement to add or modify clustered columns. The SQL statement is as follows:
ALTER TABLE table_name [SET] CLUSTER BY (column_name_list);
column_name_list:
column_name [, column_name ...]
Example:
Modify the clustered columns of the cb_tbl1 table to sort only by col4.
obclient> ALTER TABLE cb_tbl1 CLUSTER BY (col4);
Drop a clustered column from an existing table
Note
After you drop a clustered column, the data in the table is no longer physically ordered and reverts to a heap structure.
If you no longer need a clustered column or want to adjust the table structure, you can use the ALTER TABLE statement to drop it. The SQL statement is as follows:
ALTER TABLE table_name DROP CLUSTERING KEY;
Example:
Drop the clustered column from the cb_tbl1 table.
obclient> ALTER TABLE cb_tbl1 DROP CLUSTERING KEY;
References
For more information about the ALTER TABLE statement, see ALTER TABLE.