- 1【你也能从零基础学会网站开发】 Win10系统下如何完美安装SQL Server 2000+解决SysWOW64权限限制方案!_win10安装sql2000
- 2upload-labs通关详解_upload-labspass8
- 3python使用turtle画图代码_初识Python，利用turtle画图
- 4vivo手机连接adb_adb windows 连接vivo手机教程
- 5Mac下载安装配置maven_maven安装mac网盘
- 6SpringBoot+Mybatis 一级缓存和二级缓存详解_spring.cache.enabled
- 7【自然语言处理】- 作业4: 预训练语言模型BERT实现与应用_基于预训练语言模型的问答系统实例
- 82024年最新工业控制系统网络安全防护指南_工控安全防护方案
- 9VSCode切换默认终端_vs code怎么切换终端
- 10OpenCV图像滤波(5)二维卷积滤波函数filter2D()的使用
mysql普通表转换分区表_mysql 普通表转分区表
赞
踩
CREATE TABLE `history_log` (`id` bigint(20) unsigned NOT NULL,
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`timestamp` int(11) NOT NULL DEFAULT '0',
`source` varchar(64) NOT NULL DEFAULT '',
`severity` int(11) NOT NULL DEFAULT '0',
`value` text NOT NULL,
`logeventid` int(11) NOT NULL DEFAULT '0',
`ns` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `history_log_2` (`itemid`,`id`),
KEY `history_log_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
alter table history_log partition by RANGE (clock)
(PARTITION p20150806 values less than (20150807));
mysql> alter table history_log partition by RANGE (clock)
-> (PARTITION p20150806 values less than (20150807));
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
主键必须包含分区键:
mysql> CREATE TABLE `history_log` (
-> `id` bigint(20) unsigned NOT NULL,
-> `itemid` bigint(20) unsigned NOT NULL,
-> `clock` int(11) NOT NULL DEFAULT '0',
-> `timestamp` int(11) NOT NULL DEFAULT '0',
-> `source` varchar(64) NOT NULL DEFAULT '',
-> `severity` int(11) NOT NULL DEFAULT '0',
-> `value` text NOT NULL,
-> `logeventid` int(11) NOT NULL DEFAULT '0',
-> `ns` int(11) NOT NULL DEFAULT '0',
-> PRIMARY KEY (`id`,`clock`),
-> UNIQUE KEY `history_log_2` (`itemid`,`id`),
-> KEY `history_log_1` (`itemid`,`clock`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)
mysql> alter table history_log partition by RANGE (clock)
-> (PARTITION p20150806 values less than (20150807));
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function
唯一索引也得包含分区键:
CREATE TABLE `history_log` (
`id` bigint(20) unsigned NOT NULL,
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`timestamp` int(11) NOT NULL DEFAULT '0',
`source` varchar(64) NOT NULL DEFAULT '',
`severity` int(11) NOT NULL DEFAULT '0',
`value` text NOT NULL,
`logeventid` int(11) NOT NULL DEFAULT '0',
`ns` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`,`clock`),
UNIQUE KEY `history_log_2` (`itemid`,`id`,`clock`),
KEY `history_log_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
partition by RANGE (clock) (PARTITION p20150806 values less than (20150807));
---------------------------------------------------------------------------------
mysql> CREATE TABLE `history_log` (
-> `id` bigint(20) unsigned NOT NULL,
-> `itemid` bigint(20) unsigned NOT NULL,
-> `clock` int(11) NOT NULL DEFAULT '0',
-> `timestamp` int(11) NOT NULL DEFAULT '0',
-> `source` varchar(64) NOT NULL DEFAULT '',
-> `severity` int(11) NOT NULL DEFAULT '0',
-> `value` text NOT NULL,
-> `logeventid` int(11) NOT NULL DEFAULT '0',
-> `ns` int(11) NOT NULL DEFAULT '0',
-> PRIMARY KEY (`id`,`clock`),
-> UNIQUE KEY `history_log_2` (`itemid`,`id`,`clock`),
-> KEY `history_log_1` (`itemid`,`clock`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
-> partition by RANGE (clock) (PARTITION p20150806 values less than (20150807));
Query OK, 0 rows affected (0.03 sec)
mysql> desc history_log;
+------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+-------+
| id | bigint(20) unsigned | NO | PRI | NULL | |
| itemid | bigint(20) unsigned | NO | MUL | NULL | |
| clock | int(11) | NO | PRI | 0 | |
| timestamp | int(11) | NO | | 0 | |
| source | varchar(64) | NO | | | |
| severity | int(11) | NO | | 0 | |
| value | text | NO | | NULL | |
| logeventid | int(11) | NO | | 0 | |
| ns | int(11) | NO | | 0 | |
+------------+---------------------+------+-----+---------+-------+
9 rows in set (0.01 sec)
mysql> show create table history_log\G;
*************************** 1. row ***************************
Table: history_log
Create Table: CREATE TABLE `history_log` (
`id` bigint(20) unsigned NOT NULL,
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`timestamp` int(11) NOT NULL DEFAULT '0',
`source` varchar(64) NOT NULL DEFAULT '',
`severity` int(11) NOT NULL DEFAULT '0',
`value` text NOT NULL,
`logeventid` int(11) NOT NULL DEFAULT '0',
`ns` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`,`clock`),
UNIQUE KEY `history_log_2` (`itemid`,`id`,`clock`),
KEY `history_log_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (clock)
(PARTITION p20150806 VALUES LESS THAN (20150807) ENGINE = InnoDB) */
1 row in set (0.00 sec)
ERROR:
No query specified
