Utiliser l'instruction ALTER TABLE pour modifier la structure d'une table - PolarDB

L'instruction ALTER TABLE permet de modifier la structure d'une table, par exemple en ajoutant des colonnes ou des index, ou en modifiant les définitions de données. Cette instruction s'applique uniquement aux bases de données en mode DRDS.

Remarques sur l'utilisation

Vous ne pouvez pas utiliser l'instruction ALTER TABLE pour modifier une clé de sharding.

Syntaxe

Remarque

Pour connaître la syntaxe détaillée, consultez l'instruction MySQL ALTER TABLE .

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

Example

Ajouter une colonne

Ajoutez une colonne idcard à la table user_log :
```
ALTER TABLE user_log
    ADD COLUMN idcard varchar(30);
```
Ajouter un index local

Ajoutez un index nommé idcard_idx sur la colonne idcard de la table user_log :
```
ALTER TABLE user_log
    ADD INDEX idcard_idx (idcard);
```
Renommer un index local

Renommez l'index idcard_idx de la table user_log en idcard_idx_new :
```
ALTER TABLE user_log
    RENAME INDEX `idcard_idx` TO `idcard_idx_new`;
```
Supprimer un index local

Supprimez l'index idcard_idx de la table user_log :
```
ALTER TABLE user_log
    DROP INDEX idcard_idx;
```
Modifier une colonne

Modifiez la longueur de la colonne idcard (varchar) de la table user_log en la faisant passer de 30 à 40 :
```
ALTER TABLE user_log
    MODIFY COLUMN idcard varchar(40);
```

Index secondaires globaux

PolarDB-X prend en charge les index secondaires globaux (GSI). Pour plus d'informations sur les principes de base, consultez la rubrique Index secondaires globaux.

Modifications de colonnes

Pour les tables utilisant des GSI, la syntaxe de modification des colonnes est identique à celle des tables classiques.

Remarque

Lorsque vous modifiez une table contenant un index secondaire global, des restrictions supplémentaires s'appliquent aux modifications de colonnes. Pour plus d'informations sur les limites et les conventions des GSI, consultez la rubrique Créer et utiliser des index secondaires globaux.

Modifications d'index

Syntaxe

ALTER TABLE tbl_name
    alter_specification # For GSI-related changes, only one alter_specification is supported.

alter_specification:
  | ADD GLOBAL {INDEX|KEY} index_name # You must explicitly specify the GSI name.
      [index_type] (index_sharding_col_name,...)
      global_secondary_index_option
      [index_option] ...
  | ADD [CONSTRAINT [symbol]] UNIQUE GLOBAL
      [INDEX|KEY] index_name # You must explicitly specify the GSI name.
      [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 # Must contain only columns specified in index_sharding_col_name.

# Specify the sharding method for the index table.
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 syntax
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}

L'instruction ALTER TABLE ADD GLOBAL INDEX ajoute un GSI à une table existante. Elle étend la syntaxe MySQL en introduisant le mot-clé GLOBAL pour spécifier que l'index ajouté est un GSI.

La syntaxe ALTER TABLE { DROP | RENAME } INDEX permet également de modifier un GSI. Ces opérations présentent des limitations spécifiques. Pour plus d'informations sur les limites et les conventions des GSI, consultez la rubrique Créer et utiliser des index secondaires globaux.

Pour obtenir des descriptions détaillées des clauses de définition des GSI, consultez la rubrique CREATE TABLE (mode DRDS).

Exemples

Créer un index secondaire global

Cet exemple crée un GSI unique sur une table existante.

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

Table de base : la table t_order est shardée uniquement par base de données (et non par table). La méthode de sharding de la base de données est un hachage sur la colonne order_id.
Table d'index : la table g_i_buyer est shardée uniquement par base de données (et non par table). La méthode de sharding de la base de données est un hachage sur la colonne buyer_id et la colonne de couverture est order_snapshot.
Clause de définition de l'index : GLOBAL INDEX .

Utilisez l'instruction SHOW INDEX pour afficher les informations relatives aux index. Les résultats incluent l'index local sur la clé de sharding order_id ainsi que le GSI, qui est indexé sur buyer_id et comprend plusieurs colonnes de couverture. Dans cet exemple, buyer_id constitue la clé de sharding de la table d'index. Les colonnes id et order_id sont des colonnes de couverture par défaut (clé primaire et clé de sharding de la table de base). La colonne order_snapshot est la colonne de couverture spécifiée explicitement.

Remarque

Pour plus de détails sur les limites et les conventions des GSI, consultez la rubrique Créer et utiliser des index secondaires globaux. Pour plus d'informations sur l'instruction SHOW INDEX, consultez la rubrique SHOW INDEX.

show index from t_order;

Le résultat suivant est renvoyé :

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

Utilisez l'instruction SHOW GLOBAL INDEX pour afficher uniquement les informations relatives aux GSI. Pour plus d'informations, consultez la rubrique SHOW GLOBAL INDEX.

show global index from t_order;

Le résultat suivant est renvoyé :

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

Affichez la structure de la table d'index. La table d'index inclut la clé primaire et la clé de sharding de la table de base, ainsi que les colonnes de couverture par défaut et personnalisées. L'attribut AUTO_INCREMENT est supprimé de la colonne de clé primaire et les index locaux de la table de base sont exclus. Un index unique est créé par défaut sur la clé de sharding de la table d'index afin d'appliquer une contrainte d'unicité globale.

show create table g_i_buyer;

Le résultat suivant est renvoyé :

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

Supprimer un index secondaire global

Supprimez le GSI nommé g_i_seller. La table d'index correspondante est également supprimée.
```
# Drop the index
ALTER TABLE `t_order` DROP INDEX `g_i_seller`;
```
Renommer un index

Par défaut, le renommage d'un GSI est restreint. Pour plus de détails sur les limites et les conventions des GSI, consultez la rubrique Créer et utiliser des index secondaires globaux.