MySQL 8 Cookbook

Managing partitioned tables in a general tablespace

You can create a table with partitions in multiple tablespaces:

mysql> CREATE TABLE table_gen_part_ts1 (id INT, value varchar(100)) ENGINE = InnoDB       PARTITION BY RANGE(id) (        PARTITION p1 VALUES LESS THAN (1000000) TABLESPACE ts1,        PARTITION p2 VALUES LESS THAN (2000000) TABLESPACE ts2,        PARTITION p3 VALUES LESS THAN (3000000) TABLESPACE innodb_file_per_table,        PARTITION pmax VALUES LESS THAN (MAXVALUE) TABLESPACE innodb_system);Query OK, 0 rows affected (0.19 sec)

You can add a new partition in another tablespace, or if you do not mention anything, it will be created in table's default tablespace. An ALTER TABLE tbl_name TABLESPACE tablespace_name operation on a partitioned table only modifies ...