Column operations

This topic describes the column operations in 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;

The parameters are described as follows:

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

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

• data_type: 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 schema 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 schema 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;

The parameters are described as follows:

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

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

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

• FIRST | BEFORE | AFTER: the position where 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: 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 schema 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 schema of the tbl1 table again.

   obclient> DESCRIBE tbl1;
   

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

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 schema 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 schema 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;
  

  The parameters are described as follows:

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

  • old_col_name: the original name of the column.

  • new_col_name: the new name of the column.

  • data_type: 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 the Change the data type of a column section.

• Rename a column only

  ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name
  

  The parameters are described as follows:

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

  • old_col_name: the original name of the column.

  • new_col_name: the new name of the column.

Assume that the tbl1 table exists in the database and the table schema 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 schema 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;

The parameters are described as follows:

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

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

• data_type: 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 the Change the data type of a column section

• FIRST | BEFORE | AFTER: the position where 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 relocate a column to the beginning of a table, you do not need to set column_name. Otherwise, an error is returned.

• column_name: the name of the column in the specified relative position to the position where the column is to be relocated.

Assume that the tbl1 table exists in the database and the table schema 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 the 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 schema of tbl1. The result is as follows, showing that the age column is relocated before the name column and the data type of the age column 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;

The parameters are described as follows:

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

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

• data_type: the new data type.

Conversions between character data types

Create a table named test01 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 a table named test02 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 a table named test03 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 between binary data types

Create a table named test04 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 a table named test05 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, its 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;

The parameters are described as follows:

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

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

• const_value: the new default value of the column.

Assume that the tbl1 table exists in the database and the table schema 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 schema 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;

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 schema 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 schema 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 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]];

The parameters are described as follows:

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

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

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

• AUTO_INCREMENT: sets 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: sets the column as the primary key column.

• UNIQUE [KEY]: sets the UNIQUE constraint for the column.

Assume that the tbl1 table exists in the database and the table schema of tbl1 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 tbl1 MODIFY COLUMN id INT PRIMARY KEY;
   

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

   obclient> ALTER TABLE tbl1 MODIFY COLUMN id INT AUTO_INCREMENT;
   

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

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

4. Set the UNIQUE constraint for the age column.

   obclient> ALTER TABLE tbl1 MODIFY COLUMN age INT UNIQUE;
   

5. Execute DESCRIBE tbl1; again to view the table schema of tbl1.

   +-------+-------------+------+-----+---------+----------------+
   | 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 tbl1 table 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 a new record is inserted, the value of the auto-increment column (c1 in the example) increments from 12.

References

ALTER TABLE