MySQL VARCHAR를 ENUM으로 변경

1 개요

MySQL VARCHAR를 ENUM으로 변경

• 대상: devices 테이블의 power_state 컬럼
• 자료형이 varchar(32)인 것을 ENUM으로 변경

2 사전 확인

SELECT power_state, LENGTH(power_state), COUNT(*)
FROM devices
GROUP BY power_state

power_state	LENGTH(power_state)	COUNT(*)
halted	6	270
running	7	938

→ power_state 컬럼의 값은 halted와 running 2종류임

→ ENUM으로 변경시 대략 8186바이트 감소할 것으로 예상됨

→ [math]\displaystyle{ 6바이트 \times 270건 + 7바이트 \times 938건 = 1620+6566=8186 }[/math]

SELECT table_name, TABLE_ROWS, INDEX_LENGTH, DATA_LENGTH, (INDEX_LENGTH+DATA_LENGTH) size
FROM information_schema.TABLES
WHERE table_shema="mydb"
AND table_name="devices"

table_name	TABLE_ROWS	INDEX_LENGTH	DATA_LENGTH	size
devices	1208	74752	377252	452004

3 변경

ALTER TABLE `devices`
ADD `power_state2` ENUM('halted','running')
CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
AFTER `power_state`

# Query OK, 1208 rows affected (0.19 sec)
# Records: 1208  Duplicates: 0  Warnings: 0

UPDATE devices SET power_state2=power_state

# Query OK, 938 rows affected (0.06 sec)
# Records: 1208  Duplicates: 938  Warnings: 0

SELECT * FROM devices WHERE power_state2<>power_state

# Empty set (0.00 sec)

ALTER TABLE devices DROP power_state

# Query OK, 1208 rows affected (0.08 sec)
# Records: 1208  Duplicates: 0  Warnings: 0

4 사후 확인

SELECT power_state, LENGTH(power_state), COUNT(*)
FROM devices
GROUP BY power_state

power_state	LENGTH(power_state)	COUNT(*)
halted	6	270
running	7	938

→ 변동 없음 (정상)

SELECT table_name, TABLE_ROWS, INDEX_LENGTH, DATA_LENGTH, (INDEX_LENGTH+DATA_LENGTH) size
FROM information_schema.TABLES
WHERE table_shema="mydb"
AND table_name="devices"

table_name	TABLE_ROWS	INDEX_LENGTH	DATA_LENGTH	size
devices	1208	74752	368948	443700

→ INDEX_LENGTH(74752바이트)는 변동 없음 (정상)

→ DATA_LENGTH는 377252 → 368948바이트로 8304바이트 감소

→ (전체크기도 452004 → 443700바이트로 8304바이트 감소)

5 같이 보기

• VARCHAR
• ENUM