MYSQL 데이터 형식과 내장함수

숫자 데이터 형식

DECIMAL은 정확한 수치를 저장하고 FLOAT와 DOUBLE은 근사치으 숫자를 저장한다.

부호없는 정수를 지정할 때는 UNSIGNED예약어를 뒤에 붙여주고 2배 더 큰 숫자까지 표현할 수 있다.

데이터 형식	바이트 수	숫자 범위	설명
BIT(N)	N/8		1~64bit를 표현
TINYINT	1	-128~+127	정수
SMALLINT	2	-32768 ~ 23767	정수
MEDIUMINT	3	-8388608 ~ 8388607	정수
INT INTEGER	4	약 -21억 ~ 21억	정수
BIGINT	8	약 -900경~ + 900경	정수
FLOAT	4	-3.40E+38~-1.17E+38	소수점 아래 7자리까지 표현
DOUBLE REAL	8	-1.22E-308~1.79E1+308	소수점 아래 15자리까지 표현
DECIMAL(m.[d]) NUMERIC(m,[d])	5~17	-10^38+1~ 10^38 -1	전체 자리수와 소수점 이하 자리수를 가진 숫자형

문자형 데이터 형식

CHAR 형식은 고정길이 문자형으로 자릿수가 고정되어있고 VARCHAR형식은 가변길이 문자형으로 효율적으로 운영할 수 있다는 장점이 있지만 CHAR형식으로 설정하는 것이 INSERT/UPDATE시에 일반적으로 좋은 성능을 발휘한다.

데이터형식		바이트수	설명
CHAR(n)		1~255	고정길이 문자형
VARCHAR(n)		1~65535	가변길이 문자형
BINARY		1~255	고정길이의 이진 데이터
VARBINARY		1~255	고정길이의 이진 데이터
TXT형식	TINYTEXT	1~255	255 크기의 TEXT데이터
	TEXT	1~65635	N 크기의 TEXT데이터 값
	MEDIUM	1~16777215	16777215크기의 TEXT데이터 값
	LONGTEXT	1~4294967295	,최대 4GB 크기의 BLOB데이터 값
BOLB	TINYBLOB	1~255	255크기의 BLOB데이터 값
	BLOB	1~65535	N 크기의 BLOB 데이터 값
	MEDIUMBLOB	1~16777215	16777215 크기의 BLOB데이터 값
	LONGBLOB	1~4294967295	최대 4GB 크기의 BLOB 데이터
ENUM		1또는2	최대 65535개의 열거형 데이터 값
SET		1,2,3,4,8	최대 64개의 서로 다른 데이터 값

 날짜와 시간 데이터 형식

데이터 형식	바이트 수	설명
Date	3	날짜는 1001-01-01 ~ 9999-12-31까지 저장되며 날짜 형식만 사용 YYYY_MM_DD형식으로 사용됨
TIME	3	-838:59.000000~38:59.000000까지 저장되며 "HH:MM:SS' 형식으로 사용
DATETIME	8	날짜는 1001-01-01 00:00:00 ~ 9999-12-31 23:59:59까지 저장되며 형식은 YYYY-MM--DD HH:MM:SS 형식으로 사용
TIMESTAMP	4	time_zone 시스템 변수와 관련이 있으며 UTC 시간대 변환하여 저장
YEAR	1	1901 ~ 2155까지 저장.YYYY형식으로 사용

기타 데이터 형식

데이터 형식	바이트 수	설명
GEOMERTY	N?A	공간 데잍 형식으로 선 점 및 다각형 같은 공간 데이터 개체를 저장하고 조작
JSON	8	JSON 문서를 저장

데이터 형 변환 함수

BINARY,CHAR,DATE,DATETIME,DECIMAL,JSON,SIGNED,INTEGER,TIME,INSIGNED,INTEGER 등 변환이 가능하다. 

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

MYSQL 내장함수

mySQL은 많은 내장 함수를 포함한다. 내장함수는 크게, 제어 흐름 함수, 문자열 함수, 수학함수 ,날짜/시간 함수, 전체 텍스트 검색 함수, 형 변환함수, XML 함수, 비트 함수, 보안/암축 함수, 정보 함수, 공간 분석 함수,기타 함수 등으로 나눌 수 있다.

제어 흐름 함수

IF(수식,참,거짓)

수식이 참 또는 거짓인지 결과에 따라서 2중 분기한다.

SELECT IF (100>200,'참이다','거짓이다');

IFNULL(수식1, 수식2)

NULL 값 처리 함수 중 하나입니다. 이 함수는 두 개의 인자를 받으며, 첫 번째 인자가 NULL인 경우 두 번째 인자를 반환합니다. 첫 번째 인자가 NULL이 아닌 경우에는 첫 번째 인자를 그대로 반환합니다.

IFNULL(expression1, expression2)

NULLIF(수식1,수식2)

수식1과 수식2가 같으면 NULL을 반환하고, 다르면 수식1을 반환한다.

IFNULL(expression1, expression2)

 

CASE~WHEN~ELSE~ END

Case문은 조건에 따라 다른 값을 반환하는데 사용된다.

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

 

문자열 함수

문자열 함수는 문자열을 조작한다. 활용도가 높음편에 속한다.

 

ASCII(아스키 코드),CHAR(숫자)

문자의 아스키 코드값을 돌려주거나 숫자의 아스키 코드값에 해당하는 문자를 돌려준다.

SELECT ASCII('A'), CHAR(65);

BIT_LENGTH(문자열), CHAR_LENTH(문자열),LENGTH(문자열)

할당된 Bit 크기 또는 문자 크기를 반환한다. CHAR_LENGTH()는 문자의 개수를 반환하며 LENGTH()는 할당된 Byte 수를 반환한다.

SELECT BIT_LENGTH('abc'), CHAR_LENGTH('abc'), LENGTH('abc');
SELECT BIT_LENGTH('가나다'), CHAR_LENGTH('가나다'), LENGTH('가나다');

CONCAT(문자열1,문자열2,...),CONCAT_WS(구분자,문자열1,문자열2...)

CONCAT()은 문자열을 이어준다.  CONCAT_WS()는 구분자와 함께 문자열을 이어준다.

SELECT CONCAT('Hello', ' ', 'World');  -- 결과: 'Hello World'
SELECT CONCAT('Hello', NULL, 'World');  -- 결과: NULL

ELT(위치,문자열1, 문자열 2,...)

위치 번쨰에 해당하는 문자열을 반환한다.

FIELD(찾을 문자열,문자열1,문자열2,...)

찾을 문자열의 위치를 찾아서 반환한다. 매치되는 문자열이 없으면 0을 반환한다.

FIND_IN_SET(찾을 문자열,문자열 리스트)

찾은 문자열을 문자열 리스트에서 찾아 위치를 반환한다.

INSTR(기준문자열,부분 문자열)

기준 문자열에서 부분 문자열을 찾아서 그 시작 위치를 반환한다.

LOCATE(부분문자열, 기준문자열)

INSTR()와 동일하지만 파라미터의 순서가 반대로 되어있다.

SELECT ELT(2,'하나','둘'.'셋'),FIELD('둘','하나','둘','셋'), FIND_IN_SET('둘','하나,둘,셋'),INSTR('하나둘셋','둘'),LOCATE('둘','하나둘셋');
-- 결과 : '둘',2,2,3,3

FORMAT(숫자, 소수점 자릿수)

숫자를 소수점 아래 자릿수까지 표현한다, 또한 1000단위마다 콤마를 표시해 준다.

SELECT FORMAT(123456.123456,4) -- 결과: 123,456,1235

BIN(숫자),HEX(숫자),OCT(숫자)

2진수 16진수 8진수의 값을 반환한다

SELECT BIN(31),HEX(31),OCT(31); -- 결과: 11111,1F,37

INSERT(기준 문자열,위치,길이,삽일할 문자열)

기준 문자열의 위치부터 길이만큼 지우고 삽입할 문자열을 끼워 넣는다.

SELECT INSERT('abacdefghi',3,4,'@@@@') -- 결과 : ab@@@@ghi'

LEFT(문자열,길이), RIGHT(문자열,길이)

왼쪽 또는 오른쪽에서 문자열의 길이만큼 반환한다.

SELECT LEFT('abcdefghi',30,RIGHT('abcdefghi',3); -- 결과 : 'abc','ghi'

UPPDER(문자열), LOWER(문자열)

소문자를 대문자로,대문자를 소문자로 변경한다.

SELECT LOWER('abcdEFGH'), UPPER('abcdEFGH'); -- 결과 : abcdefgh ABCDEFGH

TRIM(문자열)

문자열의 앞뒤 공백을 모두 없앤다.

SELECT TRIM(' 안녕  ') -- 결과 : '안녕'

REPEAT(문자열,횟수)

문자열을 횟수만큼 반복한다

SELECT REPEAT('안녕',3) -- 결과 : '안녕안녕안녕'

REPLACE(문자열,원래 문자열, 바꿀문자열)

문자에서 원래문자열을 찾아서 바꿀 문자열로 바꿔준다.

SELECT REPLACE('안녕 나는 김태훈입니다','안녕','하이'); -- 결과 : '하이 나는 김태훈입니다'

REVERSE(문자열)

문자열의 순서를 거꾸로 만든다.

SELECT REVERS('MYSQL'); -- 결과 LQSYM

SPACE(길이)

길이의 공백만큼을 반환한다.

SELECT CONCAT('안녕',SPACE(10),'김태훈이다'); -- 결과 : '안녕          김태훈이다'

SUBSTRING(문자열, 시작위치,길이)

시작 위치부터 길이만큼 문자를 반환한다.

SELECT SUBSTRING_INDEX('cafe.naver.com','.'2) -- 결과 'cafe.naver'

수학함수

ABS(숫자)

숫자의 절댓값을 계산한다.

SELECT ABS(-100) -- 결과 : 100

CEILING(숫자),FLOOR(숫자),ROUND(숫자)

올림,내림 반올림을 계산한다.

SELECT CEILING(4.7), FLOOR(4.7) ,ROUND(4.7) --결과 : 5 4 5

외에도 CON(),DEGREES(),RADIANS,EXP,MOD,POW등 다양한 수학 함수를 제공한다.

 

날짜함수

ADDDATE(날짜,차이),SUBDATE(날짜,차이)

날짜를 기준으로 차이를 더하거나 뺸 날짜를 구한다.

SELECT ADDDATE('2025-01-01',INTERVAL 31DAY) --결과 : '2025-02-01'

ADDTIME(날짜/시간,시간),SUBTIME(날짜/시간,시간)

날짜/시간을 기준으로 시간을 더하거나 뺸 결과를 구한다.

SELECT ADDTIME('2025-01-01 23:59:59','1:1:1'),SUBTIME('15:00:00','2:10:10');

CURDATE(),DURTIME(),NOW(),SYSDATE()

CURATE()는 현재 연-월-일을 CURATIME()은 현재 시:분:초를 구한다. NOW()와 뇬SYSDATE()는 현재 '연-월-일 시:분:초'를 구한다.

그외에서 날짜 관련 함수로는 DATE(),TIME(),DATEDIFF(),DAYOFWEEK(),LAST_DAY(),MAKEDATE(),PERIOD_ADD

QUARTER()들이 있다.