MySQL의 데이터 형식
앞에서 SELECT/INSERT/UPDATE/DELETE문에 대해서 살펴보았다. 이제는 지금까지 은연 중에 계속 사용했지만 특별히 얘기하지 않았던 MySQL의 데이터 형식(Data Type)에 대해서 살펴보겠다.
Data Type은 데이터 형식, 데이터형, 자료형, 데이터 타입 등 다양하게 불릴 수 있다. SELECT문을 더 잘 할용하고 테이블을 효율적으로 생성하기 위해서는 데이터 형식에 대한 이해가 반드시 필요하다.
MySQL에서 지원하는 데이터 형식의 종류
MySQL에서 데이터 형식의 종류는 30개 가까이 된다. 이를 모두 외우는 것은 무리이며, 거의 스이지 않는 것도 있으니 그럴 필요도 없다. 또한, 각각의 바이트 수나 숫자의 범위를 외우는 것도 당장 MySQL을 학습하는 데 큰 도움이 되는 것은 아니다.
우선은 꼭 필요한 것만 눈으로 한번 익히는 시간을 갖도록 하자.
자주 사용되는 것은 이름 앞에 별표를 해 놓았으니 유심히 살펴보자.
숫자 데이터 형식
숫자형 데이터 형식은 정수, 실수 등의 숫자를 표현한다.
DECIMAl 데이터 형식은 정확한 수치를 저장하게 되고 FLOAT, DOUBLE은 근사치의 숫자를 저장한다. 대신 FLOAT, DOUBLE은 상당히 큰 숫자를 저장할 수 있다는 장점이 있다.
그러므로 소수점이 들어간 실수를 저장하려면 되도록 DECIMAL을 사용하는 것이 바람직하다. 예로 -999999.99부터 +999999.99까지의 숫자를 저장할 경우에는 DECIMAL(9.2)로 설정한다.
또 MySQL은 부호 없는 정수를 지원하는데 부호 없는 정수로 지정하면 TYNYINT는 0~255, SMALLINT는 0~65535, MEDIUMINT는 0~16777215, INT는 0~약 42억, BIGINT는 0~약 1800경으로 표현할 수 있다. 부호 없는 정수를 지정할 때는 UNSIGNED 예약어를 뒤에 붙여준다. FLOAT, DOUBLE, DECIMAL도 UNSIGNED 예약어를 사용할 수 있지만 자주 사용되지는 않는다.
문자 데이터 형식
CHAR 형식은 고정길이 문자형으로 자릿수가 고정되어 있다. 예를 들어, CHAR(100)에 'ABC' 3글자만 저장해도 100자리를 모두 확보한 후에 앞에 3자리를 사용하고 뒤의 97자리는 낭비하게 되는 결과가 나온다. VARCHAR 형식은 가변길이 문자형으로 VARCHAR(100)에 'ABC' 3글자를 저장할 경우에 3자리만 사용하게 된다. 그래서 공간을 효율적으로 운영할 수 있다. 하지만, CHAR형식으로 설정하는 것이 INSERT/UPDATE 시에 일반적으로 더 좋은 성능을 발휘한다.
*CHAR, VARCHAR의 내부 크기:
MySQL은 기본적으로 CHAR, VARCHAR는 모두 UTF-8 형태를 지니고 있으므로, 입력한 글자가 영문, 한글 등에 따라서 내부적으로 크기가 달라진다. 하지만 사용자 입장에서는 CHAR(100)은 영문, 한글 구분 없이 100글자를 입력하는 것으로 알고 있으면 되며, 내부적인 할당 크기는 신경 쓸 필요가 없다.
참고로 MySQL의 기본 문자 세트(Default Character Set)는 my.ini 또는 my.cnf 파일에 다음과 같이 기본적으로 설정 되어 있다.
#클라이언트 문자 세트
[mysql]
default-character-set=utf8
#서버 문자 세트
[mysqld]
character-set-server=utf8날짜와 시간 데이터 형식
날짜와 시간형 데이터에 대해서는 간단한 예를 통해서 그 차이를 확인하자.
SELECT CAST('2020-10-19 12:35:29.123' AS DATE) AS 'DATE' ;
SELECT CAST('2020-10-19 12:35:29.123' AS TIME) AS 'TIME' ;
SELECT CAST('2020-10-19 12:35:29.123' AS DATETIME) AS 'DATETIME' ;
기타 데이터 형식
GEOMETRY 형식은 다음에 상세히 살펴보고 JSON형식은 잠시 후에 살펴보겠다.
LONGTEXT, LONGBLOB
MySQL은 LOB(Large Object:대량의 데이터)을 저장하기 위해서 LONGTEXT, LONGBLOB 데이터 형식을 지원한다. 지원되는 데이터 크기는 약 4GB 크기의 파일을 하나의 데이터로 저장할 수 있다. 예로 장편소설과 같은 큰 텍스트 파일이라면, 그 내용을 전부 LONGTEXT 형식으로 지정된 하나의 컬럼에 넣을 수 있고, 동영상 파일과 같은 큰 바이너리 파일이라면 그 내용은 전부 LONGBLOB 형식으로 지정된 하나의 컬럼에 넣을 수 있다. 예를들어 다음과 같은 구성이 가능하다.
위의 예를 보면 영화 대본 열에는 영화 대본 전체가 들어가고, 영화 동영상 열에는 실제 영화 파일 전체가 들어갈 수 있다. 실무에서는 이러한 방식도 종종 사용되니 잘 기억해 놓으면 도움이 된다. 대량의 데이터를 입력하는 실습은 잠시 후의 실습에서 해보겠다.
변수의 사용
SQL도 다른 일반적인 프로그래밍 언어처럼 변수를 선언하고 사용할 수 있다. 변수의 선언과 값의 대입은 다음의 형식을 따른다.
SET @변수이름 = 변수의 값 ; -- 변수의 선언 및 값 대입
SELECT @변수이름 ; -- 변수의 값 출력*스토어드 프로시저나 함수 안에서의 변수를 사용하는 방법은 DECLARE문으로 선언한 후에 사용할 수 있다. 또한, 스토어드 프로시저나 함수 안에서는 @변수명 형식이 아닌 그냥 변수명만 사용한다. 구분하자면 @변수명은 '전역 변수'처럼 사용하고, DECLARE 변수명은 스토어드 프로시저나 함수 안에서 '지역 변수'처럼 사용된다.
변수는 Workbench를 재시작할 때까지는 계속 유지되지만 Workbench를 닫았다가 재시작하면 소멸된다.
<실습>
간단히 변수의 사용을 실습하자.
- 초기화하기 (File -> Open Recent -> 저장한 sqlDB.sql 요거 실행)
USE sqldb;
-- 변수를 세팅할때는 한번실행하고 바로 날라가기때문에 드래그한 후 한꺼번에 실행해야함.
SET @myVar1 = 5 ;
SET @myVar2 = 3 ;
SET @myVar3 = 4.25 ;
SET @myVar4 = '가수 이름==> ' ;
SELECT @myVar1 ; -- result1
SELECT @myVar2 + @myVar3 ; -- result2
SELECT @myVar4, Name FROM usertbl WHERE height > 180 ; -- 여기까지 한꺼번에 실행 result3
SET @myVar1 = 3; -- result4
PREPARE myQuery -- 쿼리를 준비만 해놓는 것. ?는 아직 대입이 안되었다는 것.
FROM 'SELECT Name, height FROM usertbl ORDER BY height LIMIT ?';
EXECUTE myQuery Using @myVar1 ;
result1
result2
result3
result4
