알아두면 편리한 MySQL 쿼리문

SQL 파일 불러오기 시 문자셋 강제 지정

SQL 파일을 불러올 때, --default-character-set 옵션을 사용하여 문자셋을 지정할 수 있습니다.

 

파일을 EUC-KR 문자셋으로 불러오기 (SQL 파일이 EUC-KR로 작성된 경우):

mysql -u root -p --default-character-set=euckr smartweb < C:/Apache24/htdocs/gasa.sql

 

파일을 UTF-8 문자셋으로 불러오기 (SQL 파일이 UTF-8로 작성된 경우):

mysql -u root -p --default-character-set=utf8 smartweb < C:/Apache24/htdocs/gasa.sql

 

이미 깨진 데이터가 테이블에 삽입되어 있는 경우, 해당 데이터를 삭제하고 다시 삽입해야 할 수 있습니다.

memo 컬럼에서 잘못된 문자열을 포함한 레코드를 찾아 삭제하거나 수정할 수 있습니다.

DELETE FROM 테이블명 WHERE memo LIKE '%\xFE\xC0\xCC\xBE%';

이 명령어로 깨진 문자가 포함된 레코드를 삭제할 수 있습니다.

MyISAM에서 InnoDB로 변경하는 쿼리:

ALTER TABLE 테이블명 ENGINE = InnoDB;

테이블의 저장 엔진을 MyISAM에서 InnoDB로 변경합니다.

 

InnoDB 사용 시 특징:
InnoDB는 트랜잭션을 지원하고, 외래 키 제약 조건을 지원합니다.
InnoDB의 성능은 대량의 데이터 삽입이나 복잡한 쿼리에서 더 좋은 성능을 보입니다.
InnoDB는 **행 잠금(row-level locking)**을 지원하고, **테이블 잠금(table-level locking)**이 아니라 더 세밀한 잠금을 제공합니다.

 

주의 사항:
InnoDB로 변경한 후 테이블에 외래 키나 트랜잭션 관련 작업을 설정할 수 있습니다.
변경 후에도 기존 데이터는 그대로 유지됩니다. 단, 데이터 타입이나 인덱스 설정을 변경하려면 추가적인 설정이 필요할 수 있습니다.

 

MyISAM과 InnoDB는 MySQL 데이터베이스에서 가장 많이 사용되는 두 가지 테이블 스토리지 엔진입니다. 이 두 엔진은 각기 다른 특성과 장점을 가지고 있으며, 선택은 주로 애플리케이션의 요구 사항에 따라 다릅니다.

1. MyISAM

MyISAM은 MySQL의 기본 스토리지 엔진이었으며, 읽기 작업에 대해 최적화된 성능을 제공합니다. 그러나 트랜잭션 처리나 외래 키 제약 등을 지원하지 않아서, 여러 가지 제약이 있습니다.

주요 특징:

• 트랜잭션 미지원: MyISAM은 트랜잭션을 지원하지 않습니다. 즉, 데이터베이스 작업의 일관성을 보장할 수 없습니다.
• 외래 키 미지원: MyISAM은 외래 키 제약을 지원하지 않습니다. 따라서 관계형 데이터베이스의 데이터 무결성을 유지할 수 없습니다.
• 빠른 읽기 성능: MyISAM은 읽기 작업에 최적화되어 있어, 읽기 성능이 매우 빠릅니다.
• 테이블 잠금: MyISAM은 테이블 수준에서 잠금을 사용합니다. 즉, 테이블에 대한 작업이 동시에 여러 사용자에게 영향을 미칠 수 있습니다.
• FULLTEXT 검색 지원: MyISAM은 FULLTEXT 검색을 지원하여 텍스트 데이터에 대해 효율적인 검색을 할 수 있습니다.
• 불완전한 복구: MyISAM은 시스템 장애가 발생했을 때 자동으로 복구할 수 있는 기능이 부족하여, 데이터 손실이 발생할 수 있습니다.

사용 예시:

• 읽기 위주의 애플리케이션: 예를 들어, 주로 조회 작업이 많은 웹사이트나 블로그에서 사용할 수 있습니다.
• 검색이 중요한 경우: FULLTEXT 검색을 지원하기 때문에 검색 기능이 중요한 애플리케이션에서 사용될 수 있습니다.

2. InnoDB

InnoDB는 MySQL의 기본 스토리지 엔진으로, ACID 트랜잭션을 지원하고, 데이터 무결성과 일관성 있는 작업을 보장합니다. InnoDB는 MyISAM보다 더 많은 기능을 제공하며, 대규모 트랜잭션을 처리하는 애플리케이션에서 사용됩니다.

주요 특징:

• 트랜잭션 지원: InnoDB는 ACID(Atomicity, Consistency, Isolation, Durability) 트랜잭션을 지원하여 데이터 무결성을 보장합니다.
• 외래 키 지원: InnoDB는 외래 키 제약을 지원하여 데이터 무결성을 보장합니다.
• 행 수준 잠금: InnoDB는 행 수준 잠금을 사용하여 다중 사용자가 동시에 작업을 처리할 수 있습니다. 이는 동시성이 중요한 애플리케이션에서 유리합니다.
• 빠른 쓰기 성능: InnoDB는 쓰기 작업에 있어서 MyISAM보다 성능이 떨어질 수 있지만, 데이터 안전성을 보장하며, 특히 대규모 데이터베이스에서는 성능이 우수합니다.
• 자동 복구 기능: InnoDB는 장애 발생 시 자동 복구 기능을 제공하여 데이터 손실을 방지할 수 있습니다.
• 버퍼 풀: InnoDB는 데이터를 디스크가 아닌 메모리에 캐시하여 성능을 최적화합니다.

사용 예시:

• 트랜잭션이 필요한 애플리케이션: 금융 거래, 쇼핑몰, 은행 시스템 등 트랜잭션을 지원해야 하는 애플리케이션에서 사용됩니다.
• 데이터 무결성이 중요한 시스템: 외래 키 제약과 트랜잭션을 통한 데이터의 일관성을 유지해야 하는 경우 사용됩니다.

3. 주요 차이점 비교

특성MyISAMInnoDB

트랜잭션 지원	지원하지 않음	지원 (ACID)
외래 키 제약	지원하지 않음	지원
잠금 방식	테이블 잠금	행 수준 잠금 (더 높은 동시성 지원)
읽기 성능	매우 빠름	비교적 느림 (하지만 대량 데이터 처리 시 유리)
쓰기 성능	빠르나 데이터 무결성 보장이 없음	안정적이지만 상대적으로 더 느릴 수 있음
복구 기능	자동 복구 없음	자동 복구 기능 제공
데이터 무결성	무결성 보장되지 않음	데이터 무결성 보장
지원하는 기능	FULLTEXT 검색	외래 키, 트랜잭션, 데이터 무결성 보장

4. 어떤 경우에 MyISAM을 사용해야 할까요?

• 읽기 중심의 데이터베이스: 주로 읽기 작업이 많은 시스템에서는 MyISAM이 성능 상 유리할 수 있습니다.
• 검색 성능이 중요한 경우: MyISAM은 FULLTEXT 검색을 지원하므로, 텍스트 기반의 검색이 중요한 시스템에 적합합니다.

5. 어떤 경우에 InnoDB를 사용해야 할까요?

• 트랜잭션이 중요한 시스템: 금융 시스템, 전자상거래, 예약 시스템 등 ACID 트랜잭션을 요구하는 시스템에서는 InnoDB가 필수적입니다.
• 데이터 무결성이 중요한 시스템: 데이터의 일관성과 무결성을 보장해야 하는 경우 InnoDB가 적합합니다.
• 다수의 사용자와 동시에 작업이 이루어지는 시스템: InnoDB는 행 수준 잠금을 사용하여 다수의 사용자가 동시에 작업을 수행할 수 있도록 지원합니다.

memo 칼럼의 내용에서 <br />와 공백을 <br />로 바꾸는 쿼리문은 다음과 같습니다. 여기서는 REPLACE 함수와 TRIM을 사용하여 문자열을 변환할 수 있습니다.

UPDATE 테이블명
SET memo = REPLACE(memo, '<br /> ', '<br />')
WHERE memo LIKE '%<br /> %';

 

설명:

• REPLACE(memo, '<br /> ', '<br />'): memo 칼럼의 값에서 <br /> 뒤에 공백이 있는 부분을 <br />로 변경합니다.
• WHERE memo LIKE '%<br /> %': <br /> 뒤에 공백이 포함된 레코드들만 업데이트합니다.

이 쿼리는 memo 칼럼에서 <br />와 공백을 <br />로 변경하는 작업을 수행합니다.

추가 고려 사항:

• 백업: 실제 데이터를 수정하기 전에 백업을 수행하는 것이 좋습니다.
• 테스트: 먼저 SELECT 문을 사용해 변경될 데이터를 확인한 후, UPDATE 문을 실행하세요.

테스트 쿼리 예시:

SELECT memo FROM 테이블명 WHERE memo LIKE '%<br /> %';

그 후, 문제가 없으면 UPDATE를 실행해 주세요.

 

WHERE memo LIKE '%<br />%' 조건은 옵션이며, 이 조건이 없으면 memo 칼럼의 모든 레코드에 대해 UPDATE가 실행됩니다.

이 조건의 필요성:

1. 성능 최적화: memo 칼럼의 모든 데이터에서 <br />을 <br>로 변경하는 것이 아니라, 실제로 <br />가 포함된 데이터만 수정하도록 제한합니다. 불필요한 업데이트를 방지해 성능을 최적화할 수 있습니다.
2. 불필요한 수정 방지: <br />를 <br>로 바꾸는 작업이 이미 완료된 데이터가 있을 수 있습니다. WHERE memo LIKE '%<br />%' 조건을 추가하면, 이미 변경된 데이터는 다시 수정되지 않도록 방지합니다. 즉, 실제로 <br />가 포함된 레코드만 선택하여 수정합니다.

MySQL에서 테이블 이름을 변경하려면 RENAME TABLE 명령을 사용합니다. zetyx_board_gasa 테이블의 이름을 바꾸고자 할 경우, 다음과 같은 SQL 쿼리를 사용할 수 있습니다:

RENAME TABLE table_name TO new_table_name;

여기서 new_table_name은 새 테이블 이름으로 바꾸고 싶은 이름으로 변경해 주세요. 

주의 사항:

1. 데이터베이스 연결 상태: 테이블 이름을 변경할 때, 해당 테이블에 대한 액세스 권한이 필요합니다. 데이터베이스에 연결된 상태에서 이 명령을 실행해야 합니다.
2. 테이블이 잠겨있지 않음: 테이블에 다른 쿼리가 실행 중일 때는 이름을 변경할 수 없습니다. 작업을 완료한 후 이름을 변경해야 합니다.

이 명령을 실행하면 테이블 이름이 성공적으로 변경됩니다.

 

데이터 가 존재하는 테이블 no 정렬 작업

-- STEP 1: 임시 테이블 생성 및 데이터 복사
CREATE TABLE temp_lyrics LIKE lyrics;
INSERT INTO temp_lyrics (subject, memo)
SELECT subject, memo
FROM lyrics
ORDER BY no;

-- STEP 2: 원본 테이블 삭제
DROP TABLE lyrics;

-- STEP 3: 임시 테이블 이름 변경
RENAME TABLE temp_lyrics TO lyrics;

-- STEP 4: FULLTEXT 인덱스 재설정
ALTER TABLE lyrics ADD FULLTEXT (subject);

-- STEP 5: AUTO_INCREMENT 초기화
ALTER TABLE lyrics AUTO_INCREMENT = 1;