Modify a table

After a table is created, 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 specify the collation and character set of the table, the default character set and collation of the database are used. For more information about the collation and character set of a database, see Character sets and collations at the database level.

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, see Character sets and collations at the table level.

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. Modify 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 modify the collation and character set of the existing data in the table and modify the collation and character set of the table to the corresponding settings. Example:

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

Modify the schema

OceanBase Database allows you to add columns, modify columns and their attributes, and drop columns.

Add a column

You can add a column to a table, but you cannot add a primary key column. If you need to add a primary key column, you can add the column and then set the primary key for the column. For more information about how to set the primary key, see Define the constraint type for a column.

Assume that a table named test has the following schema.

+-------+-------------+------+-----+---------+-------+
| 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 DESCRIBE test statement to query the schema. 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 set
  

• Add a column named c4 after the c1 column.

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

  After the modification, execute the DESCRIBE test statement to query the schema. 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 set
  

• Add a column named c6 before the c1 column.

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

  After the modification, execute the DESCRIBE test statement to query the schema. 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 set
  

  The following table describes the displayed fields:

  • Field: the column name.

  • Type: the data type of the column.

  • Null: whether the column allows null values. NO indicates that the column does not allow null values. YES indicates that the column allows null values.

  • key: the value PRI indicates that the column is a primary key column.

Modify column attributes

You can rename a column, modify the column type, default value, and skip index attribute of a column.

Rename a column

When you use the RENAME COLUMN statement to rename a column, take note of the following points:

• If the column to be renamed has an index or a foreign key constraint, the column name can be modified, and the index definition and foreign key constraint will be automatically updated.

• If the column to be renamed is referenced by a view or a stored procedure, the column name can be modified, but you need to manually modify the view or stored procedure definition.

• You cannot rename a column and drop the column at the same time.

• You cannot rename a column and modify the partition (add a partition or drop a partition) at the same time.

Assume that a table named test has the following schema.

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

obclient> ALTER TABLE test RENAME COLUMN c2 TO c;

After the modification, execute the DESCRIBE test statement to query the schema. 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

Renaming a column will fail in the following scenarios:

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

  There is a special scenario where renaming a column in a circular manner can succeed. For example, in this case, the original c1 column is renamed to c2, and the original c2 column is renamed to c1. Execute the statement ALTER TABLE test RENAME COLUMN c1 TO c2, rename column c2 TO c1; to successfully rename the columns.

• The column to be renamed is referenced by a generated expression.

• The column to be renamed is referenced by a partition expression.

• The column to be renamed is referenced by a CHECK constraint.

Change the column type

For more information about the conversion rules of column types in MySQL mode of OceanBase Database, see Change the column type.

Assume that a table named test has the following schema.

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

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

After the modification, execute the DESCRIBE test statement to query the schema. 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 the column name and the column type

Assume that a table named test has the following schema.

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

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

After the modification, execute the DESCRIBE test statement to query the schema. 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

Change 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 modification, execute the DESCRIBE test statement to query the schema. 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

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}

Change the Skip Index attribute of a column

OceanBase Database allows you to add, modify, and drop the Skip Index attribute by using the ALTER TABLE statement.

For more information about the Skip Index attribute, see Column Skip Index attribute.

Here is an example:

1. Run 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 the Skip Index attribute to a new column after the table is created. Add the 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. Drop the Skip Index attribute from a column after the table is created. Drop the Skip Index attribute from 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;
   

Change the collation and character set of a column

If you do not specify the character set and collation of a column in the column definition, the column uses the character set and collation of the table by default. You can modify the collation and character set of a column based on your business requirements.

Assume that the tbl1 table is created by using 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 normal column or an indexed column from a table, but you cannot delete the primary key column.

Assume that the test table has the following schema.

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

• Delete a normal column.

  Assume that the test table has the following schema.

  +-------+----------+------+-----+---------+-------+
  | 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 schema. The execution 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 has the following schema.

  +-------+-------------+------+-----+---------+-------+
  | 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 schema. The execution 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 schema of the test_tbl5 table.

   DESC test_tbl5;
   

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

Purge obsolete columns

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

The SQL syntax for purging 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, and also allows you to modify the attributes of an index.

• Add a unique index

  OceanBase Database allows you to add a unique index to a table after the table is created. If a primary key is specified when the table is created, OceanBase Database creates a unique index for the primary key column by default.

  Here is an example:

  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 INDEX or KEY as the index keyword.

  Here is an example:

  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. If you want to drop multiple indexes, separate them with commas. You can use INDEX or KEY as the index keyword. 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 constraints 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 supports renaming tables.

Here is an example:

obclient> ALTER TABLE test RENAME TO t1;

You can also use the following statement:

obclient> RENAME TABLE test TO t1;

Convert a table to a row-store or column-store table

By default, OceanBase Database creates a row-store table when you create a table. You can explicitly specify whether to create a column-store or hybrid row-column store table by using the WITH COLUMN GROUP option.

After a table is created, you can use the ALTER TABLE statement to convert the table to a row-store or column-store table. The syntax is as follows:

• Convert the table to a hybrid row-column store table:

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

• Remove the storage format of the 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): specifies to convert the table to a hybrid row-column store table.

• ADD COLUMN GROUP(each column): specifies to convert the table to a column-store table.

• DELAYED: optional. Specifies to execute the conversion from a row-store table to a column-store table asynchronously. After the statement is executed, the storage format in the table definition is modified, but the actual conversion from a row-store table to a column-store table is performed during a major compaction task. This operation does not block the current DML operation and is an online DDL operation. If DELAYED is not specified, the default is an offline DDL operation, which synchronously converts the row-store table to a column-store table.

  Notice

  • Currently, you can specify DELAYED only when you want to convert a row-store table to a column-store table (each column) or to a hybrid row-column store table (all columns, each column).
  • After you execute the statement that specifies to convert a row-store table to a column-store table asynchronously, the query performance may not meet expectations before the baseline data is compacted because the actual data storage format is not converted.

• DROP COLUMN GROUP(all columns, each column): specifies to remove the hybrid row-column store format from the table.

• DROP COLUMN GROUP(all columns): specifies to remove the row-store format from the table.

• DROP COLUMN GROUP(each column): specifies to remove the column-store format from the table.

Convert a rowstore table to a columnstore table

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

     • Offline DDL modification:

       ALTER TABLE tbl1 ADD COLUMN GROUP(each column);
       

     • Online DDL modification (asynchronous conversion from a rowstore table to a columnstore table):

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

     • Offline DDL modification:

       ALTER TABLE tbl1_ac ADD COLUMN GROUP(each column);
       

     • Online DDL modification (asynchronous conversion from a rowstore table to a columnstore table):

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

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

     ALTER TABLE tbl1_ac DROP COLUMN GROUP(all columns);
     

Convert a rowstore table to a hybrid rowstore-columnstore table

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 hybrid rowstore-columnstore table.

   • Offline DDL modification:

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

   • Online DDL modification (asynchronous conversion from a rowstore table to a hybrid rowstore-columnstore table):

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

Convert a hybrid rowstore-columnstore table to a columnstore table

Here is an example:

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

2. Convert the hybrid rowstore-columnstore table tbl3 to a columnstore table.

   ALTER TABLE tbl3 DROP COLUMN GROUP(all columns);
   

Convert a hybrid rowstore-columnstore table to a rowstore table

Here is an example:

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

2. Convert the hybrid rowstore-columnstore table tbl4 to a rowstore table.

   ALTER TABLE tbl4 DROP COLUMN GROUP(each column);
   

   or

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

References

For more information about the ALTER TABLE statement, see ALTER TABLE.