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

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 확인 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

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	443700