The ALTER TABLE statement modifies existing tables. This includes adding, deleting and renaming columns as well as renaming tables.

Syntax

ALTER TABLE tbl_name
    alter_specification [, alter_specification] ...

alter_specification:
    table_option ...
  | ADD [COLUMN] col_name column_definition
  | ADD [COLUMN] (col_name column_definition,...)
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
  | DROP [COLUMN] col_name
  | RENAME [TO] new_tbl_name
 

column_definition:
    data_type
      [NOT NULL | NULL]
      [DEFAULT default_value]
      [COMMENT '[compression=0|1];[autoincrement=startvalue]']

table_options:
    table_option [[,] table_option] ...  (see CREATE TABLE options)

ADD

The ADD clause allows you to add columns to a table. You must specify the data type after the column name.The following statement adds a priority column with an integer datatype to the orders table:

ALTER TABLE orders ADD COLUMN priority INTEGER;

• A ColumnStore autoincrement column can be added by including an autoincrement comment at either the table level or the column level. Only one autoincrement can be defined per table.
• Compression level (0 for no compression, 1 for compression) can be set at the system level. If a session default exists, this will override the system default. In turn, this can be overridden by the table level compression comment, and finally a compression comment at the column level.

Online alter table add column

ColumnStore engine fully supports online DDL (one session can be adding columns to a table while another session is querying that table). Since MySQL 5.1 did not support alter online alter table, MariaDB ColumnStore has provided a its own syntax to do so for adding columns to a table, one at a time only. Do not attempt to use it for any other purpose. Follow the example below as closely as possible

We have also provided the following workaround. This workaround is intended for adding columns to a table, one at a time only. Do not attempt to use it for any other purpose. Follow the example below as closely as possible.

Scenario: add an INT column named col7 to the existing table foo:

select calonlinealter('alter table foo add column col7 int;');
alter table foo add column col7 int comment 'schema sync only';

The select statement may take several tens of seconds to run, depending on how many rows are currently in the table. Regardless, other sessions can select against the table during this time (but they won’t be able to see the new column yet). The alter table statement will take less than 1 second (depending on how busy MariaDB is) and during this brief time interval, other table reads will be held off.

CHANGE

The CHANGE clause allows you to rename a column in a table or change a column to an autoincrement column.

Notes to CHANGE COLUMN:

• You cannot currently use CHANGE COLUMN to change the definition of thatcolumn.
• You can only change a single column at a time.The following example renames the order_qty field to quantity in the orders table:

ALTER TABLE orders CHANGE COLUMN order_qty quantity
INTEGER;

Notes to AUTOINCREMENT:

• Only one autoincrement column may be defined per table.
• In this example, column order_no will be changed to a autoincrement column with a startvalue of 10

ALTER TABLE orders ALTER order_no  COMMENT 'autoincrement=order_no,10';

DROP

The DROP clause allows you to drop columns. All associated data is removed when the column is dropped. You can DROP COLUMN (column_name). The following example alters the orders table to drop the priority column:

ALTER TABLE orders DROP COLUMN priority;

RENAME

The RENAME clause allows you to rename a table.The following example renames the orders table:

ALTER TABLE orders RENAME TO customer_orders;