什么是Mysql中RANGE分區(qū)
下面講講關(guān)于什么是MySQL中RANGE分區(qū)，文字的奧妙在于貼近主題相關(guān)。所以，閑話就不談了，我們直接看下文吧，相信看完什么是Mysql中RANGE分區(qū)這篇文章你一定會(huì)有所受益。
創(chuàng)新互聯(lián)主營(yíng)米易網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營(yíng)網(wǎng)站建設(shè)方案,app軟件開發(fā)公司,米易h5重慶小程序開發(fā)搭建,米易網(wǎng)站營(yíng)銷推廣歡迎米易等地區(qū)企業(yè)咨詢
通過范圍的方式進(jìn)行分區(qū), 為每個(gè)分區(qū)給出一定的范圍, 范圍必須是連續(xù)的并且不能重復(fù), 使用VALUES LESS THAN操作符
讓我們先來創(chuàng)建一個(gè)range分區(qū)的表
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN (21)
);store_id 小于6的, 會(huì)被放入第一個(gè)分區(qū), 小于11的會(huì)放入第二個(gè)分區(qū)。如果我的store_id大于21怎么辦呢？, 所以我們得修改一下這個(gè)創(chuàng)建分區(qū)的方式。
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN (21),
PARTITION p4 VALUES LESS THAN (MAXVALUE)
);增加了一個(gè)MAXVALUE, MAXVALUE的意思是, 大于21的數(shù)據(jù)都會(huì)放入這個(gè)分區(qū), 當(dāng)然, 還有另外一種方式去避免這個(gè)問題, 就是在INSERT的時(shí)候增加IGNORE關(guān)鍵字。
分區(qū)鍵類型為時(shí)間轉(zhuǎn)時(shí)間戳
可以使用UNIX-TIMESTAMP()
CREATE TABLE quarterly_report_status (
report_id INT NOT NULL,
report_status VARCHAR(20) NOT NULL,
report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
PARTITION p9 VALUES LESS THAN (MAXVALUE)
);除了UNIX_TIMESTAMP外,其他涉及到時(shí)間戳的表達(dá)式都是不被允許的
基于時(shí)間數(shù)字的分區(qū)
CREATE TABLE members (
firstname VARCHAR(25) NOT NULL,
lastname VARCHAR(25) NOT NULL,
username VARCHAR(16) NOT NULL,
email VARCHAR(35),
joined DATE NOT NULL
)
PARTITION BY RANGE(YEAR(joined) ) (
PARTITION p0 VALUES LESS THAN (1960),
PARTITION p1 VALUES LESS THAN (1970),
PARTITION p2 VALUES LESS THAN (1980),
PARTITION p3 VALUES LESS THAN (1990),
PARTITION p4 VALUES LESS THAN MAXVALUE
);對(duì)于以上什么是Mysql中RANGE分區(qū)相關(guān)內(nèi)容，大家還有什么不明白的地方嗎？或者想要了解更多相關(guān)，可以繼續(xù)關(guān)注我們的行業(yè)資訊板塊。
新聞標(biāo)題：什么是Mysql中RANGE分區(qū)
分享網(wǎng)址：http://m.jiaotiyi.com/article/gopies.html