有一张表:ODS_lead_system
-- dw_ods_dev.ODS_lead_system definition
CREATE TABLE `ODS_lead_system` (
`id` varchar(4000) NULL,
`theme` varchar(4000) NULL,
`title` varchar(4000) NULL,
`level` varchar(4000) NULL,
`field_1` varchar(4000) NULL,
`field_2` varchar(4000) NULL,
`field_3` varchar(4000) NULL,
`field_5` varchar(4000) NULL,
`field_8` varchar(4000) NULL,
`field_6` varchar(4000) NULL,
`field_7` varchar(4000) NULL,
`field_9` varchar(4000) NULL,
`field_4` varchar(4000) NULL,
`field_10` varchar(4000) NULL,
`field_11` varchar(4000) NULL,
`field_12` varchar(4000) NULL,
`field_13` varchar(4000) NULL,
`field_14` varchar(4000) NULL,
`field_15` varchar(4000) NULL,
`field_16` varchar(4000) NULL,
`field_17` varchar(4000) NULL,
`field_18` varchar(4000) NULL,
`field_19` varchar(4000) NULL,
`field_20` varchar(4000) NULL,
`field_21` varchar(255) NULL,
`field_22` varchar(4000) NULL,
`field_23` varchar(4000) NULL,
`field_24` varchar(4000) NULL,
`field_25` varchar(4000) NULL,
`field_26` varchar(4000) NULL,
`field_27` varchar(4000) NULL,
`field_28` varchar(4000) NULL,
`field_29` varchar(4000) NULL,
`field_30` varchar(4000) NULL,
`status` varchar(4000) NULL,
`mode` varchar(4000) NULL,
`types` varchar(255) NULL,
`source_task_id` int(11) NULL
) ENGINE=OLAP
UNIQUE KEY(`id`, `theme`, `title`, `level`)
COMMENT 'OLAP'
DISTRIBUTED BY HASH(`id`, `theme`, `title`, `level`) BUCKETS 2
PROPERTIES (
"replication_allocation" = "tag.location.default: 2",
"is_being_synced" = "false",
"storage_format" = "V2",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false"
);
修改两个字段,执行
SQL1:
ALTER TABLE `dw_ods_dev`.`ODS_lead_system` MODIFY COLUMN `field_3` VARCHAR(4000) NULL AFTER `field_1`, MODIFY COLUMN `field_2` VARCHAR(4000) NULL AFTER `field_3`;
报错:SQL 错误 [1105] [HY000]: errCode = 2, detailMessage = Column[field_3] does not exists
SQL2:
ALTER TABLE `dw_ods_dev`.`ODS_lead_system` MODIFY COLUMN `field_2` VARCHAR(4000) NULL AFTER `field_3`, MODIFY COLUMN `field_3` VARCHAR(4000) NULL AFTER `field_1`;
执行成功
SQL3:
ALTER TABLE `dw_ods_dev`.`ODS_lead_system`
MODIFY COLUMN `field_28` VARCHAR(4000) NULL AFTER `field_29`,
MODIFY COLUMN `field_29` VARCHAR(4000) NULL AFTER `field_30`,
MODIFY COLUMN `field_30` VARCHAR(4000) NULL AFTER `field_27`;
SQL4:
ALTER TABLE `dw_ods_dev`.`ODS_lead_system`
MODIFY COLUMN `field_30` VARCHAR(4000) NULL AFTER `field_27`,
MODIFY COLUMN `field_29` VARCHAR(4000) NULL AFTER `field_30`,
MODIFY COLUMN `field_28` VARCHAR(4000) NULL AFTER `field_29`;
问题:
1、为什么执行修改语句会严格要求DDL顺序?(多次测试发现第一次修改字段会严格要求DDL顺序,按正确顺序修改后, 恢复最初的字段顺序再次执行报错SQL,又能成功执行了。)
2、为什么SQL3不能保证28、29的顺序,修改DDL顺序后,SQL4能保证?
3、多个字段的变更(新增列/删除列/已存在的列修改描述/调整顺序)生成一条ddl语句时, 除了key列需要排在最前面外, 还有哪些限制? 有没有一条准确的规则可以参考,目前通过测试归纳得出的规律不稳定。