[Mysql] 버전 문자열 비교 및 숫자 변환

어제 회사에서 앱을 스토어에 배포하고, 버전업을 테스트하는 과정에서 서버가 버전을 제대로 비교하지 못하는 이슈가 있었다. app 버전 정보를 저장하는 DB TABLE은 다음과 같다.

 

[ app ]

app_id	package_name	version_name	created_at
1	'com.test.mobile'	'1.0.0'	2019-03-06 1:00:10.000000
2	'com.test.mobile'	'1.0.1'	2019-03-07 1:00:10.000000
3	'com.test.mobile'	'1.0.5'	2019-03-08 1:00:10.000000

 

SELECT * 
FROM app 
WHERE 
	package_name = {target_package_name} 
ORDER BY 
	version_name DESC,
	created_at DESC 
LIMIT 1

 

이때까지는 서버의 앱 위변조 확인 및 버전 확인 API는 최신버전을 잘 전달하고 있었으나, 다음과 같이 앱 버전이 올라가면서 이슈가 발생했다. 

 

[ app ]

app_id	package_name	version_name	created_at
1	'com.test.mobile'	'1.0.0'	2019-03-06 1:00:10.000000
2	'com.test.mobile'	'1.0.1'	2019-03-07 1:00:10.000000
3	'com.test.mobile'	'1.0.5'	2019-03-08 1:00:10.000000
4	'com.test.mobile'	'1.0.10'	2019-03-09 1:00:10.000000

 

Mysql에서 버전 정보로 정렬을 할때 현재 DB TABLE에서 가장 최신 버전인 '1.0.10'을 '1.0.1'로 계산하여 정렬을 하기에 가장 최신 버전을 조회하는 이전 쿼리는 가장 상위 버전인 '1.0.5'를 반환하고 있었다.

 

이를 해결하는 방법으로는 버전 문자열을 . 을 기준으로 잘라서 정렬하는 방법이 있고, 가장 단순하게는 값이 증가되는 PK 격인 app_id가 가장 높은걸 가져오거나, 가장 최근 일자로 등록된 앱 정보를 가져오는 방법이 있었다.

 

하지만 요구사항은 가장 높은 버전을 가져오는것이므로 버전 정보를 바탕으로 쿼리를 실행해야 한다는 생각이 들었고,

문자열을 잘라서 비교하는 쿼리는 길이가 길고 다시 작성하기 귀찮았으므로 대안으로 사용할 만한 MYSQL 내장 함수를 찾아보았다. 그중 다음과 같은 내장 함수를 찾았다.

 

https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_inet-aton

MySQL :: MySQL 8.0 Reference Manual :: 12.24 Miscellaneous Functions

 

아이피 주소를 정수로 변환하는 함수로 다음과 같이 자릿수를 계산한다.

 

SELECT INET_ATON('10.0.5.9');
        -> 167773449

10×256^3 + 0×256^2 + 5×256 + 9 = 167773449

따라서 다음과 같이 쿼리를 실행하면

 

SELECT version_name, INET_ATON(version_name) AS score FROM app

 

다음과 같이 버전별 스코어를 계산한다.

version_name	score
'1.0.0'	16777216
'1.0.1'	16777217
'1.0.5'	16777221
'1.0.10'	16777226

 

해당 스코어가 가장 높은 튜플을 조회하면, 가장 최신버전의 앱 정보를 가지고 올 수 있다. 현재 앱의 Splash 화면 단에서

버전을 체크하기 때문에, 많은 사용자가 해당 API를 사용하면 DB에 많은 연산이 발생할 수 있다. 따라서 최적의 방법은

최신 앱 버전 정보를 DB에 등록할때 Score를 계산하여 등록하고 최신 앱 버전을 조회하는 경우 Score 점수가 가장 높은 튜플만 조회하면 적은 비용으로 처리할 수 있다. 하지만 사용하고자하는 DB가 Mysql이 아니거나 해당 function을 제공하지 않는다면 다른 방법으로 해당 이슈를 처리해야한다. 그 방법에 대해서는 차차 생각하기로 했다...(업무가 너무 많았다,,