Modify the structure of an existing table.

ALTER [ONLINE] TABLE tbl_name
  [alter_specification [, alter_specification] ...]

alter_specification:
    ADD [COLUMN] col_name { column_definition | AS computed_column_definition } [FIRST | AFTER col_name]
  | ADD [COLUMN] (col_name column_definition, ...)
  | ADD [UNIQUE] { INDEX | KEY } [index_name] [index_type] (index_col_name, ...) [index_option] ...
  | DROP [COLUMN] col_name
  | DROP { INDEX | KEY } index_name
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | CHANGE old_col_name new_col_name
  | RENAME [TO | AS] new_tbl_name

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [COMMENT 'string']

computed_column_definition:
    computed_column_expression PERSISTED data_type

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING { BTREE | HASH }

index_option:
    KEY_BLOCK_SIZE [=] value
  | BUCKET_COUNT [=] value
  | index_type

Notes

• MemSQL supports online ALTER TABLE, which means that you can read and write while the table is being altered. ALTER TABLE on a sharded table is always executed online. Note that online ALTER TABLE will not begin modifying the table until all in-progress reads and writes to that table have completed. The operation briefly blocks reads and writes between when ALTER TABLE execution begins and when the in-progress reads and writes finish. This blocking period usually lasts on the order of milliseconds. As soon as the in-progress reads and writes complete and the ALTER TABLE command begins modifying the table, new reads and writes will proceed as normal.
• Online ALTER TABLE in MemSQL cannot be rolled back, so operations that may result in errors are disallowed. For example, you cannot add unique indexes with online ALTER TABLE because there may be duplicate keys in the indexed column. The following operations cannot be performed online: * Adding a unique, primary or foreign index. * Dropping a primary or foreign index.
• ALTER OFFLINE TABLE is supported on non-sharded tables only (reference tables and tables in MemSQL Single Box). ALTER OFFLINE TABLE operations will cause write queries against the table to fail until the ALTER TABLE completes. Read queries can still be executed against the table as if it were in the state immediately before the ALTER TABLE operation began executing. If the user does not specify whether to do the ALTER TABLE operation online or offline, MemSQL will do it online if possible. Note that offline ALTER TABLE completes more quickly than online, but offline blocks writes for as long as the command is executing.
• ALTER TABLE must be run on the master aggregator node (see Node Requirements for MemSQL Commands).
• Columns used to construct a view cannot be dropped. See CREATE VIEW for more information.
• computed_column_expression defines the value of a computed column using other columns in the table, constants, built-in functions, operators, and combinations thereof. For more information see Persistent Computed Columns.
• On columnstore tables, adding and dropping columns is supported, but modifying columns is unsupported.
• Adding computed columns to columnstore tables is unsupported.
• Altering indexes on columnstore tables is unsupported.
• Altering PRIMARY, SHARD, or UNIQUE keys is unsupported.

Example

memsql> ALTER TABLE `customer` ADD COLUMN `counts` binary(5);
Query OK, 150000 rows affected (19.57 sec)
Records: 150000  Duplicates: 0  Warnings: 0

Was this Article Helpful?

Thanks For Your Feedback!