MySQL 5.6 한글메뉴얼

13.1.17.1 CREATE TABLE ... SELECT 구문

CREATE TABLE 문의 끝에 SELECT 문을 추가하여 테이블을 다른 테이블에서 만들 수 있습니다.

 CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl ;

MySQL은 SELECT 의 모든 요소에 대해 새로운 컬럼을 작성합니다. 예 :

 mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT,
     -> PRIMARY KEY (a), KEY(b))
     -> ENGINE=MyISAM SELECT b,c FROM test2;

그러면 a , b , c 의 3 개의 컬럼을 포함 MyISAM 테이블이 작성됩니다. ENGINE 옵션은 CREATE TABLE 문의 일부이기 때문에 SELECT 다음에 사용해서는 안됩니다. 이렇게하면 구문 오류가 발생합니다. CHARSET 등 기타 CREATE TABLE 옵션도 마찬가지입니다.

SELECT 문에서 컬럼은 테이블에 오버랩되는 것이 아니라, 테이블 오른쪽에 추가됩니다. 다음의 예를 생각해 보겠습니다.

mysql> SELECT * FROM foo;
+---+
| n |
+---+
| 1 |
+---+

mysql> CREATE TABLE bar (m INT) SELECT n FROM foo;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM bar;
+------+---+
| m    | n |
+------+---+
| NULL | 1 |
+------+---+
1 row in set (0.00 sec)

테이블 foo 의 각 행에 대해 foo 의 값과 새로운 컬럼의 디폴트 값을 가지는 행이 bar 에 삽입됩니다.

CREATE TABLE ... SELECT 의 결과 테이블은 CREATE TABLE 부분에서만 지정되는 컬럼이 먼저 온다. 두 부분으로 지정되는 열 또는 SELECT 부분에서만 지정되는 컬럼이 그 뒤에옵니다. SELECT 컬럼의 데이터 유형은 CREATE TABLE 부분에있는 컬럼을 지정하여 재정의 할 수 있습니다.

테이블에 데이터를 복사하는 동안 오류가 발생하면 그 데이터는 자동으로 삭제되며 생성되지 않습니다.

고유 키 값을 복제하는 행을 처리하는 방법을 설명하기 위해 SELECT 의 전에 IGNORE 또는 REPLACE 을 지정할 수 있습니다. IGNORE 를 사용하면 고유 키 값에 대해 기존 행을 복제하는 행이 삭제됩니다. REPLACE 를 지정하면 새 행은 동일한 고유 키 값을 갖는 행이 바뀝니다. IGNORE 와 REPLACE 의 어느 쪽이나 지정되지 않은 경우 중복 된 고유의 키 값에 의해 오류가 발생합니다.

베이스가되는 SELECT 문에서 행의 순서를 항상 특정 할 수 없기 때문에 MySQL 5.6.4 이후에서는, CREATE TABLE ... IGNORE SELECT 및 CREATE TABLE ... REPLACE SELECT 문에는 문 기반 복제 안전하지 않은 플래그가 지정됩니다. 이러한 변경으로 인해 이러한 문은 명령문 기반 모드를 사용하는 경우에는 로그에 경고를 생성하고 MIXED 모드를 사용하는 경우 행 기반 형식을 사용하여 기록됩니다. 섹션 17.1.2.1 "문 기반 및 열 기반 리플리케이션의 장점과 단점" 을 참조하십시오.

CREATE TABLE ... SELECT 는 어떤 인덱스도 자동으로 작성하지 않습니다. 이것은 문을 가능한 한 유연하게하기 위해 의도적으로 이루어집니다. 생성 된 테이블에 인덱스를 설정하는 경우에는 이들을 SELECT 문 앞에 지정하도록하십시오.

 mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;

어떤 데이터 형식 변환이 수행 될 수 있습니다. 예를 들어, AUTO_INCREMENT 속성이 유지되지 않으므로 VARCHAR 컬럼은 CHAR 컬럼이 될 수 있습니다. 다시 훈련되는 속성은 NULL (또는 NOT NULL )과 그들을 포함 컬럼의 경우는 CHARACTER SET , COLLATION , COMMENT 및 DEFAULT 절입니다.

CREATE TABLE ... SELECT 를 사용하여 테이블을 만들려면 쿼리에있는 모든 함수 호출 또는 수식 별칭을 붙이도록하십시오. 그렇지 않으면, CREATE 문이 실패하거나 바람직하지 않은 컬럼 이름이 생성 될 수 있습니다.

 CREATE TABLE artists_and_works
   SELECT artist.name, COUNT (work.artist_id) AS number_of_works
   FROM artist LEFT JOIN work ON artist.id = work.artist_id
   GROUP BY artist.id;

또한 생성 된 컬럼의 데이터 유형을 명시 적으로 지정할 수도 있습니다.

 CREATE TABLE foo (a TINYINT NOT NULL) SELECT b + 1 AS a FROM bar;

CREATE TABLE ... SELECT 에서 IF NOT EXISTS 가 지정되어있는 경우 대상 테이블이 이미 존재하는 경우, 그 결과는 버전에 따라 달라집니다. MySQL 5.5.6 이전에는 MySQL이 문을 다음과 같이 처리합니다.

CREATE TABLE 부분에서 지정된 테이블 정의가 무시됩니다. 그 정의가 기존 테이블의 정의에 일치하지 않는 경우에도 오류가 발생하지 않습니다. MySQL은 어느쪽으로도 SELECT 부분에서 행을 삽입하려고합니다.
테이블의 컬럼 수와 SELECT 부분에 의해 생성 된 열 수 사이에 불일치가있는 경우 선택된 값은 오른쪽 컬럼에 할당됩니다. 예를 들어, 테이블에 n 개의 열이 포함되어있을 때 SELECT 에 의해 m 개의 컬럼이 생성되는 경우 (여기서 m < n ), 선택된 값은 테이블의 오른쪽 m 개의 컬럼 할당됩니다. 첫 번째 n - m 개의 각 컬럼에 기본값이 할당됩니다. 이 기본값은 컬럼 정의에서 명시 적으로 또는 그 정의에 기본값이 포함되지 않은 경우 암시 적 컬럼 데이터 유형의 기본값으로 지정됩니다. SELECT 부분에 의해 생성 된 열이 너무 많은 경우 ( m > n ) 오류가 발생합니다.
엄격한 SQL 모드가 활성화되어 있고, 또한 이들의 첫 번째 열 모두에 명시 적 기본값이 포함되지 않은 경우,이 문은 오류로 실패합니다.

다음 예제는 IF NOT EXISTS 의 처리를 보여줍니다.

mysql> CREATE TABLE t1 (i1 INT DEFAULT 0, i2 INT, i3 INT, i4 INT);
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE IF NOT EXISTS t1 (c1 CHAR(10)) SELECT 1, 2;
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1;
+------+------+------+------+
| i1   | i2   | i3   | i4   |
+------+------+------+------+
|    0 | NULL |    1 |    2 |
+------+------+------+------+
1 row in set (0.00 sec)

MySQL 5.5.6의 시점에서 CREATE TABLE IF NOT EXISTS ... SELECT 문 처리는 대상 테이블이 이미 존재하는 경우에 대해 변경되었습니다. 또한이 변경은 5.1.51 이후의 MySQL 5.1의 변경도 포함되어 있습니다.

이전에는 CREATE TABLE IF NOT EXISTS ... SELECT 의 경우 MySQL은 테이블이 존재한다는 경고를 생성했지만, 어쨌든도 그 행을 삽입하고 문을 바이너리 로그에 기록했습니다. 이에 대해 CREATE TABLE ... SELECT ( IF NOT EXISTS 가 없습니다) 오류로 실패했지만, MySQL 행을 삽입하지 않고 바이너리 로그에 문을 쓰지 않았습니다.
MySQL은 현재 대상 테이블이 존재하는 경우 두 문을 같은 방법으로 처리합니다. 즉, 중 하나도 행을 삽입하지 않고, 또한 바이너리 로그에 기록되지 않습니다. 이러한 차이는 IF NOT EXISTS 가 존재하는 경우는 MySQL이 경고를 생성하고 존재하지 않는 경우 오류를 생성하는 것입니다.

이 변경은 앞의 예에서 말하면, MySQL 5.5.6의 시점에서는 CREATE TABLE IF NOT EXISTS ... SELECT 문이 대상 테이블에 아무것도 삽입하지 않는 것을 나타냅니다.

이 IF NOT EXISTS 처리 변경으로 인해 원래 동작을 할 MySQL 5.1 마스터에서 새로운 동작을 할 MySQL 5.5 슬레이브에 문 기반 복제 비 호환성이 발생합니다. CREATE TABLE IF NOT EXISTS ... SELECT 마스터에서 실행되면 대상 테이블이 존재하고 있다고합니다. 그 결과, 마스터에서 행이 삽입되지만 슬레이브에 삽입되지 않습니다. (행 기반 복제에는이 문제가되지 않습니다.)

이 문제를 해결하기 위해 MySQL 5.1에서는 CREATE TABLE IF NOT EXISTS ... SELECT 에 대한 명령문 기반 바이너리 로깅이 5.1.51 시점에서 변경되었습니다.

대상 테이블이 존재하지 않는 경우는 변경되지 않습니다. 문은 그대로 기록됩니다.
대상 테이블이 존재하지 않으면 문은 CREATE TABLE IF NOT EXISTS 및 INSERT ... SELECT 문 동등 쌍으로 기록됩니다. (원래 문에서 SELECT 의 전에 IGNORE 또는 REPLACE 가 지정된 경우 INSERT 가 각각 INSERT IGNORE 또는 REPLACE 됩니다.)

이러한 변경으로 인해 대상 테이블이 존재하는 경우는 마스터와 슬레이브에서 행이 삽입되기 때문에 MySQL 5.1에서 5.5로 문 기반 복제의 호환성이 제공됩니다. 이 호환성 대책을 이용하려면 5.1 서버는 적어도 5.1.51이어야하며 5.5 서버는 적어도 5.5.6이어야합니다.

기존의 5.1에서 5.5로 복제 시나리오를 업그레이드하려면 먼저 마스터를 5.1.51 이상으로 업그레이드합니다. 이것은 먼저 슬레이브를 업그레이드하는 일반적인 복제 업그레이드 조언과 다릅니다.

원래 효과 (대상 테이블이 존재하는지 여부에 관계없이 행이 삽입되는)을 실현하려는 응용 프로그램에는 CREATE TABLE IF NOT EXISTS ... SELECT 문이 아니라 CREATE TABLE IF NOT EXISTS 및 INSERT ... SELECT 문을 사용하는 해결 방법이 있습니다.

지금 설명하는 변경과 함께 다음의 관련 변경되었습니다. 이전에는 CREATE TABLE IF NOT EXISTS ... SELECT 의 대상 테이블로 기존의 뷰가 지정된 경우 기본베이스 테이블에 행이 삽입 된이 문이 바이너리 로그에 기록되었습니다. MySQL 5.1.51 및 5.5.6의 시점에서는 아무것도 삽입되거나 기록되거나하지 않습니다.

바이너리 로그를 사용하여 원래의 테이블을 확실히 재 작성할 수 있도록하기 위해, MySQL은 CREATE TABLE ... SELECT 중인 병렬 삽입이 허용되지 않습니다.

중요

CREATE TABLE new_table SELECT ... FROM old_table ... 등의 문에서 SELECT 의 일부로 FOR UPDATE 를 사용할 수 없습니다. 그것을하려고하면이 문은 실패합니다. 이것은 CREATE TABLE ... SELECT 문이 작성되는 테이블 이외의 테이블로 변경할 것을 허용했다, MySQL 5.5 및 그 이전부터 동작 변경을 나타냅니다.

이 변화는 또한 이전 마스터에서 MySQL 5.6 이상 슬레이브에 문 기반 복제에 영향을 미칠 수 있습니다. 자세한 내용은 섹션 17.4.1.5 "CREATE TABLE ... SELECT 문 복제" 를 참조하십시오.