-. like 구문으로 테이블 레이아웃 복사
mysql> create table layout_org -> (num int auto_increment primary key, -> num2 int, -> num3 int) -> partition by range(num) -> (partition p1 values less than(10), -> partition p2 values less than(20), -> partition p3 values less than maxvalue); Query OK, 0 rows affected (0.03 sec) mysql> create index ix_layout_org on layout_org (num2); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0
=> 파티션 구성 + primary key,auto_increment 및 index 까지 달려있는 원본 테이블
mysql> create table layout_test like layout_org; Query OK, 0 rows affected (0.03 sec)
=> CTAS 구문이 아닌 like 구문으로 테이블 레이아웃 복사
mysql> show create table layout_test\G; *************************** 1. row *************************** Table: layout_test Create Table: CREATE TABLE "layout_test" ( "num" int(11) NOT NULL AUTO_INCREMENT, "num2" int(11) DEFAULT NULL, "num3" int(11) DEFAULT NULL, PRIMARY KEY ("num"), KEY "ix_layout_org" ("num2") ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin PARTITION BY RANGE (num) (PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (20) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) 1 row in set (0.00 sec) ERROR: No query specified
=> 파티션,인덱스,primary key 등까지 모두 생성됨
-. CTAS 구문으로 테이블 레이아웃 복사
mysql> create table layout_test2 -> as select * -> from layout_org; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
=> CTAS 문으로 생성
mysql> show create table layout_test2\G; *************************** 1. row *************************** Table: layout_test2 Create Table: CREATE TABLE "layout_test2" ( "num" int(11) NOT NULL DEFAULT 0, "num2" int(11) DEFAULT NULL, "num3" int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin 1 row in set (0.01 sec) ERROR: No query specified mysql> show index from layout_test2; Empty set (0.00 sec)
=> 파티션 및 인덱스 모두 생성안됨, 컬럼같은 테이블 구조만 생성됨