Modify a table

After you create a table, you can use the ALTER TABLE statement to modify the table.

Change the collation and character set of a table

When you create a table, if you do not explicitly specify the collation and character set for the table, the collation and character set of the database are used by default. For more information about the collation and character set of the database, see Database-level collation and character set.

After a table is created, you can modify the collation and character set of the table. The statement is as follows:

ALTER TABLE table_name [[DEFAULT] CHARACTER SET [=] charset_name] [COLLATE [=] collation_name];

For more information about the collation and character set of a table in OceanBase Database, see Table-level collation and character set.

Here is an example:

1. Create a table named tbl1.

   obclient> CREATE TABLE tbl1 (c1 int, c2 varchar(32), c3 varchar(32), PRIMARY KEY(c1), UNIQUE KEY uk1(c2));
   

2. Change the collation and character set of the table.

   obclient> ALTER TABLE tbl1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin;
   

   If the table contains data, you can change the collation and character set of the existing data in the table and set the collation and character set of the table to the corresponding values. Here is an example:

   obclient> ALTER TABLE tbl1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
   

Modify the table structure

OceanBase Database supports adding columns, modifying columns and their attributes, and deleting columns.

Add columns

You can add columns to a table, but you cannot directly add a primary key column. To add a primary key column, first add the column, and then add the primary key. For information about how to define the primary key column, see Define the column constraint type.

Suppose that you have a table named test. The table schema is as follows:

+-------+-------------+------+-----+---------+-------+
| 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 operation, execute the DESCRIBE test statement to view the schema of the table. The output 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 set
  

• Add a column named c4 after the c1 column.

  obclient> ALTER TABLE test ADD COLUMN c4 INT NULL AFTER c1;
  

  After the operation, execute the DESCRIBE test statement to view the schema of the table. The output 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 set
  

• Add a column named c6 before the c1 column.

  obclient> ALTER TABLE test ADD COLUMN c6 INT NULL BEFORE c1;
  

  After the operation, execute the DESCRIBE test statement to view the schema of the table. The output 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 set
  

  The 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. NO indicates that the column does not allow NULL, while YES indicates that the column allows NULL.

  • Key: indicates the column constraint. If the column constraint is a primary key, the value of the Key field is PRI.

Change 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 views and stored procedures, the column name can be modified, but you must manually modify the definitions of views and stored procedures.

• You cannot perform the column renaming and deletion operations at the same time.

• You cannot perform the column renaming and partition operation (add/delete a partition, for example) at the same time.

Suppose that you have a table named test. The table schema is as follows:

+-------+-------------+------+-----+---------+-------+
| 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 the c2 column to the c column in the test table.

obclient> ALTER TABLE test RENAME COLUMN c2 TO c;

After the operation, execute the DESCRIBE test statement to view the schema of the table. The output is as follows:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | int(11)     | NO   | PRI | NULL    |       |
| c     | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set

The column renaming operation can fail in the following scenarios:

• The target column name already exists in the current table.

  Special Scenario: You can rename columns that are referenced by other columns in a cyclic manner. For example, in the following statement, the original c1 column is renamed to c2, and the original c2 column is renamed to c1: ALTER TABLE test RENAME COLUMN c1 TO c2, RENAME COLUMN c2 TO c1;.

• The column to be renamed is referenced by generated column expressions.

• The column to be renamed is referenced by partitioning expressions.

• The column to be renamed is referenced by CHECK constraints.

Change the data type of a column

For more information about the data type conversion rules of columns in the MySQL mode of OceanBase Database, see Column data type change rules.

Suppose that you have a table named test. The table schema is as follows:

+-------+-------------+------+-----+---------+-------+
| 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 change the data type of the c2 column to that of the CHAR type.

obclient> ALTER TABLE test MODIFY c2 CHAR(60);

After the operation, execute the DESCRIBE test statement to view the schema of the table. The output is as follows:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | int(11)     | NO   | PRI | NULL    |       |
| c2    | char(60)    | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set

Rename a column and change its data type at the same time

Suppose that you have a table named test. The table schema is as follows:

+-------+-------------+------+-----+---------+-------+
| 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 the c2 column to the c column and change the data type of the column to the CHAR type.

obclient> ALTER TABLE test CHANGE COLUMN c2 c CHAR(60);

After the operation, execute the DESCRIBE test statement to view the schema of the table. The output 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 change the default value of a column to 2:

obclient> ALTER TABLE test CHANGE COLUMN c c2 varchar(50) DEFAULT 2;

After the operation, execute the DESCRIBE test statement to view the schema of the table. The output is as follows:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | int(11)     | NO   | PRI | NULL    |       |
| c2    | varchar(50) | YES  |     | 2       |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set

You can also use the following statement to change 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

OceanBase 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 the Skip Index feature, see Column Skip Index attributes.

Here is an example:

1. Execute the following SQL statement to create a table named 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)
     );
   

2. Change the Skip Index attribute of the col2 column in the test_skidx table to the SUM Skip Index type.

   ALTER TABLE test_skidx MODIFY COLUMN col2 FLOAT SKIP_INDEX(SUM);
   

3. Add a MIN_MAX Skip Index attribute to the col4 column in the test_skidx table.

   ALTER TABLE test_skidx MODIFY COLUMN col4 CHAR(10) SKIP_INDEX(MIN_MAX);
   

4. Delete the Skip Index attribute of the col1 column in the test_skidx table.

   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 will use the character set and collation of the table by default. You can modify the collation and character set of a column as needed.

Assume that 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 the c2 column in the tbl1 table.

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 Database, see Column-level character set and collation.

Delete a column

You can delete a regular column or an indexed column from a table. However, you cannot delete a primary key column.

Assume that the test table is created with the following statement.

+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c1    | int(11)  | NO   | PRI | NULL    |       |
| c     | char(60) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set

• Delete a regular column.

  Assume that the test table is created with the following statement.

  +-------+----------+------+-----+---------+-------+
  | Field | Type     | Null | Key | Default | Extra |
  +-------+----------+------+-----+---------+-------+
  | c1    | int(11)  | NO   | PRI | NULL    |       |
  | c     | char(60) | YES  |     | NULL    |       |
  +-------+----------+------+-----+---------+-------+
  2 rows in set
  

  Execute the following statement to delete the c column.

  obclient> ALTER TABLE test DROP c;
  

  After the column is deleted, execute the DESCRIBE test statement 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 set
  

• Delete an indexed column.

  Assume that the test table is created with the following statement.

  +-------+-------------+------+-----+---------+-------+
  | 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 set
  

  Execute the following statement to delete the c2 column.

  obclient> ALTER TABLE test DROP c2;
  

  After the column is deleted, execute the DESCRIBE test statement to 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
  

Delete multiple columns

The SQL syntax for deleting multiple columns is as follows:

ALTER TABLE table_name DROP COLUMN column_name1, DROP COLUMN column_name2, ...;

Here is an example:

1. 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));
   

2. Delete the col4 and col5 columns from the test_tbl5 table.

   ALTER TABLE test_tbl5 DROP COLUMN col4, DROP COLUMN col5;
   

3. View the structure of the test_tbl5 table.

   DESC test_tbl5;
   

   The return 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
   

Drop obsolete columns

When columns are deleted, they still occupy physical storage space even if they are no longer used. To remove these obsolete columns and reclaim the related space, you need to drop the obsolete columns.

The SQL syntax for dropping obsolete columns is as follows:

ALTER TABLE table_name FORCE;

Here is an example:

ALTER TABLE test_tbl5 FORCE;

Modify an index

OceanBase 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

  After a table is created, you can add a unique index to the table. If a primary key is specified when the table is created, OceanBase Database automatically creates a unique index for the primary key column.

  Here is an example of adding a unique index to a table:

  1. Create a table named test.

     obclient> CREATE TABLE test (c1 int PRIMARY KEY, c2 VARCHAR(50));
     

  2. Add a unique index to the table.

     obclient> ALTER TABLE test ADD UNIQUE INDEX index_name(c2);
     

• Add a normal index

  OceanBase Database allows you to add multiple indexes at a time. You can use the INDEX or KEY keyword to add an index.

  Here is an example of adding a normal index to a table:

  1. Create a table named test.

     obclient> CREATE TABLE test (c1 int PRIMARY KEY, c2 VARCHAR(50));
     

  2. Add an index named myidx to the c1 and c2 columns of the table.

     obclient> ALTER TABLE test ADD INDEX myidx(c1,c2);
     

• Add a full-text index

  OceanBase Database allows you to add a full-text index to an existing table.

  Here is an example:

  1. Create a table named test.

     CREATE TABLE test(col1 INT, col2 VARCHAR(20), col3 VARCHAR(50));
     

  2. Add a full-text index named ft_idx1_test to the test table and specify the BENG tokenizer.

     ALTER TABLE test ADD FULLTEXT INDEX ft_idx1_test(col2) WITH PARSER BENG;
     

• Drop an index

  OceanBase Database allows you to drop an index. When you drop multiple indexes, separate them with commas. You can use the INDEX or KEY keyword to drop an index. Here is an example:

  obclient> ALTER TABLE test DROP KEY index_name, DROP KEY index_name1;
  

Change the primary key, foreign key, and check constraints of a table

OceanBase Database allows you to change the primary key, foreign key, and check constraints of a table. For more information, see Define the constraint type for a column.

Change the number of table replicas

Set the number of table replicas to 2. Here is an example:

obclient> ALTER TABLE test SET REPLICA_NUM=2;

Rename a table

After a table is created, you can rename it. OceanBase Database allows you to rename a table.

Here is an example:

obclient> ALTER TABLE test RENAME TO t1;

You can also execute the following statement:

obclient> RENAME TABLE test TO t1;

Convert a table from a rowstore to a columnstore

By default, OceanBase Database creates a rowstore table. You can explicitly specify the table as a columnstore or a rowstore with columnstore redundancy by using the WITH COLUMN GROUP option.

After a table is created, you can convert it from a rowstore to a columnstore or from a rowstore with columnstore redundancy to a columnstore by using the ALTER TABLE statement. The syntax is as follows:

• Convert a table to a columnstore table:

  ALTER TABLE table_name ADD COLUMN GROUP([all columns,] each column) [DELAYED];
  

• Remove the storage format of a table:

  ALTER TABLE table_name DROP COLUMN GROUP([all columns,] each column);
  

Parameter description:

• table_name: specifies the name of the table.

• ADD COLUMN GROUP(all columns, each column): specifies that the table is converted to a rowstore with columnstore redundancy.

• ADD COLUMN GROUP(each column): specifies that the table is converted to a columnstore.

• DELAYED: specifies that the conversion from a rowstore to a columnstore is performed asynchronously. After the conversion is initiated, the storage format of the table is modified, but the actual conversion is performed during a major compaction. This operation does not block current DML operations and is an online DDL operation. If you do not specify DELAYED, the default is offline DDL, which synchronously converts the rowstore to a columnstore.

  Notice

  • Currently, you can specify DELAYED only when you convert a rowstore to a columnstore (each column) or a rowstore with columnstore redundancy to a columnstore (all columns, each column).
  • After you execute the DELAYED option to convert a rowstore to a columnstore, the query performance may not meet your expectations until the baseline data is actually compacted.

• DROP COLUMN GROUP(all columns, each column): specifies that the rowstore with columnstore redundancy is removed.

• DROP COLUMN GROUP(all columns): specifies that the rowstore is removed.

• DROP COLUMN GROUP(each column): specifies that the columnstore is removed.

Convert a rowstore to a columnstore

Here is an example:

• Create a rowstore table by default.

  1. Create a rowstore table named tbl1.

     CREATE TABLE tbl1(col1 INT, col2 VARCHAR(30), col3 DATE);
     

  2. Convert the rowstore table tbl1 to a columnstore.

     • Offline DDL:

       ALTER TABLE tbl1 ADD COLUMN GROUP(each column);
       

     • Online DDL:

       ALTER TABLE tbl1 ADD COLUMN GROUP(each column) DELAYED;
       

• Create a rowstore table by using the WITH COLUMN GROUP(all columns) option.

  1. Create a rowstore table named tbl1.

     CREATE TABLE tbl1_ac(col1 INT, col2 VARCHAR(30), col3 DATE) WITH COLUMN GROUP(all columns);
     

  2. Convert the rowstore table tbl1 to a columnstore.

     • Offline DDL:

       ALTER TABLE tbl1_ac ADD COLUMN GROUP(each column);
       

     • Online DDL:

       ALTER TABLE tbl1_ac ADD COLUMN GROUP(each column) DELAYED;
       

  3. Remove the rowstore format by using the DROP COLUMN GROUP(all columns) option.

     ALTER TABLE tbl1_ac DROP COLUMN GROUP(all columns);
     

Convert a rowstore to a rowstore with columnstore redundancy

Here is an example:

1. Create a rowstore table named tbl2.

   CREATE TABLE tbl2(col1 INT, col2 VARCHAR(30), col3 DATE);
   

2. Convert the rowstore table tbl2 to a rowstore with columnstore redundancy.

   • Offline DDL:

     ALTER TABLE tbl2 ADD COLUMN GROUP(all columns, each column);
     

   • Online DDL:

     ALTER TABLE tbl2 ADD COLUMN GROUP(all columns, each column) DELAYED;
     

Convert a rowstore with columnstore redundancy to a columnstore

Here is an example:

1. Create a rowstore with columnstore redundancy named tbl3.

   CREATE TABLE tbl3(col1 INT, col2 VARCHAR(30), col3 DATE) WITH COLUMN GROUP(all columns, each column);
   

2. Convert the rowstore with columnstore redundancy tbl3 to a columnstore.

   ALTER TABLE tbl3 DROP COLUMN GROUP(all columns);
   

Convert a rowstore with columnstore redundancy to a rowstore

Here is an example:

1. Create a rowstore with columnstore redundancy named tbl4.

   CREATE TABLE tbl4(col1 INT, col2 VARCHAR(30), col3 DATE) WITH COLUMN GROUP(all columns, each column);
   

2. Convert the rowstore with columnstore redundancy tbl4 to a rowstore.

   ALTER TABLE tbl4 DROP COLUMN GROUP(each column);
   

   or

   ALTER TABLE tbl4 DROP COLUMN GROUP(all columns, each column);
   

Modify the clustered column of an existing table

You can use the ALTER TABLE statement to add or modify the clustered column of an existing table. The SQL statement is as follows:

ALTER TABLE table_name [SET] CLUSTER BY (column_name_list);

column_name_list:
    column_name [, column_name ...]

Here is an example:

Change the clustered column of the cb_tbl1 table to be sorted only by col4.

obclient> ALTER TABLE cb_tbl1 CLUSTER BY (col4);

Drop an existing clustered column

You can use the ALTER TABLE statement to drop a clustered column. Example:

ALTER TABLE table_name DROP CLUSTERING KEY;

Here is an 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.