MySQL - CREATE TABLESPACE Statement

MySQL CREATE TABLESPACE Statement

A logical group of datafiles in a database is known as a table space each database contains at least one tablespace.

You can create a tablespace in MySQL using the CREATE TABLESPACE Statement.

Syntax

Following is the syntax to create a table space in MySQL −

CREATE [UNDO] TABLESPACE tablespace_name

Where, tablespace_name is the name of the table space you need to create.

Example

Following query creates a table with name Employee −

CREATE TABLESPACE sample_tablespace;

Following query creates another tablespace with name test −

CREATE TABLESPACE sample ADD DATAFILE 'sample.ibd' ENGINE = INNODB;

You can verify the creation of the above tablespace using the following query −

select * from INFORMATION_SCHEMA.FILES where TABLESPACE_NAME ='sample'\G;

Following is the output of the above query −

***************** 1. row *****************
             FILE_ID: 73
           FILE_NAME: ./sample.ibd
           FILE_TYPE: TABLESPACE
     TABLESPACE_NAME: sample
       TABLE_CATALOG:
        TABLE_SCHEMA: NULL
          TABLE_NAME: NULL
  LOGFILE_GROUP_NAME: NULL
LOGFILE_GROUP_NUMBER: NULL
              ENGINE: InnoDB
       FULLTEXT_KEYS: NULL
        DELETED_ROWS: NULL 
        UPDATE_COUNT: NUL
   . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
   . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
   . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
   . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
   . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
   . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
   . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1 row in set (0.00 sec)