MySQL에서 문자열(String)을 정수(Int)로 변환하는 방법

MySQL에서 데이터를 다루다 보면 문자열(string) 데이터를 정수(int)로 변환해야 할 경우가 자주 발생합니다. 특히 웹 애플리케이션, 사용자 입력 처리, 데이터 마이그레이션, 통계 연산 등 다양한 상황에서 이러한 변환은 필수적입니다. 이 글에서는 MySQL 문자열을 정수로 변환하는 방법을 다양한 각도에서 깊이 있게 분석하고, 구문, 예시, 성능 이슈까지 모두 다룹니다.

CAST와 CONVERT를 활용한 문자열(String) → 정수(Int) 변환 방법

MySQL에서는 문자열을 정수로 변환할 때 가장 일반적으로 CAST() 함수 또는 CONVERT() 함수를 사용합니다. 두 함수 모두 ANSI SQL 표준을 따르며, 타입 간 변환을 지원합니다.

CAST() 함수 사용법

SELECT CAST('1234' AS UNSIGNED);
SELECT CAST('5678' AS SIGNED);

• UNSIGNED는 음수를 허용하지 않는 정수형
• SIGNED는 음수를 포함할 수 있는 정수형

CONVERT() 함수 사용법

SELECT CONVERT('9876', UNSIGNED);
SELECT CONVERT('-1234', SIGNED);

두 함수는 기능적으로 동일하나, 일부 프로젝트에서는 스타일 가이드에 따라 하나를 고정적으로 사용하기도 합니다.

문자열에서 숫자만 남기는 변환 주의점

MySQL은 문자열의 앞부분에서 가능한 숫자까지만 읽고 이후 문자는 무시합니다.

SELECT CAST('123abc' AS UNSIGNED); -- 결과: 123
SELECT CAST('abc123' AS UNSIGNED); -- 결과: 0

숫자가 문자열의 앞에 있어야 변환이 제대로 됩니다. 앞에 숫자가 없다면 결과는 0입니다.

반응형

숫자 이외 문자가 포함된 문자열 처리 방법

데이터 정제 과정에서 문자열에 숫자 이외의 문자가 포함된 경우가 많습니다. MySQL에는 정규표현식 기반의 직접적인 필터링 기능이 부족하므로 SUBSTRING_INDEX() 또는 REGEXP_REPLACE()(MySQL 8.0+) 등을 조합해 사용할 수 있습니다.

REGEXP_REPLACE()로 숫자만 추출하기 (MySQL 8.0 이상)

SELECT CAST(REGEXP_REPLACE('전화번호: 01012345678', '[^0-9]', '') AS UNSIGNED);

• [ ^0-9 ]: 숫자가 아닌 모든 문자 제거
• REGEXP_REPLACE: 정규식 기반 치환 (MySQL 8.0부터 사용 가능)

SUBSTRING과 LOCATE로 수작업 처리하기 (MySQL 5.7 이하)

SELECT CAST(SUBSTRING(col, 1, LOCATE('a', col) - 1) AS UNSIGNED) FROM 테이블;

정규식을 사용할 수 없다면 문자열 위치 기반 함수로 최대한 숫자 부분만 잘라내는 로직을 구성해야 합니다.

반응형

정수형 변환 시 발생하는 예외 및 오류 처리 전략

MySQL은 문자열을 숫자로 변환할 때 오류를 던지지 않고 기본적으로 조용히 실패합니다. 이는 때때로 예기치 못한 동작으로 이어질 수 있습니다.

예시: 암시적 형 변환의 위험성

SELECT 'abc123' + 0; -- 결과: 0

MySQL에서는 문자열을 수학 연산에 넣으면 자동으로 숫자로 변환하려 시도하며, 숫자가 앞에 없으면 0으로 처리됩니다.

STR_TO_DATE와 같은 함수 사용 주의

날짜 문자열도 잘못된 형식이 들어오면 NULL을 반환하거나 오류가 발생할 수 있습니다.

주의사항:

• STRICT_TRANS_TABLES 모드 활성화 시, 잘못된 변환은 오류로 처리됨
• 로그 확인 필수 (SHOW WARNINGS;)

반응형

대량 데이터 처리 시 문자열 to 정수 변환 성능 고려사항

문자열을 정수로 변환하는 연산은 CPU 계산이 추가되므로 대량 쿼리 시 병목이 될 수 있습니다.

인덱스 비활성화 주의

변환 연산은 인덱스를 무효화하므로 다음과 같은 쿼리는 매우 비효율적입니다.

SELECT * FROM users WHERE CAST(phone AS UNSIGNED) = 01012345678;

이 경우 phone 필드가 문자열이라면, 변환을 수행해야 하기 때문에 인덱스 스캔이 아닌 풀 테이블 스캔이 발생합니다.

사전 정제된 컬럼 생성 전략

정규화 전략으로 숫자 컬럼을 따로 만들어 저장하는 것이 좋습니다.

ALTER TABLE users ADD phone_number BIGINT;
UPDATE users SET phone_number = CAST(REGEXP_REPLACE(phone, '[^0-9]', '') AS UNSIGNED);

이후 쿼리에서는 이 정수형 필드를 기준으로 검색하면 성능 저하 없이 운영 가능

반응형

활용 사례

MySQL에서 문자열을 정수로 변환하는 실전 활용 예시를 살펴보면 다음과 같습니다.

1. 사용자 입력 값 정수 처리

웹 폼 등에서 사용자 입력은 대부분 문자열입니다. 사용자의 나이, 전화번호, 우편번호 등은 정수로 저장해야 효율적입니다.

INSERT INTO users (age) VALUES (CAST('29' AS UNSIGNED));

2. JSON/CSV API 데이터 정제

API로 수신된 JSON이나 CSV에는 수치 데이터가 문자열로 들어올 가능성이 높습니다.

-- API로 수신된 문자열을 정수로 저장
UPDATE stocks SET volume = CAST(JSON_UNQUOTE(JSON_EXTRACT(api_data, '$.volume')) AS UNSIGNED);

3. 보고서 및 통계 생성

통계 연산을 위해 반드시 정수 변환이 필요합니다.

SELECT AVG(CAST(price_string AS UNSIGNED)) FROM sales_data;

반응형

결론

 - 변환 함수 선택

• CAST() vs CONVERT() 둘 다 가능, 일관되게 사용
• SIGNED vs UNSIGNED 명확히 구분

 - 정제 필요 여부 판단

• 숫자가 앞에 없거나 문자가 섞여 있다면 REGEXP_REPLACE() 우선

 - 대량 처리 시 최적화

• 별도 정수형 컬럼 생성 및 인덱스 활용
• 가능하면 저장 시 미리 정수화

 - MySQL 8.0 이상 기능 적극 활용

• REGEXP_REPLACE(), JSON 함수, 가상 컬럼 등