mysql 学习记录（十五）--分区

最新推荐文章于 2021-03-23 19:40:29 发布
最新推荐文章于 2021-03-23 19:40:29 发布
阅读量1k 收藏
点赞数
分类专栏： mysql mysql学习 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/bwshqh/article/details/49341451
版权 
mysql> use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show variables like '%partition%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.01 sec)

mysql> drop table emp;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE emp(empid INT,salary DECIMAL(7,2),birth_date DATE)
    -> ENGINE = INNODB
    -> PARTITION BY HASH( MONTH(birth_date) )
    -> PARTITIONS 6;
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE emp_1(
    -> id INT NOT NULL,
    -> ename VARCHAR(30),
    -> hired DATE NOT NULL DEFAULT '1970-01-01',
    ->  separated DATE NOT NULL DEFAULT '9999-12-31',
    ->  job VARCHAR(30) NOT NULL,
    ->  store_id INT NOT NULL
    -> )
    -> PARTITION BY RANGE (store_id)(
    -> PARTITION p0 VALUES LESS THAN (10),
    -> PARTITION p1 VALUES LESS THAN (20),
    -> PARTITION p2 VALUES LESS THAN (30)
    -> );
Query OK, 0 rows affected (0.04 sec)
 
mysql> create table emp_list( id int not null, ename varchar(30), hired date not null default '1970-01-01', separated date not null default '9999-12-31', job varchar(30) not null, store_id int not null ) PARTITION by range(store_id)(  PARTITION P0 VALUES LESS THAN (10), PARTITION P1 VALUES LESS THAN (20), PARTITION P2 VALUES LESS THAN (30) );
Query OK, 0 rows affected (0.05 sec)

mysql> alter table emp_list add partition ( partition p3 values less than MAXVALUE);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into emp_list(id,ename,hired,job,store_id) values 
    -> ('13259','tomil','2000-01-03','bily',50);
Query OK, 1 row affected (0.01 sec)

mysql> select * from emp_list;
+-------+-------+------------+------------+------+----------+
| id    | ename | hired      | separated  | job  | store_id |
+-------+-------+------------+------------+------+----------+
| 13259 | tomil | 2000-01-03 | 9999-12-31 | bily |       50 |
+-------+-------+------------+------------+------+----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE emp_date_new( id INT NOT NULL, ename VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job VARCHAR(30) NOT NULL, store_id INT NOT NULL ) PARTITION BY RANGE COLUMNS (separated) ( PARTITION p0 VALUES LESS THAN ('1995-01-01'), PARTITION p1 VALUES LESS THAN ('2000-01-01'), PARTITION p2 VALUES LESS THAN ('2005-01-01') );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into emp(empid) values ('15');
Query OK, 1 row affected (0.01 sec)

mysql> explain partitions select count(1) from emp \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: p0,p1,p2,p3,p4,p5
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: 
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> select * from emp;
+-------+--------+------------+
| empid | salary | birth_date |
+-------+--------+------------+
|    15 |   NULL | NULL       |
+-------+--------+------------+
1 row in set (0.00 sec)

mysql> explain partitions select count(1) from emp  where empid = 15 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: p0,p1,p2,p3,p4,p5
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: Using where
1 row in set (0.01 sec)

ERROR: 
No query specified
 
mysql> create table expenses( expens_date DATE NOT NULL, category INT, amount DECIMAL(10,3) )PARTITION BY LIST(category)( PARTITION p0 VALUES IN (3,5), PARTITION p1 VALUES IN (1,10), PARTITION p2 VALUES IN (4,9), PARTITION p3 VALUES IN (2), PARTITION p4 VALUES IN (6) );
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE expenses_1( expense_date DATE NOT NULL, category VARCHAR(30), amount DECIMAL(10,3) ) PARTITION BY LIST COLUMNS (category) ( PARTITION p0 VALUES IN ( 'aaa','bbb'),
PARTITION p1 VALUES IN ('ccc','ddd'),
PARTITION p2 VALUES IN ('eee','fff'),
PARTITION p3 VALUES IN ('ggg','hhh'),
PARTITION p4 VALUES IN ('iii','jjj')
);

Query OK, 0 rows affected (0.06 sec)

mysql> create table rc3( a int, b int) PARTITION BY RANGE COLUMNS(a,b)( PARTITION p01 VALUES less than (0,10), PARTITION p02 VALUES less than (10,15), PARTITION P03 values less than (10,MAXVALUE), PARTITION p04 values less than  (MAXVALUE,MAXVALUE) );
Query OK, 0 rows affected (0.07 sec)

mysql> insert into rc3(a) value (3);
Query OK, 1 row affected (0.00 sec)

mysql> select (1,10) < (10,10) from dul;
ERROR 1146 (42S02): Table 'test1.dul' doesn't exist
mysql> select (1,10) < (10,10) from dual;
+------------------+
| (1,10) < (10,10) |
+------------------+
|                1 |
+------------------+
1 row in set (0.01 sec)

mysql> select  partition_name part, partition_expression expr, partition_description descr, table_rows  from  information_schema.partitions where  table_schema=schema() and table_name='rc3';
+------+---------+-------------------+------------+
| part | expr    | descr             | table_rows |
+------+---------+-------------------+------------+
| p01  | `a`,`b` | 0,10              |          0 |
| p02  | `a`,`b` | 10,15             |          1 |
| P03  | `a`,`b` | 10,MAXVALUE       |          0 |
| p04  | `a`,`b` | MAXVALUE,MAXVALUE |          0 |
+------+---------+-------------------+------------+
4 rows in set (0.00 sec)

mysql> insert into rc3(a) value (5);
Query OK, 1 row affected (0.01 sec)

mysql> select  partition_name part, partition_expression expr, partition_description descr, table_rows  from  information_schema.partitions where  table_schema=schema() and table_name='rc3';
+------+---------+-------------------+------------+
| part | expr    | descr             | table_rows |
+------+---------+-------------------+------------+
| p01  | `a`,`b` | 0,10              |          0 |
| p02  | `a`,`b` | 10,15             |          2 |
| P03  | `a`,`b` | 10,MAXVALUE       |          0 |
| p04  | `a`,`b` | MAXVALUE,MAXVALUE |          0 |
+------+---------+-------------------+------------+
4 rows in set (0.01 sec)


mysql> truncate rc3;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into rc3(a,b) values (10,9);
Query OK, 1 row affected (0.01 sec)

mysql> select  partition_name part, partition_expression expr, partition_description descr, table_rows  from  information_schema.partitions where  table_schema=schema() and table_name='rc3';
+------+---------+-------------------+------------+
| part | expr    | descr             | table_rows |
+------+---------+-------------------+------------+
| p01  | `a`,`b` | 0,10              |          0 |
| p02  | `a`,`b` | 10,15             |          1 |
| P03  | `a`,`b` | 10,MAXVALUE       |          0 |
| p04  | `a`,`b` | MAXVALUE,MAXVALUE |          0 |
+------+---------+-------------------+------------+
4 rows in set (0.00 sec)

mysql> select (10,9)<(10,10) from dual;
+----------------+
| (10,9)<(10,10) |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

mysql> insert into rc3(a,b) values(10,10);
Query OK, 1 row affected (0.01 sec)

mysql> select (10,10)<(10,10) from dual;
+-----------------+
| (10,10)<(10,10) |
+-----------------+
|               0 |
+-----------------+
1 row in set (0.00 sec)

mysql> insert into rc3(a,b) values(0,6);
Query OK, 1 row affected (0.01 sec)

mysql> select (10,10)<(10,10) from dual;
+-----------------+
| (10,10)<(10,10) |
+-----------------+
|               0 |
+-----------------+
1 row in set (0.00 sec)

mysql> select  partition_name part, partition_expression expr, partition_description descr, table_rows  from  information_schema.partitions where  table_schema=schema() and table_name='rc3';
+------+---------+-------------------+------------+
| part | expr    | descr             | table_rows |
+------+---------+-------------------+------------+
| p01  | `a`,`b` | 0,10              |          1 |
| p02  | `a`,`b` | 10,15             |          2 |
| P03  | `a`,`b` | 10,MAXVALUE       |          0 |
| p04  | `a`,`b` | MAXVALUE,MAXVALUE |          0 |
+------+---------+-------------------+------------+
4 rows in set (0.00 sec)

mysql> drop table emp;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE emp(
    -> id INT NOT NULL,
    -> ename VARCHAR(30),
    -> hired DATE NOT NULL DEFAULT '1970-01-01',
    -> separated DATE NOT NULL DEFAULT '9999-12-31',
    -> job VARCHAR(30) NOT NULL,
    -> store_id INT NOT NULL 
    -> )
    -> PARTITION BY HASH ( store_id ) PARTITIONS 4;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into emp values 
    -> (1,'Tom','2010-12-03','9999-12-31','Cling',235);
Query OK, 1 row affected (0.01 sec)


mysql> explain partitions 
    -> select * from emp where store_id = 235 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: p3
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using where
1 row in set (0.01 sec)

ERROR: 
No query specified

mysql> drop table emp;
     PARTITION BY LINEAR  HASH ( store_id ) PARTITIONS 4;Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE emp(
    ->      id INT NOT NULL,
    ->      ename VARCHAR(30),
    ->      hired DATE NOT NULL DEFAULT '1970-01-01',
    ->      separated DATE NOT NULL DEFAULT '9999-12-31',
    ->      job VARCHAR(30) NOT NULL,
    ->      store_id INT NOT NULL 
    ->      )
    ->      PARTITION BY LINEAR  HASH ( store_id ) PARTITIONS 4;
Query OK, 0 rows affected (0.06 sec)


mysql> insert into emp values  (1,'Tom','2010-12-03','9999-12-31','Cling',235);
Query OK, 1 row affected (0.01 sec)

mysql> explain partitions  select * from emp where store_id = 235\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: p3
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using where
1 row in set (0.00 sec)

mysql> drop table emp;
Query OK, 0 rows affected (0.01 sec)

mysql>       CREATE TABLE emp(
    ->      id INT NOT NULL,
    ->      ename VARCHAR(30),
    ->      hired DATE NOT NULL DEFAULT '1970-01-01',
    ->      separated DATE NOT NULL DEFAULT '9999-12-31',
    ->      job VARCHAR(30) NOT NULL,
    ->      store_id INT NOT NULL 
    ->      )
    ->      PARTITION BY KEY (job)   PARTITIONS 4;
Query OK, 0 rows affected (0.05 sec)


mysql> insert into emp values  (1,'Tom','2010-12-03','9999-12-31','Cling',3);
Query OK, 1 row affected (0.01 sec)

mysql> explain partitions  select * from emp where store_id = 3 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: p0,p1,p2,p3
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.00 sec)

mysql> explain partitions  select * from emp where store_id = 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: p0,p1,p2,p3
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.00 sec)

mysql> explain partitions  select * from emp where id = 3 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: p0,p1,p2,p3
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.01 sec)


mysql>  select * from emp where id = 3 ;
Empty set (0.00 sec)

mysql>  select * from emp where id = 1 ;
+----+-------+------------+------------+-------+----------+
| id | ename | hired      | separated  | job   | store_id |
+----+-------+------------+------------+-------+----------+
|  1 | Tom   | 2010-12-03 | 9999-12-31 | Cling |        3 |
+----+-------+------------+------------+-------+----------+
1 row in set (0.00 sec)

mysql> explain partitions  select * from emp where id = 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: p0,p1,p2,p3
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.00 sec)

mysql>       drop table emp;
     PARTITION BY KEY ()   PARTITIONS 4;Query OK, 0 rows affected (0.01 sec)

mysql>       CREATE TABLE emp(
    ->      id INT NOT NULL,
    ->      ename VARCHAR(30),
    ->      hired DATE NOT NULL DEFAULT '1970-01-01',
    ->      separated DATE NOT NULL DEFAULT '9999-12-31',
    ->      job VARCHAR(30) NOT NULL,
    ->      store_id INT NOT NULL ,
    ->      PRIMARY KEY (id)
    ->      )
    ->      PARTITION BY KEY ()   PARTITIONS 4;
Query OK, 0 rows affected (0.03 sec)


mysql> insert into emp values  (1,'Tom','2010-12-03','9999-12-31','Cling',3);
Query OK, 1 row affected (0.00 sec)

mysql> explain partitions  select * from emp where id = 3 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Impossible WHERE noticed after reading const tables
1 row in set (0.00 sec)


mysql> explain partitions  select * from emp where id = 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: p0
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: 
1 row in set (0.00 sec)

mysql>       drop table emp;
Query OK, 0 rows affected (0.02 sec)


mysql>       CREATE TABLE emp(
    ->      id INT NOT NULL,
    ->      ename VARCHAR(30),
    ->      hired DATE NOT NULL DEFAULT '1970-01-01',
    ->      separated DATE NOT NULL DEFAULT '9999-12-31',
    ->      job VARCHAR(30) NOT NULL,
    ->      store_id INT NOT NULL ,
    ->      UNIQUE KEY (id)
    ->      )
    ->      PARTITION BY KEY ()   PARTITIONS 4;
Query OK, 0 rows affected (0.04 sec)

mysql>       drop table emp;
NIQUE KEY (id,ename)
     )
     PARTITION BY KEY ()   PARTITIONS 4;Query OK, 0 rows affected (0.02 sec)

mysql>      CREATE TABLE test_partition(id INT,purchased DATE)
    ->      PARTITION BY RANGE(YEAR(purchased))
    ->      SUBPARTITION BY HASH(TO_DAYS(purchased))
    ->      SUBPARTITIONS 2
    ->      (
    ->      PARTITION p0 VALUES LESS THAN (1990),
    ->      PARTITION p1 VALUES LESS THAN (2000),
    ->      PARTITION p2 VALUES LESS THAN MAXVALUE
    ->      );
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE tb_range(
    -> id INT,
    -> name VARCHAR(5)
    -> )
    -> PARTITION BY RANGE(id)(
    -> PARTITION p0 VALUES LESS THAN (-6),
    -> PARTITION P1 VALUES LESS THAN (0),
    -> PARTITION p2 VALUES LESS THAN (1),
    -> PARTITION p3 VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into tb_range values (null,'NULL');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT
    -> partition_name part,
    -> partition_expression expr,
    -> partition_description descr,
    -> table_rows
    -> FROM
    -> INFORMATION_SCHEMA.partitions
    -> WHERE
    -> TABLE_SCHEMA = schema()
    -> AND TABLE_NAME='tb_range';
+------+------+----------+------------+
| part | expr | descr    | table_rows |
+------+------+----------+------------+
| p0   | id   | -6       |          1 |
| P1   | id   | 0        |          0 |
| p2   | id   | 1        |          0 |
| p3   | id   | MAXVALUE |          0 |
+------+------+----------+------------+
4 rows in set (0.02 sec)

mysql> CREATE TABLE tb_list(
    -> id INT,
    -> name VARCHAR(5)
    -> )
    -> PARTITION BY LIST(id)(
    -> PARTITION p1 VALUES IN (0),
    -> PARTITION p2 VALUES IN (1)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> insert into tb_list values (null,'NULL');
ERROR 1526 (HY000): Table has no partition for value NULL
mysql> drop table tb_list;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE tb_list(
    -> id INT,
    -> name VARCHAR(5)
    -> )
    -> PARTITION BY LIST(id)(
    -> PARTITION p1 VALUES IN (0,NULL),
    -> PARTITION p2 VALUES IN (1)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tb_list values (NULL,'NULL');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT partition_name part, partition_expression expr, partition_description descr, table_rows FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() AND TABLE_NAME='tb_range';
+------+------+----------+------------+
| part | expr | descr    | table_rows |
+------+------+----------+------------+
| p0   | id   | -6       |          1 |
| P1   | id   | 0        |          0 |
| p2   | id   | 1        |          0 |
| p3   | id   | MAXVALUE |          0 |
+------+------+----------+------------+
4 rows in set (0.00 sec)

mysql> SELECT partition_name part, partition_expression expr, partition_description descr, table_rows FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() AND TABLE_NAME='tb_list';
+------+------+--------+------------+
| part | expr | descr  | table_rows |
+------+------+--------+------------+
| p1   | id   | NULL,0 |          1 |
| p2   | id   | 1      |          0 |
+------+------+--------+------------+
2 rows in set (0.00 sec)

mysql> CREATE TABLE tb_hash( id INT, name VARCHAR(5) ) PARTITION BY HASH(id) PARTITIONS 2;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tb_hash values (null,'NULL');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT partition_name part, partition_expression expr, partition_description descr, table_rows FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() AND TABLE_NAME='tb_hash';
+------+------+-------+------------+
| part | expr | descr | table_rows |
+------+------+-------+------------+
| p0   | id   | NULL  |          1 |
| p1   | id   | NULL  |          0 |
+------+------+-------+------------+
2 rows in set (0.00 sec)


mysql> desc emp_date \G;
*************************** 1. row ***************************
  Field: id
   Type: int(11)
   Null: NO
    Key: 
Default: NULL
  Extra: 
*************************** 2. row ***************************
  Field: ename
   Type: varchar(30)
   Null: YES
    Key: 
Default: NULL
  Extra: 
*************************** 3. row ***************************
  Field: hired
   Type: date
   Null: NO
    Key: 
Default: 1970-01-01
  Extra: 
*************************** 4. row ***************************
  Field: separated
   Type: date
   Null: NO
    Key: 
Default: 9999-12-31
  Extra: 
*************************** 5. row ***************************
  Field: job
   Type: varchar(30)
   Null: NO
    Key: 
Default: NULL
  Extra: 
*************************** 6. row ***************************
  Field: store_id
   Type: int(11)
   Null: NO
    Key: 
Default: NULL
  Extra: 
6 rows in set (0.01 sec)

ERROR: 
No query specified

mysql> show create table emp_date \G;
*************************** 1. row ***************************
       Table: emp_date
Create Table: CREATE TABLE `emp_date` (
  `id` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job` varchar(30) NOT NULL,
  `store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( YEAR(separated))
(PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2005) ENGINE = InnoDB) */
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> drop table emp_date;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE emp_date( id INT NOT NULL, ename VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job VARCHAR(30) NOT NULL, store_id INT NOT NULL ) PARTITION BY RANGE ( YEAR(separated) ) ( PARTITION p0  VALUES LESS THAN (1995), PARTITION p1  VALUES LESS THAN (2000), PARTITION p2  VALUES LESS THAN (2005), PARTITION p3  VALUES LESS THAN (2015) );
Query OK, 0 rows affected (0.06 sec)

mysql> insert into emp_date (id,ename,hired,separated,job,store_id) values 
    -> (7001,'aaaa','1981-01-01','2005-03-03','bbbbbbb',10),
    -> (7300,'dddd','1982-02-01','2006-03-03','ddddddd',30),
    -> (7600,'eeeedsfkasjdiwaer','1987-03-01','2073-03-5','adksjfakre',60);

mysql> insert into emp_date (id,ename,hired,separated,job,store_id) values  (7001,'aaaa','1981-01-01','2005-03-03','bbbbbbb',10), (7300,'dddd','1982-02-01','2006-03-03','ddddddd',30), (7600,'eeeedsfkasjdiwaer','1987-03-01','2003-03-5','adksjfakre',60);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from emp_date 
    -> where separated between '2005-01-01' and '2010-12-31';
+------+-------+------------+------------+---------+----------+
| id   | ename | hired      | separated  | job     | store_id |
+------+-------+------------+------------+---------+----------+
| 7001 | aaaa  | 1981-01-01 | 2005-03-03 | bbbbbbb |       10 |
| 7300 | dddd  | 1982-02-01 | 2006-03-03 | ddddddd |       30 |
+------+-------+------------+------------+---------+----------+
2 rows in set (0.01 sec)

mysql> select * from emp_date  where separated between '2005-01-01' and '2015-12-31';
+------+-------+------------+------------+---------+----------+
| id   | ename | hired      | separated  | job     | store_id |
+------+-------+------------+------------+---------+----------+
| 7001 | aaaa  | 1981-01-01 | 2005-03-03 | bbbbbbb |       10 |
| 7300 | dddd  | 1982-02-01 | 2006-03-03 | ddddddd |       30 |
+------+-------+------------+------------+---------+----------+
2 rows in set (0.00 sec)

mysql> alter table emp_date drop  partition p3;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> show create table emp_date \G;
*************************** 1. row ***************************
       Table: emp_date
Create Table: CREATE TABLE `emp_date` (
  `id` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job` varchar(30) NOT NULL,
  `store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( YEAR(separated))
(PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2005) ENGINE = InnoDB) */
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> select * from emp_date  where separated between '2005-01-01' and '2015-12-31';
Empty set (0.00 sec)


mysql> show create table emp_date \G;
*************************** 1. row ***************************
       Table: emp_date
Create Table: CREATE TABLE `emp_date` (
  `id` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job` varchar(30) NOT NULL,
  `store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( YEAR(separated))
(PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2005) ENGINE = InnoDB) */
1 row in set (0.01 sec)

ERROR: 
No query specified

mysql> insert into emp_date (id,ename,hired,separated,job,store_id) values (7300,'dddd','1982-02-01','2001-03-03','ddddddd',30), (7600,'eeeedsfkasjdiwaer','1987-03-01','2003-03-5','adksjfakre',60);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT partition_name part, partition_expression expr, partition_description descr, table_rows FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() AND TABLE_NAME='tb_hash';
+------+------+-------+------------+
| part | expr | descr | table_rows |
+------+------+-------+------------+
| p0   | id   | NULL  |          1 |
| p1   | id   | NULL  |          0 |
+------+------+-------+------------+
2 rows in set (0.01 sec)

mysql> SELECT partition_name part, partition_expression expr, partition_description descr, table_rows FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() AND TABLE_NAME='emp_date';
+------+------------------+-------+------------+
| part | expr             | descr | table_rows |
+------+------------------+-------+------------+
| p0   |  YEAR(separated) | 1995  |          0 |
| p1   |  YEAR(separated) | 2000  |          0 |
| p2   |  YEAR(separated) | 2005  |          3 |
+------+------------------+-------+------------+
3 rows in set (0.00 sec)

mysql> insert into emp_date (id,ename,hired,separated,job,store_id) values (7300,'dddd','1982-02-01','1996-03-03','ddddddd',30), (7600,'eeeedsfkasjdiwaer','1987-03-01','1997-03-5','adksjfakre',60);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT partition_name part, partition_expression expr, partition_description descr, table_rows FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() AND TABLE_NAME='emp_date';
+------+------------------+-------+------------+
| part | expr             | descr | table_rows |
+------+------------------+-------+------------+
| p0   |  YEAR(separated) | 1995  |          0 |
| p1   |  YEAR(separated) | 2000  |          2 |
| p2   |  YEAR(separated) | 2005  |          3 |
+------+------------------+-------+------------+
3 rows in set (0.00 sec)

mysql> alter table emp_date add partition ( partition p4 values less than (2030));
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table emp_date \G;
*************************** 1. row ***************************
       Table: emp_date
Create Table: CREATE TABLE `emp_date` (
  `id` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job` varchar(30) NOT NULL,
  `store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( YEAR(separated))
(PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2030) ENGINE = InnoDB) */
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> alter table emp_date add partition ( partition p4 values less than (2050));
ERROR 1517 (HY000): Duplicate partition name p4
mysql> show create table expenses \G;
*************************** 1. row ***************************
       Table: expenses
Create Table: CREATE TABLE `expenses` (
  `expens_date` date NOT NULL,
  `category` int(11) DEFAULT NULL,
  `amount` decimal(10,3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY LIST (category)
(PARTITION p0 VALUES IN (3,5) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (1,10) ENGINE = InnoDB,
 PARTITION p2 VALUES IN (4,9) ENGINE = InnoDB,
 PARTITION p3 VALUES IN (2) ENGINE = InnoDB,
 PARTITION p4 VALUES IN (6) ENGINE = InnoDB) */
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> alter table expenses add partition ( partition p5 values in (7,8));
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table expenses \G;
*************************** 1. row ***************************
       Table: expenses
Create Table: CREATE TABLE `expenses` (
  `expens_date` date NOT NULL,
  `category` int(11) DEFAULT NULL,
  `amount` decimal(10,3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY LIST (category)
(PARTITION p0 VALUES IN (3,5) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (1,10) ENGINE = InnoDB,
 PARTITION p2 VALUES IN (4,9) ENGINE = InnoDB,
 PARTITION p3 VALUES IN (2) ENGINE = InnoDB,
 PARTITION p4 VALUES IN (6) ENGINE = InnoDB,
 PARTITION p5 VALUES IN (7,8) ENGINE = InnoDB) */
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> show create table emp_date \G;
*************************** 1. row ***************************
       Table: emp_date
Create Table: CREATE TABLE `emp_date` (
  `id` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job` varchar(30) NOT NULL,
  `store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( YEAR(separated))
(PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2030) ENGINE = InnoDB) */
1 row in set (0.00 sec)

ERROR: 
No query specified


mysql> show create table emp_date \G;
*************************** 1. row ***************************
       Table: emp_date
Create Table: CREATE TABLE `emp_date` (
  `id` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job` varchar(30) NOT NULL,
  `store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( YEAR(separated))
(PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2030) ENGINE = InnoDB) */
1 row in set (0.00 sec)

ERROR: 
No query specified


mysql> alter table emp_date reorganize partition p4 into ( partition p5 values less than (2010), partition p6 values less than (2030) );
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create emp_date \G;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'emp_date' at line 1
ERROR: 
No query specified

mysql> show create table emp_date \G;
*************************** 1. row ***************************
       Table: emp_date
Create Table: CREATE TABLE `emp_date` (
  `id` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job` varchar(30) NOT NULL,
  `store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( YEAR(separated))
(PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (2030) ENGINE = InnoDB) */
1 row in set (0.01 sec)

ERROR: 
No query specified


mysql> CREATE TABLE tb_range(
    -> id INT,
    -> name VARCHAR(5)
    -> )
    -> PARTITION BY RANGE(id)(
    -> PARTITION p0 VALUES LESS THAN (-6),
    -> PARTITION P1 VALUES LESS THAN (0),
    -> PARTITION p2 VALUES LESS THAN (1),
    -> PARTITION p3 VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into tb_range values (null,'NULL');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT
    -> partition_name part,
    -> partition_expression expr,
    -> partition_description descr,
    -> table_rows
    -> FROM
    -> INFORMATION_SCHEMA.partitions
    -> WHERE
    -> TABLE_SCHEMA = schema()
    -> AND TABLE_NAME='tb_range';
+------+------+----------+------------+
| part | expr | descr    | table_rows |
+------+------+----------+------------+
| p0   | id   | -6       |          1 |
| P1   | id   | 0        |          0 |
| p2   | id   | 1        |          0 |
| p3   | id   | MAXVALUE |          0 |
+------+------+----------+------------+
4 rows in set (0.02 sec)

mysql> CREATE TABLE tb_list(
    -> id INT,
    -> name VARCHAR(5)
    -> )
    -> PARTITION BY LIST(id)(
    -> PARTITION p1 VALUES IN (0),
    -> PARTITION p2 VALUES IN (1)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> insert into tb_list values (null,'NULL');
ERROR 1526 (HY000): Table has no partition for value NULL
mysql> drop table tb_list;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE tb_list(
    -> id INT,
    -> name VARCHAR(5)
    -> )
    -> PARTITION BY LIST(id)(
    -> PARTITION p1 VALUES IN (0,NULL),
    -> PARTITION p2 VALUES IN (1)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tb_list values (NULL,'NULL');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT partition_name part, partition_expression expr, partition_description descr, table_rows FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() AND TABLE_NAME='tb_range';
+------+------+----------+------------+
| part | expr | descr    | table_rows |
+------+------+----------+------------+
| p0   | id   | -6       |          1 |
| P1   | id   | 0        |          0 |
| p2   | id   | 1        |          0 |
| p3   | id   | MAXVALUE |          0 |
+------+------+----------+------------+
4 rows in set (0.00 sec)

mysql> SELECT partition_name part, partition_expression expr, partition_description descr, table_rows FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() AND TABLE_NAME='tb_list';
+------+------+--------+------------+
| part | expr | descr  | table_rows |
+------+------+--------+------------+
| p1   | id   | NULL,0 |          1 |
| p2   | id   | 1      |          0 |
+------+------+--------+------------+
2 rows in set (0.00 sec)


mysql> CREATE TABLE tb_hash( id INT, name VARCHAR(5) ) PARTITION BY HASH(id) PARTITIONS 2;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tb_hash values (null,'NULL');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT partition_name part, partition_expression expr, partition_description descr, table_rows FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() AND TABLE_NAME='tb_hash';
+------+------+-------+------------+
| part | expr | descr | table_rows |
+------+------+-------+------------+
| p0   | id   | NULL  |          1 |
| p1   | id   | NULL  |          0 |
+------+------+-------+------------+
2 rows in set (0.00 sec)



mysql> show create table emp_date \G;
*************************** 1. row ***************************
       Table: emp_date
Create Table: CREATE TABLE `emp_date` (
  `id` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job` varchar(30) NOT NULL,
  `store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( YEAR(separated))
(PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2005) ENGINE = InnoDB) */
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> drop table emp_date;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE emp_date( id INT NOT NULL, ename VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job VARCHAR(30) NOT NULL, store_id INT NOT NULL ) PARTITION BY RANGE ( YEAR(separated) ) ( PARTITION p0  VALUES LESS THAN (1995), PARTITION p1  VALUES LESS THAN (2000), PARTITION p2  VALUES LESS THAN (2005), PARTITION p3  VALUES LESS THAN (2015) );
Query OK, 0 rows affected (0.06 sec)


mysql> insert into emp_date (id,ename,hired,separated,job,store_id) values  (7001,'aaaa','1981-01-01','2005-03-03','bbbbbbb',10), (7300,'dddd','1982-02-01','2006-03-03','ddddddd',30), (7600,'eeeedsfkasjdiwaer','1987-03-01','2003-03-5','adksjfakre',60);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from emp_date 
    -> where separated between '2005-01-01' and '2010-12-31';
+------+-------+------------+------------+---------+----------+
| id   | ename | hired      | separated  | job     | store_id |
+------+-------+------------+------------+---------+----------+
| 7001 | aaaa  | 1981-01-01 | 2005-03-03 | bbbbbbb |       10 |
| 7300 | dddd  | 1982-02-01 | 2006-03-03 | ddddddd |       30 |
+------+-------+------------+------------+---------+----------+
2 rows in set (0.01 sec)

mysql> select * from emp_date  where separated between '2005-01-01' and '2015-12-31';
+------+-------+------------+------------+---------+----------+
| id   | ename | hired      | separated  | job     | store_id |
+------+-------+------------+------------+---------+----------+
| 7001 | aaaa  | 1981-01-01 | 2005-03-03 | bbbbbbb |       10 |
| 7300 | dddd  | 1982-02-01 | 2006-03-03 | ddddddd |       30 |
+------+-------+------------+------------+---------+----------+
2 rows in set (0.00 sec)


mysql> alter table emp_date drop  partition p3;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table emp_date \G;
*************************** 1. row ***************************
       Table: emp_date
Create Table: CREATE TABLE `emp_date` (
  `id` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job` varchar(30) NOT NULL,
  `store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( YEAR(separated))
(PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2005) ENGINE = InnoDB) */
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> select * from emp_date  where separated between '2005-01-01' and '2015-12-31';
Empty set (0.00 sec)


mysql> show create table emp_date \G;
*************************** 1. row ***************************
       Table: emp_date
Create Table: CREATE TABLE `emp_date` (
  `id` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job` varchar(30) NOT NULL,
  `store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( YEAR(separated))
(PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2005) ENGINE = InnoDB) */
1 row in set (0.01 sec)

ERROR: 
No query specified

mysql> insert into emp_date (id,ename,hired,separated,job,store_id) values (7300,'dddd','1982-02-01','2001-03-03','ddddddd',30), (7600,'eeeedsfkasjdiwaer','1987-03-01','2003-03-5','adksjfakre',60);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT partition_name part, partition_expression expr, partition_description descr, table_rows FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() AND TABLE_NAME='tb_hash';
+------+------+-------+------------+
| part | expr | descr | table_rows |
+------+------+-------+------------+
| p0   | id   | NULL  |          1 |
| p1   | id   | NULL  |          0 |
+------+------+-------+------------+
2 rows in set (0.01 sec)

mysql> SELECT partition_name part, partition_expression expr, partition_description descr, table_rows FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() AND TABLE_NAME='emp_date';
+------+------------------+-------+------------+
| part | expr             | descr | table_rows |
+------+------------------+-------+------------+
| p0   |  YEAR(separated) | 1995  |          0 |
| p1   |  YEAR(separated) | 2000  |          0 |
| p2   |  YEAR(separated) | 2005  |          3 |
+------+------------------+-------+------------+
3 rows in set (0.00 sec)

mysql> insert into emp_date (id,ename,hired,separated,job,store_id) values (7300,'dddd','1982-02-01','1996-03-03','ddddddd',30), (7600,'eeeedsfkasjdiwaer','1987-03-01','1997-03-5','adksjfakre',60);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT partition_name part, partition_expression expr, partition_description descr, table_rows FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() AND TABLE_NAME='emp_date';
+------+------------------+-------+------------+
| part | expr             | descr | table_rows |
+------+------------------+-------+------------+
| p0   |  YEAR(separated) | 1995  |          0 |
| p1   |  YEAR(separated) | 2000  |          2 |
| p2   |  YEAR(separated) | 2005  |          3 |
+------+------------------+-------+------------+
3 rows in set (0.00 sec)

mysql> alter table emp_date add partition ( partition p4 values less than (2030));
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table emp_date \G;
*************************** 1. row ***************************
       Table: emp_date
Create Table: CREATE TABLE `emp_date` (
  `id` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job` varchar(30) NOT NULL,
  `store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( YEAR(separated))
(PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2030) ENGINE = InnoDB) */
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> alter table emp_date add partition ( partition p4 values less than (2050));
ERROR 1517 (HY000): Duplicate partition name p4
mysql> show create table expenses \G;
*************************** 1. row ***************************
       Table: expenses
Create Table: CREATE TABLE `expenses` (
  `expens_date` date NOT NULL,
  `category` int(11) DEFAULT NULL,
  `amount` decimal(10,3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY LIST (category)
(PARTITION p0 VALUES IN (3,5) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (1,10) ENGINE = InnoDB,
 PARTITION p2 VALUES IN (4,9) ENGINE = InnoDB,
 PARTITION p3 VALUES IN (2) ENGINE = InnoDB,
 PARTITION p4 VALUES IN (6) ENGINE = InnoDB) */
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> alter table expenses add partition ( partition p5 values in (7,8));
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table expenses \G;
*************************** 1. row ***************************
       Table: expenses
Create Table: CREATE TABLE `expenses` (
  `expens_date` date NOT NULL,
  `category` int(11) DEFAULT NULL,
  `amount` decimal(10,3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY LIST (category)
(PARTITION p0 VALUES IN (3,5) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (1,10) ENGINE = InnoDB,
 PARTITION p2 VALUES IN (4,9) ENGINE = InnoDB,
 PARTITION p3 VALUES IN (2) ENGINE = InnoDB,
 PARTITION p4 VALUES IN (6) ENGINE = InnoDB,
 PARTITION p5 VALUES IN (7,8) ENGINE = InnoDB) */
1 row in set (0.00 sec)

ERROR: 
No query specified


mysql> show create table emp_date \G;
*************************** 1. row ***************************
       Table: emp_date
Create Table: CREATE TABLE `emp_date` (
  `id` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job` varchar(30) NOT NULL,
  `store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( YEAR(separated))
(PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2030) ENGINE = InnoDB) */
1 row in set (0.00 sec)

ERROR: 
No query specified


mysql> show create table emp_date \G;
*************************** 1. row ***************************
       Table: emp_date
Create Table: CREATE TABLE `emp_date` (
  `id` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job` varchar(30) NOT NULL,
  `store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( YEAR(separated))
(PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2030) ENGINE = InnoDB) */
1 row in set (0.00 sec)

ERROR: 
No query specified


mysql> alter table emp_date reorganize partition p4 into ( partition p5 values less than (2010), partition p6 values less than (2030) );
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create emp_date \G;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'emp_date' at line 1
ERROR: 
No query specified

mysql> show create table emp_date \G;
*************************** 1. row ***************************
       Table: emp_date
Create Table: CREATE TABLE `emp_date` (
  `id` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job` varchar(30) NOT NULL,
  `store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( YEAR(separated))
(PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (2030) ENGINE = InnoDB) */
1 row in set (0.01 sec)

ERROR: 
No query specified

mysql> show create table emp_date \G;
*************************** 1. row ***************************
       Table: emp_date
Create Table: CREATE TABLE `emp_date` (
  `id` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job` varchar(30) NOT NULL,
  `store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( YEAR(separated))
(PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (2030) ENGINE = InnoDB) */
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> create table emp(
    -> id int not null,
    -> ename varchar(30),
    -> hired date not null default '1970-01-01',
    -> separated date not null default '9999-12-31',
    -> job varchar(30) not null,
    -> store_id int not null
    -> )
    -> partition by hash(store_id) partitions 4;
Query OK, 0 rows affected (0.05 sec)

mysql> alter table emp coalesce partition 2;
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table emp \G;
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `id` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job` varchar(30) NOT NULL,
  `store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (store_id)
PARTITIONS 2 */
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> alter table emp coalesce partition 8;
ERROR 1508 (HY000): Cannot remove all partitions, use DROP TABLE instead
mysql> alter table emp add partition partitions 8;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table emp \G;
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `id` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job` varchar(30) NOT NULL,
  `store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (store_id)
PARTITIONS 10 */
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql>

bwshqh
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
MySQL学习笔记3-数据库设计与优化.md
08-22
通过学习可以掌握良好的数据库设计原则,并能使用各种优化手段提升数据库性能,比如索引、分区、缓存等。 使用建议: 重点学习数据库优化部分,针对各种优化手段的示例代码进行理解和拓展实践。最后的设计案例也可以...
spark的转换算子2
changshupx的博客
09-08 1651
1）coalesce def coalesce(numPartitions: Int, shuffle: Boolean = false)(implicit ord: Ordering[T] = null): RDD[T] 该函数用于将RDD进行重分区，使用HashPartitioner。 第一个参数为重分区的数目，第二个为是否进行shuffle，默认为false; 作用 缩减分区数，用于大数据集过滤后，提高小数据集的执行效率。 val ListRDD: RDD[Int] = context.makeRDD
增加mysql分区的指令_三十八、MySQL基础系列笔记之MySQL分区管理
weixin_33507566的博客
01-19 667
MySQL提供了添加、删除、重庆义、合并、拆分、交换分区的命令用于管理分区的操作，这些命令都使用alter table实现。RANGE与LIST分区管理对于添加、删除、重定义，RANGE与LIST是相似的。删除分区命令：alter table 表名 drop partition创建分区表并写入数据# 创建分区表create table demo10(id int not null,name var...
mysql分区表检验,MySQL分区表管理
weixin_39630813的博客
03-23 191
RANGE,LIST分区管理1:为未分区表创建分区ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2;2:删除某个分区的数据ALTER TABLE tr DROP PARTITION p2;3:为分区表添加一个分区ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (20...
java.sql.SQLException
橙_的博客
02-21 4015
Statement stmt = connection.createStatement(); //1 ResultSet QueryF= stmt.executeQuery(sql); //2 while(QueryF.next()) { //3 //分区存在. //查找分区数据. sql=&amp;quot;select * from lte2018 partition(...
range分区(实用篇)
weixin_34308389的博客
05-05 309
上一篇blog对range分区做了个大概简介，接下来这篇对实际使用中的一些小细节进行分析总结。主要是新增和删除分区以及数据写入(null值的处理已在前面提到http://blog.sina.com.cn/s/blog_4d398f210100wrb8.html) =============================================...
MySQL学习记录之KEY分区引发的血案
12-14
需求背景 业务表tb_image部分数据如下所示，其中id唯一，image_no不唯一。image_no表示每个文件的编号，每个文件在业务系统中会生成若干个文件，每个文件的唯一ID就是字段id： 业务表tb_image的一些情况如下： ...
mysql学习交流
10-11
MYSQL 学习笔记和交流分享 主要介绍了mysql 5特征包括 分区，事件，存储引擎。
DataX Web分布式数据同步工具-其他
06-12
DataX Web是在DataX之上开发的分布式数据同步工具，提供简单易用的操作界面，降低用户使用DataX的学习成本，缩短任务配置时间，避免配置过程中出错。用户可通过页面选择数据源即可创建数据同步任务，RDBMS数据源可...
mysql 分区管理和对NULL的处理
mjlfto的博客
05-29 1316
Mysql 分区对NULL的处理 range分区中， NULL值会被当做最小值处理 list分区中， NULL值必须出现在分区枚举值中， 否在在插入数据是会报错 hash和key会将NULL当做0处理 分区管理 添加分区 ：range —> alter table talbeName add partition(partition partitionName values less than(va
MYSQL— perror 错误码详情
热门推荐
道行尚浅（老道）的博客
12-04 11万+
[root@localhost ~]# cat test_nothread.py import paramiko import threading import os def ssh2(ip,username,passwd,cmd): file_path='/root/perror.log' logfile=file(file_path,'a') try:
MySQL5.6.17学习笔记（四）复合分区及分区管理
weixin_34067102的博客
08-25 219
鲁春利的工作笔记，谁说程序员不能有文艺范？子分区http://dev.mysql.com/doc/refman/5.6/en/partitioning-subpartitions.html又称为复合分区（composite partitioning），是在一个分区表中对分区的进一步拆分。CREATETABLEts_with_sub_partition( ...
hive无法删除分区
wflh323的专栏
04-17 2480
hive中执行 ALTER TABLE xxxDROP IF EXISTS PARTITION (xx='xxx') 删除某表分区时，一直没有执行，卡住不动。其它表执行没有问题。info级别日志没什么输出，日志一直停留在那。在hive命令行使用hive --hiveconf hive.root.logger=DEBUG,console 启动客户端开启debug模式，再次执行删除分区语句，输...
【对象管理】分区表
滴水石穿
08-15 2447
当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。 第一，改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。 第二，增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
How do I remove all partitions, data and create clean empty hard disk under Linux operating systems?
菩提本無樹
10-09 443
轉載自http://www.cyberciti.biz/faq/linux-remove-all-partitions-data-empty-disk/ How do I remove all partitions, data and create clean empty hard disk under Linux operating systems?   If you are
mysql错误码解释
weixin_33910434的博客
12-25 6230
OS error code 1: Operation not permitted OS error code 2: No such file or directory OS error code 3: No such process OS error code 4: Interrupted system call OS error code 5: Inp...
Mysql-error code汇总
文学超的博客
07-03 5万+
OS error code 1: Operation not permitted OS error code 2: No such file or directory OS error code 3: No such process OS error code 4: Interrupted system call OS error code 5: In
mysql分区语句
qq_35548288的博客
11-06 8081
1:为未分区表创建分区 ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2; 2:删除某个分区的数据 ALTER TABLE tr DROP PARTITION p2; 3:为分区表添加一个分区 ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2...
mysql-connector-java-bin
最新发布
03-22
mysql-connector-java-bin是MySQL官方提供的Java连接器，用于在Java应用程序中连接和操作MySQL数据库。它是一个JDBC驱动程序，提供了与MySQL数据库的通信和交互的功能。 mysql-connector-java-bin的主要特点包括： ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值