[SQL] MySQL과 PostgreSQL

1. PostgreSQL의 강점

- SQL 표준을 더 충실히 따른다. (MySQL은 편리하지만 축약 등 비표준 문법이 꽤 있다. ex.LIMIT)

- 트랜잭션 제어(BEGIN, SAVEPOINT, ROLLBACK TO)/락/ACID 특성이 더 강력

- 데이터 타입이 더 다양하고 강력하다(JSON, ARRAY 같은 특수 타입을 기본 지원)

- 복잡한 처리(JOIN, 서브쿼리, 윈도우 함수 등)를 훨씬 빠르고 정확하게 처리

- BSD 라이선스(기업이 커스터마이징하거나 상업화 해도 거의 제약 없음)

- 확장성(PostGIS, pg_partman, TimescaleDB 등으로 GIS, 파티셔닝, 시계열데이터 추가 가능)과 플러그인 가능

 

2. 문법 차이

구분	MySQL	PostgreSQL
순서	LIMIT 10 OFFSET 20 (=LIMIT 20,10)	LIMIT 10 OFFSET 20
자동증가	AUTO_INCREMENT	SERIAL 또는 IDENTITY
문자열 연결	CONCAT(a,b)	a||b
NULL 처리	IFNULL(a,b)	COALESCE(a,b)
날짜함수	YEAR(date)	EXTRACT(YEAR FROM date)
따옴표	백틱(`)도 가능	쌍따옴표(")사용
대소문자 구분	구분x	쌍따옴표 쓰면 구분, 구분한하면 소문자로 인식
JSON	최근 지원(제한적)	강력 지원
트랜잭션	엔진에 따라 다름	무조건 ACID 준수
확장성	제한적	다양한 확장(Extension)지원

- OFFSET : 몇 개를 건너 뛸지, 언제쓸까? 페이지네이션(페이지별로 나누는 작업)

SELECT * FROM 테이블명
LIMIT 10 OFFSET 20;

# 테이블에 1~100번 데이터가 있다면
# 앞에 20개 건너뛰고
# 21번부터 30번까지 가져와라

- 문자열 연결 비교

-- MySQL 공백 넣기 
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM users;

-- PG 공백넣기
SELECT first_name || ' ' || last_name AS full_name
FROM users;

 

* ACID

- A: 원자성, 트랜잭션은 모두 성공하거나, 모두 실패해야 한다. 중간에 실패하면 아예 아무것도 안 한 것처럼 롤백해야 한다.

- C: 일관성, 트랜잭션 전후에 데이터는 항상 규칙(제약조건)을 만족해야 한다. 예. 잔고가 음수로 저장되면 안됨

- I: 격리성, 여러 트랜잭션이 동시에 실행돼도 서로 간섭하면 안된다.

- D: 지속성, 트랜잭션이 완료되면 서버가 꺼져도 결과는 영원히 저장되더야 한다.