1. 소개
MySQL로 데이터베이스를 운영하다 보면 “숫자로 취급해야 할 데이터가 문자열 타입으로 저장돼 있다”거나 “데이터가 문자열 상태인 동안에는 계산 및 집계가 수행되지 않는다”와 같은 상황을 자주 마주하게 됩니다. 예를 들어 Excel이나 CSV 파일에서 가져온 데이터가 실제로는 숫자값임에도 불구하고 VARCHAR나 CHAR로 저장되는 경우가 많습니다. 이런 경우 합계, 평균, 숫자 비교와 같은 연산이 기대대로 동작하지 않아 올바른 SQL 쿼리를 작성하기가 어려워집니다.
반대로 숫자 데이터를 문자열로 다루어야 하는 경우도 있습니다. 예를 들어 ID나 코드를 0‑패딩해야 할 때, 혹은 숫자 데이터를 다른 컬럼과 결합해 표시해야 할 때가 그렇습니다. 이런 경우 숫자 타입을 문자열 타입으로 변환하는 것이 필요합니다.
이처럼 “문자열과 숫자 간의 타입 변환” 은 MySQL에서 데이터를 유연하게 다루기 위해 반드시 갖추어야 할 기본 기술 중 하나입니다. 타입 변환을 적절히 활용하면 데이터 무결성을 유지하면서도 유연한 집계와 처리를 구현할 수 있습니다.
본 문서에서는 MySQL에서 문자열과 숫자 간을 변환하는 방법, 이를 효과적으로 활용하는 방법, 실제 현장에서 겪은 흔한 함정과 모범 사례를 명확히 설명합니다. 초보자이든 숙련된 SQL 개발자이든 이 가이드는 실용적인 가치를 제공할 것입니다.
2. MySQL에서의 타입 변환 종류
MySQL은 데이터 타입을 변환하기 위한 여러 편리한 기능을 제공합니다. 이 섹션에서는 두 가지 주요 패턴인 “명시적 타입 변환”과 “암시적 타입 변환”을 대표적인 방법과 함께 설명합니다.
2.1 명시적 타입 변환
명시적 타입 변환은 SQL에서 “이 값은 특정 타입으로 변환되어야 한다”는 것을 명확히 지정하는 것을 의미합니다. 다음 두 함수가 일반적으로 사용됩니다.
CAST Function
CAST()는 값을 지정된 타입으로 변환하는 표준 SQL 함수이며 MySQL에서 널리 사용됩니다.
SELECT CAST('123' AS SIGNED);
이 예시에서는 문자열 '123'이 부호가 있는 정수(SIGNED)로 변환됩니다. 변환 가능한 다른 타입으로는 UNSIGNED(부호 없는 정수), DECIMAL(고정 소수점 숫자), CHAR(문자열), DATE(날짜) 등이 있습니다.
CONVERT Function
CONVERT()는 값을 다른 데이터 타입으로 변환할 때 사용하는 또 다른 함수이며 구문은 다음과 같습니다.
SELECT CONVERT('456', UNSIGNED);
이 예시에서는 문자열 '456'이 부호가 없는 정수(UNSIGNED)로 변환됩니다. CAST()와의 주요 차이점은 CONVERT()가 문자 집합 변환에도 사용할 수 있다는 점입니다.
2.2 암시적 타입 변환
암시적 타입 변환은 MySQL이 연산이나 비교를 수행할 때 자동으로 데이터 타입을 변환하는 메커니즘입니다.
예를 들어 숫자와 문자열을 더할 경우, MySQL은 문자열을 자동으로 숫자값으로 변환합니다.
SELECT 1 + '2';
-- Result: 3
마찬가지로 숫자 값을 문자열로 연결할 때도 마찬가지입니다:
SELECT CONCAT(10, ' apples');
-- Result: '10 apples'
암시적 타입 변환은 편리하지만 의도치 않은 결과를 초래할 수 있습니다. 따라서 복잡한 로직이나 중요한 프로세스에서는 명시적 타입 변환을 사용하는 것이 강력히 권장됩니다.
3. 실용 예제: 문자열을 숫자로 변환하기
MySQL에서 숫자값이 CHAR나 VARCHAR와 같은 문자열로 저장된 경우, 그대로는 계산이나 숫자 비교를 신뢰할 수 없습니다. 이러한 데이터를 올바르게 집계·분석하려면 문자열을 숫자 타입으로 변환해야 합니다. 이 섹션에서는 흔히 사용되는 변환 방법과 주요 주의사항을 소개합니다.
3.1 CAST 함수로 변환하기
가장 기본적인 방법은 CAST() 함수를 사용하는 것입니다. 예를 들어 문자열 '100'을 정수로 변환하려면 다음과 같이 작성합니다:
SELECT CAST('100' AS SIGNED) AS numeric_result;
-- Result: 100 (integer)
Use SIGNED를 사용하여 부호가 있는 정수를, UNSIGNED를 사용하여 부호가 없는 정수를 지정합니다. 소수 데이터의 경우 DECIMAL 또는 FLOAT를 사용할 수도 있습니다.
SELECT CAST('123.45' AS DECIMAL(10,2)) AS decimal_result;
-- Result: 123.45
3.2 CONVERT 함수 사용하기
CONVERT() 함수는 거의 동일한 방식으로 사용할 수 있습니다:
SELECT CONVERT('200', SIGNED) AS converted_result;
-- Result: 200
두 방법 모두 동일한 결과를 반환하지만, CAST()는 표준 SQL이며 이식성이 더 뛰어나므로, 확신이 서지 않을 때는 일반적으로 CAST()를 권장합니다.
3.3 산술 연산을 통한 암시적 변환
SQL 식에서 숫자형과 문자열형을 함께 산술 연산하면, MySQL은 문자열을 자동으로 숫자로 변환합니다. 예를 들어:
SELECT '50' + 25 AS total;
-- Result: 75
이 동작은 집계 함수에도 적용할 수 있습니다. 예를 들어, 문자열형 컬럼을 SUM()에 전달하면 MySQL이 자동으로 숫자 변환을 시도하고 값을 합산합니다:
SELECT SUM(amount) FROM sales_data;
-- Even if the amount column is VARCHAR, MySQL will attempt numeric summation
3.4 앞에 0이 채워진 문자열 및 숫자가 아닌 값에 주의
앞에 0이 채워진 문자열(예: '000100')도 숫자로 변환될 수 있습니다:
SELECT CAST('000100' AS SIGNED) AS converted_result;
-- Result: 100
하지만 문자열에 숫자가 아닌 문자가 포함되어 있으면 주의가 필요합니다. CAST('abc123' AS SIGNED)와 같이 변환할 경우, 시작 부분에 숫자가 없으면 MySQL은 0을 반환합니다. 데이터 품질에 따라 변환 전에 입력 검증을 수행하는 것이 중요합니다.
3.5 일반적인 실제 사용 사례
- Excel/CSV 가져오기 때문에 문자열이 된 매출 또는 금액 데이터를 집계하기
- 숫자를 나타내지만 문자열로 저장된 ID를 숫자 순서대로 정렬하기
YYYYMMDD형식으로 저장된 날짜 문자열을 날짜 순서대로 정렬하기 (아래에서 설명)
4. 실용 예제: 숫자를 문자열로 변환하기
MySQL에서는 숫자 데이터를 문자열처럼 다루고 싶을 때가 많이 있습니다. 일반적인 예로는 앞에 0을 채워 ID나 코드를 표시하거나, 숫자 값을 다른 텍스트와 연결하여 메시지를 만드는 경우가 있습니다. 이 섹션에서는 대표적인 방법과 실용적인 사용 사례를 소개합니다.
4.1 CAST 함수 사용하기
숫자형(예: INT 또는 DECIMAL)을 문자열형으로 명시적으로 변환하려면 CAST()를 사용합니다:
SELECT CAST(123 AS CHAR) AS string_result;
-- Result: '123'
이 방법을 사용하면 숫자 컬럼을 다른 문자열과 쉽게 연결할 수 있습니다.
4.2 CONVERT 함수 사용하기
CONVERT()를 사용해서도 동일하게 할 수 있습니다:
SELECT CONVERT(456, CHAR) AS converted_result;
-- Result: '456'
CAST()와 큰 차이는 없지만, 표준 SQL 관점에서는 CAST()가 약간 더 선호됩니다.
4.3 연결을 통한 암시적 변환
CONCAT()와 같은 함수를 사용해 숫자와 문자열을 연결하면 MySQL이 자동으로 숫자를 문자열로 변환합니다.
SELECT CONCAT(2024, ' year') AS fiscal_year_display;
-- Result: '2024 year'
이러한 암시적 변환은 일상적인 보고서 출력 및 데이터 포맷팅에 흔히 사용됩니다.
4.4 일반적인 실제 사용 사례
- 앞에 0을 채운 ID 생성 숫자 ID를 5자리로 표시하려면
LPAD()와 결합합니다:SELECT LPAD(CAST(id AS CHAR), 5, '0') AS zero_padded_id FROM users; -- id=7인 경우 결과는 '00007' - 날짜나 금액을 문자열로 연결
SELECT CONCAT('The total amount is ', CAST(total AS CHAR), ' yen.') AS message FROM orders; -- total=1500인 경우 결과는 'The total amount is 1500 yen.'
4.5 참고 사항
변환된 문자열이 시각적으로 동일해 보여도, “정렬” 및 “비교 연산” 동작은 달라집니다. 예를 들어 문자열로 정렬하면 '20'이 '100'보다 먼저 나올 수 있습니다(사전식 순서). 목적에 따라 적절한 방식을 선택하는 것이 중요합니다.
5. 고급 사용 사례와 타입 변환
타입 변환은 단순한 숫자나 문자열 변환에 국한되지 않습니다. 실제 시스템의 다양한 실용적인 시나리오에도 적용될 수 있습니다. 이 섹션에서는 일반적으로 사용되는 고급 사례와 주요 고려 사항을 소개합니다.
5.1 날짜와 유사한 문자열 비교 및 변환
데이터베이스에 YYYYMMDD와 같은 형식으로 날짜를 숫자형 또는 문자열형으로 저장하고 있다면, 단순 문자열 비교만으로는 기대한 대로 동작하지 않을 수 있습니다.
이러한 경우 CAST()를 사용해 값을 숫자형으로 변환하면 올바른 연대순 정렬 및 비교가 가능합니다.
SELECT *
FROM events
ORDER BY CAST(event_date AS UNSIGNED);
-- Values such as '20240501', '20240502', etc. are sorted in date order
또한 REPLACE() 함수를 함께 사용하면 '2024-05-01'과 같이 하이픈으로 구분된 날짜를 정수형으로 변환할 수 있습니다.
SELECT CAST(REPLACE('2024-05-01', '-', '') AS UNSIGNED);
-- Result: 20240501
5.2 ENUM 타입 또는 코드 값을 숫자형으로 정렬
숫자 의미를 갖는 ENUM 타입이나 코드 값은 사전식 순서가 아니라 숫자 순서대로 정렬하고 싶을 수 있습니다. 정렬하기 전에 CAST()로 숫자형으로 변환하면 직관적인 순서를 얻을 수 있습니다.
SELECT *
FROM products
ORDER BY CAST(product_code AS UNSIGNED);
5.3 집계 및 데이터 포맷팅에서의 활용
예를 들어 매출 금액이 VARCHAR로 저장되어 있다면, SUM() 안에서 명시적으로 변환하여 정확한 합계를 구할 수 있습니다.
SELECT SUM(CAST(sales_amount AS SIGNED)) AS total_sales
FROM sales_data;
5.4 정밀도와 타입 선택에 따른 문제 방지
소수점이 있거나 큰 숫자값의 경우 DECIMAL 타입으로 변환하면 정밀도를 유지할 수 있습니다.
데이터에 음수값이 존재할 가능성이 있는지에 따라 SIGNED와 UNSIGNED 중 적절한 타입을 선택하는 것도 중요합니다.
SELECT CAST('1234.567' AS DECIMAL(10, 3));
-- Result: 1234.567
5.5 오류 또는 잘못된 데이터 감지
변환 결과가 예상치 못한 NULL이나 0값이 나오면 이는 잘못된 데이터일 수 있습니다.
예를 들어 문자열을 숫자로 변환할 수 없을 경우, SQL 모드에 따라 CAST()가 0 또는 NULL을 반환할 수 있습니다.
이러한 경우 변환 결과를 확인함으로써 데이터 정제나 검증이 필요한지를 파악할 수 있습니다.
SELECT original, CAST(original AS SIGNED) AS converted
FROM test_data
WHERE CAST(original AS SIGNED) = 0 AND original <> '0';
-- Extract only data that cannot be properly converted to numeric values
6. CAST와 CONVERT의 차이점
MySQL에서 타입 변환을 수행할 때는 일반적으로 CAST 함수 또는 CONVERT 함수를 사용합니다. 겉보기에는 비슷해 보이지만 사용 방식과 특성에 차이가 있습니다. 이 섹션에서는 그 차이점을 설명하고 어떤 상황에서 어느 함수를 선택해야 하는지 안내합니다.
6.1 기본 차이점
- CAST Function
CAST(value AS type)구문을 사용해 값을 지정한 타입으로 명시적으로 변환합니다. 표준 SQL에 정의되어 있어 여러 데이터베이스 시스템에서 유사하게 동작합니다.SELECT CAST('123' AS SIGNED); - CONVERT Function
CONVERT(value, type)구문을 사용해 값을 지정한 타입으로 변환합니다. MySQL에서는 문자 집합 변환에도 사용할 수 있습니다.SELECT CONVERT('123', SIGNED); -- 타입 변환 SELECT CONVERT('hello' USING utf8mb4); -- 문자 집합 변환
6.2 표준 SQL과의 호환성
CAST는 국제 SQL 표준의 일부이므로 PostgreSQL, SQL Server, Oracle 등 다른 데이터베이스로의 이식성이 높습니다. 반면 CONVERT는 MySQL 고유의 확장 기능을 포함하고 있으며, 특히 문자 집합 변환을 위해 CONVERT(expr USING charset_name)와 같은 구문을 제공합니다.
6.3 언제 어떤 것을 선택할까?
- 형 변환 (숫자, 문자열, 날짜 등) 대부분의 경우
CAST를 사용하는 것이 가장 안전합니다. 표준 SQL이며, 이식성이 높고 향후 데이터베이스 마이그레이션에도 적합합니다. - 문자 집합 변환 (예: sjis to utf8mb4)
CONVERT(expr USING charset_name)를 사용해야 합니다. - MySQL 전용 또는 특수한 경우
CONVERT는 추가적인 유연성을 제공할 수 있지만, 일반적인 목적에서는CAST부터 시작하고 필요할 때만CONVERT를 사용하십시오.
6.4 샘플 비교
-- Type conversion using CAST (convert to integer)
SELECT CAST('456' AS SIGNED);
-- Type conversion using CONVERT
SELECT CONVERT('456', SIGNED);
-- Character set conversion using CONVERT
SELECT CONVERT('Hello' USING utf8mb4);
6.5 중요한 참고 사항
- 변환이 실패하면 두 함수 모두
NULL또는0을 반환할 수 있습니다. - 문자 집합 변환은
CAST로 수행할 수 없습니다. - 동작은 SQL 모드나 MySQL 버전에 따라 다를 수 있으므로, 개발 및 운영 환경 모두에서 항상 테스트하십시오.
7. 중요한 참고 사항 및 모범 사례
MySQL에서 문자열 및 숫자 형 변환을 사용할 때 주의해야 할 잠재적인 함정이 여러 가지 있습니다. 이 섹션에서는 문제를 예방하고 안전하고 정확한 사용을 보장하기 위한 모범 사례를 소개합니다.
7.1 잘못된 변환으로 인한 오류 또는 예상치 못한 NULL/0
형 변환을 수행할 때 원본 값이 올바른 형식이 아니면 MySQL이 예상치 못한 NULL 또는 0 값을 반환할 수 있습니다.
SELECT CAST('abc' AS SIGNED) AS result;
-- Result: 0 (default MySQL behavior)
위와 같이 숫자가 아닌 문자열을 숫자형으로 변환하면 0이 됩니다. 따라서 변환하기 전에 원본 데이터에 잘못된 값이 포함되어 있지 않은지 항상 확인하십시오.
SQL 모드 설정에 따라 변환이 오류나 NULL을 발생시킬 수도 있습니다. 항상 운영 환경의 설정을 확인하십시오.
7.2 정밀도 및 부호/무부호 유형 선택
- 소수점을 포함하는 값은
DECIMAL또는FLOAT로 변환하십시오. - 음수 값이 가능하면
SIGNED를 사용하고, 값이 양의 정수만이라면UNSIGNED를 사용하십시오.
항상 데이터 특성에 맞는 적절한 유형을 선택하십시오.
7.3 인덱스에 미치는 영향
WHERE 또는 ORDER BY 절에서 CAST나 CONVERT와 같은 형 변환 함수를 사용하면 인덱스가 사용되지 않아 성능이 저하될 수 있습니다.
SELECT * FROM users WHERE CAST(user_id AS SIGNED) = 1000;
-- Even if user_id has an index, it is often not used
대용량 데이터셋이나 성능이 중요한 쿼리의 경우, 불필요한 변환을 피하기 위해 스키마 설계 단계에서 컬럼 유형을 통일하는 것이 가장 좋습니다.
7.4 암시적 변환에 과도하게 의존하지 말 것
MySQL의 암시적 형 변환은 편리하지만 의도하지 않은 동작을 초래할 수 있습니다. 중요한 로직에서는 항상 명시적인 CAST 또는 CONVERT를 사용하십시오.
SELECT '100a' + 20;
-- Result: 100 (only the leading numeric portion is used)
미묘한 버그와 데이터 불일치를 방지하려면 명시적 변환을 습관화하십시오.
7.5 데이터 입력 단계에서 유형 검증
데이터베이스를 설계할 때는 숫자 데이터는 숫자형으로, 문자열 데이터는 문자열형으로 저장하십시오. 적절한 스키마 설계를 통해 변환 필요성을 최소화하는 것이 문제를 예방하는 가장 효과적인 방법 중 하나입니다.
8. 요약
MySQL에서 문자열과 숫자 간의 형 변환은 일상적인 데이터 처리, 집계 및 시스템 운영에서 피할 수 없는 주제입니다. 이 글에서는 기본 사용법부터 고급 기법 및 중요한 주의 사항까지 모두 다루었습니다.
문자열을 숫자로 변환할 때는 CAST와 CONVERT와 같은 명시적인 방법을 사용할 수 있습니다. 실무에서는 암시적 변환이 자주 사용되지만, 명시적 변환을 우선시하면 신뢰성과 데이터 품질이 향상됩니다. 반대로 숫자를 문자열로 변환하는 경우도 ID, 코드 포맷팅 및 출력 메시지 생성 등에 널리 활용됩니다.
또한, 날짜 관련 응용, 집계 및 정렬, 오류 감지와 같은 기술은 실제 시나리오에서 타입 변환이 얼마나 강력한지 보여줍니다. 그러나 변환에는 잠재적인 함정과 성능 영향을 초래할 수도 있습니다. 위에서 논의한 모범 사례를 항상 따르세요.
타입 변환을 제대로 마스터하면 MySQL에서 데이터를 효과적으로 조작하는 능력을 크게 확장할 수 있습니다.
이 글이 여러분의 일상 업무와 학습에 도움이 되길 바랍니다.
9. FAQ (자주 묻는 질문)
Q1. 문자열 ‘abc’를 숫자로 CAST 하면 어떻게 되나요?
MySQL에서 문자열을 숫자형(SIGNED 또는 UNSIGNED 등)으로 변환하려고 할 때, 앞부분에 숫자가 있으면 해당 숫자 부분을 반환하고, 없으면 0을 반환합니다.
SELECT CAST('abc' AS SIGNED); -- Result: 0
SELECT CAST('123abc' AS SIGNED); -- Result: 123
하지만 SQL 모드(예: STRICT_TRANS_TABLES)에 따라 결과가 오류이거나 NULL이 될 수 있습니다.
Q2. CAST와 CONVERT 중 어느 것을 사용해야 할까요?
일반적인 타입 변환(숫자, 문자열, 날짜 등)에는 표준 SQL을 따르기 때문에 CAST 사용을 권장합니다. 문자 집합 변환의 경우 USING 절과 함께 CONVERT 함수를 사용해야 합니다. 사용 사례에 따라 선택하세요.
Q3. 암시적 타입 변환으로 충분한가요?
암시적 변환은 작은 쿼리나 테스트에서는 동작할 수 있지만, 중요한 집계 및 프로덕션 시스템 개발에서는 명시적 변환(CAST 또는 CONVERT)을 강력히 권장합니다. 명시적 변환을 작성하면 의도치 않은 동작과 버그를 방지할 수 있습니다.
Q4. WHERE나 ORDER BY에서 타입 변환을 사용하면 인덱스가 비활성화되나요?
네. 컬럼에 CAST나 CONVERT와 같은 함수를 적용하면 MySQL이 해당 컬럼에 정의된 인덱스를 사용하지 못할 수 있습니다.
대용량 데이터셋에 대한 고성능 쿼리가 필요하다면 미리 컬럼 타입을 통일하거나 서브쿼리 또는 생성된 컬럼을 사용하는 것을 고려하세요.
Q5. 소수점이나 큰 숫자에 대한 타입 변환은 안전한가요?
DECIMAL이나 FLOAT를 사용하면 정밀도를 유지할 수 있지만, 반올림 오류, 정밀도 손실 또는 절단이 발생할 수 있습니다. 요구 사항에 맞게 충분한 정밀도와 스케일을 항상 지정하세요.
Q6. 문자열로 정렬하는 것과 숫자로 정렬하는 것의 차이는 무엇인가요?
문자열로 정렬하면 사전식 순서 때문에 '10'이 '2'보다 먼저 나타날 수 있습니다. 숫자 순서를 원한다면 정렬하기 전에 항상 타입 변환을 수행하세요.
