tencent cloud

TDSQL-C for MySQL

Service Agreement
Service Level Agreement
Terms of Service
Política TDSQL- C
Política de privacidade
Contrato de segurança e processamento de dados
DocumentaçãoTDSQL-C for MySQL

Columns Syntax

Modo Foco
Tamanho da Fonte
Última atualização: 2024-10-09 10:38:07

Feature Overview

The columns syntax is used to query the column information of a table.

Supported Versions

The kernel version should be 3.1.15 or later for TXSQL 8.0.

Applicable Scenarios

It is applicable to scenarios where a lot of column information is queried from a table and the query performance needs improvement.

Use Instructions

List Columns Syntax

A single column is specified:
	CREATE TABLE `t1` (
	  `id` int DEFAULT NULL,
	  `purchased` varchar(12) DEFAULT NULL,
	  KEY `idx` (`id`,`purchased`) GLOBAL
	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
	PARTITION BY RANGE (`id`)
	SUBPARTITION BY LIST COLUMNS (`purchased`)
	SUBPARTITION TEMPLATE 
	(SUBPARTITION s0 VALUES IN ('0', '1', '2') ENGINE = InnoDB, 
	 SUBPARTITION s1 VALUES IN ('5', '6', '8') ENGINE = InnoDB) 
	(PARTITION p0 VALUES LESS THAN (1990) , 
	 PARTITION p1 VALUES LESS THAN (1999));
Multiple columns are specified:
	CREATE TABLE `t2` (
	  `id` int DEFAULT NULL,
	  `purchased` varchar(12) DEFAULT NULL,
	  KEY `idx` (`id`,`purchased`) GLOBAL
	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
	PARTITION BY RANGE (`id`)
	SUBPARTITION BY LIST COLUMNS (`purchased`, `id`)
	SUBPARTITION TEMPLATE 
	(SUBPARTITION s0 VALUES IN (('0', 1), ('1', 1), ('2', 1995)) ENGINE = InnoDB, 
	 SUBPARTITION s1 VALUES IN (('5' ,5), ('6', 6)) ENGINE = InnoDB) 
	(PARTITION p0 VALUES LESS THAN (1990) , 
	 PARTITION p1 VALUES LESS THAN (1999));

Range Columns Syntax

A single column is specified:
	CREATE TABLE `t3` (
	  `id` int DEFAULT NULL,
	  `purchased` varchar(12) DEFAULT NULL,
	  KEY `idx` (`id`,`purchased`) GLOBAL
	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
	PARTITION BY RANGE (`id`)
	SUBPARTITION BY RANGE COLUMNS (`purchased`)
	SUBPARTITION TEMPLATE 
	(SUBPARTITION s0 VALUES LESS THAN ('5') ENGINE = InnoDB, 
	 SUBPARTITION s1 VALUES LESS THAN ('8') ENGINE = InnoDB) 
	(PARTITION p0 VALUES LESS THAN (1990) , 
	 PARTITION p1 VALUES LESS THAN (1999));
Multiple columns are specified:
	CREATE TABLE `t4` (
	  `id` int DEFAULT NULL,
	  `purchased` varchar(12) DEFAULT NULL,
	  KEY `idx` (`id`,`purchased`) GLOBAL
	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
	PARTITION BY RANGE (`id`)
	SUBPARTITION BY RANGE COLUMNS (`purchased`, `id`)
	SUBPARTITION TEMPLATE 
	(SUBPARTITION s0 VALUES LESS THAN ('5', 55) ENGINE = InnoDB, 
	 SUBPARTITION s1 VALUES LESS THAN ('8', 88) ENGINE = InnoDB) 
	(PARTITION p0 VALUES LESS THAN (1990) , 
	 PARTITION p1 VALUES LESS THAN (1999));

Supported Data Types of Columns

For supported data types of columns, see official documentation.

Pruning Instructions

Subpartition pruning and partition pruning use the same method and are independent of each other. Specifically, partition pruning can filter the partition key part in a WHERE condition to obtain a partition set s1, and subpartition pruning can filter the subpartition key part in the WHERE condition to obtain a subpartition template set s2. The final result is that s2 in each s1 is selected.

Restrictions

The new parameter txsql_subpartition_support_multiple_columns restricts the use of multiple columns. When it is set to OFF, only single-column syntax is allowed. When it is set to ON, both multiple-column and single-column syntax are allowed.
Parameter Name
Dynamic
Type
Default Value
Valid Values/Value Range
Description
txsql_subpartition_support_multiple_columns
yes
bool
OFF
OFF/ON
Restricts the use of multiple columns. When it is set to OFF, only single-column syntax is allowed. When it is set to ON, both multiple-column and single-column syntax are allowed.
Próximo: Service Level Agreement

Ajuda e Suporte

Esta página foi útil?

comentários