mysql分区错误代码1486_MySQL • 最佳实践 • 分区表基本类型

CREATE TABLE my_range_datetime(

id INT,

hiredate DATETIME

)

PARTITION BY RANGE (TO_DAYS(hiredate) ) (

PARTITION p1 VALUES LESS THAN ( TO_DAYS('20171202') ),

PARTITION p2 VALUES LESS THAN ( TO_DAYS('20171203') ),

PARTITION p3 VALUES LESS THAN ( TO_DAYS('20171204') ),

PARTITION p4 VALUES LESS THAN ( TO_DAYS('20171205') ),

PARTITION p5 VALUES LESS THAN ( TO_DAYS('20171206') ),

PARTITION p6 VALUES LESS THAN ( TO_DAYS('20171207') ),

PARTITION p7 VALUES LESS THAN ( TO_DAYS('20171208') ),

PARTITION p8 VALUES LESS THAN ( TO_DAYS('20171209') ),

PARTITION p9 VALUES LESS THAN ( TO_DAYS('20171210') ),

PARTITION p10 VALUES LESS THAN ( TO_DAYS('20171211') )，

PARTITION p11 VALUES LESS THAN (MAXVALUE)

);

复制代码mysql> insert into my_range_datetime select * from test;

Query OK, 1000000 rows affected (8.15 sec)

Records: 1000000 Duplicates: 0 Warnings: 0

mysql> explain partitions select * from my_range_datetime where hiredate >= '20171207124503' and hiredate<='20171210111230';

+----+-------------+-------------------+--------------+------+---------------+------+---------+------+--------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------------------+--------------+------+---------------+------+---------+------+--------+-------------+

| 1 | SIMPLE | my_range_datetime | p7,p8,p9,p10 | ALL | NULL | NULL | NULL | NULL | 400061 | Using where |

+----+-------------+-------------------+--------------+------+---------------+------+---------+------+--------+-------------+

1 row in set (0.03 sec)

复制代码CREATE TABLE my_range_timestamp (

id INT,

hiredate TIMESTAMP

)

PARTITION BY RANGE ( UNIX_TIMESTAMP(hiredate) ) (

PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-02 00:00:00') ),

PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-03 00:00:00') ),

PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-04 00:00:00') ),

PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-05 00:00:00') ),

PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-06 00:00:00') ),

PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-07 00:00:00') ),

PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-08 00:00:00') ),

PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-09 00:00:00') ),

PARTITION p9 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-10 00:00:00') ),

PARTITION p10 VALUES LESS THAN (UNIX_TIMESTAMP('2017-12-11 00:00:00') )

);

复制代码mysql> insert into my_range_timestamp select * from test;

Query OK, 1000000 rows affected (13.25 sec)

Records: 1000000 Duplicates: 0 Warnings: 0

mysql> explain partitions select * from my_range_timestamp where hiredate >= '20171207124503' and hiredate<='20171210111230';

+----+-------------+-------------------+--------------+------+---------------+------+---------+------+--------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------------------+--------------+------+---------------+------+---------+------+--------+-------------+

| 1 | SIMPLE | my_range_timestamp | p7,p8,p9,p10 | ALL | NULL | NULL | NULL | NULL | 400448 | Using where |

+----+-------------+-------------------+--------------+------+---------------+------+---------+------+--------+-------------+

1 row in set (0.00 sec)

复制代码create table t_list(

a int(11),

b int(11)

)(partition by list (b)

partition p0 values in (1,3,5,7,9),

partition p1 values in (2,4,6,8,0)

);

复制代码CREATE TABLE my_member (

id INT NOT NULL,

fname VARCHAR(30),

lname VARCHAR(30),

created DATE NOT NULL DEFAULT '1970-01-01',

separated DATE NOT NULL DEFAULT '9999-12-31',

job_code INT,

store_id INT

)

PARTITION BY HASH(id)

PARTITIONS 4;

复制代码CREATE TABLE my_members (

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,

store_id INT

)

PARTITION BY LINEAR HASH( id )

PARTITIONS 4;

复制代码CREATE TABLE k1 (

id INT NOT NULL PRIMARY KEY,

name VARCHAR(20)

)

PARTITION BY KEY()

PARTITIONS 2;

复制代码CREATE TABLE tm1 ( mmysql

s1 CHAR(32)

)

PARTITION BY KEY(s1)

PARTITIONS 10;

复制代码