꿈을 꾸는 블루비

UNION 명령의 일반적인 MySQL 오류

Incorrect usage of UNION and ORDER BY

이 문제를 해결하기 위해서는 select 문을 괄호(parenthesis) 로 감싸주어야 합니다.

(SELECT col1,col2,col3 FROM table1)
UNION
(SELECT cola,colb,colc FROM table2)

우분투 서버의 경우 mysql 원격 접속을 하기 위해서
/etc/mysql/my.cnf 파일의 bind-address 부분은 주석처리해준다

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1
#

우분투를 설치시 mysql character set 은 latin1 으로 되어 있기 때문에 my.cnf 파일에

다음 항목을 추가 하거나 수정해준다. 빨간 부분은 추가된 부분, 파란색 부분은 수정한 부분

----------------------------------------------------------
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
default-character-set = euckr

[mysqld]
language = /usr/share/mysql/korean

character-set-client-handshake=FALSE
init_connect="SET collation_connection = euckr_korean_ci"
init_connect="SET NAMES euckr"

default-character-set = euckr
character-set-server = euckr
collation-server = euckr_korean_ci

[mysqldump]
default-character-set = euckr

[mysql]
default-character-set = euckr
-----------------------------------------------

데이터 베이스와 테이블의 Character set 이 맞지 않는 경우에도 한글이 깨짐 현상이 일어 나는데,
이문제를 위해서 데이터 베이스와 테이블의 Character set 동일하게 세트해줘야 합니다.










데이터베이스 Character 변경

mysql> ALTER DATABASE [Database] DEFAULT CHARACTER SET utf8;

테이블 Character 변경

mysql> ALTER TABLE [Table] DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

MySQL root 비밀번호 분실시 권한 없이 MySQlL 에 접속하여 새로운 비번을 부여후 다시 MySQL을 가동하면 해결됩니다.

# cd /usr/local/mysql/bin //mysql이 설치된 디렉토리의 bin
# killall mysqld
# ./safe_mysqld --skip-grant &
# mysql
mysql> use mysql;
mysql> update user set password=PASSWORD('패스워드') where user='root';
mysql> flush privileges;
mysql> quit;
# killall mysqld
# ./safe_mysqld -uroot &

썬(sun.com) 인 MySQL을 인수 했다는 기사가 나왔군요.
갑작스런 소식인지라 향후 MySQL 행보가 어떨지.

오는 28일 http://www.sun.com/MySQL/download 에서
다운로드 서비스를 받을 수 있다고 합니다.

http://mysql.com 사이트에 들어가 보니 역시 로고가 바뀌었군요.. ㅎㅎ

출처 : http://www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql/

In this article I’ll show several ways to emulate a FULL OUTER join on a RDBMS that doesn’t support it, as is the case with even the most recent versions of MySQL. This useful query is surprisingly tricky to get right.

Introduction

A standard SQL FULL OUTER join is like a LEFT or RIGHT join, except that it includes all rows from both tables, matching them where possible and filling in with NULLs where there is no match. I’ll illustrate that for clarity. Here are two of my favorite tables, apples and oranges:

apples

Variety	Price
Fuji	5.00
Gala	6.00

oranges

Variety	Price
Valencia	4.00
Navel	5.00

I’ll join them on price. Here is the left join:

select * from apples as a
    left outer join oranges as o on a.price = o.price

variety	price	variety	price
Fuji	5	Navel	5
Gala	6	NULL	NULL

And the right join:

select * from apples as a
    right outer join oranges as o on a.price = o.price

variety	price	variety	price
NULL	NULL	Valencia	4
Fuji	5	Navel	5

The FULL OUTER JOIN of these two tables, on price, should give the following result:

variety	price	variety	price
Fuji	5	Navel	5
Gala	6	NULL	NULL
NULL	NULL	Valencia	4

That’s the result I’ll be working toward in this article. Here is a script to create and populate the example tables, so you can follow along:

create table apples (variety char(10) not null primary key, price int not null);
create table oranges (variety char(10) not null primary key, price int not null);
insert into apples(variety, price) values('Fuji',5),('Gala',6);
insert into oranges(variety, price) values('Valencia',4),('Navel',5);

Method 1: two JOINs and a UNION

One method to simulate a full join is to take the union of two outer joins, for example,

select * from apples as a
    left outer join oranges as o on a.price = o.price
union
select * from apples as a
    right outer join oranges as o on a.price = o.price

This gives the desired results in this case, but it isn’t correct for all cases. Suppose there are duplicate records in the tables (remove the primary key and insert twice to create this situation). UNION eliminates duplicates, which a full join doesn’t do. UNION ALL isn’t the right answer either, because it will cause spurious duplicates. In fact, UNION generates two independent result sets and then combines them, so there is no way to get around this, because the two result sets need to “know about each other” to produce the right results.

There are legitimate cases where duplicate results are expected and correct. For instance, even when the rows are unique, selecting only certain columns, in which there are duplicates, could cause this situation. This doesn’t apply in relational theory, because a set never has duplicates no matter what, but it does in SQL.

Method 2: UNION ALL and an exclusion join

One way to make UNION include only the duplicates I want is to use an exclusion join to eliminate anything from the second result that is already included in the first, like this:

select * from apples as a
   left outer join oranges as o on a.price = o.price
union all
select * from apples as a
   right outer join oranges as o on a.price = o.price
where a.price is null;

This handles duplicate rows correctly and doesn’t include anything it shouldn’t. It’s necessary to use UNION ALL instead of plain UNION, which would eliminate the duplicates I want to keep. This may be significantly more efficient on large result sets, since there’s no need to sort and remove duplicates.

Method 3: use a mutex table

There’s a case where UNION won’t work: older versions of MySQL don’t support it. All is not lost, though.

I’ve written several articles explaining how to start with a set of mutually exclusive numbers (which I informally call a “mutex table“), then use the mutual exclusivity of the numbers to join things together in interesting ways. This lets me simulate subqueries and unions on earlier versions of MySQL, for example. This approach seems like it might work well here, too. For the following queries I’ll assume my mutex table has the values 0 and 1. Here’s a baseline mutex query for these two tables:

select * from mutex
    left outer join apples as a on i = 0
    left outer join oranges as o on i = 1;

i	variety	price	variety	price
0	Fuji	5	NULL	NULL
0	Gala	6	NULL	NULL
1	NULL	NULL	Valencia	4
1	NULL	NULL	Navel	5

Of course this isn’t a full join. If I want to full join on price, naturally, I need to include price in the join criteria somewhere, and the query above doesn’t even mention the price. But it’s a starting point for tinkering.

The mutex values in the leftmost column, combined with the join criteria, ensure that every row in the two tables gets included on its own row. The mutual exclusivity causes the Navel row not to be matched to the Fuji row, even though they have the same price. The correct behavior of a full join on price is to “fill in” the NULL values where the prices are equal. This modification to the join criteria will fill it in:

select * from mutex
    left outer join apples as a on i = 0
    left outer join oranges as o on i = 1 or a.price = o.price;

The or a.price = o.price relaxes the mutual exclusivity, telling the join to keep the rows separated unless they have the same price:

i	variety	price	variety	price
0	Fuji	5	Navel	5
0	Gala	6	NULL	NULL
1	NULL	NULL	Valencia	4
1	NULL	NULL	Navel	5

That’s getting closer. There is a spurious row, though. The Navel row at the bottom of the result set shouldn’t be there; it has already been matched to the Fuji row earlier, so there’s no need to include it with all those NULLs as though there were no matching row in apples. Can I eliminate the Navel row without eliminating the Valencia row?

That turns out to be harder to do. I stared at it for a while, thinking I could include a WHERE clause that would eliminate spurious rows based on the value of i, but after a bit I got a reality check: the row has already been included above, and WHERE clauses work a row at a time, so there’s no way to assert something about one row while applying the WHERE clause to another row. This simple fact is all I needed to realize there’s no way to eliminate the Navel row with the given information.

What I can do, though, is stack another copy of the apples table onto the right-hand side of the results thus far, matching them to the oranges values and confining them to rows with mutex value 1 instead of 0. Now I can write a WHERE clause to see if a row in the i = 1 part of the result set matches a row in the i = 0 part. I’ll write it without the WHERE clause to start:

select * from mutex
   left outer join apples as a on i = 0
   left outer join oranges as o on i = 1 or a.price = o.price
   left outer join apples as a2 on i = 1 and a2.price = o.price;

i	variety	price	variety	price	variety	price
0	Fuji	5	Navel	5	NULL	NULL
0	Gala	6	NULL	NULL	NULL	NULL
1	NULL	NULL	Valencia	4	NULL	NULL
1	NULL	NULL	Navel	5	Fuji	5

Now there is a way to tell between the Navel row, which I don’t want, and the Valencia, which I do: the Navel has non-NULL values in the rightmost copy of apples, but the Valencia doesn’t. All I have to do is eliminate rows that have matching values:

select * from mutex
   left outer join apples as a on i = 0
   left outer join oranges as o on i = 1 or a.price = o.price
   left outer join apples as a2 on i = 1 and a2.price = o.price
where o.price is null or a2.price is null

If the WHERE clause is hard to understand, perhaps it’s easier to think of it this way: where not(o.price is not null and a2.price is not null). Both clauses are identical; all I did was apply some boolean identities. Here is the result:

i	variety	price	variety	price	variety	price
0	Fuji	5	Navel	5	NULL	NULL
0	Gala	6	NULL	NULL	NULL	NULL
1	NULL	NULL	Valencia	4	NULL	NULL

That result has the correct rows, but it has some extra columns, which I don’t need. Here’s the final query:

select a.*, o.* from mutex
   left outer join apples as a on i = 0
   left outer join oranges as o on i = 1 or a.price = o.price
   left outer join apples as a2 on i = 1 and a2.price = o.price
where o.price is null or a2.price is null

Remember, this can’t be done without the mutex table, because I need something to provide non-NULL values for every row; otherwise the joins would not include values where the leftmost table has no rows to contribute.

This technique works if there are duplicate rows, and works on older versions of MySQL, but is probably the least efficient of the three I’ve demonstrated here. As usual, which query is appropriate depends on circumstances.

//추가
mysql>grant select, insert, delete, update, create, drop, alter on 테이타베이스명.* to 사용자아이디@localhost identified by '비밀번호';

//적용
mysql>flush privileges;



//루트 권한 부여
grant all privileges on *.* to 사용자아이디@localhost identified by '비밀번호' with grant option;

mysql> show variables like 'c%';

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | euckr                      |
| character_set_connection | euckr                      |
| character_set_database   | euckr                      |
| character_set_results    | euckr                      |
| character_set_server     | euckr                      |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
| collation_connection     | euckr_korean_ci            |
| collation_database       | euckr_korean_ci            |
| collation_server         | euckr_korean_ci            |
| concurrent_insert        | ON                         |
| connect_timeout          | 5                          |
+--------------------------+----------------------------+

mysql> CREATE TABLE t1 (
->a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> message CHAR(20)) ENGINE=MyISAM;
mysql> CREATE TABLE t2 (
-> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> message CHAR(20)) ENGINE=MyISAM;
mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
mysql> CREATE TABLE total (
-> a INT NOT NULL AUTO_INCREMENT,
-> message CHAR(20), INDEX(a))
-> ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

t1, t2 테이블의 데이터를 total 테이블에 병합된 데이터로 insert 하게 된다.

NSERT_METHOD는 total 테이블에 다이렉트로 INSERT할경우 UNION에
정의한 테이블중 어느 테이블에 INSERT를 할건지 정의하는것이다.(FISRT or LAST)


※ 주의할점은 두 테이블의 레코드 형식 및 타입까지 모두 일치하여야 한다.


mysql charset 의 설정이 utf8 인 경우 일반 백업시에 한글이 깨지는 현상이 일어난다.
이를 해결하기 우해서 옵션을 붙여 백업을 하면 해결을 할 수 있다.


#mysql character set latin1

mysql>mysqldump -uroot -p --default-character-set=latin1 --set-charset [db name] > backup.sql
mysql>mysql -uroot -p --default-character-set=utf8 [db name] < backup.sql

#mysql character set euckr

mysql>mysqldump -uroot -p --default-character-set=euckr --set-charset [db name] > backup.sql
mysql>mysql -uroot -p --default-character-set=utf8 [db name] < backup.sql

/etc.my.cnf 파일을 얼어 아래의 charset 에 맞게 셋팅 후 mysql 을 재실행한다.

---------------------- utf8 ---------------------------
[mysqld]
init_connect=SET collation_connection=utf8_general_ci
init_connect=SET NAMES utf8
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_general_ci



---------------------- euckr ---------------------------
[mysqld]
init_connect=SET collation_connection=euckr_korean_ci
init_connect=SET NAMES euckr
default-character-set=euckr
character-set-server=euckr
collation-server=euckr_korean_ci


--------------------------------------------------------

# killall mysqld
# /usr/bin/mysqld_safe&

출처 : http://deuxism.tistory.com/2694599

EXPLAIN tbl_name OR EXPLAIN SELECT select_options

tbl_name은 테이블의 구조를 나타냅니다.

EXPLAIN SELECT select_optionsEXPLAIN SELECT select_options
은 SQL의 실행계획을 표시합니다.
결과에 나오는 항목으로는
table : 테이블 명
type : 조인의 타입
- system : 테이블이 하나의 레코드만 가지는 경우
- const : 테이블에 조건을 만족하는 레코드가 하나일 때, 상수 취급
- eq_ref : 인덱스가 UNIQUE이거나 PRIMARY KEY인 경우의 조인으로 const를 제외한 조인 중 가장 좋은 형태
- ref : eq_ref와 다른 점은 UNIQUE가 아닐 경우 사용한다는 것.
- range : 조건에 레코드의 범위가 주어진 조인.
- index : all 형태와 비슷하며, 인덱스를 사용한다.
- all : 모든 레코드를 스캔한다.
possible_keys : 테이블에서 사용 할 수 있는 키(인덱스)
key : possible_keys에 나온 키 중에서 실제로 사용한 키
key_len : 키의 길이
ref : 인덱스를 사용할 때 어떤 컬럼과 조건이 맞아야 하는지
rows : 조건을 만족하는 레코드를 찾기 위해 몇개의 레코드를 검사해야 하는지
Extra : 부가적인 정보
- distinct : 조건을 만족하는 레코드를 찾았을 때 같은 조건을 만족하는 또 다른 레코드가 있는지 검사하지 않음.
- not exist : left join 조건을 만족하는 하나의 레코드를 찾았을 때 다른 레코드의 조합은 더 이상 검사하지 않는다.
- range checked for each record : 최적의 인덱스가 없는 차선의 인덱스를 사용한다는 의미.
- using filesort : mysql이 정렬을 빠르게 하기 위해 부가적인 일을 한다.
- unsing index : select 할때 인덱스 파일만 사용
- using temporary : 임시 테이블을 사용한다. order by 나 group by 할때 주로 사용
- where used : 조건을 사용한다는 의미.

MySQL Table의 auto_increment 의 값을 수동으로 원하는 값으로 변경이 가능하다..

아래와 같이 쿼리를 날려주는 것으로 해결!!!

ex)
mysql>alter table [테이블명] auto_increment = 99;