MySQL

MySQL(RDS) - FULLTEXT 인덱스 2글자로 검색하기

풀텍스트 검색으로 DB에서 식당을 검색해서 조회하는 api를 만들었는데 이상하게 결과수가 너무 적게 나왔다. 알아보니 FULLTEXT 인덱스를 검색하는 글자수에 대한 최소값이 있었기 때문이었다. InnoDB 검색 인덱스의 경우 기본값은 innodb_ft_min_token_size=3 MyISAM 검색 인덱스의 경우 기본값은 ft_min_word_len=4 이다. 확인하는 명령은 다음과 같다. SHOW VARIABLES LIKE 'innodb_ft_min_token_size'; SHOW VARIABLES LIKE 'ft_min_word_len'; ㄱMySQL의 my.ini를 수정해주면 된다는데 나 같은 경우는 AWS의 RDS를 사용중이기 때문에 AWS RDS 콘솔에서 수정해줘야 한다. 먼저 RDS 콘솔에서..

MySQL - FULLTEXT Index 사용으로 검색 성능 높이기

텍스트 데이터를 검색하는 방법은 크게 두 가지가 있다. 첫 번째는 SQL LIKE 검색으로 모든 텍스트를 스캔하면서 검색 키워드가 존재하는지 검사하는 방법이다. 별도의 추가 작업 없이 간단하게 처리할 수 있으므로 일반적으로 널리 사용된다. 하지만 풀스캔 방식으로 데이터를 하나하나씩 살펴보기 때문에 검색할 텍스트 데이터가 많아지면 그 크기에 비례해 검색 속도가 느려지는 문제가 있다. 두 번째 방법은 Full-Text search 기능을 사용하는 것이다. 일반적으로 인터넷 검색 엔진들이 사용하는 방식으로 텍스트 데이터에서 미리 키워드를 추출해 인덱스를 구성하며 검색시는 이 인덱스를 조회해 빠르게 검색할 수 있다. MySQL Workbench 에서 컬럼에 FULLTEXT 인덱스 설정하는 법

MySQL Workbench 다중 컬럼 Unique 설정하기

두개 이상 컬럼의 조합으로 유니크 처리를 해야할 경우가 있다. 예를 들어 인스타그램 좋아요를 관리하는 테이블의 경우, 어뷰징을 막기 위해서는 좋아요를 누른 유저id와 사진id의 조합을 유니크하게 저장해야 한다. MySQL Workbench 툴에서 두 개의 컬럼 조합으로 unique 설정하는 법을 알아보자. 테이블 설정에 있는 Indexes 란에서 UNIQUE를 고르고 묶을 두 개의 컬럼을 선택 후 Apply 한다. 유니크 설정이 제대로 되었는지 확인해보자. select * from likes; insert into likes (user_id, photo_id) values (1, 100); user_id 1과 photo_id 100의 조합은 이미 있기 때문에 insert 할 시, Error Code: 1..

MySQL - null을 다른 값으로 대체하는 방법. ifnull( )

ifnull(컬럼명, null일 경우 대체값) 해당 컬럼의 값이 null을 반환할 때, 다른 값으로 출력하고 싶다면 ifnull 함수를 사용하면 된다. 예제 select s.first_name, ifnull(p.title, 'MISSING'), ifnull(p.grade, 0) from students s left join papers p on s.id = p.student_id; select s.first_name, avg(ifnull(p.grade, 0)) as average, if(avg(ifnull(p.grade, 0)) >= 75, 'PASSING', 'FAILING') as passing_status from students s left join papers p on s.id = p.stude..

MySQL Workbench - foreign key로 연결되어 있는 데이터 삭제

고객정보가 담긴 customers, 주문정보가 담긴 orders 테이블이 있다. orders 테이블의 customer_id 컬럼은 foreign key로, customers 테이블의 id컬럼을 참조한다. select * from customers; select * from orders order by customer_id; 4번 회원이 탈퇴해서 회원정보를 삭제하려고 한다. delete from customers where id = 4; Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails 라는 에러가 발생하며 삭제되지 않는다. 그 이유는 여러 개의 테이블이 foreign key 로 묶여있는 경우, 서로가..

MySQL - 두 개 이상의 테이블을 연결하기 join, left join

두 개 이상의 테이블을 서로 연결하여 데이터를 검색할 때 사용하는 방법이 바로 join이다. 두 개의 테이블을 마치 하나의 테이블인 것처럼 보여준다. 이외에도 cross join, self join 등이 있다. 가장 많이 쓰는 (inner) join과 left join의 문법을 알아보자. 공통으로 들어있는 값만 가져올 때는 join, 공통된 부분이 없더라도 한 쪽 테이블의 값을 모두 가져오고 싶을 때는 left join을 쓰면 된다. customers 테이블의 id컬럼과, orders 테이블의 customer_id 컬럼으로 두 테이블을 연결할 수 있다. select * from orders join customers on orders.customer_id = customers.id; select * fr..

MySQL Workbench에서 foreign key 설정하기

외래키(Foreign key) 외래키는 두 테이블을 서로 연결하는 데 사용되는 키이다. 외래키가 포함된 테이블을 자식 테이블이라고 하고 외래키 값을 제공하는 테이블을 부모 테이블이라한다. 외래키 값은 NULL이거나 부모 테이블의 기본키 값과 동일해야한다. 외래키로 지정할 두 테이블의 필드는 같은 데이터 타입이어야 한다. orders 테이블의 customer_id를, customers 테이블의 id 컬럼을 참조하는 외래키로 만들어보자. 먼저 customers 테이블의 id 컬럼과 데이터타입이 같아야 하므로 unsigned를 체크해 준다. Foreign Keys를 클릭하고 이름을 지어준다. 참조할 테이블을 고른다. 참조할 컬럼을 고른다 (orders 테이블의 customor_id 컬럼은 customers 테..

MySQL 조건문 사용하기 case, if

SQL에서 조건문을 사용하는 방법을 알아보자. 1. case 문 사용 case와 end는 한 쌍이고 위치는 select 와 from의 사이이다. -- 년도가 2000년 이상이면, 'Modern Book'이라고 하고, -- 그렇지 않으면, '20th Book' 이라고 하는, 새로운 컬럼 genre를 만드세요 select *, case when released_year >= 2000 then 'Modern Book' else '20th Book' end as genre from books; -- 재고가 0~50이면, * -- 51~100이면, ** -- 101~150이면, *** -- 그렇지 않으면, **** 로 표시하는 컬럼 stock을 만드세요 select *, case when stock_quantit..