[Mysql] 자주 쓰이는 변환함수 모음

변환함수

• 데이터의 데이터타입을 변환하는데 사용하는 함수

DATE_FORMAT

• 날짜 데이터의 요소들을 원하는 형식으로 조합하여 문자열값으로 출력해주는 함수
• 각 요소들에 대해서 사용 횟수나 순서의 제한은 없음.
• DATE_FORMAT(날짜, 형식)

SELECT DATE_FORMAT(NOW(), '%Y - %y');

DATE_FORMAT(NOW(), '%Y - %y')|
-----------------------------+
2023 - 23

SELECT DATE_FORMAT(NOW(), '%M, %b, %m, %c');

DATE_FORMAT(NOW(), '%M, %b, %m, %c')|
------------------------------------+
August, Aug, 08, 8                  |

SELECT DATE_FORMAT('2023-09-05', '%d, %e');

DATE_FORMAT('2023-09-05', '%d, %e')|
-----------------------------------+
05, 5                              |

SELECT DATE_FORMAT(NOW(), '%W, %a');

DATE_FORMAT(NOW(), '%W, %a')|
----------------------------+
Wednesday, Wed              |

SELECT DATE_FORMAT(now(), '%k, %l');

DATE_FORMAT(now(), '%k, %i')|
----------------------------+
16, 26                      |

SELECT DATE_FORMAT(now(), '%i, %s');

DATE_FORMAT(now(), '%i, %s')|
----------------------------+
25, 06                      |

SELECT DATE_FORMAT(now(), '%h:%i:%s %p');

DATE_FORMAT(now(), '%h:%i:%s %p')|
---------------------------------+
04:28:08 PM                      |

SELECT employee_id, DATE_FORMAT(hire_date, '%Y-%M-%d %W') AS "입사일"
FROM employees;

employee_id|입사일                       |
-----------+--------------------------+
        100|1987-June-17 Wednesday    |
        101|1989-September-21 Thursday|
        102|1993-January-13 Wednesday |
        103|1990-January-03 Wednesday |
        104|1991-May-21 Tuesday       |
        105|1997-June-25 Wednesday    |
        106|1998-February-05 Thursday |

---

CAST

• 입력받은 데이터를 원하는 데이터타입으로 변환한 결과를 돌려준다.
• 무조건적인 변환을 지원하지는 않으며, 변환 가능한 값이나 형태를 가진 값들의 변환만 가능하다.

SELECT cast("123" AS signed), cast("-123.45" AS signed);

cast("123" AS signed)|cast("-123.45" AS signed)|
---------------------+-------------------------+
                  123|                     -123|

자료형

• BINARY(N) : 0~255바이트의 고정 길이의 이진 문자열의 형태로 저장할 수 있다.
• 이진 문자열은 0,1로 구성된 2진수 형태로 표현된 문자열을 말한다. 주로 바이너리 데이터를 저장하거나, 문자열 데이터를 바이너리로 변환하여 연산에 활용한다. 일반적인 문자열의 경우 MYSQL에서는 대소문자를 구분할 수 없으나 이진 문자열에서는 구분이 된다.
• CHAR(N) : 0~255문자의 고정 길이의 문자열을 저장한다.
• SIGNED : 부호가 있는 정수를 저장한다.
• UNSIGNED : 부호가 없는 정수를 저장한다.
• DECIMAL(p[,s]) : 고정 소수점 숫자를 저장한다. p는 정밀도, s는 스케일이다. 숫자의 전체 길이는 정밀도로 지정하고, 소수점 아래의 길이는 스케일값으로 지정한다. 최대 65자리의 숫자를 저장할 수 있으며, 스케일값은 30까지 설정가능. 스케일값을 생략하는 경우 소수점 아래를 표현하지 않는다
• DATETIME : DATETIME은 날짜와 시간을 함께 저장한다. 'YYYY-MM-DD HH:MI:SS' 형식을 사용하며, 1000-01-01 00:00:00에서 9999-12-31 23:59:59 사이의 값이다.
• DATE : DATE는 날짜만을 저장한다. 'YYYY-MM-DD' 형식을 사용하며, 1000-01-01에서 9999-12-31 사이의 값이다.
• TIME : TIME은 시간만을 저장한다. 'HH:MI:SS' 형식을 사용하며, '-838:59:59'에서 '838:59:59' 사이의 값이다.
• YEAR : 년도의 4자리값을 저장하는 1바이트의 데이터타입이다. 1901~2155, 0000의 값의 범위를 표현할 수 있다.
• 참조 : MySQL :: MySQL 8.0 Reference Manual :: 12.10 Cast Functions and Operators
• signed, unsigned의 경우 정수형의 값만 받을 수 있으므로 실수의 값을 입력하는 경우 자동으로 버림 연산으로 소수점 아래를 정리한 뒤 출력한다.

SELECT cast("123" AS signed), cast("-123.45" AS signed);

cast("123" AS signed)|cast("-123.45" AS signed)|
---------------------+-------------------------+
                  123|                     -123|

• unsigned의 경우 양수의 정수값만 받을 수 있는 대신 signed에 비해 두배 가량의 더 큰 범위의 정수를 표현할 수 있다.
• 음수의 값을 unsigned로 변환하는 경우 아래와 같이 정상적이지 않은 값이 출력된다.

SELECT cast("123" AS unsigned), cast("-123.45" AS unsigned);

cast("123" AS unsigned)|cast("-123.45" AS unsigned)|
-----------------------+---------------------------+
                    123|       18446744073709551493|

• 구분자의 기호를 임의로 바꾸더라도 어느정도 양식이 일치한다면 변환을 지원한다.

SELECT CAST('2022/03/18' AS DATE) AS '날짜',
CAST('2022@03@18' AS DATE) AS '날짜2';

날짜        |날짜2       |
----------+----------+
2022-03-18|2022-03-18|

SELECT CAST('2022-01-02 21:24:33.123' AS DATE) AS "DATE",
CAST('2022-01-02 21:24:33.123' AS TIME) AS "TIME",
CAST('2022-01-02 21:24:33.123' AS DATETIME) AS "DATETIME";

DATE      |TIME    |DATETIME           |
----------+--------+-------------------+
2022-01-02|21:24:33|2022-01-02 21:24:33|

DECIMAL 변환

• 고정소수점숫자의 경우 변환형에 크기를 명시해야한다.
• DECIMAL(12,4)는 12자리의 숫자 중 4자리는 소수 표현에 사용을 의미한다.

SELECT CAST(123.456789 AS DECIMAL(12,4));

CAST(123.456789 AS DECIMAL(12,4))|
---------------------------------+
                         123.4568|

BINARY 변환

• BINARY 데이터타입은 정확하게는 이진스트림으로 변환하는 방법으로 데이터 사용 시 이진 형식으로 텍스트와는 다른 방식으로 전송하게 된다. 이는 이진 표기법이 아니므로 진법 변환은 일어나지 않는다.
• 문자열의 경우 'A'와 'a'는 mysql에서는 같은 문자로 취급되지만 이진 스트림의 형태에서는 서로 다른 값으로 구분이 된다.

SELECT CAST(20 AS BINARY);

CAST(20 AS BINARY)|
------------------+
20                |

SELECT last_name
FROM employees
WHERE last_name = 'abel';

last_name|
---------+
Abel     |

SELECT last_name
FROM employees
WHERE CAST(last_name AS BINARY) = 'abel';

last_name|
---------+

• 이진 문자열에서는 대소문자도 구분이 되므로 아래와 같이 동일한 스펠링이더라도 대소문자가 다르다면 일치하지 않은 문자열로 결과가 나온다.

이진수 변환

• BIN 함수로 입력된 숫자값을 이진수로 변환하는 것이 가능하다.

SELECT BIN(20);

BIN(20)|
-------+
10100  |