Gunakan ALTER TABLE untuk mengubah skema tabel - PolarDB - Alibaba Cloud - PolarDB

Gunakan pernyataan ALTER TABLE untuk mengubah struktur tabel yang sudah ada, seperti menambahkan kolom, membuat indeks, atau mengubah definisi kolom.

Catatan penggunaan

Anda tidak dapat menggunakan pernyataan ALTER TABLE untuk mengubah kunci shard.
Untuk menggunakan pernyataan ALTER pada tabel yang berisi global secondary index (GSI), Anda harus menggunakan MySQL 5.7 atau versi yang lebih baru serta PolarDB-X 1.0 5.4.1 atau versi yang lebih baru.

Ubah tabel standar

Catatan Di PolarDB-X 1.0, sintaks untuk mengubah tabel standar sama dengan sintaks native MySQL. Untuk informasi selengkapnya, lihat dokumentasi MySQL untuk Pernyataan ALTER TABLE.

Sintaks

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

Example

Tambahkan kolom
Contoh berikut menambahkan kolom bernama idcard ke tabel user_log:
```
ALTER TABLE user_log ADD COLUMN idcard varchar(30);
```
Buat indeks lokal
Contoh berikut membuat indeks lokal bernama idcard_idx pada kolom idcard di tabel user_log:
```
ALTER TABLE user_log ADD INDEX idcard_idx (idcard);
```
Ubah nama indeks lokal
Contoh berikut mengubah nama indeks idcard_idx menjadi idcard_idx_new di tabel user_log:
```
ALTER TABLE user_log RENAME INDEX `idcard_idx` TO `idcard_idx_new`;
```
Hapus indeks lokal
Contoh berikut menghapus indeks idcard_idx dari tabel user_log:
```
ALTER TABLE user_log DROP INDEX idcard_idx;
```
Ubah kolom
Contoh berikut mengubah panjang kolom idcard yang bertipe data varchar dari 30 menjadi 40 di tabel user_log:
```
ALTER TABLE user_log MODIFY COLUMN idcard varchar(40);
```

Ubah tabel dengan GSI

Column modifications

Saat mengubah kolom pada tabel yang berisi GSI, sintaksnya sama seperti pada tabel standar, tetapi terdapat beberapa batasan. Untuk informasi selengkapnya, lihat Pertimbangan saat menggunakan global secondary index.

Tabel berikut merangkum dukungan untuk modifikasi kolom dengan pernyataan ALTER TABLE.

Pernyataan	Kunci shard tabel utama	Kunci primer (juga kunci primer tabel indeks)	Kolom indeks unik lokal	Kunci shard GSI	Kolom indeks unik	Kolom indeks	Kolom covering
ADD COLUMN	N/A	Tidak didukung	N/A	N/A	N/A	N/A	N/A
ALTER COLUMN SET DEFAULT dan ALTER COLUMN DROP DEFAULT	Tidak didukung	Tidak didukung	Didukung	Tidak didukung	Tidak didukung	Tidak didukung	Tidak didukung
CHANGE COLUMN	Tidak didukung	Tidak didukung	Didukung	Tidak didukung	Tidak didukung	Tidak didukung	Tidak didukung
DROP COLUMN	Tidak didukung	Tidak didukung	Didukung hanya jika kunci unik memiliki satu kolom.	Tidak didukung	Tidak didukung	Tidak didukung	Tidak didukung
MODIFY COLUMN	Tidak didukung	Tidak didukung	Didukung	Tidak didukung	Tidak didukung	Tidak didukung	Tidak didukung

Tabel berikut merangkum dukungan untuk modifikasi indeks dengan pernyataan ALTER TABLE.

Pernyataan	Dukungan
ALTER TABLE ADD PRIMARY KEY	Didukung
ALTER TABLE ADD [UNIQUE/FULLTEXT/SPATIAL/FOREIGN] KEY	Didukung. Anda dapat menambahkan indeks lokal baik pada tabel utama maupun tabel indeks. Nama indeks tidak boleh sama dengan nama GSI mana pun.
ALTER TABLE ALTER INDEX index_name {VISIBLE \| INVISIBLE}	Dilarang
ALTER TABLE {DISABLE \| ENABLE} KEYS	Didukung. Pernyataan ini hanya dijalankan pada tabel utama. Mengubah status GSI dilarang.
ALTER TABLE DROP PRIMARY KEY	Dilarang
ALTER TABLE DROP INDEX	Didukung hanya untuk menghapus indeks lokal atau global secondary index.
ALTER TABLE RENAME INDEX	Dilarang

Modifikasi indeks

Sintaks

ALTER TABLE tbl_name
    alter_specification # Hanya satu alter_specification yang didukung untuk modifikasi GSI.

alter_specification:
  | ADD GLOBAL {INDEX|KEY} index_name # Anda harus secara eksplisit menentukan nama indeks untuk GSI.
      [index_type] (index_sharding_col_name,...)
      global_secondary_index_option
      [index_option] ...
  | ADD [CONSTRAINT [symbol]] UNIQUE GLOBAL
      [INDEX|KEY] index_name # Anda harus secara eksplisit menentukan nama indeks untuk 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,...)] # Covering Index
    drds_partition_options # Harus hanya berisi kolom yang ditentukan dalam index_sharding_col_name.

# Tentukan metode sharding untuk tabel indeks.
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) 

# Berikut adalah sintaks DDL MySQL.
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}

Gunakan pernyataan ALTER TABLE ADD GLOBAL INDEX untuk menambahkan GSI ke tabel yang sudah ada. Pernyataan ini menambahkan kata kunci GLOBAL ke sintaks MySQL standar guna menunjukkan bahwa indeks baru tersebut merupakan GSI.

Anda juga dapat menggunakan pernyataan ALTER TABLE { DROP | RENAME } INDEX untuk mengubah GSI. Namun, terdapat beberapa batasan saat membuat GSI setelah tabel dibuat. Untuk detail mengenai batasan dan konvensi GSI, lihat Pertimbangan saat menggunakan global secondary index.

Untuk informasi selengkapnya tentang klausa yang digunakan untuk mendefinisikan global secondary index, lihat CREATE TABLE.

Contoh

Contoh berikut menunjukkan cara membuat global secondary index unik setelah tabel dibuat.

Buat GSI

# Buat tabel.
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`);
# Buat global secondary index (GSI).
ALTER TABLE t_order ADD UNIQUE GLOBAL INDEX `g_i_buyer` (`buyer_id`) COVERING (`order_snapshot`) dbpartition by hash(`buyer_id`);

Tabel utama: Tabel t_order menggunakan sharding database tetapi tidak menggunakan sharding tabel. Data dishard di berbagai database berdasarkan hash kolom order_id.
Tabel indeks: Tabel g_i_buyer menggunakan sharding database tetapi tidak menggunakan sharding tabel. Data dishard di berbagai database berdasarkan hash kolom buyer_id. Kolom order_snapshot ditentukan sebagai kolom covering.
Klausa definisi indeks: GLOBAL INDEX `g_i_buyer` ON t_order (`buyer_id`) dbpartition by hash(`buyer_id`).

Jalankan pernyataan SHOW INDEX untuk melihat informasi indeks. Hasilnya mencakup indeks lokal pada kunci shard order_id dan GSI, yang mencakup kolom buyer_id, id, order_id, dan order_snapshot. Dalam GSI ini, buyer_id adalah kunci shard tabel indeks. id (kunci primer tabel utama) dan order_id (kunci shard tabel utama) disertakan sebagai kolom covering default, sedangkan order_snapshot adalah kolom covering yang ditentukan secara eksplisit.

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 |               |
+---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+

Anda dapat menjalankan pernyataan SHOW GLOBAL INDEX untuk melihat informasi spesifik GSI. Untuk informasi selengkapnya, lihat 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 |
+---------------------+---------+------------+-----------+-------------+------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+

Lihat struktur tabel indeks. Tabel indeks berisi kolom kunci primer, kunci shard, kolom covering default, dan kolom covering yang ditentukan secara eksplisit dari tabel utama. Atribut AUTO_INCREMENT dihapus dari kolom kunci primer, dan indeks lokal dari tabel utama tidak disertakan. Untuk GSI unik, indeks unik dibuat secara default pada semua kolom kunci shard-nya untuk menegakkan batasan unik global.

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`) |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Hapus GSI
Hapus GSI bernama g_i_buyer. Tabel indeks yang sesuai juga akan dihapus.
```
ALTER TABLE `t_order` DROP INDEX `g_i_buyer`;
```
Ubah nama indeks
Secara default, mengubah nama GSI dilarang.