ALTER TABLE を使用してテーブルスキーマを変更する - PolarDB - Alibaba Cloud - PolarDB

ALTER TABLE ステートメントを使用して、既存のテーブルの構造を変更します。たとえば、列の追加、インデックスの作成、または列定義の変更ができます。

使用上の注意

ALTER TABLE ステートメントを使用して、シャードキーを変更することはできません。
グローバルセカンダリインデックス (GSI) を含むテーブルに対して ALTER ステートメントを使用するには、MySQL 5.7 以降および PolarDB-X 1.0 5.4.1 以降を使用する必要があります。

標準テーブルの変更

説明 PolarDB-X 1.0 では、標準テーブルを変更する構文はネイティブの MySQL 構文と同じです。詳細については、MySQL ドキュメントの「ALTER TABLE Statement」をご参照ください。

構文

ALTER [ONLINE|OFFLINE] [IGNORE] TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]

[例]

列の追加
次の例では、user_log テーブルに idcard という名前の列を追加します：
```
ALTER TABLE user_log ADD COLUMN idcard varchar(30);
```
ローカルインデックスの作成
次の例では、user_log テーブルの idcard 列に、idcard_idx という名前のローカルインデックスを作成します：
```
ALTER TABLE user_log ADD INDEX idcard_idx (idcard);
```
ローカルインデックス名の変更
次の例では、user_log テーブル内の idcard_idx インデックス名を idcard_idx_new に変更します：
```
ALTER TABLE user_log RENAME INDEX `idcard_idx` TO `idcard_idx_new`;
```
ローカルインデックスの削除
次の例では、user_log テーブルから idcard_idx インデックスを削除します：
```
ALTER TABLE user_log DROP INDEX idcard_idx;
```
列の変更
次の例では、user_log テーブルの idcard 列 (データ型は varchar) の長さを 30 から 40 に変更します：
```
ALTER TABLE user_log MODIFY COLUMN idcard varchar(40);
```

GSI を含むテーブルの変更

[列の変更]

GSI を含むテーブルで列を変更する場合、構文は標準テーブルと同じですが、一部の制限があります。詳細については、「グローバルセカンダリインデックスの使用に関する考慮事項」をご参照ください。

次の表は、ALTER TABLE ステートメントを使用した列変更のサポート状況をまとめたものです。

ステートメント	プライマリテーブルのシャードキー	プライマリキー (インデックステーブルのプライマリキーでもある)	ローカル一意インデックス列	GSI のシャードキー	一意インデックス列	インデックス列	カバリング列
ADD COLUMN	該当なし	サポートされていません	該当なし	該当なし	該当なし	該当なし	該当なし
ALTER COLUMN SET DEFAULT and ALTER COLUMN DROP DEFAULT	サポートされていません	サポートされていません	サポートされています	サポートされていません	サポートされていません	サポートされていません	サポートされていません
CHANGE COLUMN	サポートされていません	サポートされていません	サポートされています	サポートされていません	サポートされていません	サポートされていません	サポートされていません
DROP COLUMN	サポートされていません	サポートされていません	一意キーが 1 列の場合にのみサポートされます。	サポートされていません	サポートされていません	サポートされていません	サポートされていません
MODIFY COLUMN	サポートされていません	サポートされていません	サポートされています	サポートされていません	サポートされていません	サポートされていません	サポートされていません

次の表は、ALTER TABLE ステートメントを使用したインデックス変更のサポート状況をまとめたものです。

ステートメント	サポート
ALTER TABLE ADD PRIMARY KEY	サポートされています
ALTER TABLE ADD [UNIQUE/FULLTEXT/SPATIAL/FOREIGN] KEY	サポートされています。プライマリテーブルとインデックステーブルの両方にローカルインデックスを追加できます。インデックス名は、どの GSI 名とも同じにすることはできません。
ALTER TABLE ALTER INDEX index_name {VISIBLE \| INVISIBLE}	禁止されています
ALTER TABLE {DISABLE \| ENABLE} KEYS	サポートされています。このステートメントはプライマリテーブルでのみ実行されます。GSI の状態変更は禁止されています。
ALTER TABLE DROP PRIMARY KEY	禁止されています
ALTER TABLE DROP INDEX	ローカルインデックスまたはグローバルセカンダリインデックスを削除する場合にのみサポートされます。
ALTER TABLE RENAME INDEX	禁止されています

インデックスの変更

構文

ALTER TABLE tbl_name
    alter_specification # GSI の変更では、alter_specification は 1 つのみサポートされます。

alter_specification:
  | ADD GLOBAL {INDEX|KEY} index_name # GSI の場合は、インデックス名を明示的に指定する必要があります。
      [index_type] (index_sharding_col_name,...)
      global_secondary_index_option
      [index_option] ...
  | ADD [CONSTRAINT [symbol]] UNIQUE GLOBAL
      [INDEX|KEY] index_name # GSI の場合は、インデックス名を明示的に指定する必要があります。
      [index_type] (index_sharding_col_name,...)
      global_secondary_index_option
      [index_option] ...
  | DROP {INDEX|KEY} index_name
  | RENAME {INDEX|KEY} old_index_name TO new_index_name

global_secondary_index_option:
    [COVERING (col_name,...)] # カバリングインデックス
    drds_partition_options # index_sharding_col_name で指定した列のみを含める必要があります。

# インデックステーブルのシャーディング方法を指定します。
drds_partition_options:
    DBPARTITION BY db_sharding_algorithm
    [TBPARTITION BY {table_sharding_algorithm} [TBPARTITIONS num]]
db_sharding_algorithm:
    HASH([col_name])
  | {YYYYMM|YYYYWEEK|YYYYDD|YYYYMM_OPT|YYYYWEEK_OPT|YYYYDD_OPT}(col_name)
  | UNI_HASH(col_name)
  | RIGHT_SHIFT(col_name, n)
  | RANGE_HASH(col_name, col_name, n)
table_sharding_algorithm: 
    HASH(col_name) 
  | {MM|DD|WEEK|MMDD|YYYYMM|YYYYWEEK|YYYYDD|YYYYMM_OPT|YYYYWEEK_OPT|YYYYDD_OPT}(col_name)
  | UNI_HASH(col_name)
  | RIGHT_SHIFT(col_name, n)
  | RANGE_HASH(col_name, col_name, n) 

# 以下は MySQL DDL 構文です。
index_sharding_col_name:
    col_name [(length)] [ASC | DESC]
index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
index_type:
    USING {BTREE | HASH}

ALTER TABLE ADD GLOBAL INDEX ステートメントを使用して、既存のテーブルに GSI を追加します。このステートメントでは、新しいインデックスが GSI であることを示すために、標準の MySQL 構文に GLOBAL キーワードを追加します。

また、ALTER TABLE { DROP | RENAME } INDEX ステートメントを使用して GSI を変更することもできます。ただし、テーブルの作成後に GSI を作成する際には、特定の制限が適用されます。GSI の制限事項と規約の詳細については、「グローバルセカンダリインデックスの使用に関する考慮事項」をご参照ください。

グローバルセカンダリインデックスを定義するために使用する句の詳細については、「CREATE TABLE」をご参照ください。

例

次の例は、テーブル作成後に一意グローバルセカンダリインデックスを作成する方法を示します。

GSI の作成
```
# テーブルを作成します。
CREATE TABLE t_order (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext DEFAULT NULL,
  `order_detail` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `l_i_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`order_id`);
# グローバルセカンダリインデックス (GSI) を作成します。
ALTER TABLE t_order ADD UNIQUE GLOBAL INDEX `g_i_buyer` (`buyer_id`) COVERING (`order_snapshot`) dbpartition by hash(`buyer_id`);
```
- プライマリテーブル：t_order テーブルでは、データベースシャーディングは使用しますが、テーブルシャーディングは使用しません。データは、order_id 列のハッシュに基づいてデータベース間でシャーディングされます。
- インデックステーブル：g_i_buyer テーブルでは、データベースシャーディングは使用しますが、テーブルシャーディングは使用しません。データは、buyer_id 列のハッシュに基づいてデータベース間でシャーディングされます。order_snapshot 列はカバリング列として指定されています。
- インデックス定義句：GLOBAL INDEX `g_i_buyer` ON t_order (`buyer_id`) dbpartition by hash(`buyer_id`)。

SHOW INDEX ステートメントを実行して、インデックス情報を表示します。結果には、order_id シャードキー上のローカルインデックスと、buyer_id、id、order_id、order_snapshot 列をカバーする GSI が含まれます。この GSI では、buyer_id がインデックステーブルのシャードキーです。id (プライマリテーブルのプライマリキー) と order_id (プライマリテーブルのシャードキー) はデフォルトのカバリング列として含まれ、order_snapshot は明示的に指定したカバリング列です。

mysql> show index from t_order;
+---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
| TABLE   | NON_UNIQUE | KEY_NAME  | SEQ_IN_INDEX | COLUMN_NAME    | COLLATION | CARDINALITY | SUB_PART | PACKED | NULL | INDEX_TYPE | COMMENT  | INDEX_COMMENT |
+---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
| t_order |          0 | PRIMARY   |            1 | id             | A         |           0 |     NULL | NULL   |      | BTREE      |          |               |
| t_order |          1 | l_i_order |            1 | order_id       | A         |           0 |     NULL | NULL   | YES  | BTREE      |          |               |
| t_order |          0 | g_i_buyer |            1 | buyer_id       | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | INDEX    |               |
| t_order |          1 | g_i_buyer |            2 | id             | NULL      |           0 |     NULL | NULL   |      | GLOBAL     | COVERING |               |
| t_order |          1 | g_i_buyer |            3 | order_id       | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | COVERING |               |
| t_order |          1 | g_i_buyer |            4 | order_snapshot | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | COVERING |               |
+---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+

SHOW GLOBAL INDEX ステートメントを実行して、GSI 固有の情報を表示できます。詳細については、「SHOW GLOBAL INDEX」をご参照ください。

mysql> show global index from t_order;
+---------------------+---------+------------+-----------+-------------+------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
| SCHEMA              | TABLE   | NON_UNIQUE | KEY_NAME  | INDEX_NAMES | COVERING_NAMES               | INDEX_TYPE | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT | STATUS |
+---------------------+---------+------------+-----------+-------------+------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
| ZZY3_DRDS_LOCAL_APP | t_order | 0          | g_i_buyer | buyer_id    | id, order_id, order_snapshot | NULL       | buyer_id         | HASH                | 4                  |                  | NULL                | NULL               | PUBLIC |
+---------------------+---------+------------+-----------+-------------+------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+

インデックステーブルの構造を表示します。インデックステーブルには、プライマリテーブルのプライマリキー、シャードキー列、デフォルトのカバリング列、および明示的に指定したカバリング列が含まれます。プライマリキー列から AUTO_INCREMENT 属性は削除され、プライマリテーブルのローカルインデックスは除外されます。一意 GSI の場合、グローバルな一意性制約を強制するために、すべてのシャードキー列に一意インデックスがデフォルトで作成されます。

mysql> show create table g_i_buyer;
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                 |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| g_i_buyer | CREATE TABLE `g_i_buyer` (
  `id` bigint(11) NOT NULL,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext,
  PRIMARY KEY (`id`),
  UNIQUE KEY `auto_shard_key_buyer_id` (`buyer_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`buyer_id`) |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

GSI の削除
g_i_buyer という名前の GSI を削除します。対応するインデックステーブルも削除されます。
```
ALTER TABLE `t_order` DROP INDEX `g_i_buyer`;
```
インデックス名の変更
デフォルトでは、GSI の名前変更は禁止されています。