mysql 에서 update시 순서대로 값 먹여서 업데이트하기 (mysql sequential numbering update)

study/mysql 2013. 2. 20. 03:43

mysql에서 가끔 보면 PK가 아닌 부분에서 indexing을 걸지않고 내부에서 indexing 역할하는 변수를 만드는데 이빨빠지는 경우가 많다 (잦은 경우에 많이 일어난다. 예를 들어서 특정 사용자별로 ordering을 줘야할 때 따로 order같은데서 남길 필요가 없는 데이터라서 날릴 때 이빨이 빠지게 된다)


이게 한 두개면 그냥 console에서 업데이트를 박아버리던가 할텐데 이게 양이 늘어나면 무지막지하게 귀찮으며 비효율적이다. 프로그래머가 병목자원이라 불리는 이 시대에 이딴 짓을 할 시간이 어디있겠는가.


이럴 때 방금 전에 올린 글  http://oddly.tistory.com/68 을 보면 변수를 선언해서 사용하는데 이걸 좀 응용해봤다


Table student 가 column 이 name, score, rank 가 있을 때


Hwangho / 70 / 1

Minho / 50 / 2

Changho / 40 / 3

Yeonho / 30 / 4

Hyunho / 10 / 5


같은 식으로 있는데 Minho가 빠져버리면 1,3,4,5로 이빨이 빠져버리는데.. 이럴 때 DB에서 싸그리 처리를 하려면..


SELECT @rank:=0;

UPDATE student SET rank=@rank:=@rank+1 ORDER BY rank


이런식으로 하면 1,2,3,4로 다시 이쁘게 들어간다


생각보다 이런 걸 쓸일이 많으므로 숙지해두는게 좋을 것 같다.

저작자표시 비영리 동일조건

'study > mysql' 카테고리의 다른 글

mysql select 할 때 순환자 넘버링하기 (iterator numbering)  (0) 2013.02.20
mysql - DISTINCT를 잘 이용하자  (0) 2012.07.03
PHP + MySQL에서 FETCH 방식에 따른 차이  (1) 2010.12.26
Posted by TY
,

mysql select 할 때 순환자 넘버링하기 (iterator numbering)

study/mysql 2013. 2. 20. 01:02

table test 의 column이


name, score가 있다고 할 때


John / 50

Matthew / 40

Cain / 70


일 때 


select @rownum:=@rownum+1 as num, t.* from test t, (SELECT @rownum:=0) r 


로 하면 


num/name/score


1 / John / 50

2 / Matthew / 40

3 / Cain / 70


로 되고


select @rownum:=@rownum+1 as num, t.* from test t, (SELECT @rownum:=0) r  ORDER BY score DESC


로 하면


1 / Cain / 70

2 / John / 50

3 / Metthew / 40


로 나온다

저작자표시 비영리 동일조건

'study > mysql' 카테고리의 다른 글

mysql 에서 update시 순서대로 값 먹여서 업데이트하기 (mysql sequential numbering update)  (0) 2013.02.20
mysql - DISTINCT를 잘 이용하자  (0) 2012.07.03
PHP + MySQL에서 FETCH 방식에 따른 차이  (1) 2010.12.26
Posted by TY
,

mysql - DISTINCT를 잘 이용하자

study/mysql 2012. 7. 3. 21:12

우선 DISTINCT에 대해서 설명을 하자면 중복된 결과를 제거하고 하나만 원하고자할 때 쓰이는 문법이다.


예를 들어서  DB에


a, a, a, b, b, c 


과 같이 존재할 경우에는


a,b,c만 나오게 된다. -.,- 흔히 COUNT와 엮어서 특정 사용자가 어떤 행동을 했는지 뽑아달라고 할 때 많이 썼던 것 같다.


최근에 약 50만건의 DB에서 중복되지 않게 email들을 뽑아달라는 요청이 왔었다. 


그래서 아무 생각없이. 'DISTINCT를 쓰면 되겠구나'라고 생각을 했는데, 음... 결론적으로 말하자면 결과가 별로 좋진 않았다.


테이블을 간략화하자면 다음과 같다.


idx / int / NOT NULL, PK, AUTO_INCREMENT, INDEX

email / varchar(100) / NOT NULL

name / varchar(30) / NOT NULL


동일한 사용자는 여러개의 name(많은 사람은 400개 정도 --;)를 가질 수 있고, 이것을 DISTINCT로 거르자는게 내 생각이었다.


그래서 아무 생각없이


SELECT DISTINCT email FROM table;


로 끝내려고 했는데, 음 캐릭터 이름도 나와야할 것 같아서 캐릭터 이름을 그냥 나중에 뒤로 추가했다.


SELECT DISTINCT email, name FROM table;


그랬더니 왠지 모르게 결과가 많이 늘었지만 (그 당시에는 인지하지 못했다) 무시하고 지나갔는데 오늘 생각해보니 이런 이유였던 것 같다.


 SELECT DISTINCT email, name FROM table 에서


DISTINCT는 (email, name) 짝을 하나의 결과물로 묶고 저 두개가 같은 결과들만 쳐낸 것이 아닐까 --; 그럼에도 불구하고 결과가 약 17만건이 줄었던 것이 여전히 충격적이다.


그래서 다음과 같은 경우에는 이제 아래와 같은 방법을 사용하면 될 것 같다...;;


SELECT email, name FROM table WHERE idx in (SELECT DISTINCT email FROM table) ORDER BY idx DESC


만약 저렇게 한다면 최신 캐릭터명으로 이메일들이 1개씩만 나오지 않을까 생각한다 -_-;


mysql만 쓴지 벌써 5년짼데 아직도 이딴 삽질을 하는거 보면 맨날 헛공부를 하는 것 같다.



저작자표시 비영리 동일조건

'study > mysql' 카테고리의 다른 글

mysql 에서 update시 순서대로 값 먹여서 업데이트하기 (mysql sequential numbering update)  (0) 2013.02.20
mysql select 할 때 순환자 넘버링하기 (iterator numbering)  (0) 2013.02.20
PHP + MySQL에서 FETCH 방식에 따른 차이  (1) 2010.12.26
Posted by TY
,

PHP + MySQL에서 FETCH 방식에 따른 차이

study/mysql 2010. 12. 26. 23:17
 흔히들 실습하거나 소규모 개발을 할 때 APM을 참 많이 쓰는 것 같아요. 저도 개인적으로 테스트 프로젝트를 만들거나 연습할 때는 WIN32 환경에서 APMSETUP을 설치해서 씁니다. 예전에는 리눅스나 솔라리스 계열에 VM웨어를 써서 연습하곤 했는데, 요즘은 간편하게 APM SETUP 쓰는 것을 즐겨합니다.

 문득 글을 써야 겠단 생각이 들어서, 예전에 누군가와 같이 에러를 잡고 싸매다가 알아낸 사실을 적어보고자 합니다.

다들 알고 계시겠지만 FETCH 방식은 크게 3개의 방식이 있습니다. 

우선 테이블을 다음과 같이 제시하겠습니다. 예제를 위해서 많이 과장을 했습니다 :)

- user
   - idx(PK, auto increment), name(VARCHAR), notes(VARCHAR)

 idx name notes 
 1  john  student
 2  eric  programmer
 3  max  designer

- user_import
   - idx(PK, auto increment), name(VARCHAR), notes(VARCHAR), assistant_id(INT, FK - user(idx))

 idx  name  notes  assistant_id
 1  susan  tester  2
 2  Mc  professor  1
 3  lucy  illustrator  3

그리고 쿼리는 동일하게 다음과 같이 제시합니다.

 SELECT * FROM user a LEFT JOIN user_import b ON a.idx=b.assistant_id;

그리고 각각의 첫 행을 print_r 함수로 출력하기 위해 간단히 소스를 작성했습니다. :) 

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<?php
	mysql_connect("127.0.0.1","root","apmsetup");

	mysql_select_db("test");

	$query = "SELECT * FROM user a LEFT JOIN user_import b ON a.idx=b.assistant_id;";

	ECHO "1. fetch_row<HR />";

	$rs = mysql_query($query);	
	print_r(mysql_fetch_row($rs));

	ECHO "<HR />2. fetch_assoc<HR />";

	$rs = mysql_query($query);
	print_r(mysql_fetch_assoc($rs));

	ECHO "<HR />3. fetch_array<HR />";

	$rs = mysql_query($query);
	print_r(mysql_fetch_array($rs));

	ECHO "<HR />";
	
?>

그러면 결과는 다음과 같은데요!



1은 쉽게 예상할 수 있지만, 2와 3은 좀 의아하지 않나요?

fetch를 할 때 동일한 column 명을 가졌는데 행 명을 배열의 키로 받는(즉, assoc)방식은 동일한 이름이 있을 경우 마지막에 나온 결과로 처리를 합니다. 

그래서 assoc에서는 애초에 name과 notes가 1개씩 밖에 나오지 않은 것이고, array에서는 1은 john인데 그 옆 name은 Mc이 나오는 결과가 나오게 됩니다.

그 당시, 기존 프로그램을 받아서 거의 개조 수준으로 바꾸는 일이었는데, 기존 개발자가 assoc을 써서 코드 분위기 맞춘다고 assoc을 쓰다가 JOIN을 할 일이 있엉서 썼다가 결과가 나오지 않아서 당황했던 기억이 나서 적어봤습니다 :)
저작자표시 비영리 동일조건

'study > mysql' 카테고리의 다른 글

mysql 에서 update시 순서대로 값 먹여서 업데이트하기 (mysql sequential numbering update)  (0) 2013.02.20
mysql select 할 때 순환자 넘버링하기 (iterator numbering)  (0) 2013.02.20
mysql - DISTINCT를 잘 이용하자  (0) 2012.07.03
Posted by TY
,

티스토리툴바