mysql的使用25

最新推荐文章于 2021-10-11 15:01:57 发布

Jack_Lpz

最新推荐文章于 2021-10-11 15:01:57 发布

阅读量1.6k

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/u012016940/article/details/39338923

版权

MySQL 专栏收录该内容

27 篇文章 0 订阅

订阅专栏

mysql> create table listPart(
    -> a int,b int)engine=innodb
    -> partition p0values in (1,3,5,7,9),
    -> partition p0values in (0,2,4,6,8));
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 'p0values in (1,3,5,7,9),
partition p0values in (0,2,4,6,8))' at line 3
mysql>
mysql> create table listPart( a int,b int)engine=innodb partition by list(b)(partition p0 values in (1,3,5,7,9), partition p0values in (0,2,4,6,8));
ERROR 1479 (HY000): Syntax error: LIST PARTITIONING requires definition of VALUES IN for each partition
mysql>
mysql> create table listPart( a int,b int)engine=innodb partition by list(b)(partition p0 values in (1,3,5,7,9), partition p1 values in (0,2,4,6,8));
Query OK, 0 rows affected (0.13 sec)

mysql> #ok list partition
mysql>
mysql> insert into listPart select 1,2;
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into listPart select 1,1;
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into listPart select 1,3;
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into listPart select 1,4;
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select table_name, partition_name, table_rows from information_schema.partitions where table_name='listPart' and table_schema=database();
+------------+----------------+------------+
| table_name | partition_name | table_rows |
+------------+----------------+------------+
| listPart   | p0             |          2 |
| listPart   | p1             |          2 |
+------------+----------------+------------+
2 rows in set (0.01 sec)

mysql> #现在可以看出两组
mysql>
mysql> #hash分区
mysql> #目的是将数据均匀的分布到预先定义的各个分区中，保证各分区的数据量大致是一样的，
mysql> #在range和list分区中，必须明确制定一个给定的列值或列值集合应该保存在哪个分区，而在hash分区中，MySQL自动完成这些工作
mysql>
mysql> create table t_hash(a int, b int)engine=innodb partition by hash(year(b))
    -> partition 4;
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 'partition 4' at line 2
mysql>
mysql> create table t_hash(a int, b datetime)engine=innodb partition by hash(year(b)) partition 4;
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 'partition 4' at line 1
mysql>
mysql> create table t_hash(a int, b datetime)engine=innodb partition by hash(year(b)) partitions 4;
Query OK, 0 rows affected (0.21 sec)

mysql> insert into t_hash select 1,'2010-04-01';
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select table_name, partition_name, table_rows
    -> from information_schema.partitions where table_schema=database() and table_name='t_hash';
+------------+----------------+------------+
| table_name | partition_name | table_rows |
+------------+----------------+------------+
| t_hash     | p0             |          0 |
| t_hash     | p1             |          0 |
| t_hash     | p2             |          1 |
| t_hash     | p3             |          0 |
+------------+----------------+------------+
4 rows in set (0.00 sec)

mysql> #可以很明显的看出分区
mysql>
mysql> #还有key,columns分区的
mysql> #还有子分区等
mysql>
mysql> exit