Column operations

This topic describes the column operations in the MySQL mode of OceanBase Database, including adding a column to the end of a table, adding a column to a position other than the end of a table, dropping a column, renaming a column, relocating a column, changing the data type of a column, managing the default value of a column, managing constraints, and changing the value of an auto-increment column.

Add a column

The syntax for adding a column to the end of a table differs from that for adding a column to a position other than the end of a table.

Add a column to the end of a table

The syntax for adding a column to the end of a table is as follows:

ALTER TABLE table_name ADD COLUMN column_name data_type;

where

• table_name specifies the name of the table to which the column is to be added.

• column_name specifies the name of the column to be added.

• data_type specifies the data type of the column to be added.

Assume that the tbl1 table exists in the database. The following example adds the c1 column to the end of the tbl1 table.

1. View the structure of the tbl1 table.

   obclient> DESCRIBE tbl1;
   

   The result is as follows, showing that the table has three columns, id, name, and age:

   +-------+-------------+------+-----+---------+-------+
   | Field | Type        | Null | Key | Default | Extra |
   +-------+-------------+------+-----+---------+-------+
   | id    | int(11)     | NO   | PRI | NULL    |       |
   | name  | varchar(50) | NO   |     | NULL    |       |
   | age   | int(11)     | YES  |     | NULL    |       |
   +-------+-------------+------+-----+---------+-------+
   

2. Add the c1 column to the end of the table and specify the data type of the column as INT.

   obclient> ALTER TABLE tbl1 ADD COLUMN c1 INT;
   

3. View the structure of the tbl1 table again.

   obclient> DESCRIBE tbl1;
   

   The result is as follows, showing that the c1 column is added to the end of the table:

   +-------+-------------+------+-----+---------+-------+
   | Field | Type        | Null | Key | Default | Extra |
   +-------+-------------+------+-----+---------+-------+
   | id    | int(11)     | NO   | PRI | NULL    |       |
   | name  | varchar(50) | NO   |     | NULL    |       |
   | age   | int(11)     | YES  |     | NULL    |       |
   | c1    | int(11)     | YES  |     | NULL    |       |
   +-------+-------------+------+-----+---------+-------+
   

Add a column to a position other than the end of a table

The syntax for adding a column to a position other than the end of a table is as follows:

ALTER TABLE table_name
    ADD COLUMN new_column_name data_type
    {FIRST | BEFORE | AFTER} column_name;

where

• table_name specifies the name of the table to which the column is to be added.

• new_column_name specifies the name of the column to be added.

• data_type specifies the data type of the column to be added.

• FIRST | BEFORE | AFTER specifies the position to which the column is to be added. FIRST indicates the beginning of the table, and BEFORE or AFTER indicates the position before or after the specified column.

  Notice

  If you use FIRST to add a column to the beginning of a table, you do not need to set column_name. Otherwise, an error is returned.

• column_name specifies the name of the column in the specified relative position to the column to be added.

Assume that the tbl1 table exists in the database and the table structure of tbl1 is as follows:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

The following example adds the c1 column to the beginning of the tbl1 table, and adds the c2 column before the name column and the c3 column after the name column.

1. Add the c1 column to the beginning of the tbl1 table and specify the data type of the column as INT.

   obclient> ALTER TABLE tbl1 ADD COLUMN c1 INT FIRST;
   

2. Add the c2 column before the name column in the tbl1 table and specify the data type of the new column as VARCHAR.

   obclient> ALTER TABLE tbl1 ADD COLUMN c2 VARCHAR(50) BEFORE name;
   

3. Add the c3 column after the name column in the tbl1 table and specify the data type of the new column as VARCHAR and a constraint that the new column cannot be empty.

   obclient> ALTER TABLE tbl1 ADD COLUMN c3 VARCHAR(25) NOT NULL AFTER name;
   

4. View the structure of the tbl1 table again.

   obclient> DESCRIBE tbl1;
   

   The result is as follows:

   +-------+-------------+------+-----+---------+-------+
   | Field | Type        | Null | Key | Default | Extra |
   +-------+-------------+------+-----+---------+-------+
   | c1    | int(11)     | YES  |     | NULL    |       |
   | id    | int(11)     | NO   | PRI | NULL    |       |
   | c2    | varchar(50) | YES  |     | NULL    |       |
   | name  | varchar(50) | NO   |     | NULL    |       |
   | c3    | varchar(25) | NO   |     | NULL    |       |
   | age   | int(11)     | YES  |     | NULL    |       |
   +-------+-------------+------+-----+---------+-------+
   

Drop a column

The syntax for dropping a column is as follows:

ALTER TABLE table_name DROP COLUMN column_name

Here, table_name specifies the name of the table to which the column belongs, and column_name specifies the name of the column to be dropped.

Assume that the tbl1 table exists in the database and the table structure of tbl1 is as follows:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

The following example drops the name column:

obclient> ALTER TABLE tbl1 DROP COLUMN name;

After executing the preceding statement, execute DESCRIBE tbl1; again to view the table structure of tbl1. The result is as follows, showing that the name column is removed from the tbl1 table:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Rename a column

The two syntaxes for renaming a column are as follows:

• Rename a column while changing the type of the column

  ALTER TABLE table_name CHANGE old_col_name new_col_name data_type;
  

  where

  • table_name specifies the name of the table to which the column belongs.

  • old_col_name specifies the original name of the column.

  • new_col_name specifies the new name of the column.

  • data_type specifies the new data type of the column to be renamed. You can specify the current data type or another data type. For more information, see Change the data type of a column below.

• Rename a column only

  ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name
  

  where

  • table_name specifies the name of the table to which the column belongs.

  • old_col_name specifies the original name of the column.

  • new_col_name specifies the new name of the column.

Assume that the tbl1 table exists in the database and the table structure of tbl1 is as follows:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

The following example renames the name column as c2 and the age column as c3 and changes the data type of the age column to VARCHAR.

1. Rename the name column as c2.

   obclient> ALTER TABLE tbl1 RENAME COLUMN name TO c2;
   

2. Rename the age column as c3 and change the data type of the age column to VARCHAR.

   obclient> ALTER TABLE tbl1 CHANGE age c3 VARCHAR(50);
   

3. Execute DESCRIBE tbl1; again to view the table structure of tbl1.

   The output is as follows, showing that the tbl1 table has three columns, id, c2, and c3, and that the data type of the c3 column is VARCHAR.

   +-------+-------------+------+-----+---------+-------+
   | Field | Type        | Null | Key | Default | Extra |
   +-------+-------------+------+-----+---------+-------+
   | id    | int(11)     | NO   | PRI | NULL    |       |
   | c2    | varchar(50) | YES  |     | NULL    |       |
   | c3    | varchar(50) | YES  |     | NULL    |       |
   +-------+-------------+------+-----+---------+-------+
   

Relocate a column

The syntax for relocating a column is as follows:

ALTER TABLE table_name
    MODIFY [COLUMN] column_name data_type
    {FIRST | BEFORE | AFTER} column_name;

where

• table_name specifies the name of the table for which the column is to be relocated.

• column_name specifies the name of the column to be relocated.

• data_type specifies the new data type of the column to be relocated. You can specify the current data type or another data type. For more information, see Change the data type of a column below.

• FIRST | BEFORE | AFTER specifies the position to which the column is to be relocated. FIRST indicates the beginning of the table, and BEFORE or AFTER indicates the position before or after the specified column.

  Notice

  If you use FIRST to relocate a column to the beginning of a table, you do not need to set column_name. Otherwise, an error is returned.

• column_name specifies the name of the column in the specified relative position to the column to be relocated.

Assume that the tbl1 table exists in the database and the table structure of tbl1 is as follows:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

The following example relocates the age column before name column and changes the data type of the age column to VARCHAR.

obclient> ALTER TABLE tbl1 MODIFY COLUMN age VARCHAR(50) BEFORE name;

Execute DESCRIBE tbl1; again to view the table structure of tbl1. The result is as follows, showing that the age column is relocated after the name column and the data type is VARCHAR:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| age   | varchar(50) | YES  |     | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Change the data type of a column

OceanBase Database supports the following data type conversions:

• Conversion between the following character data types: CHAR, VARCHAR, TINYTEXT, TEXT, and LONGTEXT.

• Conversion between the following numeric data types: TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT.

• Conversion between the following binary data types: BINARY, VARBINARY, BLOB, TINYBLOB, MEDIUMBLOB, and LONGBLOB.

• Precision change for the following data types with a precision: VARCHAR, FLOAT, DOUBLE, and DECIMAL.

• Conversion between the following data types with a precision: FLOAT, DOUBLE, and DECIMAL.

• Conversion between the following data types: INT, VARCHAR, DOUBLE, FLOAT, and DECIMAL.

For more information about the rules for changing the data types of columns in OceanBase Database, see Column type change rules.

The syntax for changing the data type of a column is as follows:

ALTER TABLE table_name MODIFY [COLUMN] column_name data_type;

where

• table_name specifies the name of the table to which the column belongs.

• column_name specifies the name of the column whose data type is to be changed.

• data_type specifies the new data type.

Conversions between character data types

Create the test01 table as follows:

obclient> CREATE TABLE test01 (c1 INT PRIMARY KEY, c2 CHAR(10), c3 CHAR(10));

The following examples show how to change the data type and length limit for a column of a character data type.

• Change the data type of the c2 column in the test01 table to VARCHAR, and set the length limit to 20 characters.

  obclient> ALTER TABLE test01 MODIFY c2 VARCHAR(20);
  

• Change the data type of the c2 column in the test01 table to VARCHAR, and set the length limit to 40 characters.

  obclient> ALTER TABLE test01 MODIFY c2 VARCHAR(40);
  

• Change the data type of the c2 column in the test01 table to TINYTEXT.

  obclient> ALTER TABLE test01 MODIFY c2 TINYTEXT;
  

• Change the data type of the c2 column in the test01 table to LONGTEXT.

  obclient> ALTER TABLE test01 MODIFY c2 LONGTEXT;
  

• Change the length limit of the c3 column in the test01 table to 20 characters.

  obclient> ALTER TABLE test01 MODIFY c3 CHAR(20);
  

• Change the data type of the c3 column in the test01 table to VARCHAR, and set the length limit to 30 characters.

  obclient> ALTER TABLE test01 MODIFY c3 VARCHAR(30);
  

Conversion between numeric data types

Integer data types

Create the test02 table as follows:

obclient> CREATE TABLE test02 (id INT PRIMARY KEY, name VARCHAR(10),age TINYINT, description VARCHAR(65525));

The following examples show how to change the data type and length limit for a column of an integer data type.

• Change the data type of the age column in the test02 table to SMALLINT.

  obclient> ALTER TABLE test02 MODIFY age SMALLINT;
  

• Change the data type of the age column in the test02 table to INT.

  obclient> ALTER TABLE test02 MODIFY age INT;
  

• Change the data type of the age column in the test02 table to BIGGINT.

  obclient> ALTER TABLE test02 MODIFY age BIGGINT;
  

Numeric Data types with a precision

Create the test03 table as follows:

obclient> CREATE TABLE test03(c1 INT, c2 FLOAT(8,2), c3 FLOAT(8,2), UNIQUE(c2, c3));

The following examples show how to change the data type and length limit for a column of a numeric data type with a precision.

• Change the maximum precision of the c2 column in the test03 table to 5 digits.

  obclient> ALTER TABLE test03 MODIFY c2 FLOAT(5,2);
  

• Change the data type of the c2 column in the test03 table to DOUBLE, and change the maximum precision of the column to 10 digits, including 4 digits for the fractional part.

  obclient> ALTER TABLE test03 MODIFY c2 DOUBLE(10,4);
  

• Change the data type of the c2 column in the test03 table to DOUBLE, and change the maximum precision of the column to 5 digits, including 2 digits for the fractional part.

  obclient> ALTER TABLE test03 MODIFY c2 DOUBLE(5,2);
  

• Change the data type of the c2 column in the test03 table to DECIMAL, and change the maximum precision of the column to 20 digits, including 4 digits for the fractional part.

  obclient> ALTER TABLE test03 MODIFY c2 DECIMAL(20, 4);
  

Conversion for binary data types

Create the test04 table as follows:

obclient> CREATE TABLE test04 (c1 TINYBLOB, c2 BINARY(64));

The following examples show how to change the data type and length limit for a column of a binary data type.

• Change the data type of the c1 column in the test04 table to BLOB.

  obclient> ALTER TABLE test04 MODIFY c1 BLOB;
  

• Change the data type of the c1 column in the test04 table to BINARY and set the length limit to 256 bytes.

  obclient> ALTER TABLE test04 MODIFY c1 BINARY(256);
  

• Change the data type of the c1 column in the test04 table to VARCHAR and set the length limit to 256 characters.

  obclient> ALTER TABLE test04 MODIFY c1 VARCHAR(256);
  

Conversion between an integer data type and a character data type

Create the test05 table as follows:

obclient> CREATE TABLE test05 (c1 INT);

1. Execute the following statement to change the data type of the c1 column in the test05 table to VARCHAR and set the length limit to 64 characters.

   obclient> ALTER TABLE test05 MODIFY c1 VARCHAR(64);
   

2. Execute the following statement to change the data type of the c1 column in the test05 table to INT.

   obclient> ALTER TABLE test05 MODIFY c1 INT;
   

Manage the default value of a column

In MySQL mode of OceanBase Database, you can change or drop the default value of a column. This section describes how to manage the default value of a column.

Change the default value of a column

If a column is not configured with a default value, the default value is NULL by default. The syntax for changing the default value of a column is as follows:

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT const_value;

where

• table_name specifies the name of the table to which the column belongs.

• column_name specifies the name of the column whose default value is to be changed.

• const_value specifies the new default value of the column.

Assume that the tbl1 table exists in the database and the table structure of tbl1 is as follows:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

The following example changes the default value of the age column to 18:

obclient> ALTER TABLE tbl1 ALTER COLUMN age SET DEFAULT 18;

Execute DESCRIBE tbl1; again to view the table structure of tbl1. The result is as follows, showing that the default value of age is 18:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | int(11)     | YES  |     | 18      |       |
+-------+-------------+------+-----+---------+-------+

Drop the default value of a column

The syntax for dropping the default value of a column is as follows:

ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;

Here, table_name specifies the name of the table to which the column belongs, and column_name specifies the name of the column whose default value is to be dropped.

Assume that the tbl1 table exists in the database and the table structure of tbl1 is as follows:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | int(11)     | YES  |     | 18      |       |
+-------+-------------+------+-----+---------+-------+

The following example drops the default value of the age column:

obclient> ALTER TABLE tbl1 ALTER COLUMN age DROP DEFAULT;

Execute DESCRIBE tbl1; again to view the table structure of tbl1. The result is as follows, showing that the default value of age is NULL:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Manage constraints

In the MySQL mode of OceanBase Database, you can add column constraints to tables. For example, you can change an existing column to an auto-increment column, set or cancel a NOT NULL constraint for a column, and set or cancel a UNIQUE constraint for a column.

The syntax for managing constraints is as follows:

ALTER TABLE table_name
    MODIFY [COLUMN] column_name data_type
    [AUTO_INCREMENT]
    [NULL | NOT NULL]
    [[PRIMARY] KEY]
    [UNIQUE [KEY]];

where

• table_name specifies the name of the table to which the column belongs.

• column_name specifies the name of the column to which the constraint is to be added.

• data_type specifies the new data type of the column. You can specify the current data type or another data type. For more information, see Change the data type of a column.

• AUTO_INCREMENT specifies to set the column as an auto-increment column.

• NULL | NOT NULL specifies whether the column can contain null values (NULL) or cannot contain null values (NOT NULL).

• [PRIMARY] KEY specifies to set the column as the primary key column.

• UNIQUE [KEY]: specifies to set the UNIQUE constraint for the column.

Assume that the tal1 table exists in the database and the table structure of tal1 is as follows:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

1. Set the id column as the primary key column.

   obclient> ALTER TABLE tal1 MODIFY COLUMN id INT PRIMARY KEY;
   

2. Set the id column as an auto-increment column.

   obclient> ALTER TABLE tal1 MODIFY COLUMN id INT AUTO_INCREMENT;
   

3. Set the NOT NULL constraint for the name column.

   obclient> ALTER TABLE tal1 MODIFY COLUMN name VARCHAR(50) NOT NULL;
   

4. Set the UNIQUE constraint for the age column.

   obclient> ALTER TABLE tal1 MODIFY COLUMN age INT UNIQUE;
   

5. Execute DESCRIBE tal1; again to view the table structure of tal1.

   +-------+-------------+------+-----+---------+----------------+
   | Field | Type        | Null | Key | Default | Extra          |
   +-------+-------------+------+-----+---------+----------------+
   | id    | int(11)     | NO   | PRI | NULL    | auto_increment |
   | name  | varchar(50) | NO   |     | NULL    |                |
   | age   | int(11)     | YES  | UNI | NULL    |                |
   +-------+-------------+------+-----+---------+----------------+
   

Change the value of an auto-increment column

The syntax for changing the value of an auto-increment column is as follows:

ALTER TABLE table_name [SET] AUTO_INCREMENT = next_value;

Assume that the table tbl1 exists in the database. The following example changes the value of the auto-increment column in tbl1 to 12:

obclient> ALTER TABLE tbl1 AUTO_INCREMENT = 12;

After the change, the next auto-increment value of the tbl1 table is 12. When new records are inserted, the value of the auto-increment column (c1 in the example) increments from 12.

References

ALTER TABLE