1
2
3
4
5
6
7
8

CREATE TABLE `tb_partition_test` (
  `user_id` bigint(20) NOT NULL ,
  `city_id` bigint(20) NOT NULL DEFAULT '0',
  `record_type` smallint(6) NOT NULL DEFAULT '0',
  `record` smallint(6) NOT NULL DEFAULT '0' ,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`user_id`,`record_type`),
 ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='分区测试';

1
2
3
4
5
6
7
8
9
10

CREATE TABLE `tb_partition_test_part` (
  `user_id` bigint(20) NOT NULL ,
  `city_id` bigint(20) NOT NULL DEFAULT '0',
  `record_type` smallint(6) NOT NULL DEFAULT '0',
  `record` smallint(6) NOT NULL DEFAULT '0' ,
  `create_time` timestamp NOT NULL DEFAULT 大专栏  记一次MySQL表分区操作 CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`user_id`,`record_type`),
 ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='分区测试'
PARTITION BY HASH(record_type)
PARTITIONS 100;

1
2
3
4
5

for i in {1..100};do
    "INSERT IGNORE INTO tb_partition_test_part 
SELECT * from tb_partition_test WHERE score_type=$i;"
sleep 5     
done

1
2
3
4
5
6

set AUTOCOMMIT = 0;
BEGIN ;
RENAME TABLE tb_partition_test to tb_partition_test_20170916;
RENAME TABLE tb_partition_test_part to tb_partition_test;
COMMIT ;
set AUTOCOMMIT = 1;

1
2
3
4
5
6

+----------+
| count(0) |
+----------+
|   361795 |
+----------+
1 row in set (42.89 sec)

1
2
3
4
5
6

+----------+
| count(0) |
+----------+
|   361795 |
+----------+
1 row in set (3.04 sec)