Real MySQL - 1

그냥 읽으면서 배운점을 일단 써놓고 나중에 정리하려고 한다.

1. MySQL 서버의 sql_mode

• sql_mode는 시스템 변수, 설정된 값들이 SQL 작성 규칙이나 이런 옵션들은 관리한다.
• 원래 DB 수업에서 group by 절에 나온 컬럼들은 반드시 select절에 나와야 한다고 알고 있다.

  하지만 다른 DBMS에서는 안되지만 mysql에서는 없어도 오류가 발생하지 않았는데 8.0 버전으로 업데이트를 하며 ONLY_FULL_GROUP_BY라는 것이 기본으로 활성화가 되어있어 Group by 절이 사용된 쿼리의 SELECT 절에는 Group by절에 명시된 컬럼과 집계 함수만 사용할 수 있다. SELECT절에 집계 함수가 사용되는 경우 GROUP BY절에 명시되지 않은 컬럼도 집계함수의 인자로 사용할 수 있다.

• PIPE_AS_CONCAT: MySQL에서 "||"는 OR연산자와 같은 의미로 사용, 하지만 이 값을 설정함녀 오라클과 같이 CONCAT으로 사용할 수 있다.

2. MYSQL은 원래 대소문자를 구분 X

• 유닉스 계열의 os에서는 대소문자를 구분
• 운영체제에 관계없이 대소문자 구분의 영향을 받지 않으려면 설정파일에 lower_case_table_names 시스템 변수를 설정해야 함
• 1로 설정하면 모두 소문자로 저장된다.
• 기본 값은 0이다.
• 맥이나 윈도우는 2로 설정가능한데 테이블은 대소문자 구분하고 쿼리는 대소문자 구분 x
• 일반적으로는 1로 설정해서 테이블과 쿼리를 모두 통일하는 편이 좋다.

3. MySQL은 정해진 형태의 날짜 포맷으로 표기하면 MySQL서버가 자동으로 DATE나 DATETIME으로 변환하기 때문에 그냥 문자열로 '2023-12-27' 이렇게 해도 사용 가능하다.

4. BOOL, BOOLEAN 타입이 있지만 결국 TINYINT타입에 대한 동의어다. FALSE는 0이고 TRUE는 오직 1만이다.

5. <=> 연산자는 = 연산자와 같으며 NULL 값에 대한 비교까지 수행한다. 한쪽만 NULL이면 FALSE 반환, 둘다 NULL이면 TRUE 반환

6. 일반적으로 쿼리에선 <> 을 같지 않다는 연산자로 많이 사용한다. !=도 가능하지만 통일해서 <>로 사용하는 것을 추천한다.

7. &&와 ||이 사용가능하지만 AND와 OR를 사용하자, 순서는 AND가 우선순위 높다

8. LIKE연산자는 와일드카드(%, _ ) 가 검색어의 뒤에 있다면 인덱스 레인지 스캔이 가능하지만 앞쪽에 있다면 사용할 수 없다. ex) christ%면 가능, %rist는 x, 이유는 인덱스의 left-most 특성 때문

9. between 대신 IN을 활용해서 인덱스 효과를 더욱 잘 활용하자

• between은 선형으로 인덱스를 검색
• In은 동등 비교를 여러번 수행하는 것과 같은 효과가 있기 때문에 인덱스를 최적으로 사용할 수 있다.
• 이전 버전의 MySQL에서는 between을 in으로 변경하기 위해서 만약 3 ~ 5 사이면 In (3, 4, 5) 이런 식으로 작성해야 했는데 8.0 부터는 In(subquery)형태로 작성하면 옵티이저가 세미조인 최적화를 이용해 더 빠른 쿼리로 변환해서 실행한다.

  where num in (select num from table where num between 3 and 5) 이런식

10. now와 sysdate

• 모두 현재 시간을 반환한다.
• 하지만 now 함수는 여러개를 사용했을 때 위치에 상관없이 동일한 시간을 반환
• sysdate는 다른 값을 반환한다.
• 예를 들어 sleep(2)를 걸고 전 후로 비교하면 2초가 차이난다.
• 따라서 sysdate와 비교되는 칼럼은 인덱스를 효율적으로 사용 x

11. 날짜 타입 DateTime타입

• 문자열로 해도 자동 반환된다.
• (년-월-일 시:분:초) 이런 표준 형태를 지켰을 때에
• 하지만 만약 저런 형태가 아닐때 사용하는 것이 str_to_date() 함수다.
• 지정 문자만 잘 지키면 된다.

  지정 문자
  %Y : 4자리 연도
  %m : 2자리 숫자 표시의 월 (01 ~ 12)
  %d : 2자리 숫자 표시의 일 (01 ~ 31)
  %H : 2자리 숫자 표시의 시 (00 ~ 23)
  %i : 2자리 숫자 표시의 분 (00 ~ 59)
  %s : 2자리 숫자 표시의 초 (00 ~ 59)

12. 문자열 처리 함수 (RPAD, LPAD / LTRIM, RTRIM, LTRIM)

• RPAD, LPAD : 좌측이나 우측에 문자열을 붙여서 지정된 길이로 만듬
  - RPAD('A', 5, 'C') -> ACCCC
• LTRIM, RTRIM, TRIM : 공백제거 함수

13. 문자열 결합 CONCAT

• 숫자값을 인자로 전달하면 문자열 타입으로 자동 변환 후 연결
• 그래도 CAST() 함수 사용해서 안전하게 변환해주자 -> CAST(2 as char)
• CONCAT_WS()가 있는데 처음 인자를 구분자로 ->CONCAT_WS('-','as','d'): as-d

14. case when then

• 이걸 사용해서 만약 여자는 이름이 필요하고 남자는 필요없을때 불필요한 서브쿼리 조회가 없이 필요할때만 서브쿼리를 조회할 수 있다.

15. MD5, SHA: 암호화 및 해쉬함수

• 비밀번호와 같은 암호화가 필요한 정보를 인코딩 하는데 사용
• MD5는 char(32)가 필요, SHA는 char(40)이 필요
• URL 같이 길이가 긴 칼럼들을 MD5로 해시칼럼으로 저장하면 효율적이다.
• 비교할때도 where MD5(url) = MD5('www.naver.com') 해야한다

  전
  create table t(
  	a_id BIGINT not null auto_increment,
      		a_url VARCHAR(1000) not null,
           	primary key (a_id),
            index ix_accessurl (a_url)
  ) ENGINE=INNODB;

  후
   create table t(
  		a_id BIGINT not null auto_increment,
       	a_url VARCHAR(1000) not null,
          primary key (a_id),
          index ix_accessurl ((MD5(a_url)))
          // 만약 index ix_accessurl (UNHEX(MD5(a_url))) 이렇게 하면 저장 공간 더 줄일 수 있음
  );