PARTITION p2 VALUES in (20) ) ; 6) 分区表的管理操作 删除分区: alter table emp drop partition p1; 不可以删除hash或者key分区。 一次性删除多个分区,alter table emp drop partition p1,p2; 增加分区: alter table emp add partition (partition p3 values less than (4000)); alter table empl add p...
PARTITION_NAME: p0 SUBPARTITION_NAME: NULL PARTITION_ORDINAL_POSITION: 1 SUBPARTITION_ORDINAL_POSITION: NULL PARTITION_METHOD: RANGE SUBPARTITION_METHOD: NULL PARTITION_EXPRESSION: id SUBPARTITION_EXPRESSION: NULL PARTITION_DESCRIPTION: 10 TABLE_ROWS: 2 AVG_ROW_LENGTH: 8192 DATA_LENGTH: 16384 MAX_DATA...
CATALOG: def TABLE_SCHEMA: test2 TABLE_NAME: t PARTITION_NAME: p0 SUBPARTITION_NAME: NULL PARTITION_ORDINAL_POSITION: 1 SUBPARTITION_ORDINAL_POSITION: NULL PARTITION_METHOD: RANGE SUBPARTITION_METHOD: NULL PARTITION_EXPRESSION: id SUBPARTITION_EXPRESSION: NULL PARTITION_DESCRIPTION: 10 TABLE_ROWS: 1 ...
('xiaoping',15,'shenzhen',6,20190603,1);--[4]查询分区信息SELECTPARTITION_NAME, TABLE_ROWS, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION, PARTITION_ORDINAL_POSITIONFROMINFORMATION_SCHEMA.PARTITIONSWHERETABLE_NAME='t_partition_by_range';---END RANGE--- 分区存储信息结果: 如上表,理论...
SELECT PARTITION_NAME, TABLE_ROWS, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION, PARTITION_ORDINAL_POSITION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 't_partition_by_range'; -- ---END RANGE--- 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14....
PARTITION_EXPRESSION:分区的表达式。 SUBPARTITION_EXPRESSION:子分区的表达式,如果表有子分区的话。 PARTITION_DESCRIPTION:分区的描述信息。 TABLE_ROWS:分区内的行数。 AVG_ROW_LENGTH:分区内平均行长度。 DATA_LENGTH:分区的数据长度。 INDEX_LENGTH:分区的索引长度。
[root@BlackGhost mysql-5.1.50]# ./configure --help |grep -A 3 Partition === Partition Support === Plugin Name: partition Description: MySQL Partitioning Support Supports build: static Configurations: max, max-no-ndb 如果发现有上面这个东西,说明他是支持分区的,默认是打开的。 mysql> show vari...
PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION, TABLE_ROWS FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = SCHEMA () AND TABLE_NAME = 'my_table'; 效果如下图,我们看到4个分区名分别为p0~p3: 插入数据验证 现在我们使用如下SQL插入数据来验证下数据插入情况: ...
partition p2 values less than (1990), partition p3 values less than maxvalue ); 以year(birthdate)表达式(计算员工的出生日期)作为范围分区依据。这里最值得注意的是表达式必须有返回值。 2) 创建list分区 create table emp (empnovarchar(20) not null , ...
SELECT PARTITION_NAME AS Name ,PARTITION_ORDINAL_POSITION AS Position ,PARTITION_METHOD AS Method ,PARTITION_EXPRESSION AS Expression ,PARTITION_DESCRIPTION AS Description ,TABLE_ROWS AS Rows ,CREATE_TIME AS CreateTime FROM INFORMATION_SCHEMA.PARTITIONS ...