Column operations

2023-10-31 11:17:11  Updated

In MySQL mode of OceanBase Database, you can add a column to the end of a table, add a column to a table at a position other than the end of the table, drop a column, rename a column, rearrange a column, change the data type of a column, set a default value for a column, drop the default value of a column, change the value of an auto-increment column, set a NULL constraint on a column, and set a NOT NULL constraint on a column.

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

Here is an example:

obclient> CREATE TABLE tbl1 (c1 INT PRIMARY KEY,c2 VARCHAR(50));
Query OK, 0 rows affected

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

obclient> ALTER TABLE tbl1 ADD c3 INT;
Query OK, 0 rows affected

obclient> DESCRIBE tbl1;
+-------+-------------+------+-----+---------+-------+
| 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 to a table at a position other than the end of the table

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

ALTER TABLE tbl_name ADD COLUMN new_column_name column_definition
            BEFORE column_name;

Here is an example:

obclient> ALTER TABLE tbl1 ADD COLUMN c4 INT BEFORE c3;
Query OK, 0 rows affected

obclient> DESCRIBE tbl1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | int(11)     | NO   | PRI | NULL    |       |
| c2    | varchar(50) | YES  |     | NULL    |       |
| c4    | int(11)     | YES  |     | NULL    |       |
| c3    | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set

Drop a column

The syntax for dropping a column is as follows:

ALTER TABLE table_name DROP COLUMN column_name

Here is an example:

obclient> ALTER TABLE tbl1 DROP COLUMN c3;
Query OK, 0 rows affected

obclient> DESCRIBE tbl1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | int(11)     | NO   | PRI | NULL    |       |
| c2    | varchar(50) | YES  |     | NULL    |       |
| c4    | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set

Rename a column

The syntax for renaming a column is as follows:

ALTER TABLE table_name CHANGE old_col_name new_col_name data_type;

Here is an example:

obclient> DESCRIBE tbl1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | int(11)     | NO   | PRI | NULL    |       |
| c2    | varchar(50) | YES  |     | NULL    |       |
| c4    | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set

obclient> ALTER TABLE tbl1 CHANGE COLUMN c4 c3 VARCHAR(50);
Query OK, 0 rows affected

obclient> DESCRIBE tbl1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | int(11)     | NO   | PRI | NULL    |       |
| c2    | varchar(50) | YES  |     | NULL    |       |
| c3    | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set

Rearrange a column

The syntax for rearranging a column is as follows:

ALTER TABLE table_name MODIFY COLUMN column_name column_definition
            FIRST | [AFTER column_name];

Here is an example:

obclient> ALTER TABLE tbl1 MODIFY COLUMN c3 INT FIRST;
Query OK, 0 rows affected

obclient> DESCRIBE tbl1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c3    | int(11)     | YES  |     | NULL    |       |
| c1    | int(11)     | NO   | PRI | NULL    |       |
| c2    | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set

obclient> ALTER TABLE tbl1 MODIFY COLUMN c3 INT AFTER c2;
Query OK, 0 rows affected

obclient> DESCRIBE tbl1;
+-------+-------------+------+-----+---------+-------+
| 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

Change the 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 type of a column is as follows:

ALTER TABLE table_name MODEFY column_name data_type;

Examples of column type changes

Conversions between character data types

Change the data type of a character column and increase the length.

obclient> CREATE TABLE test01 (c1 INT PRIMARY KEY, c2 CHAR(10), c3 CHAR(10));
Query OK, 0 rows affected

obclient> ALTER TABLE test01 MODIFY C2 VARCHAR(20);
Query OK, 0 rows affected

obclient> ALTER TABLE test01 MODIFY C2 VARCHAR(40);
Query OK, 0 rows affected

obclient> ALTER TABLE test01 MODIFY C2 TINYTEXT;
Query OK, 0 rows affected

obclient> ALTER TABLE test01 MODIFY C2 LONGTEXT;
Query OK, 0 rows affected

obclient> ALTER TABLE test01 MODIFY C3 CHAR(20);
Query OK, 0 rows affected

obclient> ALTER TABLE test01 MODIFY C3 VARCHAR(30);
Query OK, 0 rows affected

Conversion between numeric data types

  • Change the data type of an integer type column and increase the length of the column.

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

obclient> ALTER TABLE test02 MODIFY age SMALLINT;
Query OK, 0 rows affected

obclient> ALTER TABLE test02 MODIFY age INT;
Query OK, 0 rows affected

obclient> ALTER TABLE test02 MODIFY age BIGGINT;
Query OK, 0 rows affected

  • Change the data type and length for a column of a data type that has a precision.

    obclient> CREATE TABLE test03(c1 INT, c2 FLOAT(8,2), c3 FLOAT(8,2), UNIQUE(c2, c3));
Query OK, 0 rows affected

obclient> ALTER TABLE test03 MODIFY c2 FLOAT(5,2);
Query OK, 0 rows affected

obclient> ALTER TABLE test03 MODIFY c2 DOUBLE(10,4);
Query OK, 0 rows affected

obclient> ALTER TABLE test03 MODIFY c2 DOUBLE(5,2);
Query OK, 0 rows affected

obclient> ALTER TABLE test03 MODIFY c2 DECIMAL(20, 4);
Query OK, 0 rows affected

Conversion for binary data types

obclient> CREATE TABLE test04 (c1 TINYBLOB, c2 BINARY(64));
Query OK, 0 rows affected

obclient> ALTER TABLE test04 MODIFY c1 BLOB;
Query OK, 0 rows affected

obclient> ALTER TABLE test04 MODIFY c1 BINARY(256);
Query OK, 0 rows affected

obclient> CREATE TABLE test05 (id INT PRIMARY KEY, name TINYTEXT,age INT, description VARCHAR(65535));
Query OK, 0 rows affected

obclient> ALTER TABLE test05 MODIFY name VARCHAR(256);
Query OK, 0 rows affected

Conversion between an integer data type and a character data type

obclient> CREATE TABLE test06 (c1 INT);
Query OK, 0 rows affected

obclient> ALTER TABLE test06 MODIFY c1 VARCHAR(64);
Query OK, 0 rows affected

obclient> CREATE TABLE test07 (c1 VARCHAR(32));
Query OK, 0 rows affected

obclient> ALTER TABLE test07 MODIFY c1 INT;
Query OK, 0 rows affected

Set a default value for a column

The syntax for setting a default value for a column is as follows:

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT literal;

Here is an example:

obclient> ALTER TABLE tbl1 ALTER COLUMN c1 SET DEFAULT 111;
Query OK, 0 rows affected

obclient>  DESCRIBE tbl1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c4    | int(11)     | YES  |     | NULL    |       |
| c1    | int(11)     | NO   | PRI | 111     |       |
| c2    | varchar(50) | YES  |     | NULL    |       |
| c3    | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set

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 is an example:

obclient> ALTER TABLE tbl1 ALTER COLUMN c1 DROP DEFAULT;
Query OK, 0 rows affected

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 AUTO_INCREMENT=next_value;

Here is an example:

obclient> ALTER TABLE tbl1 AUTO_INCREMENT=12;
Query OK, 0 rows affected

Set a NULL constraint on a column

The syntax for setting a NULL constraint on a column is as follows:

ALTER TABLE table_name MODIFY COLUMN column_name data_type NULL;

Here is an example:

obclient> DESCRIBE tbl1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c4    | int(11)     | YES  |     | NULL    |       |
| c1    | int(11)     | NO   | PRI | NULL    |       |
| c2    | varchar(50) | YES  |     | NULL    |       |
| c3    | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set

obclient> ALTER TABLE tbl1 MODIFY COLUMN c1 INT NULL;
Query OK, 0 rows affected

obclient> DESCRIBE tbl1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c4    | int(11)     | YES  |     | NULL    |       |
| c1    | int(11)     | NO   | PRI | NULL    |       |
| c2    | varchar(50) | YES  |     | NULL    |       |
| c3    | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set

Set a NOT NULL constraint on a column

The syntax for setting a NOT NULL constraint on a column is as follows:

ALTER TABLE table_name MODIFY COLUMN column_name data_type NOT NULL;

Here is an example:

obclient> ALTER TABLE tbl1 MODIFY COLUMN c1 INT NOT NULL;
Query OK, 0 rows affected

obclient> DESCRIBE tbl1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c4    | int(11)     | YES  |     | NULL    |       |
| c1    | int(11)     | NO   | PRI | NULL    |       |
| c2    | varchar(50) | YES  |     | NULL    |       |
| c3    | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set
Last
Next

OceanBaseOceanBase

OceanBase, A Highly Scalable Database for Transactional, Analytical, and AI Workloads.

Company

About OceanBaseContact UsPartnerTrust Center

Product

OceanBase CloudOceanBase EnterpriseQuick StartDownloadPricing

Resources

DocsBlogWhite Paper

Follow us

LinkedInLinkedInDiscordDiscordTwitterTwitterYoutubeYoutubeForumForumGitHubGitHubStack OverflowStack Overflow

© OceanBase 2024. All rights reserved | Cloud Service Agreement | Privacy Policy | Legal | Security

Contact Us