MySQL DDL 쿼리 정리 / 테이블 생성 및 수정 방법

MySQL DDL 쿼리

테이블 생성

CREATE TABLE `테이블명` (
  `컬럼명1` 컬럼타입 NOT NULL COMMENT '컬럼1 설명',
  `컬럼명2` 컬럼타입 NOT NULL COMMENT '컬럼2 설명',
  `컬럼명3` 컬럼타입 NOT NULL COMMENT '컬럼3 설명',
  PRIMARY KEY (`컬럼명1`,`컬럼명2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='테이블 설명';

CHARSET=utf8mb4 설정 시 COLLATE을 명시하지 않으면 기본 Collation은 utf8mb4_general_ci으로 적용됩니다.

DB 기본 Collation 조회

SHOW VARIABLES LIKE 'collation_database';

데이터베이스 Collation이 지정되어 있어도, CREATE문에서 CHARSET 또는 COLLATE를 명시하면 변경됩니다.

테이블 Collation 변경

ALTER TABLE 테이블명 COLLATE=utf8mb4_unicode_ci;

테이블 문자셋 비교 및 규칙을 utf8mb4_unicode_ci으로 변경합니다.
쿼리에서 테이블 비교 및 조인 시 서로 Collation이 다르면 에러가 발생할 수 있습니다.

테이블 복제

CREATE TABLE 신규테이블명 AS SELECT * FROM 테이블명;

기존 테이블의 데이터를 포함하여 테이블 구조를 그대로 복제하는 명령어입니다.
Not Null, 디폴트, Comment도 복제되지만 PK 등 제약조건, PK 인덱스, 인덱스는 복제되지 않습니다.

데이터 없이 테이블 구조만 복제

CREATE TABLE 신규테이블명 AS SELECT * FROM 테이블명 WHERE 1=0;

테이블 삭제

DROP TABLE 테이블명;

테이블 컬럼 추가

ALTER TABLE 테이블명 ADD COLUMN 컬럼명 데이터타입(사이즈) COMMENT '컬럼 설명';

특정 컬럼 뒤 컬럼 추가

ALTER TABLE 테이블명 ADD COLUMN 컬럼명 데이터타입(사이즈) COMMENT '컬럼 설명' AFTER 특정컬럼명;

MySQL에서는 특정 컬럼 앞에 컬럼을 추가하는 BEFORE 키워드가 지원되지 않습니다.

테이블 컬럼 삭제

ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

테이블 기본키 추가 (PK 추가)

ALTER TABLE 테이블명 ADD PRIMARY KEY (`컬럼명1`, `컬럼명2`);

테이블에 이미 존재하는 컬럼이어야 기본키로 설정할 수 있습니다.
PK 지정 시, PK 인덱스가 자동으로 추가됩니다.

작은따옴표 사용하여 PK 추가 시 에러

SQL Error [1064] [42000]: (conn=3941) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''컬럼명')' at line 1

컬럼명을 `가 아니라 ‘로 감싸면 위와 같은 에러가 납니다.

PK 추가 시 제약조건 위반 에러

SQL Error [1062] [23000]: (conn=3941) Duplicate entry '1' for key 'PRIMARY'

각 행마다 PK 데이터 중복이 없도록 컬럼을 조합해서 한 쿼리로 함께 지정하면 됩니다.
PK 인덱스 (클러스터드 인덱스) 는 테이블 기본 데이터 구조를 변경하기 때문에 1개만 존재 가능합니다.

테이블 기본키 삭제 (PK 삭제)

ALTER TABLE 테이블명 DROP PRIMARY KEY;

테이블 외래키 추가 (FK 추가)

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 FOREIGN KEY(컬럼명) REFERENCES 참조테이블명(참조컬럼명) ON DELETE CASCADE ON UPDATE CASCADE;

ON DELETE CASCADE 옵션은 참조테이블의 참조컬럼 값이 삭제되면, 참조하던 ROW들을 자동 삭제해 줍니다.

외래키 추가 시 에러메세지

Cannot add or update a child row: a foreign key constraint fails

외래키로 지정한 컬럼의 값으로 참조테이블 컬럼에 없는 값이 저장되어 있으면 외래키를 추가할 수 없습니다.
전체 INSERT문 추출하고 외래키 지정 후 제약조건에 맞는 INSERT문들만 실행하면 됩니다.

테이블 외래키 삭제 (FK 삭제)

ALTER TABLE 테이블명 DROP FOREIGN KEY 제약조건명;