使用 PARTITION BY LIST 方式建表后，按照 alter table描述的方式加默认分区，报错。
建表语句如下：
show create table dev_ifa_android;
+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dev_ifa_android | CREATE TABLE `dev_ifa_android` (
`country` VARCHAR(20) NULL COMMENT '国家',
`ifa` VARCHAR(60) NULL COMMENT '设备id-可以为null',
`source` VARCHAR(100) REPLACE_IF_NOT_NULL NULL COMMENT '媒体源',
`os` VARCHAR(100) REPLACE_IF_NOT_NULL NULL COMMENT '操作系统-方便对数据',
`app_bundle` VARCHAR(200) REPLACE_IF_NOT_NULL NULL COMMENT 'app',
`language` VARCHAR(100) REPLACE_IF_NOT_NULL NULL COMMENT 'language',
`user_agent` VARCHAR(1000) REPLACE_IF_NOT_NULL NULL COMMENT 'UA',
`last_ip` VARCHAR(60) REPLACE_IF_NOT_NULL NULL COMMENT '用户的IP',
`osv` VARCHAR(100) REPLACE_IF_NOT_NULL NULL COMMENT '操作系统版本',
`model` VARCHAR(100) REPLACE_IF_NOT_NULL NULL COMMENT '型号',
`make` VARCHAR(100) REPLACE_IF_NOT_NULL NULL COMMENT '厂商',
`city` VARCHAR(100) REPLACE_IF_NOT_NULL NULL COMMENT 'city',
`region` VARCHAR(100) REPLACE_IF_NOT_NULL NULL COMMENT 'region',
`device_type` SMALLINT REPLACE_IF_NOT_NULL NULL,
`connection_type` SMALLINT REPLACE_IF_NOT_NULL NULL,
`bid_date` DATE REPLACE NULL COMMENT '日期',
`bid_date_time` DATETIME REPLACE NULL COMMENT '最后活跃时间',
`ad_media` VARCHAR(60) REPLACE NULL COMMENT '展示媒体',
`active_cnt` INT SUM NULL DEFAULT "0" COMMENT '活跃数',
`ifa_hll` HLL HLL_UNION NOT NULL COMMENT 'ifa排重辅助'
) ENGINE=OLAP
AGGREGATE KEY(`country`, `ifa`)
COMMENT 'OLAP'
PARTITION BY LIST(`country`)
(PARTITION p_AE VALUES IN ("AE")PROPERTIES ("STORAGE POLICY" = "s3_cold_down_10d"),
PARTITION p_BR VALUES IN ("BR")PROPERTIES ("STORAGE POLICY" = "s3_cold_down_10d"),
PARTITION p_GB VALUES IN ("GB")PROPERTIES ("STORAGE POLICY" = "s3_cold_down_10d"),
PARTITION p_ID VALUES IN ("ID")PROPERTIES ("STORAGE POLICY" = "s3_cold_down_10d"),
PARTITION p_IN VALUES IN ("IN")PROPERTIES ("STORAGE POLICY" = "s3_cold_down_10d"),
PARTITION p_KR VALUES IN ("KR")PROPERTIES ("STORAGE POLICY" = "s3_cold_down_10d"),
PARTITION p_MX VALUES IN ("MX")PROPERTIES ("STORAGE POLICY" = "s3_cold_down_10d"),
PARTITION p_OTHER VALUES IN ("NG","SG","TK","NU","TV","ER","FK","MS","PN","BL","KI","VA","IO","WF","NR","PM","CK","AS","VU","SM","TC","LI","AX","MC","TO","SB","ST","FO","MH","GL","YT","GI","FM","VG","WS","PW","GG","CF","AD","AI","LS","VC","LC","AW","IM","JE","PG","NC","GW","GD","KM","KN","DJ","SZ","CV","BI","DM","KY","MO","FJ","TM","BW","GP","CW","UM","MQ","BS","IS","GQ","SS","AG","BT","BF","BM","MP","MW","PF","ME","GY","LR","NE","NA","BB","TL","SR","BQ","ZW","SL","SI","RE","CU","MG","GF","TD","MZ","BN","GM","CY","MT","HR","TT","CG","SK","BA","RW","VI","BJ","TG","GA","CR","SV","ML","GN","MR","HT","NI","MK","MF","ZM","TJ","PA","EE","AO","GU","TZ","UG","LU","KG","HN","BZ","LT","HU","CD","SD","AM","GR","JM","UZ","LV","KE","AT","PT","MV","SC","AZ","CM","SN","MN","MD","NO","BY","ET","SO","BG","AL","BH","NZ","UY","TN","IE","JO","DZ","MM","LA","SX","GH","BE","YE","CI","LK","CZ","PS","BO","CH","AF","RO","PY","VE","NF","DO","ES","OM","FI","PR","DK","SY","MU","LB","CL","IL","KH","SE","AU","KW","IR","LY","EC","MA","TR","NP","GE","QA","CO","AR","KZ","GT","HK","CN","EG","PE","BD","JP","PL","IT","UA","IQ","ZA","CA","RS","PK","FR","NL","TW","DE")PROPERTIES ("STORAGE POLICY" = "s3_cold_down_10d"),
PARTITION p_PH VALUES IN ("PH")PROPERTIES ("STORAGE POLICY" = "s3_cold_down_10d"),
PARTITION p_RU VALUES IN ("RU")PROPERTIES ("STORAGE POLICY" = "s3_cold_down_10d"),
PARTITION p_SA VALUES IN ("SA")PROPERTIES ("STORAGE POLICY" = "s3_cold_down_10d"),
PARTITION p_TH VALUES IN ("TH")PROPERTIES ("STORAGE POLICY" = "s3_cold_down_10d"),
PARTITION p_US VALUES IN ("US")PROPERTIES ("STORAGE POLICY" = "s3_cold_down_10d"),
PARTITION p_VN VALUES IN ("VN")PROPERTIES ("STORAGE POLICY" = "s3_cold_down_10d"))
DISTRIBUTED BY HASH(`ifa`) BUCKETS 16
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"compression" = "ZSTD",
"light_schema_change" = "true",
"storage_policy" = "s3_cold_down_10d",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
); |
+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
创建后 按照 alter table文档描述添加默认分区
如果用户使用的是List Partition则可以增加default partition，default partition将会存储所有不满足其他分区键要求的数据。
ALTER TABLE table_name ADD PARTITION partition_name
执行报错：
MySQL []> ALTER TABLE dev_ifa_android ADD PARTITION `p_DEFAULT`;
ERROR 1105 (HY000): errCode = 2, detailMessage = Invalid list value format: errCode = 2, detailMessage = Invalid data type for creating infinity: VARCHAR(20)
这个是 bug呢还是我创建的方式不对。
