[DBMS] MySQL(2) - MySQL 특화 SQL

MySQL SQL

MySQL은 기본적으로 표준 SQL을 따르지만 특정 기능이나 성능 최적화를 위해 고유한 구문이나 확장 기능을 추가하게 됨.

MySQL 특화 SELECT 구문 및 기능

SELECT 는 데이너를 조회하는 가장 기본적인 구문이며, MySQL은 결과 집합을 제어하는데 특화된 구문 제겅

결과 개수 제한

표준 SQL에서는 결과 집합 개수를 제한하기 위해 Workspace FIRST n ROWS ONLY 를 사용하나 MySQL은 LIMIT 절을 사용함

--표준 SQL
SELECT column1, column2 FROM table_name FETCH FIRST 10 ROWS ONLY;

--MySQL
SELECT column1, column2 FROM table_name LIMIT count; --처음부터 count 개 행
SELECT column1, column2 FROM table_name LIMIT offset,count; --offset번 행~ count개 행

GROUP_CONCAT() 함수

표준 SQL에는 그룹 내 문자열을 연결하는 표준함수가 없음. MySQL은 해당 기능을 위해 GROUP_CONCAT() 함수 제공

SELECT column1, GROUP_CONCAT(colum2 SEPERATOR 'delimiter')
FROM table_name
GROUP BY column1;

--예시 : 각 카테고리별 상품 이름 목록을 콤마(,)로 구분하여 가져오기
SELECT category, GROUP_CONCAT(product_name SEPERATOR ', ') AS product_list
FROM products
GROUP BY category;

흐름제어 함수

CASE 문은 표준 SQL이지만 MySQL은 간결한 조건 처리를 위한 IF() 함수도 제공

--MySQL IF 함수
SELECT IF(condition, value_if_true, falue_if_false);

--표준 SQL (MySQL도 사용 가능)
SELECT CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE result_else
END;

---

MySQL 특화 INSERT 구문 및 기능

데이터 삽입 시 발생하는 충돌 처리하거나 삽입 방식 제어하는 MySQL 기능

중복 키 발생 시 업데이트

INSERT 시 PRIMARY KEY 또는 UNIQUE 제약 위반으로 중복이 발생한 경우, 오류를 발생시키는 대신 지정된 대로 데이터를 업데이트하는 기능. INSERT ... ON DUPLICATTE KEY UPDATE 를 통해 수행. upsert 연산(insert or update)을 효율적으로 수행할 수 있게 함

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE
    column1 = new_value1,
    column2 = new_value2,
    ...;

ON DUPLICATE KEY UPDATE 절에서 VALUES(column_name) 또는 NEW.column_name (MySQL 8.0 이상)를 사용하여 삽입하려던 값을 참조할 수 있음

INSERT INTO users (username, email, last_login_at)
VALUES ('existing_user', 'updated_email@example.com', NOW())
ON DUPLICATE KEY UPDATE
    email = VALUES(email), -- 또는 email = 'updated_email@example.com'
    last_login_at = VALUES(last_login_at); -- 또는 last_login_at = NOW()

--예시 (8.0.20 이상)
INSERT INTO users (username, email, last_login_at)
VALUES ('existing_user', 'updated_email@example.com', NOW())
AS new
ON DUPLICATE KEY UPDATE
    username = new.username,
    last_login_at = new.last_login_at; 

중복 시 교체

REPLACE 는 INSERT 와 유사하지만, 중복 키가 발생하면 기존 행을 삭제하고 새 행을 삽입. DELETE 이후 INSERT 하는 것과 동일하지만 원자적(Atomically)으로 실행됨. 표준 SQL에 없음

REPLACE INTO table_name(column1, column2,...)
VALUES (value1, value2, ...);

오류 무시

INSERT 시 오류가 발생하면 기본적으로 쿼리 실패. INSERT IGNORE 를 사용하면 오류가 발생한 행은 건너뛰고, 오류가 없는 행만 삽입. 대량의 데이터 삽입시 유용하나 데이터 무결성을 해칠 위험 존재. 표준 SQL에 없음

INSERT IGNORE INTO table_name(column1, column2)
VALUES (value1, value2);

다중행 삽입

대부분의 RDBMS가 다중 행 삽입을 지원하지만, VALUES 절에 여러 행을 콤마로 구분하여 나열하는 문법은 MySQL에서 특히 흔하게 사용되고 잘 지원됨

INSERT INTO table_name (column1, column2) VALUES
(value1_1, value1_2),
(value2_1, value2_2),
...;

---

MySQL 특화 UPDATE & DELETE

여러 테이블을 조인하여 데이터 업데이트를하거나 삭제하는 기능 제공, 이는 표준 SQL에는 없는 경우가 많음

여러 테이블을 사용한 UPDATE

조인된 결과를 기반으로 한 테이블의 데이터 업데이트

UPDATE table1, table2
SET table1.column = table2.column
WHERE table1.join_column = table2.join_column
AND condition;

-- 예시 : orders 테이블의 총 급액을 order_item 테이블의 합계로 업데이트
UPDATE orders o, (
    SELECT order_id, SUM(price * quantitiy) AS total
    FROM order_items
    GROUP BY order_id
) oi
SET o.total_amount = oi.total
WHERE o.order_id = oi.order_id;

여러 테이블을 사용한 DELETE

조인된 결과를 기반으로 한 테이블 데이터 삭제 기능

DELETE table1
FROM tabl1
JOIN table2 on table1.join_column = table2.join_column
WHERE condition;

--예시 : 주문 정보가 없는 사용자 삭제
DELETE u
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.order_id IS NULL;

---

MySQL 특정 문법 및 기타 특징

• 식별자 인용 부호 : 표준 SQL은 식별자(테이블 이름, 열 이름 등)를 인요할 때 큰따옴표(”)를 사용하지만 MySQL은 기본적으로 백틱(``)을 사용. 큰따옴표도 SQL 모드 설정에 따라 사용할 수 있지만 기본적으로는 백틱
• 주석 : 표준 SQL은 -- (한줄 주석) /*…*/ (여러줄 주석)를 지원, MySQL은 추가로 # 도 한줄 주석 지원
• 데이터 타입 속성 : UNSIGNED , ZEROFILL 과 같은 정수형 데이터 타입의 추가 속성은 MySQL 특화 기능
• 스토리지 엔진(ENGINE): CREATE TABLE 문에서 ENGINE = InnoDB 같이 지정하는 MySQL특화 기능