MySQL 의 고급? 기능들

1.group_concat

예: name을 기준으로 그룹핑 시 ...

select name from `user`
group by name;

 

하지만 이름이 같은 놈들을 다시 묶고 싶을때. 그리고 " , " 로 구분하고 싶을때

select name,group_concat(code) from `user`
group by name;

2.char_length

예: 특정문자열 길이를 가져와서 다시 정렬하고 싶으면...

select * from brand where name like '%석삼%' 
order by char_length(name) asc limit 5;

 

name 으로 like 조회후 char_length 로 name 길이를 얻고 , 그걸 다시 오름차순으로...

3.locate

select * from brand where name like '%석삼%' 
order by char_length(name) asc, locate('석삼',name) asc limit 5,5;

 

4.replace

 

예: A를 B로 치환 

update brand set name=REPLACE(name,'A','B') 
where id=1;

 

 

앞뒤 공백을 없애기도 가능.

update brand set name=REPLACE(name,' ','') where name like ' %';
update brand set name=REPLACE(name,' ','') where name like '% ';

 

5.now

select now() from brand limit 1;

 

밀리세컨드를 리턴받고 싶을때.

select now(3) from brand limit 1;

 

6.insert into ... select

실무에서 가끔 데이터 insert가 필요하다 . 일반적으로 아래와 같이 하겠지?

INSERT INTO `brand`(`id`, `code`, `name`, `edit_date`) 
VALUES (5, '108', '석삼', '2022-12-21 19:42:21');

 

일반적인 소량의 데이터를 insert시는 문제 없지만 타 table에서 데이터를 읽어서 insert 하는 경우는 아래와 같이 작성한다.

INSERT INTO `brand`(`id`, `code`, `name`, `edit_date`) 
select null,code,name,now(3) from `order` where code in ('004','005');

 

이처럼 order 테이블의 데이터를 쉽게 brand 테이블에 넣을수 있다.

 

7.insert into ... ignore

예: 1000개의 데이터 입력 전에 name이 있을시 insert  안하고, 없을시에 만 데이터를 insert 하는 작업을 어떻게 수행할까?

INSERT INTO `brand`(`id`, `code`, `name`, `edit_date`) 
VALUES (123, '108', '석삼', now(3));

 

이렇게 하면? 당연이 안되겠지? 왜냐면 name , brand 모두 unique 로 설정되었기 때문(뭐 가설이니까 이해해주라! ^^).

그래서 많은 친구분들은 not exists 문법을 써서 이런 문제를 해결하겠지.

INSERT INTO `brand`(`id`, `code`, `name`, `edit_date`) 
select null,'108', '석삼',now(3) 
from dual where  not exists (select * from `brand` where name='석삼');

 

뭐 괜찮은 방법이긴 하다만 이것보다 더 쉽게 작성하는 문법이 있다. 즉 insert into ... ignore 를 사용하면 된다.

INSERT ignore INTO `brand`(`id`, `code`, `name`, `edit_date`) 
VALUES (123, '108', '석삼', now(3));

 

error도 없고 exception 을 무시하므로 sql 수행에 영향끼친 행수는 0개, 그리고 중복입력도 안되고 꿀이다! 

8.select ... for update

begin;
select * from `user` where id=1 
for update;
// .....
// 업무로직 처리
// .....
update `user` set score=score-1 where id=1;
commit;

 

9.on duplicate key update

예: 7번과 비슷한 상황 ,입력하기전 조회를 하고 해당 컬럼에 데이터가 있으면 pass, 없으면 insert 하는 상황이 이다.

병렬환경이 아니라면 위 문제7 과 동일하게 처리할수 있지만 병렬처리 상황에서는 데이터 중복 상황이 발생한다. 물론 중복데이터 입력 방지를 위한 여러가지 방법이 있다. unique index 를 추가한다던지...

이때 사용할수 있는게 바로 on duplicate key updat 이다.

insert 전에 PK 혹은 unique index 있는지를 판단하는데 , 없으면 insert 하고 있으면 update 한다. 

INSERT  INTO `brand`(`id`, `code`, `name`, `edit_date`) 
VALUES (123, '108', '석삼', now(3))
on duplicate key update name='석삼',edit_date=now(3);

 

이 쿼리로 쉽게 중복데이터도 없이 , 데이터를 갱신하는 좋은 방법이다.

병렬환경에서 on duplicate key update 사용시 dead lock 상황이 발생할수 있니 실제 상황에 맞게 주의하여 사용하여야 한다.

10.show create table

특정 테이블의 스키마 정보를 보고 싶을때 자주 사용하는 desc 한다.

desc `order`;

테블의 스키마명,사이즈,타입,null 허용여부, pk , defalut 등등 정보들.

하지만 index 관련 정보는 보이지 않는다. 이때 사용하는 명령;

show index from `order`;

하지만 보기 불편한다 ? 그러면 아래 명령을 사용해 보라;

show create table `order`;

 

11.create table ... select

임시테이블 order_2022121819 생성,order 테이블과 동일한 , 빈 테이블을 생성한다.

create table order_2022121819 like `order`;

 

order 데이터를 생성된 임시 테이블 order_2022121819 에 넣는다.

 

insert into order_2022121819 select * from `order`;

 

 

부분 테이블의 백업시 유용하게 사용한다. (내기준)

 

12.explain

뭐 이건 다 알겠지? 쿼리 분석시....

explain select * from `order` where code='002';

 

13.show processlist

쿼리수행후 너무 속도가 느리다(진중라면)  해당명령어로 진행과정을 살펴볼수 있다.

mysql> show processlist;
+---------+------+-----------+-------+---------+------+-------+------------------+
| Id      | User | Host      | db    | Command | Time | State | Info             |
+---------+------+-----------+-------+---------+------+-------+------------------+
| 2212724 | root | localhost | test  | Sleep   | 70   |       | NULL             |
| 2212286 | root | localhost | NULL  | Query   | 0    | NULL  | show processlist |
+---------+------+-----------+-------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

프로세스를 찾았으니 죽여야지?

mysql> kill 2212724;
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;
+---------+------+-----------+-------+---------+------+-------+------------------+
| Id      | User | Host      | db    | Command | Time | State | Info             |
+---------+------+-----------+-------+---------+------+-------+------------------+
| 2212286 | root | localhost | NULL  | Query   | 0    | NULL  | show processlist |
+---------+------+-----------+-------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

1. Id    : Process id ,MySQL 이 관리하는 thread 번호
2. User  : Thread에 접속하고 있는 MySQL 사용자
3. Host  : 사용자가 접속하고 있는 호스트명 , IP 주소
4. Command : Thread의 현재 command 상태 .
5. Time  : Process가 현재 command상태에서 동작 시간
6. State : Thread의 상태에 대해 사람이 읽을 수 있는 형태의 정보
   Info  : 현 실행되고 있는 SQL ."SHOW PROCESSLIST" 최대 100자까지 표시전부 표시하려면 "SHOW FULL PROCESSLIST"

14.mysqldump

mysqldump -h 172.0.0.1 -u root -proot dbname > backup.sql​

 

mysqldump -h{ip} -P{port} -u{user} -p{password} {parameter1},{parameter2}.... > {document}.sql

뭐 너무 유명한? 명령어라 어느정도 개발?운영?서버 만져본 분이라면 다 아는 ...

이글의 타이틀과 약간 어긋난 놈이긴한데 일단 적어본다.

 

끝!