PolarDB:Colonnes générées

col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED | LOGICAL] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

Limites

Les colonnes générées dans PolarDB-X partagent les contraintes standard de MySQL et ajoutent plusieurs restrictions spécifiques aux environnements distribués.

Contraintes communes avec MySQL

• Les valeurs par défaut ne sont pas prises en charge.

• Il est impossible de définir AUTO_INCREMENT sur une colonne générée, et une colonne générée ne peut pas référencer une colonne AUTO_INCREMENT.

• Les fonctions non déterministes (telles que UUID(), CONNECTION_ID() et NOW()) ne peuvent pas être utilisées dans l'expression d'une colonne générée.

• L'utilisation de variables dans les expressions est interdite.

• Les sous-requêtes ne sont pas autorisées dans les expressions.

• Vous ne pouvez pas spécifier les valeurs des colonnes générées dans les instructions INSERT ou UPDATE. La base de données les calcule toujours automatiquement.

Spécificités PolarDB-X

• Vous ne pouvez pas ajouter de colonnes générées aux tables pour lesquelles l'archivage des données froides est activé.

• Les colonnes VIRTUAL et STORED ne peuvent servir ni de clés de partition, ni de clés primaires, ni de clés uniques.

• Les colonnes VIRTUAL et STORED ne peuvent pas référencer de fonctions stockées.

• Si un index secondaire global (GSI) inclut des colonnes générées VIRTUAL ou STORED, il doit également contenir toutes les colonnes référencées dans les expressions de ces colonnes générées.

• Une colonne générée LOGICAL ne peut pas référencer de colonne générée VIRTUAL ou STORED dans son expression.

• Vous ne pouvez plus modifier le type d'une colonne générée LOGICAL ni les types des colonnes référencées dans son expression après sa création.

• Les colonnes générées LOGICAL et les colonnes qu'elles référencent prennent uniquement en charge les types de données suivants :

  • Types entiers : BIGINT, INT, MEDIUMINT, SMALLINT, TINYINT

  • Types date : DATETIME, DATE, TIMESTAMP (les colonnes avec ON UPDATE CURRENT_TIMESTAMP ne sont pas prises en charge)

  • Types chaîne : CHAR, VARCHAR

Exemples : colonnes VIRTUAL et STORED

Exemple 1 : Calcul automatique d'une valeur dérivée

Créez une table où l'hypoténuse d'un triangle rectangle est calculée sous forme de colonne générée :

CREATE TABLE triangle (
  sidea DOUBLE,
  sideb DOUBLE,
  sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
);

INSERT INTO triangle (sidea, sideb) VALUES (1, 1), (3, 4), (6, 8);

SELECT * FROM triangle;

Résultat :

+-------+-------+--------------------+
| sidea | sideb | sidec              |
+-------+-------+--------------------+
|   1.0 |   1.0 | 1.4142135623730951 |
|   3.0 |   4.0 |                5.0 |
|   6.0 |   8.0 |               10.0 |
+-------+-------+--------------------+

Exemple 2 : Colonne générée dans une table partitionnée

Créez une table partitionnée comportant une colonne générée VIRTUAL nommée b :

CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` int(11) GENERATED ALWAYS AS (`a` + 1),
  PRIMARY KEY (`a`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 dbpartition by hash(`a`);

INSERT INTO t1 (a) VALUES (1);

SELECT * FROM t1;

Résultat :

+---+---+
| a | b |
+---+---+
| 1 | 2 |
+---+---+

Exemples : colonnes LOGICAL

Les colonnes générées LOGICAL constituent le choix idéal lorsque vous avez besoin d'un partitionnement flexible basé sur une valeur dérivée, ou lorsque vous souhaitez utiliser une UDF dans l'expression.

Exemple 1 : Partitionner selon une sous-chaîne d'une colonne texte

Utilisez les deux derniers caractères de la colonne b comme clé de partition :

CREATE TABLE `t2` (
  `a` int(11) NOT NULL,
  `b` varchar(32) DEFAULT NULL,
  `c` varchar(2) GENERATED ALWAYS AS (SUBSTR(`b`, -2)) LOGICAL,
  PRIMARY KEY (`a`),
  KEY `auto_shard_key_c` USING BTREE (`c`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 dbpartition by hash(`c`);

Exemple 2 : Référencer une UDF dans une colonne générée LOGICAL

Commencez par créer la fonction définie par l'utilisateur my_abs :

DELIMITER &&
CREATE FUNCTION my_abs (
  a INT
)
RETURNS INT
BEGIN
  IF a < 0 THEN
    RETURN -a;
  ELSE
    RETURN a;
  END IF;
END&&
DELIMITER ;

Créez ensuite une table avec une colonne générée LOGICAL qui appelle my_abs :

CREATE TABLE `t3` (
  `a` int(11) NOT NULL,
  `b` int(11) GENERATED ALWAYS AS (MY_ABS(`a`)) LOGICAL,
  PRIMARY KEY (`a`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 dbpartition by hash(`b`);

INSERT INTO t3 (a) VALUES (1), (-1);

Vérifiez que les requêtes sur b sont bien transmises au nœud de données :

EXPLAIN SELECT * FROM t3 WHERE b = 1;

+-----------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                     |
+-----------------------------------------------------------------------------------------------------------+
| LogicalView(tables="TEST_000002_GROUP.t3_WHHZ", sql="SELECT `a`, `b` FROM `t3` AS `t3` WHERE (`b` = ?)") |
+-----------------------------------------------------------------------------------------------------------+

SELECT * FROM t3 WHERE b = 1;

+----+------+
| a  | b    |
+----+------+
| -1 |    1 |
|  1 |    1 |
+----+------+

Limites

Exemples

Exemple 1 : Index local sur une colonne VIRTUAL

Cet exemple montre comment indexer un champ JSON en l'extrayant vers une colonne générée VIRTUAL. Les colonnes JSON ne pouvant pas être indexées directement dans PolarDB-X (ni dans MySQL), ce modèle constitue la solution de contournement standard :

CREATE TABLE t4 (
    a BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    c JSON,
    g INT AS (c->"$.id") VIRTUAL
) DBPARTITION BY HASH(a);

CREATE INDEX `i` ON `t4`(`g`);

INSERT INTO t4 (c) VALUES
  ('{"id": "1", "name": "Fred"}'),
  ('{"id": "2", "name": "Wilma"}'),
  ('{"id": "3", "name": "Barney"}'),
  ('{"id": "4", "name": "Betty"}');

EXPLAIN EXECUTE SELECT c->>"$.name" AS name FROM t4 WHERE g > 2;

+------+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id   | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+------+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| 1    | SIMPLE      | t4    | NULL       | range | i             | i    | 5       | NULL | 1    | 100      | Using where |
+------+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+

Le plan d'exécution confirme l'utilisation de l'index i.

Exemple 2 : Index local sur une colonne LOGICAL

CREATE TABLE t5 (
    a BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    c varchar(32),
    g char(2) AS (substr(`c`, 2)) LOGICAL
) DBPARTITION BY HASH(a);

CREATE INDEX `i` ON `t5`(`g`);

INSERT INTO t5 (c) VALUES
  ('1111'),
  ('1112'),
  ('1211'),
  ('1311');

EXPLAIN EXECUTE SELECT c AS name FROM t5 WHERE g = '11';

+------+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+
| id   | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+------+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+
| 1    | SIMPLE      | t5    | NULL       | ref  | i             | i    | 8       | NULL | 4    | 100.00   | Using XPlan, Using where |
+------+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+

Exemple 3 : Index global sur une colonne LOGICAL

CREATE TABLE t6 (
    a BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    c varchar(32),
    g char(2) AS (substr(`c`, 2)) LOGICAL
) DBPARTITION BY HASH(a);

CREATE GLOBAL INDEX `g_i` ON `t6`(`g`) COVERING(`c`) DBPARTITION BY HASH(`g`);

INSERT INTO t6 (c) VALUES
  ('1111'),
  ('1112'),
  ('1211'),
  ('1311');

EXPLAIN SELECT c AS name FROM t6 WHERE g = '11';

+---------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                               |
+---------------------------------------------------------------------------------------------------------------------+
| IndexScan(tables="TEST_DRDS_000000_GROUP.g_i_J1MT", sql="SELECT `c` AS `name` FROM `g_i` AS `g_i` WHERE (`g` = ?)") |
+---------------------------------------------------------------------------------------------------------------------+

Limites

• Les index d'expression sont désactivés par défaut. Activez cette fonctionnalité avec la commande suivante :

  SET GLOBAL ENABLE_CREATE_EXPRESSION_INDEX = TRUE;

• Les index globaux ne sont pas pris en charge.

• Les index uniques ne sont pas pris en charge.

• Vous ne pouvez pas créer d'index d'expression dans une instruction CREATE TABLE. Créez d'abord la table, puis utilisez ALTER TABLE ou CREATE INDEX.

• La suppression d'un index d'expression via DROP INDEX n'efface pas les colonnes générées créées automatiquement. Supprimez-les manuellement à l'aide de ALTER TABLE en mode DRDS ou en mode AUTO.

Exemples

Exemple 1 : Création d'un index d'expression

1. Créez la table :

   CREATE TABLE t7 (
       a BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
       c varchar(32)
   ) DBPARTITION BY HASH(a);

2. Créez un index d'expression sur substr(c, 2) :

   CREATE INDEX `i` ON `t7`(substr(`c`, 2));

3. Après la création, PolarDB-X réécrit la structure de la table pour ajouter une colonne générée VIRTUAL correspondant à chaque entrée d'expression :

   CREATE TABLE `t7` (
     `a` bigint(20) NOT NULL AUTO_INCREMENT BY GROUP,
     `c` varchar(32) DEFAULT NULL,
     `i$0` varchar(32) GENERATED ALWAYS AS (substr(`c`, 2)) VIRTUAL,
     PRIMARY KEY (`a`),
     KEY `i` (`i$0`)
   ) ENGINE = InnoDB dbpartition by hash(`a`);

   La colonne i$0 contient le résultat de l'expression, et l'index i est construit sur i$0.

4. Vérifiez que l'index est bien sélectionné pour les requêtes filtrant sur l'expression :

   EXPLAIN EXECUTE SELECT * FROM t7 WHERE substr(`c`, 2) = '11';

   +------+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
   | id   | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
   +------+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
   | 1    | SIMPLE      | t7    | NULL       | ref  | i             | i    | 131     | const | 1    | 100      | NULL  |
   +------+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+

Exemple 2 : Index d'expression avec plusieurs expressions

Lorsqu'un index comprend plusieurs expressions, PolarDB-X crée une colonne générée distincte pour chaque entrée d'expression (les entrées sans expression restent inchangées).

Créez l'index :

CREATE INDEX idx ON t8(
  a + 1,
  b,
  SUBSTR(c, 2)
);

Structure de table résultante :

CREATE TABLE `t8` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  `c` varchar(32) DEFAULT NULL,
  `idx$0` bigint(20) GENERATED ALWAYS AS (`a` + 1) VIRTUAL,
  `idx$2` varchar(32) GENERATED ALWAYS AS (substr(`c`, 2)) VIRTUAL,
  PRIMARY KEY (`a`),
  KEY `idx` (`idx$0`, `b`, `idx$2`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 dbpartition by hash(`a`);

Les colonnes idx$0 et idx$2 correspondent respectivement à la première et à la troisième entrée de l'index. La colonne b étant une colonne classique, elle ne nécessite pas d'enveloppe de colonne générée.

Les requêtes qui filtrent simultanément sur ces trois conditions tirent parti de l'index :

EXPLAIN EXECUTE SELECT * FROM t8 WHERE a+1=10 AND b=20 AND SUBSTR(c,2)='ab';

+------+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
| id   | select_type | table | partitions | type | possible_keys | key  | key_len | ref               | rows | filtered | Extra |
+------+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
| 1    | SIMPLE      | t8    | NULL       | ref  | idx           | idx  | 145     | const,const,const | 1    | 100      | NULL  |
+------+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------+