MySQL 데이터베이스의 고유 함수 완벽 가이드

DB/MySql

MySQL 데이터베이스의 고유 함수 완벽 가이드

shaprimanDev 2025. 5. 24. 21:57

MySQL은 세계에서 가장 널리 사용되는 오픈 소스 관계형 데이터베이스 시스템으로, 웹 애플리케이션 개발에 특화된 다양한 고유 함수들을 제공합니다. 이러한 함수들은 웹 개발자들이 자주 마주치는 실무 상황에서 강력한 도구가 되며, 코드의 간결성과 성능 향상에 크게 기여합니다. MySQL만의 독특하고 유용한 함수들을 실제 사용 예제와 함께 깊이 있게 살펴보겠습니다.

1. UUID() - 웹 애플리케이션의 필수 고유 식별자

UUID() 함수는 MySQL에서 RFC 4122 표준을 준수하는 범용 고유 식별자를 생성합니다. 웹 애플리케이션에서 세션 ID, 파일명, API 키 등을 생성할 때 매우 유용합니다.

기본 특징과 형식

SELECT UUID();
-- 결과 예: 550e8400-e29b-41d4-a716-446655440000

-- 하이픈 제거 버전
SELECT REPLACE(UUID(), '-', '');
-- 결과 예: 550e8400e29b41d4a716446655440000

MySQL의 UUID()는 하이픈(-)이 포함된 소문자 형식으로 출력되며, 36자의 문자열로 구성됩니다.

실무 활용 예제

예제 1: 사용자 세션 관리

-- 사용자 로그인 시 세션 생성
INSERT INTO user_sessions (
    session_id,
    user_id,
    created_at,
    expires_at
) VALUES (
    UUID(),
    123,
    NOW(),
    DATE_ADD(NOW(), INTERVAL 24 HOUR)
);

-- 활성 세션 조회
SELECT session_id, user_id, created_at
FROM user_sessions
WHERE expires_at > NOW()
  AND user_id = 123;

예제 2: 파일 업로드 시 고유 파일명 생성

-- 파일 업로드 테이블
CREATE TABLE file_uploads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    original_filename VARCHAR(255),
    stored_filename VARCHAR(100) DEFAULT (CONCAT(REPLACE(UUID(), '-', ''), '.jpg')),
    upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    file_size INT,
    user_id INT
);

-- 파일 업로드 처리
INSERT INTO file_uploads (original_filename, file_size, user_id)
VALUES ('profile_photo.jpg', 2048576, 456);

2. UUID_TO_BIN()과 BIN_TO_UUID() - 효율적인 UUID 저장

MySQL 8.0에서 도입된 이 함수들은 UUID를 16바이트 바이너리 형식으로 변환하여 저장 공간을 크게 절약하고 성능을 향상시킵니다.

저장 공간 비교

문자열 UUID: 36바이트 (하이픈 포함)
바이너리 UUID: 16바이트 (약 55% 공간 절약)

실무 활용 예제

예제 1: 효율적인 주문 시스템

-- 주문 테이블 생성 (바이너리 UUID 사용)
CREATE TABLE orders (
    order_id BINARY(16) DEFAULT (UUID_TO_BIN(UUID())),
    customer_id INT,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10,2),
    INDEX idx_order_id (order_id)
);

-- 주문 생성
INSERT INTO orders (customer_id, total_amount)
VALUES (789, 149900.00);

-- 주문 조회 (사람이 읽기 쉬운 형태로)
SELECT BIN_TO_UUID(order_id) AS order_uuid,
       customer_id,
       order_date,
       total_amount
FROM orders
WHERE customer_id = 789
ORDER BY order_date DESC;

예제 2: API 키 관리 시스템

-- API 키 테이블
CREATE TABLE api_keys (
    id INT AUTO_INCREMENT PRIMARY KEY,
    key_uuid BINARY(16) DEFAULT (UUID_TO_BIN(UUID())),
    user_id INT,
    key_name VARCHAR(100),
    permissions JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

-- API 키 생성
INSERT INTO api_keys (user_id, key_name, permissions)
VALUES (123, 'Mobile App Key', '["read", "write"]');

-- API 키 검증 (문자열로 받은 키를 바이너리로 변환하여 조회)
SELECT user_id, permissions, is_active
FROM api_keys
WHERE key_uuid = UUID_TO_BIN('550e8400-e29b-41d4-a716-446655440000')
  AND is_active = TRUE
  AND (expires_at IS NULL OR expires_at > NOW());

3. GROUP_CONCAT() - 데이터 집계의 강력한 도구

GROUP_CONCAT()은 MySQL의 대표적인 고유 함수로, 그룹화된 데이터를 하나의 문자열로 연결합니다. 웹 애플리케이션에서 태그, 카테고리, 권한 등을 표시할 때 매우 유용합니다.

기본 문법과 옵션

GROUP_CONCAT([DISTINCT] 표현식 
             [ORDER BY 정렬기준] 
             [SEPARATOR '구분자'])

실무 활용 예제

예제 1: 사용자별 권한 목록

-- 사용자 권한 조회
SELECT u.username,
       u.email,
       GROUP_CONCAT(
           p.permission_name 
           ORDER BY p.permission_name 
           SEPARATOR ', '
       ) AS permissions
FROM users u
JOIN user_permissions up ON u.id = up.user_id
JOIN permissions p ON up.permission_id = p.id
WHERE u.is_active = TRUE
GROUP BY u.id, u.username, u.email
ORDER BY u.username;

예제 2: 상품별 태그 관리

-- 상품과 태그 정보
SELECT p.product_name,
       p.price,
       GROUP_CONCAT(
           DISTINCT t.tag_name 
           ORDER BY t.tag_name 
           SEPARATOR ' | '
       ) AS tags,
       COUNT(DISTINCT pt.tag_id) AS tag_count
FROM products p
LEFT JOIN product_tags pt ON p.id = pt.product_id
LEFT JOIN tags t ON pt.tag_id = t.id
WHERE p.is_active = TRUE
GROUP BY p.id, p.product_name, p.price
HAVING tag_count > 0
ORDER BY p.product_name;

예제 3: 월별 판매 실적 요약

-- 각 월의 베스트셀러 상품들을 문자열로 연결
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
       COUNT(*) AS total_orders,
       SUM(total_amount) AS total_revenue,
       GROUP_CONCAT(
           DISTINCT CONCAT(p.product_name, ' (', oi.quantity, '개)')
           ORDER BY SUM(oi.quantity * oi.unit_price) DESC
           SEPARATOR ', '
       ) AS top_products
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.order_date >= DATE_SUB(NOW(), INTERVAL 6 MONTH)
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month DESC;

4. GET_LOCK()과 RELEASE_LOCK() - 웹 애플리케이션의 동시성 제어

MySQL의 사용자 정의 락 함수들은 웹 애플리케이션에서 동시성 제어와 중복 처리 방지에 매우 유용합니다.

기본 사용법

-- 락 획득 시도 (10초 대기)
SELECT GET_LOCK('lock_name', 10);
-- 반환값: 1(성공), 0(타임아웃), NULL(오류)

-- 락 해제
SELECT RELEASE_LOCK('lock_name');
-- 반환값: 1(성공), 0(해제할 락 없음), NULL(오류)

실무 활용 예제

예제 1: 이메일 발송 큐 처리

-- 이메일 발송 처리 (중복 처리 방지)
DELIMITER $$

CREATE PROCEDURE ProcessEmailQueue()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE email_id INT;
    DECLARE lock_acquired INT DEFAULT 0;
    
    -- 이메일 큐 처리 락 획득
    SET lock_acquired = GET_LOCK('email_queue_processor', 1);
    
    IF lock_acquired = 1 THEN
        -- 대기 중인 이메일 처리
        SELECT id INTO email_id 
        FROM email_queue 
        WHERE status = 'PENDING' 
        ORDER BY created_at ASC 
        LIMIT 1;
        
        IF email_id IS NOT NULL THEN
            -- 이메일 상태 업데이트
            UPDATE email_queue 
            SET status = 'PROCESSING', 
                processed_at = NOW() 
            WHERE id = email_id;
            
            -- 실제 이메일 발송 로직 (생략)
            -- CALL SendActualEmail(email_id);
            
            -- 발송 완료 처리
            UPDATE email_queue 
            SET status = 'SENT', 
                sent_at = NOW() 
            WHERE id = email_id;
        END IF;
        
        -- 락 해제
        DO RELEASE_LOCK('email_queue_processor');
    END IF;
END$$

DELIMITER ;

예제 2: 재고 관리 시스템

-- 상품 주문 시 재고 차감 (동시성 제어)
DELIMITER $$

CREATE PROCEDURE OrderProduct(IN p_product_id INT, IN p_quantity INT, OUT p_result VARCHAR(50))
BEGIN
    DECLARE current_stock INT DEFAULT 0;
    DECLARE lock_name VARCHAR(100);
    DECLARE lock_acquired INT DEFAULT 0;
    
    SET lock_name = CONCAT('product_stock_', p_product_id);
    SET lock_acquired = GET_LOCK(lock_name, 5);
    
    IF lock_acquired = 1 THEN
        -- 현재 재고 확인
        SELECT stock_quantity INTO current_stock
        FROM products 
        WHERE id = p_product_id;
        
        IF current_stock >= p_quantity THEN
            -- 재고 차감
            UPDATE products 
            SET stock_quantity = stock_quantity - p_quantity,
                updated_at = NOW()
            WHERE id = p_product_id;
            
            SET p_result = 'SUCCESS';
        ELSE
            SET p_result = 'INSUFFICIENT_STOCK';
        END IF;
        
        -- 락 해제
        DO RELEASE_LOCK(lock_name);
    ELSE
        SET p_result = 'LOCK_TIMEOUT';
    END IF;
END$$

DELIMITER ;

5. INET_ATON()과 INET_NTOA() - IP 주소 처리의 효율성

웹 애플리케이션에서 IP 주소 로깅과 분석은 필수적입니다. MySQL의 IP 주소 변환 함수들을 사용하면 효율적으로 IP 데이터를 저장하고 검색할 수 있습니다.

기본 개념

INET_ATON(): IP 주소 문자열을 4바이트 정수로 변환
INET_NTOA(): 정수를 IP 주소 문자열로 변환

실무 활용 예제

예제 1: 사용자 접속 로그 시스템

-- 접속 로그 테이블
CREATE TABLE access_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    ip_address INT UNSIGNED,  -- IP를 정수로 저장
    user_agent TEXT,
    access_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    page_url VARCHAR(500),
    INDEX idx_ip_time (ip_address, access_time),
    INDEX idx_user_time (user_id, access_time)
);

-- 접속 로그 기록
INSERT INTO access_logs (user_id, ip_address, user_agent, page_url)
VALUES (
    123,
    INET_ATON('192.168.1.100'),
    'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36',
    '/dashboard'
);

-- IP 주소별 접속 통계
SELECT INET_NTOA(ip_address) AS ip_address,
       COUNT(*) AS access_count,
       COUNT(DISTINCT user_id) AS unique_users,
       MIN(access_time) AS first_access,
       MAX(access_time) AS last_access
FROM access_logs
WHERE access_time >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY ip_address
HAVING access_count > 100  -- 의심스러운 활동 탐지
ORDER BY access_count DESC;

예제 2: IP 기반 지역 제한 시스템

-- IP 차단 목록 관리
CREATE TABLE ip_restrictions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ip_start INT UNSIGNED,
    ip_end INT UNSIGNED,
    restriction_type ENUM('BLOCK', 'ALLOW', 'MONITOR'),
    region VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ip_range (ip_start, ip_end)
);

-- 특정 IP 대역 차단 설정
INSERT INTO ip_restrictions (ip_start, ip_end, restriction_type, region)
VALUES (
    INET_ATON('10.0.0.0'),      -- 시작 IP
    INET_ATON('10.255.255.255'), -- 끝 IP
    'BLOCK',
    'Internal Network'
);

-- IP 접근 권한 확인 함수
DELIMITER $$

CREATE FUNCTION CheckIPAccess(ip_addr VARCHAR(15))
RETURNS VARCHAR(20)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE restriction VARCHAR(20) DEFAULT 'ALLOW';
    DECLARE ip_num INT UNSIGNED;
    
    SET ip_num = INET_ATON(ip_addr);
    
    SELECT restriction_type INTO restriction
    FROM ip_restrictions
    WHERE ip_num BETWEEN ip_start AND ip_end
    ORDER BY 
        CASE restriction_type 
            WHEN 'BLOCK' THEN 1 
            WHEN 'MONITOR' THEN 2 
            WHEN 'ALLOW' THEN 3 
        END
    LIMIT 1;
    
    RETURN restriction;
END$$

DELIMITER ;

-- IP 접근 권한 확인 사용 예제
SELECT CheckIPAccess('192.168.1.100') AS access_status;

6. LAST_INSERT_ID()와 AUTO_INCREMENT - 웹 개발의 핵심

LAST_INSERT_ID() 함수는 웹 애플리케이션에서 연관 데이터를 입력할 때 필수적인 함수입니다.

실무 활용 예제

예제: 블로그 포스트와 태그 관리

-- 블로그 포스트 작성 과정
START TRANSACTION;

-- 1. 블로그 포스트 저장
INSERT INTO blog_posts (
    title, 
    content, 
    author_id, 
    status, 
    created_at
) VALUES (
    'MySQL 고유 함수 활용법',
    '이 글에서는 MySQL의 다양한 고유 함수들을 살펴봅니다...',
    456,
    'PUBLISHED',
    NOW()
);

-- 2. 방금 생성된 포스트 ID 가져오기
SET @post_id = LAST_INSERT_ID();

-- 3. 포스트 태그 연결
INSERT INTO post_tags (post_id, tag_name) VALUES
(@post_id, 'MySQL'),
(@post_id, 'Database'),
(@post_id, 'Tutorial'),
(@post_id, 'Programming');

-- 4. 포스트 통계 초기화
INSERT INTO post_statistics (
    post_id, 
    view_count, 
    like_count, 
    created_at
) VALUES (
    @post_id, 
    0, 
    0, 
    NOW()
);

COMMIT;

-- 생성된 포스트 정보 확인
SELECT p.id,
       p.title,
       p.author_id,
       GROUP_CONCAT(pt.tag_name ORDER BY pt.tag_name) AS tags,
       ps.view_count,
       ps.like_count
FROM blog_posts p
LEFT JOIN post_tags pt ON p.id = pt.post_id
LEFT JOIN post_statistics ps ON p.id = ps.post_id
WHERE p.id = @post_id
GROUP BY p.id;

7. JSON 함수들 - 현대 웹 개발의 필수 도구

MySQL 5.7부터 도입된 JSON 관련 함수들은 NoSQL과 관계형 데이터베이스의 장점을 결합한 강력한 기능을 제공합니다.

실무 활용 예제

예제: 사용자 설정 및 프로필 관리

-- 사용자 프로필 테이블 (JSON 활용)
CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    preferences JSON,
    profile_data JSON,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 사용자 프로필 데이터 저장
INSERT INTO user_profiles (user_id, preferences, profile_data) VALUES
(123, 
 JSON_OBJECT(
     'theme', 'dark',
     'language', 'ko',
     'notifications', JSON_OBJECT(
         'email', true,
         'push', false,
         'sms', true
     ),
     'timezone', 'Asia/Seoul'
 ),
 JSON_OBJECT(
     'displayName', '홍길동',
     'bio', 'MySQL 개발자',
     'skills', JSON_ARRAY('MySQL', 'PHP', 'JavaScript'),
     'social', JSON_OBJECT(
         'github', 'github.com/honggildong',
         'linkedin', 'linkedin.com/in/honggildong'
     )
 )
);

-- JSON 데이터 조회 및 활용
SELECT user_id,
       JSON_EXTRACT(profile_data, '$.displayName') AS display_name,
       JSON_EXTRACT(profile_data, '$.bio') AS bio,
       JSON_EXTRACT(preferences, '$.theme') AS theme,
       JSON_EXTRACT(preferences, '$.notifications.email') AS email_notifications,
       JSON_LENGTH(JSON_EXTRACT(profile_data, '$.skills')) AS skill_count
FROM user_profiles
WHERE user_id = 123;

-- 특정 스킬을 가진 사용자 검색
SELECT user_id,
       JSON_EXTRACT(profile_data, '$.displayName') AS display_name,
       JSON_EXTRACT(profile_data, '$.skills') AS skills
FROM user_profiles
WHERE JSON_CONTAINS(
    JSON_EXTRACT(profile_data, '$.skills'), 
    '"MySQL"'
);

-- 사용자 설정 업데이트
UPDATE user_profiles 
SET preferences = JSON_SET(
    preferences,
    '$.theme', 'light',
    '$.notifications.push', true
)
WHERE user_id = 123;

MySQL 고유 함수의 실무 가치

MySQL의 고유 함수들은 웹 애플리케이션 개발에서 실질적이고 즉시 활용 가능한 도구들입니다. UUID() 함수는 분산 환경에서의 고유성을 보장하고, GROUP_CONCAT()은 복잡한 데이터 집계를 간단하게 만들며, 락 관련 함수들은 동시성 제어 문제를 우아하게 해결합니다.

특히 웹 개발에서 자주 마주치는 IP 주소 처리, JSON 데이터 관리, 동시성 제어와 같은 실무 과제들을 MySQL의 고유 함수로 효율적으로 해결할 수 있습니다. 이러한 함수들을 적절히 활용하면 애플리케이션의 성능을 향상시키고, 코드의 복잡성을 줄이며, 개발 생산성을 크게 높일 수 있습니다.

MySQL의 지속적인 발전과 함께 새로운 함수들이 계속 추가되고 있으므로, 최신 버전의 기능들을 주기적으로 확인하고 실무에 적용해보는 것이 중요합니다. 특히 JSON 관련 함수들은 현대 웹 개발의 트렌드를 반영한 강력한 기능이므로, 적극적으로 활용해볼 만합니다.

[전문용어]

[UUID]: Universally Unique Identifier, 범용 고유 식별자로 전 세계적으로 유일한 값을 생성하는 표준
[UUID_TO_BIN]: UUID 문자열을 16바이트 바이너리 형식으로 변환하는 MySQL 8.0 함수
[GROUP_CONCAT]: 그룹화된 데이터를 하나의 문자열로 연결하는 MySQL 전용 집계 함수
[GET_LOCK/RELEASE_LOCK]: MySQL의 사용자 정의 락 함수로 애플리케이션 레벨 동시성 제어에 사용
[INET_ATON/INET_NTOA]: IP 주소 문자열과 정수 간 변환을 수행하는 MySQL 함수
[LAST_INSERT_ID]: AUTO_INCREMENT로 생성된 마지막 ID 값을 반환하는 함수
[JSON 함수]: MySQL 5.7부터 지원하는 JSON 데이터 타입 및 관련 처리 함수들

저작자표시 비영리 변경금지 (새창열림)

현재글MySQL 데이터베이스의 고유 함수 완벽 가이드

개발

python, 데이터베이스 마이그레이션, 개발툴, 오라클, Oracle, useEffect, 힌트, redux, 개발소스, React, TODOlist, 튜닝, 게시판, decode, 병렬처리, 기본문법, case 문, 도구, coalesce, 최적화,

Today :
Yesterday :

개발