alter table algorithm=inplace 컬럼사이즈 변경

alter table algorithm=inplace 로 컬럼사이즈 변경할 때 주의사항 

메뉴얼에는 256 바이트 미만에서는 된다고 나와 있다.

dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html

 

문제는 256바이트 미만이다. 

 

utf8mb4 의 경우 4바이트.. 

256자가 아니라 256 바이트다.

utf8mb4면 4 * 64 = 256, varchar(63)까지 된다.

 

테스트 

테스트용으로 우편번호 테이블을 만들어 보자 

CREATE DATABASE zipcode;
use zipcode;

CREATE TABLE zipcode ( 
 zipcode VARCHAR(5) NULL,
 sido VARCHAR(25) NULL,
 sido_en VARCHAR(20) NULL,
 sigungu VARCHAR(30) NULL,
 sigungu_en VARCHAR(30) NULL,
 eupmyun VARCHAR(20) NULL,
 eupmyun_en VARCHAR(25) NULL,
 doro_code VARCHAR(12) NULL,
 doro VARCHAR(40) NULL,
 doro_en VARCHAR(50) NULL,
 under_yn VARCHAR(1) NULL,
 buildno1 VARCHAR(5) NULL,
 buildno2 VARCHAR(4) NULL,
 buildnum VARCHAR(25) NULL,
 multiple VARCHAR(1) NULL,
 buildname VARCHAR(70) NULL,
 dong_code VARCHAR(10) NULL,
 dong VARCHAR(20) NULL,
 ri VARCHAR(20) NULL,
 dong_hj VARCHAR(30) NULL,
 mount_yn VARCHAR(1) NULL,
 jibun1 VARCHAR(4) NULL,
 eupmyundong_no VARCHAR(2) NULL,
 jibun2 VARCHAR(4) NULL,
 zipcode_old VARCHAR(7) NULL,
 zipcode_seq VARCHAR(3) NULL,
 seq BIGINT NOT NULL AUTO_INCREMENT, 
 PRIMARY KEY(seq)
) NGINE=InnoDB DEFAULT CHARSET=utf8mb4;

LOAD DATA LOCAL INFILE '/root/zipcode_DB/강원도.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/경기도.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/경상남도.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/경상북도.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/광주광역시.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/대구광역시.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/대전광역시.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/부산광역시.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/서울특별시.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/세종특별자치시.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/울산광역시.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/인천광역시.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/전라남도.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/전라북도.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/제주특별자치도.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/충청남도.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/root/zipcode_DB/충청북도.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '|' IGNORE 1 LINES;

sigungu 를 algorithm=inplace 로 varchar(30) -> varchar(63) 로 바꾸어보자!

mysql> alter table zipcode algorithm=inplace, change sigungu sigungu varchar(63);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

다시 sigungu 를 algorithm=inplace 로 varchar(63) -> varchar(64) 로 바꾸어보자!

mysql> alter table zipcode algorithm=inplace, change sigungu sigungu varchar(64);
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.