56,754
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE `pis_supply_chain_a` (
`ID` varchar(50) NOT NULL,
`CMMDTY_CODE` varchar(18) NOT NULL,
`CITY_TO` varchar(12) DEFAULT NULL,
`LOGISTICS_RAD_MODE` varchar(3) DEFAULT NULL,
`CITY_FROM` varchar(12) DEFAULT NULL,
`VERSION_NO` varchar(18) DEFAULT NULL,
`CREATE_TIME` datetime DEFAULT NULL,
`UPDATE_TIME` datetime DEFAULT NULL,
`DELETE_FLAG` varchar(4) DEFAULT 'N',
PRIMARY KEY (`ID`,`CMMDTY_CODE`),
UNIQUE KEY `supply_chain_a_index` (`CMMDTY_CODE`,`CITY_TO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='1\n3000W10'
PARTITION BY KEY (CMMDTY_CODE) PARTITIONS 10
mysql> SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME ='pis_supply_chain_a';
+--------------------+----------------+------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS |
+--------------------+----------------+------------+
| pis_supply_chain_a | p0 | 3630867 |
| pis_supply_chain_a | p1 | 0 |
| pis_supply_chain_a | p2 | 4537399 |
| pis_supply_chain_a | p3 | 0 |
| pis_supply_chain_a | p4 | 5225392 |
| pis_supply_chain_a | p5 | 0 |
| pis_supply_chain_a | p6 | 5495859 |
| pis_supply_chain_a | p7 | 1 |
| pis_supply_chain_a | p8 | 4757965 |
| pis_supply_chain_a | p9 | 0 |
+--------------------+----------------+------------+
10 rows in set (0.35 sec)
mysql> select distinct CMMDTY_CODE from pis_supply_chain_a limit 20;
+--------------------+
| CMMDTY_CODE |
+--------------------+
| 000000000100003931 |
| 000000000100004594 |
| 000000000100005662 |
| 000000000100019670 |
| 000000000100025155 |
| 000000000100028229 |
| 000000000100028764 |
| 000000000100030935 |
| 000000000100032337 |
| 000000000100032338 |
| 000000000100033242 |
| 000000000100034839 |
| 000000000100039735 |
| 000000000100039837 |
| 000000000100039848 |
| 000000000100040161 |
| 000000000100044946 |
| 000000000100044950 |
| 000000000100048556 |
| 000000000100050836 |
+--------------------+
20 rows in set (0.02 sec)
17.2.4. KEY Partitioning Partitioning by key is similar to partitioning by hash, except that where hash partitioning employs a user-defined expression, the hashing function for key partitioning is supplied by the MySQL server. MySQL Cluster uses MD5() for this purpose; for tables using other storage engines, the server employs its own internal hashing function which is based on the same algorithm as PASSWORD().