11.1 쿼리 작성과 연관된 시스템 변수
SQL 모드
- SQL 모드는 MySQL에 익숙하지 않을 시 default 사용이 권장된다.
- SQL모드를 기본값 상태에서 MySQL8.0 버전으로 업그레이드 가능하다.
영문 대소문자 구분
- lower_case_table_names 변수를 통해 영문 대소문자 구분을 설정 할 수 있다.
- DB, 테이블 생성 시 대문자 혹은 소문자로 통일하여 사용이 권장된다.
- 0 : 대소문자구분
- 1: 입력값 대소문자 상관없이 소문자 저장
- 2 : 윈도우,macOS 에서만 설정가능, 대소문자 구분하여 저장하지만, 쿼리에서는 부분하지 않는다.
MySQL 예약어
Q. 예약어는 키워드에 포함되며, 예약어를 컬러명, 테이블명으로 사용하기위해서는 O 문자를 사용해야 한다.
`(역따옴표)
11.2 메뉴얼의 SQL 문법 표기를 읽는 방법
- [] 키워드 및 표현식이 선택사항임을 의미
- | 앞과 뒤 키워드 중 한개만 선택하여 사용 가능함을 의미
- {} 괄호 내 아이템중 한가지는 반드시 사용해야 함을 의미
MySQL reference manual - select statement
11.3 MySQL 연산자와 내장 함수
문자열
- SQL표준에서 문자열은 항상 '(홀따옴표)를 사용해 표기해야 하나
- "(쌍따옴표)를 사용해 문자열을 표기 할 수 있다.
- `(역따옴표)를 감싸서 예약어와 충돌을 피할 수 있다.
숫자
문자타입을 숫자로 조회하거나, 숫자를 문자로 조건으로 사용하여 조회 할 수 있다.
하지만, 형변환에 따른 성능저하나 잘못된 결과를 리턴 할 수 있기에 조회시 형을 맞추어 사용해야 한다.
날짜
- MySQL의 정해진 날짜 포맷으로 표기하면 자동으로 DATE | DATETIME 값으로 변환해준다.
과거 mysql5.6 버전 이하에서는 datetime타입에 default current_timestamp를 적용 할 수 없다
불리언
- 컬럼타입을 불리언으로 생성 시 TINYINT 타입으로 생성된다.
- TRUE, FALSE 와 같은 문자로 삽입이 가능하며 1, 0으로 저장된다.
- TINYINT 컬럼 조회시 TRUE, FALSE를 조건으로 사용 할 수 있다.
연산자
연산자에는 여러 표기방법이 있으나 가독성과 다른용도로 사용가능한 연산자가 있기에 전사표준과 같은 방식으로 통일하여 사용이 권장된다.
Q. AND 와 OR 중 우선순위가 높은 연산자는?
AND
REGEXP 연산자
RLIKE 와 REGEXP는 동일 기능을 수행하며, RLIKE는 Regular expression을 뜻하므로 오른쪽 일치(Right LIKE)와 혼동에 주의해야한다.
- POSIX 표준을 다른다.
- REGEXP 조건의 비교는 Range scan을 사용 할 수 없다.
LIKE 연산자
- Range-scan을 탄다
- \% | _(와일드카드)를 문자 앞에 사용 시 인덱스를 활용하지 못한다
BETWEEN 연산자
- BETWEEN은 문자타입에서도 사용가능한 크다|작다를 비교하는 연산자이다.
- Range-scan을 사용 할 수 있다.
IN 연산자
- 여러번 동등 비교로 실행하기에 성능상 이점이 있다.
- NOT IN절은 Full-scan을 한다.
mysql IN절 어아탬 수 는 최대 쿼리길이 관련된 max_allowed_packet 옵션에 영향을 받는다.
현재시각조회(NOW, SYSDATE)
- 쿼리단위에서 NOW는 같은값을 뱉지만, SYSDATE의 경우 쿼리단위에서 호출 시점에 따라 결괏값이 달라진다.
- SYSDATE() 레플리카서버에 영향을 줄 수 있으며, 인덱스를 효율적으로 사용하지 못한다.
- NOW() 사용이 권장된다.
- 시스템변수에서 sysdate-is-now 변수로 SYSDATE()가 NOW()로 동작하게 설정가능하다.
날짜와 시간의 연산(DATE_ADD, DATE_SUB)
- 날짜의 연산에 사용하는 함수이다.
주로 파티션삭제와 같은 배치성 작업에 사용하고 있다
문자열 결함(CONCAT)
- 문자열을 결합할때 사용하며, CONCAT_WS()는 첫번째 인자로 구분자를 선언하고 사용한다.
GROUP BY 문자열(GROUP_CONCAT)
- 문자열을 결합하고 정렬, 중복제거, 구분자변경 할 수 있다.
- group_concat_max_len 시스템변수로 조정 할 수있는 메모리버퍼 영역을 사용하며,
- MySQL에서는 경고처리하지만, JDBC에서는 에러처리하기에 메모리버퍼 조정이 필요 할 수 있다.
값의 비교와 대체(CASE WHEN ...THEN ...END)
- 프로그래밍 언어의 SWITCH 구문과 같은 역할을 한다.
타입의 변환(CAST,CONVERT)
- CAST() ,CONVERT() 두 함수는 거의 비슷하며 인자의 사용규칙만 조금 다르다.
- 문자열과 숫자, 날짜의 변환은 자동으로 변환되는 경우가 많지만 SIGEND, UNSIGNED의 경우는 명시적변환이 필요하다.
암호화 및 해시 함수(MD5, SHA, SHA2)
- MySQL8.0버전부터 함수 기반의 인덱스를 생성 시 별도의 컬럼을 추가하지 않아도 된다.
처리 대기(SLEEP)
- 쉘 스크립트의 SLEEP 기능을 수행하며, 디바깅 및 쿼리유지에 사용할 수 있다.
벤치마크(BENCHMARK)
- 2개의 인자를 필요로하며, 첫번째는 수행횟수 두번째는 수행쿼리이다.
- 반환값은 중요하지 않다.
IP주소 변환(INET_ATON, INET_NTOA)
- IP주소는 4바이트의 부호없는 정수로 저장되는데,
- '.' 문자를 사용하기에 더많은 저장공간을 필요로 해 INET_ATON(), INET_NTOA() 함수를 이용, 부호없는 정수형으로 저장한다.
JSON 필드크기
- SELECT 절 JSON_STORAGE_SZIE()로 반환가능하며 단위는 바이트이다.
JSON 필드추출(JSON_EXTRACT)
- 2개의 인자를 필요로 하며 첫번째는 JSON 타입 컬럼 및 도큐먼트, 두번째는 JSON PATH 이다.
JSON 오브젝트 포함 여부 확인(JSON_CONTANINS)
- 첫번째 인자는 JSON 타입 및 도큐먼트, 두번째 인자는 JSON 오브젝트를 사용한다.
- 세번째 인자는 선택적이며, 해당 경로에 JSON 오브젝트가 있는지 탐색한다.
11.4 SELECT
🌚Stone Kid