ALTER TABLE

概要

このステートメントは、既存のテーブルの構造を変更するために使用されます。例えば、テーブルおよびテーブルプロパティの変更、列の追加、列およびプロパティの変更、列の削除などを行います。

構文

alter_table_stmt:
    ALTER TABLE [TEMPORARY] table_name alter_table_action_list;

alter_table_action_list:
    alter_table_action [, alter_table_action ...]

alter_table_action:
      ADD [COLUMN] column_definition
          [ opt_position_column ]
    | ADD [COLUMN] (column_definition_list)
    | ADD [CONSTRAINT [constraint_name]] UNIQUE {INDEX | KEY}
          [index_name] index_desc
    | ADD [CONSTRAINT [constraint_name]] FOREIGN KEY
          [index_name] index_desc
          REFERENCES reference_definition
          [match_action][opt_reference_option_list]
    | ADD PRIMARY KEY (column_name)
    | ADD CONSTRAINT [constraint_name] CHECK (expression)[[NOT] ENFORCED]
    | ADD PARTITION (range_partition_list)
    | ADD {INDEX | KEY} [index_name] [index_type] (key_part,...) [index_option_list][index_column_group_option] [algorithm = algorithm_value] [lock = lock_value]
    | ADD FULLTEXT [INDEX | KEY] [index_name](column_name, [column_name ...]) [WITH PARSER tokenizer_option] [PARSER_PROPERTIES[=](parser_properties_list)] [LOCAL]
    | ADD COLUMN GROUP([all columns, ]each column) [DELAYED]
    | ALTER [COLUMN] column_name {
          SET DEFAULT const_value
        | DROP DEFAULT
      }
    | ALTER {CHECK | CONSTRAINT} symbol [NOT] ENFORCED
    | ALTER INDEX index_name
          [VISIBLE | INVISIBLE]
    | CHANGE [COLUMN] column_name column_definition
    | DROP [COLUMN] column_name [, DROP COLUMN column_name ...]
    | DROP {PARTITION | SUBPARTITION} partition_name_list
    | DROP TABLEGROUP
    | DROP FOREIGN KEY fk_name
    | DROP PRIMARY KEY [, ADD PRIMARY KEY (column)]
    | DROP CHECK constraint_name
    | DROP COLUMN GROUP([all columns, ]each column)
    | MODIFY [COLUMN] column_definition
    | RENAME COLUMN old_col_name [TO|AS] new_col_name
    | RENAME [TO|AS] table_name
    | RENAME {INDEX | KEY} old_index_name [TO|AS] new_index_name
    | [SET] table_option_list
    | TRUNCATE {PARTITION | SUBPARTITION} partition_name_list
    | EXCHANGE PARTITION partition_name WITH TABLE origin_table_name WITHOUT VALIDATION
    | REORGANIZE PARTITION partition_name INTO (split_range_partition_list)
    | PARTITION BY {RANGE [COLUMNS]([column_name_list]) [SIZE('size_value')] [range_partition_list]
                   | partition_option}
    | MODIFY PARTITION partition_name ADD SUBPARTITION (add_subpartition_option)
    | FORCE

column_definition_list:
    column_definition [, column_definition ...]

column_definition:
    column_name data_type
    [DEFAULT const_value] [AUTO_INCREMENT]
    [NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] [COMMENT string_value] [SKIP_INDEX(skip_index_option_list)] [ opt_position_column ]

skip_index_option_list:
    skip_index_option [,skip_index_option ...]

skip_index_option:
    MIN_MAX
    | SUM

opt_position_column:
    FIRST | BEFORE | AFTER column_name

index_desc:
   (column_desc_list) [index_type] [index_option_list]

match_action:
   MATCH {SIMPLE | FULL | PARTIAL}

opt_reference_option_list:
   reference_option [,reference_option ...]

reference_option:
   ON {DELETE | UPDATE} {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}

column_desc_list:
    column_desc [, column_desc ...]

column_desc:
     column_name [(length)] [ASC | DESC]

key_part:
    {index_col_name [(length)] | (expr)} [ASC | DESC]

index_type:
    USING BTREE

index_option_list:
    index_option [ index_option ...]

index_option:
     [GLOBAL | LOCAL]
    | block_size
    | compression
    | STORING(column_name_list)
    | COMMENT string_value

index_column_group_option:
    WITH COLUMN GROUP([all columns, ]each column)

algorithm_value:
    default
    | inplace
    | copy
    | instant

lock_value:
    default
    | none
    | shared
    | exclusive

tokenizer_option:
    SPACE
    | NGRAM
    | BENG
    | IK
    | NGRAM2

parser_properties_list:
    parser_properties, [parser_properties]

parser_properties:
    min_token_size = int_value
    | max_token_size = int_value
    | ngram_token_size = int_value
    | ik_mode = 'char_value'
    | min_ngram_size = int_value
    | max_ngram_size = int_value

table_option_list:
    table_option [ table_option ...]

table_option:
      TABLEGROUP = tablegroup_name
    | block_size
    | lob_inrow_threshold [=] num
    | compression
    | AUTO_INCREMENT [=] INT_VALUE
    | COMMENT string_value
    | parallel_clause
    | TABLE_MODE [=] 'table_mode_value'
    | AUTO_INCREMENT_CACHE_SIZE [=] INT_VALUE
    | READ {ONLY | WRITE}
    | DUPLICATE_SCOPE= 'none | cluster'
    | enable_macro_block_bloom_filter [=] {True | False}
    | DYNAMIC_PARTITION_POLICY [=] (dynamic_partition_policy_list)
    | SEMISTRUCT_ENCODING_TYPE [=] 'encoding'

parallel_clause:
    {NOPARALLEL | PARALLEL integer}

table_mode_value:
    NORMAL
    | QUEUING
    | MODERATE
    | SUPER
    | EXTREME

dynamic_partition_policy_list:
    dynamic_partition_policy_option [, dynamic_partition_policy_option ...]

dynamic_partition_policy_option:
    ENABLE = {true | false}
    | PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}
    | EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}

partition_option:
      PARTITION BY HASH(expression)
      [subpartition_option] PARTITIONS partition_count
    | PARTITION BY KEY([column_name_list])
      [subpartition_option] PARTITIONS partition_count
    | PARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
      [subpartition_option] (range_partition_list)

subpartition_option:
      SUBPARTITION BY HASH(expression)
      SUBPARTITIONS subpartition_count
    | SUBPARTITION BY KEY(column_name_list)
      SUBPARTITIONS subpartition_count
    | SUBPARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
      (range_subpartition_list)

split_range_partition_list:
    PARTITION split_partition_name VALUES LESS THAN (value)
    [, PARTITION split_partition_name VALUES LESS THAN (value) ...]
     , PARTITION split_partition_name VALUES LESS THAN (source_value)

range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION partition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE}

range_subpartition_list:
    range_subpartition [, range_subpartition ...]

range_subpartition:
    SUBPARTITION subpartition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE}

expression_list:
    expression [, expression ...]

column_name_list:
    column_name [, column_name ...]

partition_name_list:
    partition_name [, partition_name ...]

partition_count | subpartition_count:
    INT_VALUE

add_subpartition_option:
    {SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr
        [, SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr] ...}
    | {SUBPARTITION subpartition_name VALUES IN list_partition_expr
        [, SUBPARTITION subpartition_name VALUES IN list_partition_expr] ...}

パラメータの説明

ADD INDEX KEY

• ADD {INDEX | KEY} [index_name] [index_type] (key_part,...) [index_option_list][index_column_group_option] [algorithm] [lock]
  • INDEX | KEY :作成するテーブルにキーまたはインデックスを指定します。この2つのキーワードは同等です。
  • index_name：オプションです。インデックス名を指定します。インデックス名が指定されていない場合、インデックス参照の最初の列を使用します。重複する名前がある場合は、アンダースコア（_）＋番号で名前付けされます。
  • index_type：オプションです。インデックスが使用するインデックスタイプを指定します。
  • key_part：インデックスに含める列名または式を指定します。
  • index_option：オプションです。インデックスオプションリストを指定します。
  • algorithm：DDL操作(例えば、インデックスの追加など)を実行する際の下層アルゴリズムタイプを指定します。

    注意

    現在のバージョンでは、algorithm パラメータはMySQL構文との互換性のためにのみ存在し、実際の影響はありません。

  • lock：DDL操作を実行する際のテーブルロックレベルを定義し、並行アクセスの粒度を制御します。

    注意

    現在のバージョンでは、lock パラメータはMySQL構文との互換性のためにのみ存在し、実際の影響はありません。

table_mode_value

• NORMAL：デフォルト値で、通常を意味します。このモードでは、データダンプ後にマージをトリガーする確率は非常に低いです。
• QUEUING：このモードでは、データダンプ後にマージをトリガーする確率は低いです。
• MODERATE：適度を意味します。このモードでは、データダンプ後にマージをトリガーする確率は中程度です。
• SUPER：スーパーを意味します。このモードでは、データダンプ後にマージをトリガーする確率は高いです。
• EXTREME：極端を意味します。このモードでは、ダンプ後にマージをトリガーする確率は比較的高いです。

マージに関する詳細については、適応的マージを参照してください。

tokenizer_option

• SPACE：デフォルト値。スペースで単語を分割することを示します。以下のプロパティを指定できます。

  プロパティ	値の範囲
  min_token_size	[1, 16]
  max_token_size	[10, 84]

• NGRAM：N-Gram(Chinese)に基づく単語分割方式を示します。以下のプロパティを指定できます。

  プロパティ	値の範囲
  ngram_token_size	[1, 10]

• NGRAM2：テキストを min_ngram_size～max_ngram_size の範囲の連続する文字列に分割することを示します。以下のプロパティを指定できます。

  プロパティ	値の範囲
  min_ngram_size	[1, 16]
  max_ngram_size	[1, 16]

  説明

  OceanBaseデータベースV4.3.5では、V4.3.5 BP2以降のバージョンから NGRAM2 分詞器がサポートされています。

• BENG：Beng (Basic English)に基づく単語分割方式を示します。以下のプロパティを指定できます。

  プロパティ	値の範囲
  min_token_size	[1, 16]
  max_token_size	[10, 84]

• IK：IK(Chinese)に基づく単語分割方式を示します。現在は utf-8 文字セットのみサポートしています。以下のプロパティを指定できます。

  プロパティ	値の範囲
  ik_mode	smart max_word

  説明

  OceanBaseデータベースV4.3.5では、V4.3.5 BP1以降のバージョンから IK 分詞器がサポートされています。

TOKENIZE 関数を使用して、指定された分詞器とJSON形式のパラメータに基づいてテキストの分詞結果を確認できます。

parser_properties

• min_token_size：最小分詞長さを示します。デフォルト値は3で、値の範囲は1から16です。

• max_token_size：最大分詞長さを示します。デフォルト値は84で、値の範囲は10から84です。

• ngram_token_size：NGRAM の分詞長さを示します。これは NGRAM 分詞器でのみ有効です。デフォルト値は2で、値の範囲は1から10です。

• ik_mode: IK 分詞器の分詞モードを示します。値は以下のとおりです：

  • smart：デフォルト値。辞書内の単語が使用され、分詞の正確性が向上し、辞書内の単語の境界が優先的に考慮されるため、不要な拡張が減少する可能性があります。
  • max_word：辞書内で定義された単語が識別されるが、分詞の最大化拡張には影響しません。辞書に定義されていなくても、max_word モードではテキストをより多くの単語に分割しようと試みます。

• min_ngram_size：最小分詞長さ。値の範囲は[1,16]です。

• max_ngram_size: 最大分詞長さ。値の範囲は[1,16]です。

例：

1. テーブル tbl1 を作成します。

   CREATE TABLE tbl1 (col1 VARCHAR(200), col2 TEXT);
   

2. テーブル tbl1 に全文インデックス fidx を追加します。

   ALTER TABLE tbl1 ADD FULLTEXT INDEX fidx(col2) WITH PARSER ik PARSER_PROPERTIES=(ik_mode='max_word');
   

dynamic_partition_policy_option

• ENABLE = {true | false}：動的パーティション管理を有効にするかどうかを示します。値は以下のとおりです：

  • true：デフォルト値。動的パーティション管理を有効にします。
  • false：動的パーティション管理を無効にします。

• PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}：予約作成時間。動的パーティション管理をスケジュールすると、最大パーティション上界 > now() + precreate_time となるようにパーティションを予約作成します。値は以下のとおりです：

  • -1：デフォルト値。パーティションを予約作成しないことを示します。
  • 0：現在のパーティションのみを予約作成します。
  • n {hour | day | week | month | year}：対応する時間範囲のパーティションを予約作成します。例えば、3 hour は3時間以内のパーティションを予約作成することを示します。

  説明

  • 複数のパーティションを予約作成する場合、パーティション境界間隔は TIME_UNIT です。
  • 最初に予約作成されるパーティション境界は、既存の最大パーティション境界を TIME_UNIT に基づいて切り上げたものになります。

• EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}：オプション。パーティションの有効期限。動的パーティション管理をスケジュールすると、パーティション上界 < now() - expire_time のすべての有効期限切れパーティションが削除されます。変更可能です。値は以下のとおりです：

  • -1：デフォルト値。パーティションは常に有効期限切れにならないことを示します。
  • 0：現在のパーティションを除く、以前のすべてのパーティションが有効期限切れであることを示します。
  • n {hour | day | week | month | year}：パーティションの有効期限。例えば、1 day はパーティションの有効期限が1日であることを示します。

動的パーティションテーブルの変更に関する詳細については、動的パーティションテーブルの変更を参照してください。

例：

ALTER TABLE tbl2 SET DYNAMIC_PARTITION_POLICY(
    ENABLE = true,
    PRECREATE_TIME = '1 day',
    EXPIRE_TIME = '-1'
);

例

カラムの追加と削除

サンプルテーブル tbl1 を作成し、DESCRIBE コマンドを実行してテーブル情報を確認します。

obclient> CREATE TABLE tbl1 (c1 INT(11) 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    |       |
+-------+------------+----------+--------+---------+-------+

• c3 列を追加し、DESCRIBE コマンドを実行してテーブル情報を確認します。

  obclient> ALTER TABLE tbl1 ADD c3 INT;
  Query OK, 1 row 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 row affected
  

• c3 列を削除し、DESCRIBE コマンドを実行してテーブル情報を確認します。

  obclient> ALTER TABLE tbl1 DROP c3;
  Query OK, 1 row affected
  
  obclient> DESCRIBE tbl1;
  +-------+------------+----------+--------+---------+-------+
  | Field | Type       | Null     | Key    | Default | Extra |
  +-------+------------+----------+--------+---------+-------+
  | c1    | int(11)    | NO       | PRI    | NULL    |       |
  | c2    | varchar(50)| YES      |        | NULL    |       |
  +-------+------------+----------+--------+---------+-------+
  2 row affected
  

• テーブル tbl1 のフィールド c2 を c3 にリネームし、DESCRIBE コマンドを実行してテーブル情報を確認します。

  obclient> ALTER TABLE tbl1 CHANGE COLUMN c2 c3 VARCHAR(50);
  Query OK, 1 row affected
  
  obclient> DESCRIBE tbl1;
  +-------+------------+----------+--------+---------+-------+
  | Field | Type       | Null     | Key    | Default | Extra |
  +-------+------------+----------+--------+---------+-------+
  | c1    | int(11)    | NO       | PRI    | NULL    |       |
  | c3    | varchar(50)| YES      |        | NULL    |       |
  +-------+------------+----------+--------+---------+-------+
  2 rows affected
  

• テーブル tbl1 に c4 列を追加し、その列をテーブルの最初の列として設定し、DESCRIBE コマンドを実行してテーブル情報を確認します。

  obclient> ALTER TABLE tbl1 ADD COLUMN c4 INTEGER FIRST;
  Query OK, 1 row affected
  
  obclient> DESCRIBE tbl1;
  +-------+------------+----------+--------+---------+-------+
  | Field | Type       | Null     | Key    | Default | Extra |
  +-------+------------+----------+--------+---------+-------+
  | c4    | int(11)    | YES      |        | NULL    |       |
  | c1    | int(11)    | NO       | PRI    | NULL    |       |
  | c3    | varchar(50)| YES      |        | NULL    |       |
  +-------+------------+----------+--------+---------+-------+
  3 row affected
  

• c1 列の後に c2 列を追加し、DESCRIBE コマンドを実行してテーブル情報を確認します。

  obclient> ALTER TABLE tbl1 ADD COLUMN c2 INTEGER AFTER c1;
  Query OK, 1 row affected
  
  obclient> DESCRIBE tbl1;
  +-------+------------+----------+--------+---------+-------+
  | Field | Type       | Null     | Key    | Default | Extra |
  +-------+------------+----------+--------+---------+-------+
  | c4    | int(11)    | YES      |        | NULL    |       |
  | c1    | int(11)    | NO       | PRI    | NULL    |       |
  | c2    | int(11)    | YES      |        | NULL    |       |
  | c3    | varchar(50)| YES      |        | NULL    |       |
  +-------+------------+----------+--------+---------+-------+
  4 row affected
  

• c4 列の前に c5 列を追加し、DESCRIBE コマンドを実行してテーブル情報を確認します。

  obclient> ALTER TABLE tbl1 ADD COLUMN c5 INT BEFORE c4;
  Query OK, 1 row affected
  
  obclient> DESCRIBE tbl1;
  +-------+------------+----------+--------+---------+-------+
  | Field | Type       | Null     | Key    | Default | Extra |
  +-------+------------+----------+--------+---------+-------+
  | c5    | int(11)    | YES      |        | NULL    |       |
  | c4    | int(11)    | YES      |        | NULL    |       |
  | c1    | int(11)    | NO       | PRI    | NULL    |       |
  | c2    | int(11)    | YES      |        | NULL    |       |
  | c3    | varchar(50)| YES      |        | NULL    |       |
  +-------+------------+----------+--------+---------+-------+
  5 row affected
  

• テーブル tbl2 に外部キー制約 fk1 を追加します。UPDATE 操作が親テーブルのキー値に影響を与える場合、対応する子テーブルの行に SET NULL 操作を実行します。

  obclient> CREATE TABLE tbl2(c1 INT PRIMARY KEY,c2 INT);
  Query OK, 0 row affected
  obclient> CREATE TABLE tbl3(c1 INT PRIMARY KEY,c2 INT);
  Query OK, 0 row affected
  obclient> ALTER TABLE tbl2 ADD CONSTRAINT fk1 FOREIGN KEY (c2) REFERENCES tbl3(c1) ON UPDATE SET NULL;
  Query OK, 0 row affected
  

• tbl3 テーブルの外部キー制約 fk1 を削除します。

  obclient> ALTER TABLE tbl2 DROP FOREIGN KEY fk1;
  Query OK, 0 row affected
  

• tbl2 テーブルの主キーを削除します。

  obclient> ALTER TABLE tbl2 DROP PRIMARY KEY;
  Query OK, 0 row affected
  

インデックス操作

• テーブル tbl2 のインデックス ind1 を ind2 にリネームします。

  obclient> CREATE TABLE tbl2(c1 INT(11) PRIMARY KEY,c2 INT(50));
  Query OK, 0 row affected
  
  obclient> CREATE INDEX ind1 ON tbl2 (c2 ASC);
  Query OK, 0 row affected
  obclient> ALTER TABLE tbl2 RENAME INDEX ind1 TO ind2;
  Query OK, 0 row affected
  

• テーブル tbl2 にインデックス ind3 を作成し、c1、c2 列を参照します。

  obclient> ALTER TABLE tbl2 ADD INDEX ind3 (c1,c2);
  Query OK, 0 row affected
  

  SHOW INDEX ステートメントを使用して作成されたインデックスを確認できます。

  obclient> SHOW INDEX FROM tbl2;
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  | tbl2  |          0 | PRIMARY  |            1 | c1          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
  | tbl2  |          1 | ind2     |            1 | c2          | A         |        NULL | NULL     | NULL   |  YES | BTREE      | available |               | YES     |
  | tbl2  |          1 | ind3     |            1 | c1          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
  | tbl2  |          1 | ind3     |            2 | c2          | A         |        NULL | NULL     | NULL   |  YES | BTREE      | available |               | YES     |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  4 rows in set
  

• テーブル tbl2 のインデックス ind2 を削除します。

  obclient> ALTER TABLE tbl2 DROP INDEX ind2;
  Query OK, 0 row affected
  

  説明

  実際の運用シナリオでは、上記の方法を使用してインデックスのアトミック変更を実現できます。

• t1_func に3つの関数ベースのインデックスを追加します。そのうち1つは i2 という名前で、残りの2つはシステムが自動的に生成する名前で、形式は functional_index というプレフィックスに番号を付加したものになります。

  obclient> ALTER TABLE t1_func ADD INDEX ((CONCAT(c1,'a')));
  Query OK, 0 rows affected
  obclient> ALTER TABLE t1_func ADD INDEX ((c1+1));
  Query OK, 0 rows affected
  obclient> ALTER TABLE t1_func ADD INDEX i2 ((CONCAT(c1,'a')));
  Query OK, 0 rows affected
  obclient> SHOW CREATE TABLE t1_func;
  +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
  +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | t1_func | CREATE TABLE `t1_func` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  KEY `i1` (((`c1` + `c2`) < 1)) BLOCK_SIZE 16384 LOCAL,
  KEY `functional_index` (concat(`c1`,'a')) BLOCK_SIZE 16384 LOCAL,
  KEY `functional_index_2` ((`c1` + 1)) BLOCK_SIZE 16384 LOCAL,
  KEY `i2` (concat(`c1`,'a')) BLOCK_SIZE 16384 LOCAL
  ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
  +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set
  

• テーブルにカラムストアインデックスを作成します。

  1. 次のSQLステートメントを使用してテーブル tbl3 を作成します。

     CREATE TABLE tbl3 (col1 INT, col2 VARCHAR(50));
     

  2. tbl3 テーブルにカラムストアインデックス idx1_tbl3 を作成し、col1 列を参照します。

     ALTER TABLE tbl3 ADD INDEX idx1_tbl3 (col1) WITH COLUMN GROUP(each column);
     

• インデックスを非表示にします。

  説明

  インデックスを非表示に設定すると、テーブル構造内で /*!80000 INVISIBLE */ のマークを見ることができます。

  1. テーブル tbl4 を作成します。

     CREATE TABLE tbl4 (col1 INT PRIMARY KEY, col2 INT, col3 INT);
     

  2. インデックス idx1_tbl4 を作成します。

     CREATE INDEX idx1_tbl4 ON tbl4(col2);
     

  3. インデックス idx1_tbl4 を非表示にします。

     ALTER TABLE tbl4 ALTER INDEX idx1_tbl4 INVISIBLE;
     

  4. 再びテーブル tbl4 の構造を確認します。

     SHOW CREATE TABLE tbl4;
     

     実行結果は次のとおりです：

     +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     | Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                              |
     +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     | tbl4  | CREATE TABLE `tbl4` (
       `col1` int(11) NOT NULL,
       `col2` int(11) DEFAULT NULL,
       `col3` int(11) DEFAULT NULL,
       PRIMARY KEY (`col1`),
       KEY `idx1_tbl4` (`col2`) BLOCK_SIZE 16384 LOCAL /*!80000 INVISIBLE */
     ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(each column) |
     +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     1 row in set
     

パーティション操作

• パーティションテーブルt_log_part_by_rangeのパーティションM202001とM202002内のすべてのデータを削除します。

  obclient> CREATE TABLE t_log_part_by_range (
      log_id      bigint NOT NULL
      , log_value varchar(50)
      , log_date  timestamp NOT NULL
  ) PARTITION BY RANGE(UNIX_TIMESTAMP(log_date))
  (
      PARTITION M202001 VALUES LESS THAN(UNIX_TIMESTAMP('2020/02/01'))
      , PARTITION M202002 VALUES LESS THAN(UNIX_TIMESTAMP('2020/03/01'))
      , PARTITION M202003 VALUES LESS THAN(UNIX_TIMESTAMP('2020/04/01'))
      , PARTITION M202004 VALUES LESS THAN(UNIX_TIMESTAMP('2020/05/01'))
      , PARTITION M202005 VALUES LESS THAN(UNIX_TIMESTAMP('2020/06/01'))
  );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE t_log_part_by_range TRUNCATE PARTITION M202001, M202002;
  Query OK, 0 rows affected
  

• パーティションテーブルt_log_part_by_rangeにパーティションM202006を追加します。

  obclient> CREATE TABLE t_log_part_by_range (
      log_id      bigint NOT NULL
      , log_value varchar(50)
      , log_date  timestamp NOT NULL
  ) PARTITION BY RANGE(UNIX_TIMESTAMP(log_date))
  (
      PARTITION M202001 VALUES LESS THAN(UNIX_TIMESTAMP('2020/02/01'))
      , PARTITION M202002 VALUES LESS THAN(UNIX_TIMESTAMP('2020/03/01'))
      , PARTITION M202003 VALUES LESS THAN(UNIX_TIMESTAMP('2020/04/01'))
      , PARTITION M202004 VALUES LESS THAN(UNIX_TIMESTAMP('2020/05/01'))
      , PARTITION M202005 VALUES LESS THAN(UNIX_TIMESTAMP('2020/06/01'))
  );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE t_log_part_by_range ADD PARTITION
           (PARTITION M202006 VALUES LESS THAN(UNIX_TIMESTAMP('2020/07/01'))
           );
  

パラレル度の変更

• テーブル tbl3 のパラレル度を 2 に変更します。

  obclient> ALTER TABLE tbl3 PARALLEL 2;
  

  ALTER TABLE table_name PARALLEL int; を使用してテーブルのパラレル度を変更するだけでなく、PARALLEL ヒントを使用することもできます。構文は alter /*+ parallel(int) */ table です。

カラムタイプ関連の操作

• テーブル tbl4 の通常列 col1 を自動インクリメント列に変更します。

  obclient> CREATE TABLE tbl4 (col1 BIGINT(10) NOT NULL,col2 INT);
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl4 MODIFY col1 BIGINT(10) AUTO_INCREMENT;
  Query OK, 0 rows affected
  

• テーブル tbl4 に主キー col1 を追加し、その後主キーを col2 に変更します。

  obclient> ALTER TABLE tbl4 ADD PRIMARY KEY (col1);
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl4 DROP PRIMARY KEY,ADD PRIMARY KEY (`col2`);
  Query OK, 0 rows affected
  

• CHECK 拘束条件を追加します。

  obclient> CREATE TABLE tbl7(col1 VARCHAR(10),col2 VARCHAR(10));
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl7 ADD CONSTRAINT my_check CHECK (col1> col2) ;
  Query OK, 0 rows affected
  

• テーブル tbl7 の CHECK 拘束条件を削除します。

  obclient> ALTER TABLE tbl7 DROP CHECK my_check;
  Query OK, 0 rows affected
  

• カラムの型を NOT NULL に変更します。

  obclient> CREATE TABLE dept(
      deptno           NUMBER(2,0),
      dname            VARCHAR(14),
      location         VARCHAR(13),
      CONSTRAINT pk_dept PRIMARY KEY(deptno)
  );
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE emp(
      empno         NUMBER(4,0) NOT NULL,
      empname       VARCHAR(10) NOT NULL,
      job           VARCHAR(9) NOT NULL,
      mgr           NUMBER(4,0) NOT NULL,
      hiredate      DATE NOT NULL,
      sal           NUMBER(7,2) DEFAULT NULL,
      comm          NUMBER(7,2) DEFAULT NULL,
      deptno        NUMBER(2,0) DEFAULT NULL,
      CONSTRAINT PK_emp PRIMARY KEY (empno),
      CONSTRAINT FK_deptno  FOREIGN KEY (deptno)  REFERENCES dept (deptno)
  );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE emp MODIFY deptno NUMBER(2,0) DEFAULT '12' NOT NULL;
  Query OK, 0 rows affected
  

列名の変更とNOT NULL制約の追加

obclient> ALTER TABLE test_alter1 MODIFY COLUMN `C1` varchar(10) NOT NULL;
Query OK, 0 rows affected (2.421 sec)

2列に同時にNOT NULL制約を追加

次のコマンドを実行して、C1列とc2列に同時にNOT NULL制約を追加します。

obclient> ALTER TABLE test_alter4 MODIFY COLUMN c1 varchar(10) NOT NULL, MODIFY COLUMN c2 varchar(10) NOT NULL;
Query OK, 0 rows affected (2.605 sec)

前方インデックスを持つ列のデータ型を変更する

1. 次のコマンドを実行して、test_alter5という名前のテーブルを作成し、c1列を含めます。データ型はCHAR(20)であり、c1列にidx_c1という名前の前方インデックスが作成されています：

   obclient> CREATE TABLE test_alter5 (c1 char(20), KEY idx_c1(c1(10)));
   

2. 次のコマンドを実行して、c1列のデータ型をvarchar(20)に変更します：

   obclient> ALTER TABLE test_alter5 MODIFY COLUMN c1 varchar(20);
   

外部キー制約ルールの変更

1. 以下のコマンドを実行して、テーブルt1を作成し、整数列c1に一意性制約を追加します。

   obclient> CREATE TABLE t1(c1 int unique key);
   

2. 以下のコマンドを実行して、整数列c1を含むt2という名前のテーブルを作成し、外部キー制約を定義します。

   obclient> CREATE TABLE t2(c1 int, CONSTRAINT fk_cst_test FOREIGN KEY (c1) REFERENCES t1(c1) on update set null on delete no action);
   

3. 以下のコマンドを実行して、システムテーブルINFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTSをクエリし、外部キー制約fk_cst_testの更新および削除ルールを取得します。

   obclient> SELECT update_rule, delete_rule FROM information_schema.referential_constraints WHERE CONSTRAINT_NAME = 'fk_cst_test';
   

   実行結果は次のとおりです：

   +-------------+-------------+
   | update_rule | delete_rule |
   +-------------+-------------+
   | SET NULL    | NO ACTION   |
   +-------------+-------------+
   

   • UPDATE_RULE：SET NULLが返されます（更新時に外部キーがNULLに設定されることを示します）。
   • DELETE_RULE：NO ACTIONが返されます（削除時に制約をチェックし、操作をブロックする可能性があることを示します）。

列のリネーム操作

• RENAME COLUMNは列の定義を変更せず、列名のみを変更します。ターゲットの名前がテーブル内に既に存在する場合、RENAME COLUMNの実行はエラーとなりますが、元の名前にリネームした場合はエラーになりません。

  obclient> CREATE TABLE tbl8 (a INT, b INT);
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl8 RENAME COLUMN a TO b;
  ERROR 1060 (42S21): Duplicate column name 'b'
  
  obclient> ALTER TABLE tbl8 RENAME COLUMN a TO a;
  Query OK, 0 rows affected
  

• リネームされた列にインデックスが作成されている場合、RENAME COLUMNは正常に実行され、インデックスの定義は自動的に連鎖的に変更されます。

  obclient> CREATE TABLE tbl9 (a INT, b INT, index idx_a(a));
  Query OK, 0 rows affected
  
  obclient> SHOW INDEX FROM tbl9;
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  | tbl9  |          1 | idx_a    |            1 | a           | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  1 row in set
  
  obclient> ALTER TABLE tbl9 RENAME COLUMN a TO c;
  Query OK, 0 rows affected
  
  obclient> SHOW INDEX FROM tbl9;
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  | tbl9  |          1 | idx_a    |            1 | c           | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  1 row in set
  

• リネームされた列がプレフィックスインデックスによって参照されている場合、RENAME COLUMNは正常に実行され、プレフィックスインデックスは連鎖的な変更をサポートします。

  DROP TABLE tbl9;
  obclient> CREATE TABLE tbl9 (c1 INT PRIMARY KEY, c2 BLOB, c3 INT, INDEX i1 (c2(10)));
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl9 RENAME COLUMN c2 TO c2_, RENAME COLUMN c1 TO c2_1;
  DESC tbl9;
  Query OK, 0 rows affected
  
  obclient> SHOW INDEX FROM tbl9;
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  | tbl9  |          0 | PRIMARY  |            1 | c2_1        | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     | NULL       |
  | tbl9  |          1 | i1       |            1 | c2_         | A         |        NULL | 10       | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  2 rows in set
  

• リネームされた列に外部キー制約が設定されている場合、RENAME COLUMNは正常に実行され、外部キー制約は自動的に連鎖的に変更されます。

  obclient> CREATE TABLE tbl10 (a INT PRIMARY KEY);
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl11(b INT, FOREIGN KEY (b) REFERENCES tbl10(a));
  
  obclient> SHOW CREATE TABLE tbl11;
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table | Create Table                                                                                                                                                                                                                                                                                                                                                      |
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | tbl11 | CREATE TABLE `tbl11` (
  `b` int(11) DEFAULT NULL,
  CONSTRAINT `tbl11_OBFK_1694681944513338` FOREIGN KEY (`b`) REFERENCES `test`.`tbl10`(`a`) ON UPDATE RESTRICT ON DELETE RESTRICT
  ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE =  16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set
  
  obclient> ALTER TABLE tbl10 RENAME COLUMN a TO c;
  Query OK, 0 rows affected
  
  obclient> SHOW CREATE TABLE tbl11;
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table | Create Table                                                                                                                                                                                                                                                                                                                                                      |
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | tbl11 | CREATE TABLE `tbl11` (
  `b` int(11) DEFAULT NULL,
  CONSTRAINT `tbl11_OBFK_1694681944513338` FOREIGN KEY (`b`) REFERENCES `test`.`tbl10`(`c`) ON UPDATE RESTRICT ON DELETE RESTRICT
  ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set
  

OceanBaseデータベースでは、以下のシナリオでは列の変更や自動的な連鎖的な変更はサポートされていません。

• リネームされた列が生成リスト式によって参照されている場合、列名の変更はサポートされておらず、実行時にエラーが発生します。

  obclient> CREATE TABLE tbl12(a INT, b INT AS (a + 1), c INT, d INT, CONSTRAINT d_check CHECK(d > 0)) PARTITION BY HASH(c + 1) PARTITIONS 2;
  
  obclient> ALTER TABLE tbl12 RENAME COLUMN a TO e;
  ERROR 3108 (HY000): Column 'a' has a generated column dependency
  

• リネームされた列がパーティション式によって参照されている場合、列名の変更はサポートされておらず、実行時にエラーが発生します。

  obclient> ALTER TABLE tbl12 RENAME COLUMN c TO e;
  ERROR 3855 (HY000): Column 'c' has a partitioning function dependency and cannot be dropped or renamed.
  

• リネームされた列がCHECK制約によって参照されている場合、列名の変更はサポートされておらず、実行時にエラーが発生します。

  obclient> ALTER TABLE tbl12 RENAME COLUMN d TO e;
  ERROR 3959 (HY000): Check constraint 'd_check' uses column 'd', hence column cannot be dropped or renamed.
  

• リネームされた列が関数ベースのインデックスによって参照されている場合、列名の変更はサポートされておらず、実行時にエラーが発生します。

  DROP TABLE IF EXISTS tbl12;
  obclient> CREATE TABLE tbl12(i INT, INDEX ((i+1)));
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl12 RENAME COLUMN i TO j;
  ERROR 3837 (HY000): Column 'i' has a functional index dependency and cannot be dropped or renamed.
  

• リネームされた列がビューによって参照されている場合、RENAME COLUMNは正常に実行されますが、ビューのクエリ時にエラーが発生します。ユーザーによるビュー定義の手動修正が必要です。

  obclient> CREATE TABLE tbl13(a INT);
  Query OK, 0 rows affected
  
  obclient> CREATE VIEW v1 AS SELECT a + 1 FROM tbl13;
  Query OK, 0 rows affected
  
  obclient> SELECT * FROM v1;
  Empty set
  
  obclient> ALTER TABLE tbl13 RENAME COLUMN a TO b;
  Query OK, 0 rows affected
  
  obclient> SELECT * FROM v1;
  ERROR 1356 (42S22): View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
  

• リネームされた列がストアドプロシージャによって参照されている場合、RENAME COLUMNは正常に実行されますが、CALLプロシージャの呼び出し時にエラーが発生します。ユーザーによる手動修正が必要です。

  obclient> CREATE TABLE tbl14(a INT);
  Query OK, 0 rows affected
  
  obclient> CREATE PROCEDURE proc() SELECT a + 1 FROM tbl14;
  Query OK, 0 rows affected
  
  obclient> CALL proc();
  Empty set
  
  obclient> ALTER TABLE tbl14 RENAME COLUMN a TO b;
  Query OK, 0 rows affected
  
  obclient> CALL proc();
  ERROR 1054 (42S22): Unknown column 'a' in 'field list'
  

テーブルのカラムストア属性を変更する

1. 次のSQLステートメントを使用して、テーブル tbl1 を作成します。

   CREATE TABLE tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(50));
   

2. テーブル tbl1 を行ストアとカラムストアの両方を保持する冗長テーブルに変更し、その後、行ストアとカラムストアの両方を保持する冗長属性を削除します。

   ALTER TABLE tbl1 ADD COLUMN GROUP(all columns, each column);
   

   ALTER TABLE tbl1 DROP COLUMN GROUP(all columns, each column);
   

3. テーブル tbl1 をカラムストアテーブルに変更し、その後、カラムストア属性を削除します。

   ALTER TABLE tbl1 ADD COLUMN GROUP(each column);
   

   ALTER TABLE tbl1 DROP COLUMN GROUP(each column);
   

列のSkip Indexプロパティを変更する

次のSQLステートメントを使用して、テーブルtest_skidxを作成します。

CREATE TABLE test_skidx(
  col1 INT SKIP_INDEX(MIN_MAX, SUM),
  col2 FLOAT SKIP_INDEX(MIN_MAX),
  col3 VARCHAR(1024) SKIP_INDEX(MIN_MAX),
  col4 CHAR(10)
  );

• テーブルtest_skidx内の列col2のSkip IndexプロパティをSUM Skip Indexタイプに変更します。

  ALTER TABLE test_skidx MODIFY COLUMN col2 FLOAT SKIP_INDEX(SUM);
  

• テーブル作成後に新規列のSkip Indexプロパティを追加します。テーブルtest_skidx内の列col4にMIN_MAX Skip Indexタイプを追加します。

  ALTER TABLE test_skidx MODIFY COLUMN col4 CHAR(10) SKIP_INDEX(MIN_MAX);
  

• テーブル作成後に列のSkip Indexプロパティを削除します。テーブルtest_skidx内の列col1のSkip Indexプロパティを削除します。

  ALTER TABLE test_skidx MODIFY COLUMN col1 INT SKIP_INDEX();
  

  または

  ALTER TABLE test_skidx MODIFY COLUMN col1 INT;
  

テーブルプロパティの変更

永続的なマクロブロックレベルのBloomフィルターを無効にするテーブル tb 。

ALTER TABLE tb SET enable_macro_block_bloom_filter = False;

関連ドキュメント

テーブルの変更