코린이[코딩 어린이]

MySQL에서 데이터를 삽입하려고 할 때 문제가 생겼던 부분입니다.

Data too long for column 'img' at row 1 ... 어쩌구 하는 에러가 나왔습니다.

이미지를 넣을때 생긴 에러이며, 처음에는 문제없이 이미지가 삽입되는걸 확인했습니다.

일단 이미지 파일을 BLOB 형식으로 DB에 저장하다 나온 에러인데, 어떤 이미지를 넣으면 들어가고 어떤 이미지를 넣으면 안들어가는 현상이어서 여기저기 찾아보니 BLOB의 최대 용량 때문이었습니다. BLOB의 용량이 최대 64kb... 그래서 더 큰 용량이 없는지 확인해보니 그 우로 MEDIUMBLOB, LONGBLOB이 있었습니다.

BLOB 종류

문자열은 몇가지가 있는걸로 알고 있었는데 BLOB도 종류가 4가지나 있었습니다...

그래서 BLOB -> MEDIUMBLOB으로 데이터타입을 변경해주었더니 문제가 말끔하게 해결되었습니다.

웬만해선 MySQL에 이미지를 직접 저장하는 일이 없었기에 잘 몰랐던 내용인데 이렇게 또 한가지 새로운 사실을 깨우치게 되었습니다..!

MySQLWorkbench 응용 프로그램이 자동으로 꺼지는 것에 대한 내용입니다.

지금까지 잘 사용하고 있던 MySQLWorkbench가 갑자기 실행되지 않는 현상이 발생했습니다.

Mac 업데이트를 미루고 미루다 이번에 업데이트를 했는데 업데이트를 하면서 생겼던 에러였던거 같습니다.

보니까 MySQLWorkbench버전이 8.0.20이었는데 새로나온 버전들을 확인해보니 8.0.33까지 나왔던 것이었습니다.

MySQLWorkbench Update 바로가기

여기서 새로운 버전을 받아 실행했더니 문제없이 잘 실행되었습니다.

SELECT로 데이터를 불러올 때 가상 칼럼을 추가해서 불러오는 방법입니다.

아래처럼 _id, createdAt, text만 있는 테이블에 type 칼럼을 추가 시키고 싶을 때 사용하는 방법입니다.

기본적으로 아래처럼 테이블을 불러오면 _id, createdAt, text까지의 칼럼을 불러오게 됩니다.

SELECT * FROM 테이블;

가상 칼럼을 추가하는 방법은 아래처럼 추가해주면 됩니다.

SELECT *, 'like' AS 'type' FROM 테이블;

테이블에 DATETIME, TIMESTAMP등일 경우 사용가능한 내용입니다.

데이터 불러오기를 사용하다보면 오늘부터 어제까지의 데이터, 오늘부터 일주일 전까지의 데이터 등등 기간에 따라 데이터를 불러와야 할 때가 있습니다. 이럴때 DATE_ADD 함수를 사용해서 날짜나 시간에 일정한 값을 더하고 결과를 반환시킬 수 있습니다.

# 기본 구문
DATE_ADD(date, INTERVAL value unit);

# 시간 더하기
SELECT DATE_ADD(NOW(), INTERVAL 3 DAY);
# 시간 빼기
SELECT DATE_SUB(NOW(), INTERVAL 3 DAY);

파라미터

• date: 날짜나 시간 값을 나타내는 인자입니다.
• value: 더할 값입닏. 음수 값을 사용하면 뺄 수도 있습니다.
• unit: 더할 값의 단위를 지정합니다. DAY, MONTH, YEAR, HOUR, MINUTE, SECOND등이 있습니다.

이제부터 BETWEEN을 사용해서 특정 범위 내의 값을 검색하는 방법입니다.

BETWEEN: 특정 범위 내의 값을 검색하는데 사용됩니다.

SELECT * FROM table_name WHERE table_name_column BETWEEN DATE_ADD(NOW(), INTERVAL -7 DAY) AND NOW();

위의 쿼리는 현재 날짜를 기준으로 7일 전부터 현재까지의 값을 검색합니다.

위와 비슷한 형태로 날짜 범위와 기간을 조절해서 쿼리를 생성할 수 있습니다.

MySQL을 사용하던 도중에 아래와 같은 오류가 발생했습니다. 

Expression #26 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'commentCounts.commentsCnt' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

호환관련 내용으로 보입니다.

저의 경우에는 NN ( Not Null )을 체크해줌으로써 해결했습니다.

수정 전

수정 후

위처럼 NN부분을 체크해줌으로써 해결해주었습니다.

https://stackoverflow.com/questions/41887460/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-inc

스택플로우에 비슷한 내용으로 올라와 있던 글입니다.

Javascript의 indexOf 메서드와 비슷한 INSTR 함수에 관한 내용입니다.

INSTR 함수는 특정 문자열에서 찾고자하는 문자가 있는지 확인하고 있으면 시작 자리수를, 없는 경우에는 0을 리턴해줍니다.

기본 사용법

SELECT INSTR("hello", "lo");
-> 4

SELECT INSTR("hello", "new");
-> 0

WHERE에서 선택한 column에 일치하는 내용이 있는것을 찾을 때

SELECT * FROM tests WHERE INSTR(tags, '안녕');

위 테이블에 tags에 안녕이라는 문자가 포함된 모든 column을 가져옵니다.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at Line 1

위 에러 내용은 기본적인 문법 오류입니다.

그런데 이번에 위 오류가 특이한 상황에 떠서 적은 내용입니다.

sql

const _sql = `UPDATE messages SET read='1', updatedAt='${new Date()}' WHERE _id='${_id}';`;

위처럼 ''가 잘못되거나 오타가 있는 내용이 아니었습니다.

그런데... 칼럼 중에 read라는 칼럼을 사용하고 있었는데, 생성할 때, 불러올 때는 전혀 문제가 없었는데 UPDATE를 사용하려고 하니까 계속해서 위 에러 구문이 나오는 상황이었습니다...!

read라는 예약어가 있었던 것 같습니다.

뭔가 검색해봐도 없길래 문제없는 줄 알았더니 예약어 목록을 찾아보니 READ라는 이름이 있었습니다..ㅠ

그래서 read의 이름을 바꿔주고 에러를 해결했습니다. 역시 있을법한 이름은 사용하지 않는 것이 좋을 듯합니다.

예약어 목록

MySQL 예약어 바로가기

테이블의 정렬을 ORDER BY로 정렬할 때 여러 컬럼으로 정렬하는 방법입니다.

예를들어  SELECT * FROM 테이블로 데이터를 부를때 ASC( 오름차순 ), DESC( 내림차순 )을 사용해서 데이터들이 정렬되어 나오게 해줍니다.

예시

createdAt = 생성한 날짜

// 생성한 날짜 내림차순
SELECT * FROM users ORDER BY createdAt DESC;

다중으로 사용할 때

age = 나이

// 생성한 날짜 내림차순으로 정렬한 이후에 나이 오름차순
SELECT * FROM users ORDER BY createdAt DESC, age ASC;

ORDER BY를 다중으로 사용하면 앞쪽 ( createdAt )부터 순차적으로 진행되기 때문에 순서를 잘 생각해야 합니다.

Err. Column 'type' in where clause is ambiguous 모호한 유형의 열

이번에 JOIN을 사용하는 과정에서 위와 같은 에러가 나타났습니다.

두 테이블 모두 type이라는 컬럼명을 사용했는데, 두 개의 테이블에 type이 충돌하면서 일어난 에러였던 것 같습니다.

에러가 난 원인

SELECT 테이블1.*, 테이블2.coverGenre, 테이블2.coverPlot, 테이블2.coverTitle, 테이블2.coverImage, 테이블2.coverType FROM 테이블1 JOIN 테이블2 ON 테이블1.cover = 테이블2._id WHERE type='${type}' ORDER BY createdAt DESC LIMIT 20;

위부분에서 WHERE에 type이 어떤 type인지 알 수 없기에 일어난 에러입니다.

그래서 아래처럼 어떤 테이블의 type인지 정확하게 알려주면 에러가 해결됩니다.

// 변경 전
WHERE type='${type}'

// 변경 후
WHERE 테이블1.type='${type}'

AUTO_INCREMENT값을 초기화하는 방법입니다.

작업을 하다보면 아래처럼 Primary key를 자동으로 증가시키는 내용을 만들때가 있습니다.

위처럼 만들어진 키는 이전 데이터가 지워진다고해서 값이 재조정되지 않습니다.

세번째 네번째 PK를 지우고 다섯번째 PK를 생성하면 3번이 만들어지지 않고, 5번이 생성됩니다.

이럴때, 이 AUTO_INCREMENT 값을 초기화 하려면 아래처럼 쿼리를 실행시켜주면 됩니다.

테이블 명과 AUTO_INCREMENT를 시작할 값을 적어주면 됩니다. ( tests: 테이블명, 10: 시작할 번호 )

ALTER TABLE tests AUTO_INCREMENT = 10;

**주의: 새로 시작하는 값보다 높은값이 존재하면 안됩니다 !

아래 쿼리문은 현재 모든 값을 1부터 재조정하고, 다음 값을 마지막 번호 + 1로 정하는 명령어입니다.

ALTER TABLE tests AUTO_INCREMENT = 1;
SET @COUNT = 0;
UPDATE tests SET _id=@COUNT:=@COUNT+1;

아래처럼 초기화할 수 있습니다.