[sql] MariaDB Table layout copy 방법 [펌]

[sql] MariaDB Table layout copy 방법 [펌]

 

-. 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)

=> 파티션 및 인덱스 모두 생성안됨, 컬럼같은 테이블 구조만 생성됨