[SQL] MySQL의 데이터 형식

목차

• 정수형
• 문자형
• 날짜형
• 변수의 사용
• 데이터 형 변환

 

 

데이터 형식

• 테이블을 만들 때는 데이터 형식을 설정해야 한다.
• 데이터 형식에는 크게 숫자형, 문자형, 날짜형이 있다.
• 다양한 데이터 형식이 존재하는 이유는 실제로 저장될 데이터의 형태가 다양하기 때문이다.
• 각 데이터에 맞는 데이터 형식을 지정함으로써 효율적으로 저장할 수 있다.

예시

• 이름을 저장하기 위해 내부적으로 100 글자를 저장할 칸을 준비하는 것은 낭비이다.

 

데이터 형식

• MySQL에서 제공하는 데이터 형식의 종류는 수십 개 정도이다.
• 데이터 형식마다 크기나 표현할 수 있는 숫자의 범위가 다르다.

 

정수형

정수형은 소수점이 없는 숫자이다.

인원 수, 가격, 수량 등에 사용

정수형으로 03을 저장한다면 0은 사라진다.

 

정수형으로 테이블을 만들어보자.

CREATE TABLE hongong4 (
    tinyint_col TINYINT,
    smallint_col SMALLINT,
    int_col INT,
    bigint_col BIGINT);

 

최대값을 넣어보자.

INSERT INTO hongong4 VALUES (127, 32767, 2147483647, 9000000000000000000);

 

최대값 이상을 넣어보자.

INSERT INTO hongong4 VALUES (128, 32768, 2147483648, 90000000000000000000);

 

Out of range는 입력값의 범위를 벗어났다는 의미이다.

 

 

UNSIGNED

키(height) 열은 SMALLINT 로 지정할 수 있다. (-32768 ~ 32767)

하지만 키는 30000cm가 넘는 사람은 없을 것이다.

TINYINT를 고려하고 싶지만 범위가 너무 작다. (-128 ~ 127)

 

이를 해결하기 위해 값의 범위가 0부터 시작되는 UNSIGNED 예약어를 사용할 수 있다.

TINYINT 와 TINYINT UNSIGNED 모두 1바이트의 크기이다.

1BYTE는 256개의 정보를 표현하므로 (-128 ~ 127), (0 ~ 255) 같이 모두 256개를 표현하는 것이다.

 

회원 테이블의 키(height) 열의 데이터 형식을 구성하라고 하면 TINYINT UNSIGNED 으로 구성하는 것이 효율적이다.

TINYINT UNSIGNED

 

나머지 정수형도 마찬가지로 UNSIGNED 예약어를 붙이면 0부터 범위가 지정된다.

예를 들어 SMALLINT UNSINGED는 0 부터 65535 까지 저장된다.

 

 

 

문자형

 

문자형은 글자를 저장하기 위해 사용하며, 입력할 최대 글자의 개수를 지정해야 한다.

CHAR는 글자의 개수가 고정된 경우 사용하자.

VARCHAR는 글자의 개수가 변동될 경우에 사용하자.

 

CHAR는 문자를 의미하는 Character의 약자로, 고정길이 문자형이라고 부른다.

예를 들어 CHAR(10)에 '가나다' 3글자만 저장해도 앞에 3자리만 사용하고 뒤의 7자리는 낭비하게 된다.

VARCHAR(Variable Character)는 가변길이 문자형으로, '가나다' 3글자를 저장할 경우 3자리만 사용한다.

CHAR, VARCHAR로만 쓰면 CHAR(1), VARCHAR(1) 과 동일하다.

 

VARCHAR가 CHAR보다 공간을 효율적으로 운영할 수 있지만

MySQL 내부적으로 성능(빠른 속도)면에서는 CHAR로 설정하는 것이 조금 더 좋다.

 

전화번호 같은 경우에는 모두 숫자로 이루어져 있어서 정수형으로 지정해야 할 것 같다.

하지만 전화번호는 숫자로서 의미가 없다. 숫자로서 의미를 가지려면 다음 2가지 중 1가지는 충족해야 한다.

• 더하기/빼기 등의 연산에 의미가 있다.
• 크다/작다 또는 순서에 의미가 있다.
• 전화번호는 위 2가지 중 어떤 것에도 해당되지 않는다.

전화번호는 문자형이 더 효과적이긴 하지만 반드시 틀렸다곤 할 수 없다.

효율성이나 타당성 면에서 좀 더 비효율적일 뿐이다.

 

 

대량의 데이터 형식

문자형인 CHAR는 최대 255 자 까지

VARCHAR는 최대 16383 자 까지 지정이 가능하다.

 

더 큰 데이터를 저장하려면 다음과 같은 형식을 사용한다.

추가로 TINYTEXT, MEDIUMTEXT, TINYBLOB, MEDIUMBLOB 등이 있지만 잘 사용하지 않는다.

 

TEXT로 지정하면 최대 65535 자까지

LONGTEXT로 지정하면 최대 약 42억 자까지 저장된다.

소설이나, 영화 대본과 같은 내용을 저장한다면 필요한 데이터 형식이다.

 

BLOB는 Binary Long Object의 약자로 글자가 아닌 이미지, 동영상 등의 데이터라고 생각하면 된다.

이런 것을 이진 데이터라고 부른다. 테이블에 사진이나 동영상과 같은 것을 저장하고 싶다면

BLOB이나 LONGBLOB 으로 데이터 형식을 지정해야 한다.

 

넷플릭스 같은 동영상 사이트가 운영하는 테이블을 만들어보자.

CREATE TABLE movie (
    movie_id INT,
    movie_title VARCHAR(30),
    movie_director VARCHAR(20),
    movie_star VARCHAR(20),
    movie_script LONGTEXT,
    movie_film LONGBLOB
);

 

자막(movie_script) 열은 LONGTEXT, 동영상(movie_film) 열은 LONGBLOB로 설정해야

대용량의 텍스트와 이진 데이터를 저장할 수 있다.

LONGTEXT 및 LONGBLOB으로 설정하면 각 데이터는 최대 4GB 까지 입력할 수 있다.

 

 

실수형

 

실수형은 소수점이 있는 숫자를 저장할 때 사용한다.

 

•  FLOAT와 DOUBLE은 거의 비슷하다.
• 소수점 아래를 어디까지 정밀하게 표현하는지의 차이인데,
• 과학 기술용 데이터가 아닌 이상 FLOAT 이면 충분하다.

 

예를 들어 시력은 2.0, 1.5, 0.7 과 같이 나오므로 FLOAT로 설정하는 것이 적합하다.

 

 

날짜형

날짜형은 날짜 및 시간을 저장할 때 사용한다.

 

DATE는 날짜만, TIME은 시간만 저장한다.

날짜와 시간을 둘 다 저장하고 싶으면 DATETIME을 사용한다.

날짜 또는 시간을 입력할 때는 문자와 마찬가지로 작은따옴표로 묶어줘야 한다.

 

 

 

변수의 사용

SQL도 다른 일반적인 프로그래밍 언어처럼 변수를 선언하고 사용할 수 있다.

변수의 선언과 값의 대입은 다음 형식을 따른다.

SET @변수이름 = 변수의 값; -- 변수의 선언 및 값 대입
SELECT @변수이름; -- 변수의 값 출력

 

예시

SET @txt = '가수 이름==> ';
SET @height = 166;

SELECT @txt , mem_name FROM member WHERE height > @height;

 

 

SELECT 문에서 행의 개수를 제한하는 LIMIT에는 변수를 사용할 수 없다.

SET @count = 3;
SELECT mem_name, height FROM member ORDER BY height LIMIT @count;

 

 

이를 해결하는 것은  PREPARE 와 EXECUTE 이다.

PREPARE는 실행하지 않고 SQL 문만 준비해 놓고 EXECUTE 에서 실행하는 방식이다.

SET @count = 3;
PREPARE mySQL FROM 'SELECT mem_name, height FROM member ORDER BY height LIMIT ?';
EXECUTE mySQL USING @count;

 

PREPARE는 'SELECT mem ~ ... ' 문을 실행하지 않고 mySQL 이라는 이름으로 준비만 해놓는다.

주의해서 볼 것은 LIMIT 다음에 나오는 물음표(?) 이다.

?는 '현재는 모르지만 나중에 채워짐' 정도로 이해하면 된다.

 

EXECUTE로 mySQL에 저장된 SELECT 문을 실행할 때, USING 으로 물음표(?)에 @count 변수의 값을 대입하는 것이다.

결론적으로 다음과 같은 SQL 이 실행된다.

SELECT mem_name, height FROM member ORDER BY height LIMIT 3;

 

 

 

데이터 형 변환

문자형을 정수형으로 바꾸거나, 반대로 정수형을 문자형으로 바꾸는 것을 형 변환이라고 부른다.

형 변환에는 직접 함수를 사용해서 변환하는 명시적인 변환

지시 없이 자연스럽게 변환되는 암시적인 변환이 있다.

 

 

함수를 이용한 명시적인 변환

데이터 형식을 변환하는 함수

• CAST( )
• CONVERT( )
• 형식만 다를 뿐 동일한 기능

CAST ( 값 AS 데이터_형식 [ (길이) ])
CONVERT ( 값, 데이터_형식 [ (길이) ])

 

SELECT AVG(price) AS '평균 가격' FROM buy;

 

가격은 실수보다 정수로 표현하는 것이 보기 좋기 때문에 형 변환 함수를 사용하여 정수로 표현할 수 있다.

CAST( )나 CONVERT( ) 함수 안에 올 수 있는 데이터 형식은 다음과 같다.

SIGNED는 부호가 있는 정수, UNSIGNED는 부호가 없는 정수를 의미한다.

• CHAR
• SIGNED
• UNSIGNED
• DATE
• TIME
• DATETIME 등

SELECT CAST(AVG(price) AS SIGNED INTEGER) '평균 가격' FROM buy;
SELECT CONVERT(AVG(price), SIGNED) '평균 가격' FROM buy;

 

 

이번에는 날짜 예제를 확인해보자. 

다양한 구분자를 날짜형으로 변경할 수 있다.

SELECT CAST('2022$12$12' AS DATE);
SELECT CAST('2022/12/12' AS DATE);
SELECT CAST('2022%12%12' AS DATE);
SELECT CAST('2022@12212' AS DATE);

 

SQL의 결과를 원하는 형태로 표현할 때도 사용할 수 있다.

SELECT num, CONCAT(CAST(price AS CHAR), 'X', CAST(amount AS CHAR), '=')
        '가격X수량', price * amount '구매액'
	FROM buy;

• 가격(price)과 수량(amount)은 정수지만, CAST( ) 함수를 통해 문자로 바꿨다.
• CONCAT( ) 함수는 문자를 이어주는 역할을 한다.

 

 

암시적인 변환

암시적인 변환은 CAST( ) 나 CONVERT( )를 사용하지 않고도 자연스럽게 형 변환되는 것이다.

SELECT '100' + '200';

 

만약 문자 '100' 과 '200'을 '100200' 으로 만들려면 CONCAT( ) 함수를 사용해야 한다.

SELECT CONCAT('100', '200');

 

숫자와 문자를 CONCAT( ) 함수로 연결하면 어떻게 될까?

SELECT CONCAT(100, '200');

SELECT 100 + '200';

 

결과를 보면 숫자 100이 문자 '100' 으로 변환되어 연결되었다.

CONCAT( ) 함수를 사용하지 않으면 뒤에 있는 문자가 숫자 200으로 자동 변환된다.

 

 

 

해당 포스팅의 글과 내용은 우재남 저자님의 혼자 공부하는 SQL 책을 재구성하여 만들었습니다.

좀 더 자세한 내용이 필요하시다면 '혼자 공부하는 SQL 책'을 구매해주세요

우재남 저자님 카페: https://cafe.naver.com/thisisMySQL

이것이MySQL이다 : 네이버 카페