content_image/751806.jpg
integers. As with the table-level options with the same names, these act only as “suggestions”  to the server and are not hard limits. 

• TABLESPACE 

May be used to designate an InnoDB  file-per-table tablespace for the partition by specifying TABLESPACE ‘innodb_file_per_table‘ . All partitions must belong to the same storage engine. 

Placing InnoDB  table partitions in shared InnoDB  tablespaces is not supported. Shared tablespaces include the InnoDB  system tablespace and general tablespaces. 

• subpartition_definition 

The partition definition may optionally contain one or more subpartition_definition  clauses. Each of these consists at a minimum of the SUBPARTITION name , where name  is an identifier for the subpartition. Except for the replacement of the PARTITION  keyword with SUBPARTITION , the syntax for a subpartition definition is identical to that for a partition definition. 

Subpartitioning must be done by HASH  or KEY , and can be done only on RANGE  or LIST  partitions. See Section 24.2.6, “Subpartitioning” . 

## Partitioning by Generated Columns 

Partitioning by generated columns is permitted. For example: 

CREATE TABLE t1 ( s1 INT, s2 INT AS (EXP(s1)) STORED ) PARTITION BY LIST (s2) ( PARTITION p1 VALUES IN (1) ); 

Partitioning sees a generated column as a regular column, which enables workarounds for limitations on functions that are not permitted for partitioning (see Section 24.6.3, “Partitioning Limitations Relating to Functions” ). The preceding example demonstrates this technique: EXP()  cannot be used directly in the PARTITION BY  clause, but a generated column defined using EXP()  is permitted. 

## 13.1.20.1 Files Created by CREATE TABLE 

For an InnoDB  table created in a file-per-table tablespace or general tablespace, table data and associated indexes are stored in a .ibd file in the database directory. When an InnoDB  table is created in the system tablespace, table data and indexes are stored in the ibdata* files that represent the system tablespace. The innodb_file_per_table  option controls whether tables are created in file-per- table tablespaces or the system tablespace, by default. The TABLESPACE  option can be used to place a table in a file-per-table tablespace, general tablespace, or the system tablespace, regardless of the innodb_file_per_table  setting. 

For MyISAM  tables, the storage engine creates data and index files. Thus, for each MyISAM  table tbl_name , there are two disk files. 

<img src='content_image/751806.jpg'>

Chapter 16, Alternative Storage Engines , describes what files each storage engine creates to represent tables. If a table name contains special characters, the names for the table files contain encoded versions of those characters as described in Section 9.2.4, “Mapping of Identifiers to File Names” . 

