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

最新推荐文章于 2021-03-23 19:40:29 发布
最新推荐文章于 2021-03-23 19:40:29 发布
阅读量1.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
关注
专栏目录
MySQL学习笔记--分区分表
lliweei的博客
12-27 725
学习笔记
spark的转换算子2
changshupx的博客
09-08 1793
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
How do I remove all partitions, data and create clean empty hard disk under Linux operating systems?
菩提本無樹
10-09 467
轉載自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分区的指令_三十八、MySQL基础系列笔记之MySQL分区管理
weixin_33507566的博客
01-19 718
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 249
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 4467
Statement stmt = connection.createStatement(); //1 ResultSet QueryF= stmt.executeQuery(sql); //2 while(QueryF.next()) { //3 //分区存在. //查找分区数据. sql=&amp;quot;select * from lte2018 partition(...
MYSQL学习资源及笔记-入门必备
最新发布
09-10
这份“MYSQL学习资源及笔记-入门必备”压缩包文件显然包含了帮助初学者掌握MySQL基础知识的各种资料。下面，我们将深入探讨MySQL的一些核心概念和关键知识点。 1. **MySQL基础知识** - **数据库与表**：MySQL中的...
MySQL入门+进阶资源合集+MySQL学习资料-mysql-dba-master.zip
07-31
这份"MySQL入门+进阶资源合集+MySQL学习资料"的压缩包文件，旨在为初学者和进阶用户提供了全面的学习材料，帮助他们掌握MySQL的基础知识以及更高级的DBA（数据库管理员）技巧。 首先，让我们深入了解一下MySQL的...
MySql学习样例-脚本-示例.7z
01-13
这个" MySql学习样例-脚本-示例.7z "压缩包显然包含了用于教学或实践的MySQL脚本，可能是创建数据库、表、插入数据、更新查询等操作的实例。下面，我们将深入探讨MySQL的一些核心知识点。 首先，我们要理解MySQL的...
mysql-refman-5.7-en.pdf
06-28
总的来说，MySQL 5.7参考手册是学习和掌握MySQL 5.7及其相关NDB集群版本的重要资源，无论你是新手还是经验丰富的开发者，都能从中受益匪浅。通过深入阅读和实践，你可以了解到MySQL的强大功能，并能有效地运用到实际...
range分区(实用篇)
weixin_34308389的博客
05-05 340
上一篇blog对range分区做了个大概简介，接下来这篇对实际使用中的一些小细节进行分析总结。主要是新增和删除分区以及数据写入(null值的处理已在前面提到http://blog.sina.com.cn/s/blog_4d398f210100wrb8.html) =============================================...
mysql 分区管理和对NULL的处理
mjlfto的博客
05-29 1361
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 236
鲁春利的工作笔记，谁说程序员不能有文艺范？子分区http://dev.mysql.com/doc/refman/5.6/en/partitioning-subpartitions.html又称为复合分区（composite partitioning），是在一个分区表中对分区的进一步拆分。CREATETABLEts_with_sub_partition( ...
【对象管理】分区表
滴水石穿
08-15 2557
当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。 第一，改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。 第二，增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
hive无法删除分区
wflh323的专栏
04-17 2581
hive中执行 ALTER TABLE xxxDROP IF EXISTS PARTITION (xx='xxx') 删除某表分区时，一直没有执行，卡住不动。其它表执行没有问题。info级别日志没什么输出，日志一直停留在那。在hive命令行使用hive --hiveconf hive.root.logger=DEBUG,console 启动客户端开启debug模式，再次执行删除分区语句，输...
mysql错误码解释
weixin_33910434的博客
12-25 6570
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分区语句
qq_35548288的博客
11-06 8881
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...
PostgreSQL中 drop table指令出现ERROR: cannot drop table userinfo because other objects depend on it
程序员日常记录
03-15 1万+
PostgreSQL中 drop table指令出现ERROR: cannot drop table userinfo because other objects depend on it。blue=# drop table userinfo; //删除table指令 //错误信息提示 ERROR: cannot drop table userinfo because other objects
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值