mysql 修改分区_mysql – 修改用于分区的AUTO_INCREMENT PRIMARY KEY

我需要在时态数据中对MySQL表进行分区(字段从下表开始).

CREATE TABLE `table1` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`fk_id` bigint(20) NOT NULL,

`begin` bigint(20) NOT NULL,

PRIMARY KEY (`id`),

KEY `FK1E57078DB20EC268` (`fk_id`)

) ENGINE=MyISAM AUTO_INCREMENT=10443288 DEFAULT CHARSET=latin1

当我尝试像这样分区时：

alter table table1 partition by range (begin) (

PARTITION until_2010_07 VALUES LESS THAN (1280620800000),

PARTITION 2010_08 VALUES LESS THAN (1283299200000),

PARTITION 2010_09 VALUES LESS THAN (1285891200000),

PARTITION 2010_10 VALUES LESS THAN (1288569600000),

PARTITION 2010_11 VALUES LESS THAN (1291161600000),

PARTITION 2010_12 VALUES LESS THAN (1293840000000),

PARTITION from_2011 VALUES LESS THAN MAXVALUE

);

我收到MySQL错误：ERROR 1503(HY000)：PRIMARY KEY必须包含表的分区函数中的所有列

据我所知,从mysql doc中,分区列应该属于主键.

对我来说问题是我想要将PRIMARY_KEY更改为复合的,即PRIMARY KEY(‘id’,’fk_id’,’begin’)而不更改现有的id列(因为它是应用程序中使用的字段)生成可收藏的网址,因此重新编号ID不是一个选项)

如何更改PRIMARY_KEY以便我可以进行分区？

解决方法:

我终于找到了办法,我为其他遇到这个问题的人回答

//drop auto_increment capability

alter table table1 change column id id BIGINT NOT NULL;

//in one line, drop primary key and rebuild one

alter table table1 drop primary key, add primary key(id,fk_id,begin);

//re add the auto_increment capability, last value is remembered

alter table table1 change column id id BIGINT NOT NULL AUTO_INCREMENT;

//build the partition

alter table table1 partition by range (begin) (

PARTITION until_2010_07 VALUES LESS THAN (1280620800000),

PARTITION 2010_08 VALUES LESS THAN (1283299200000),

PARTITION 2010_09 VALUES LESS THAN (1285891200000),

PARTITION 2010_10 VALUES LESS THAN (1288569600000),

PARTITION 2010_11 VALUES LESS THAN (1291161600000),

PARTITION 2010_12 VALUES LESS THAN (1293840000000),

PARTITION from_2011 VALUES LESS THAN MAXVALUE

);

分区确实很容易,但我建议人们在生成实际使用数据库之前创建主键时要考虑它:-)

我的笔记本电脑每步需要3分钟,然后我必须停止服务以保持数据库的一致性

标签：mysql,primary-key,auto-increment,partitioning

来源： https://codeday.me/bug/20190726/1542987.html