Use the LIKE clause instead of a full table definition to create a table with the same definition as another table, including columns, indexes, and table options. Foreign key definitions, as well as any DATA DIRECTORY or INDEX DIRECTORY table options specified on the original table, will not be created.

MariaDB [test]> CREATE TABLE test.innodb_partitioned_table1 (

    -> myid BIGINT(20) NOT NULL,

    -> PRIMARY KEY (myid)

    -> ) ENGINE=INNODB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC

    -> PARTITION BY HASH (myid)

    -> (PARTITION p0001 DATA DIRECTORY = '/data/mysqldata/mysql' ENGINE = INNODB,

    -> PARTITION p0002 DATA DIRECTORY = '/data/mysqldata/mysql' ENGINE = INNODB,

    -> PARTITION p0003 DATA DIRECTORY = '/data/mysqldata/mysql' ENGINE = INNODB,

    -> PARTITION p0004 DATA DIRECTORY = '/data/mysqldata/mysql' ENGINE = INNODB,

    -> PARTITION p0005 DATA DIRECTORY = '/data/mysqldata/mysql' ENGINE = INNODB

    -> );

Query OK, 0 rows affected (0.043 sec)

-- files were created under `/data/mysqldata/mysql/test` directory. In this case `test` directory was created automatically.

[root@node1 mysql]# ll test/

total 480

-rw-rw---- 1 mysql mysql 98304 Jun 15 10:00 innodb_partitioned_table1#P#p0001.ibd

-rw-rw---- 1 mysql mysql 98304 Jun 15 10:00 innodb_partitioned_table1#P#p0002.ibd

-rw-rw---- 1 mysql mysql 98304 Jun 15 10:00 innodb_partitioned_table1#P#p0003.ibd

-rw-rw---- 1 mysql mysql 98304 Jun 15 10:00 innodb_partitioned_table1#P#p0004.ibd

-rw-rw---- 1 mysql mysql 98304 Jun 15 10:00 innodb_partitioned_table1#P#p0005.ibd

MariaDB [test]> CREATE TABLE test.innodb_partitioned_table2 LIKE test.innodb_partitioned_table1;

Query OK, 0 rows affected (0.034 sec)

-- files were created under `/data/mysqldata/mysql/`

[root@node1 mysql]# ll

total 24

drwxrwx--- 3 mysql mysql 4096 Jun 15 10:01 innodb_partitioned_table1#P#p0001

drwxrwx--- 3 mysql mysql 4096 Jun 15 10:01 innodb_partitioned_table1#P#p0002

drwxrwx--- 3 mysql mysql 4096 Jun 15 10:01 innodb_partitioned_table1#P#p0003

drwxrwx--- 3 mysql mysql 4096 Jun 15 10:01 innodb_partitioned_table1#P#p0004

drwxrwx--- 3 mysql mysql 4096 Jun 15 10:01 innodb_partitioned_table1#P#p0005

drwxrwx--- 2 mysql mysql 4096 Jun 15 10:00 test

MariaDB [test]> CREATE TABLE test.myisam_partitioned_table1 (

    -> myid BIGINT(20) NOT NULL,

    -> PRIMARY KEY (myid)

    -> ) ENGINE=MYISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC

    -> PARTITION BY HASH (myid)

    -> (PARTITION p0001 DATA DIRECTORY = '/data/mysqldata/mysql' ENGINE = MYISAM,

    -> PARTITION p0002 DATA DIRECTORY = '/data/mysqldata/mysql' ENGINE = MYISAM,

    -> PARTITION p0003 DATA DIRECTORY = '/data/mysqldata/mysql' ENGINE = MYISAM,

    -> PARTITION p0004 DATA DIRECTORY = '/data/mysqldata/mysql' ENGINE = MYISAM,

    -> PARTITION p0005 DATA DIRECTORY = '/data/mysqldata/mysql' ENGINE = MYISAM

    -> )

    -> ;

Query OK, 0 rows affected (0.004 sec)

-- files were created under '/data/mysqldata/mysql'

[root@node1 mysql]# ll

total 0

-rw-rw---- 1 mysql mysql 0 Jun 15 10:08 myisam_partitioned_table1#P#p0001.MYD

-rw-rw---- 1 mysql mysql 0 Jun 15 10:08 myisam_partitioned_table1#P#p0002.MYD

-rw-rw---- 1 mysql mysql 0 Jun 15 10:08 myisam_partitioned_table1#P#p0003.MYD

-rw-rw---- 1 mysql mysql 0 Jun 15 10:08 myisam_partitioned_table1#P#p0004.MYD

-rw-rw---- 1 mysql mysql 0 Jun 15 10:08 myisam_partitioned_table1#P#p0005.MYD

MariaDB [test]> CREATE TABLE test.myisam_partitioned_table2 LIKE test.myisam_partitioned_table1;

ERROR 1 (HY000): Can't create/write to file '/data/mysqldata/mysql/myisam_partitioned_table1#P#p0001.MYD/myisam_partitioned_table2#P#p0001.MYD' (Errcode: 20 "Not a directory")