How to Create Partitions with Multiple Columns

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 如何创建多个字段的分区

| username: TiDBer_7Q5CQdQd

[TiDB Usage Environment] Production Environment
[TiDB Version] v7.3

Requirement: I want to create partitions for all provinces with reg_province = 'Anhui Province', display=1, reg_province=Beijing, display=1, etc., and also create a large partition for display=1 without distinguishing provinces. The table will have data where display is not equal to one. How should the following SQL be rewritten?
PARTITION BY LIST COLUMNS(reg_province,display)
(PARTITION ahs VALUES IN (‘安徽省’,1),
PARTITION bjs VALUES IN (‘北京市’,1),
PARTITION fjs VALUES IN (‘福建省’,1),
PARTITION gds VALUES IN (‘广东省’,1),
PARTITION hebs VALUES IN (‘河北省’,1),
PARTITION hubs VALUES IN (‘湖北省’,1),
PARTITION hens VALUES IN (‘河南省’,1),
PARTITION huns VALUES IN (‘湖南省’,1),
PARTITION jss VALUES IN (‘江苏省’,1),
PARTITION lns VALUES IN (‘辽宁省’,1),
PARTITION scs VALUES IN (‘四川省’,1),
PARTITION sds VALUES IN (‘山东省’,1),
PARTITION shs VALUES IN (‘上海市’,1),
PARTITION sxs VALUES IN (‘陕西省’,1),
PARTITION tjs VALUES IN (‘天津市’,1),
PARTITION zjs VALUES IN (‘浙江省’,1),
PARTITION gzs VALUES IN (‘贵州省’,1),
PARTITION di DEFAULT,1
PARTITION other DEFAULT,DEFAULT
)

| username: zhanggame1 | Original post link

After some research, it seems that it won’t work.

| username: 小龙虾爱大龙虾 | Original post link

TiDB does not have secondary partitioning. You can create a list columns partition with these two columns, and pass the condition to display. Check if it can use partition pruning. If it can, the performance difference from secondary partitioning is not significant.

| username: tidb菜鸟一只 | Original post link

Creating a partition with display=1 that does not distinguish between provinces is quite difficult, right? Does it mean that the province field is empty and display is 1? Or does it mean that the province field is not one of the given strings and then display is 1?

| username: TiDBer_7Q5CQdQd | Original post link

The province field can be anything, as long as it meets display=.

| username: TiDBer_7Q5CQdQd | Original post link

Is there a problem with my writing? It keeps reporting errors.

| username: zhanggame1 | Original post link

Neither of these are supported. Normally, PARTITION 'other' DEFAULT is supported.

| username: TiDBer_小阿飞 | Original post link

It can only achieve the remaining partitions for “other” and cannot achieve putting the entire table into one partition.

| username: TiDBer_小阿飞 | Original post link

You can try ### List COLUMNS partition

PARTITION BY LIST COLUMNS(reg_province, display)
(PARTITION ahs VALUES IN ((‘Anhui Province’), (1)),

PARTITION di VALUES IN (DEFAULT, (1)),
PARTITION other VALUES IN ((‘Anhui Province’, ‘Beijing’, …), (1))

| username: tidb菜鸟一只 | Original post link

Doesn’t that conflict? The ones with provinces above also have display=1.