Mysql- insert절 용법 총정리(Real Mysql)

 1. insert auto_increment

- 테이블 컬럼에 부여하는 옵션형태로 한 테이블에 serial increment number 컬럼의 기능을 제공한다.

1-1. insert와 auto_increment

- 사용방법: CTA구문에 혹은 create table 구문으로 테이블을 생성 후, insert into 테이블명 (컬럼명_1) values ( 'value명');

or

insert into 테이블명(컬럼명1, 컬럼명2) values ('value명1','values명2'); -- auto increment값의 현재값을 저장하려면 insert into 테이블명(null or 0) values ('value명1','values명2'); 하면된다.

- 특성: 

강제저장한값이 auto_increment 현재값보다 작을때는 auto_increment의 현재값이 변하지 않는다

강제저장한 값이 auto_increment의 현재값보다 클때는 auto_increment의 현재값이 얼마였든지 관계없이 강제로 저장된값에 1을 더한값이 auto_increment의 다음값으로 변경된다. 

auto_increment컬럼에 0을 insert하려면 sql_mode 시스템변수에 "no_auto_values_on_zero"값을 추가하면 된다.

 

1-2. insert ignore

auto_increment컬럼 사용시 반드시 지켜야할 규칙

- auto_increment속성 가진 컬럼은 반드시 프라이머리키나 유니크키의 일부로 정의되어야 한다.

- auto_increment속성을 가진 컬럼 하나로 프라이머리키를 생성할 때는 아무런 제약이 없다

- 여러개의 컬럼으로 프라이머리 키를 생성할때

 auto_increment속성의 컬럼이 제일 앞일 때: auto_increment속성의 컬럼이 프라이머리 키의 제일 앞쪽에 위치하면 MyISAM이나 InnoDB테이블에서 아무런 제약이 없다.

- auto_increment속성의 컬럼이 제일 앞이 아닐 때

MyISAM테이블에서는 유니크키없이 프라이머리키로만 auto_increment를 생성할 수 있지만, innodb에서는 반드시 unique키를 하나더 생성해야만 한다. 

 

MyIsam과 InnoDB테이블의 auto_increment증가방식의 차이점

- MyIsam은 auto_increment컬럼을 프라이머리키의 제일앞에 정의하여 단순 선형적 증가하지만 프라이머리키 뒤쪽에 auto_increment컬럼을 사용하면 앞쪽 컬럼에 의존해서 증가한다. 

auto_increment는 항상 1씩 증가하는것이 아니다. 

• auto_increment_offset: 속성 컬럼의 초기값을 정의 
• auto_increment_increment: 얼마씩 증가시킬 것인가

- auto_increment잠금

1-3. replace

- 용법: replace into 테이블 values('values1', 'values2', 'values3');

-특징: 중복된 값이 존재하면 update를 실행하지않고, 중복된값을 delete하고 새로운 레코드를 update한다. 

(중복된 레코드에 대한 판정기준이 포인트다)

 

1-4. insert into .... on duplicate key update

- 용법: 테이블에 중복된 레코드가 존재할 때 기존 레코드의 컬럼값을 참조해서 업데이트 하는것이 가능하다. 

- insert into (select from group by ~) on duplicate key update 컬럼명 = values(count); -- select절의 함수값의 결과를 반환하는 용법 사용이 가능하다. 

 

1-5. insert.... select .....

- limit 절에대한 insert into 는 사용불가하다. 

- load data(local) infile.....

load data infile은 selelct into outfile에 대응하는 적재쿼리로 insert into보다 약 20배정도 빠르다.

case1) 데이터 파일의 값과 컬럼개수가 동일할 경우 

=> 데이터 value값과 준비된 컬럼 개수가 동일해야 insert된다. 

case2) 데이터 파일의 갑의 개수가 테이블의 컬럼 수보다 적은 경우

=>5개뿐이라면 나머지 칼럼에 대해서는 LOAD DATA INFILE 문장의 마지막에 SET 절을 이용해 초기 값을 명시해야 한다.  빈컬럼에 대해서는 set 컬럼명 = '채울값'을 명시해야한다. 

case3) 데이터 파일의 값의 개수가 테이블 칼럼수보다 많은 경우 

=> 태이블의 칼럼과 데이터 파일의 값이 일대일로 매치되지 않을 때는 일단 데이터 파일의 값을 사용자 변수로
읽어들이면된다. 버리려는 컬럼명의 @태깅을해서 사용하지않고 버리면된다. 

case4)  데이터 파일의 값을 연산해서 테이블의 칼럼에 저장하려는 경우 

=> 이름이라는 컬럼을 first_name, last_name(middle_name||first_name)연산으로 두개의 컬럼으로 가르려는 경우 일단 @middle_name와 @first_name으로 가른 후 set절에서 두개변수의 값을 문자열 결합함수로 저장한다.

set name = concat(@middle_name,' ' ,@first_name); 추가

case5) 데이터 파일이 mysql서버가 아닌 다른 컴퓨터에 있는 경우 

적재하려는 데이터 파일이 MySQL 서버가 아니라 다른 원격 컴퓨터에 있을 때는 LOCAL 키워드를 추
가해 LOAD DATA INFILE 문장을 사용하면 된다. MySQL 클라이언트나 ]DBC 드라이버는 그 데이터 파일을 찾아서
MySQL 서버로 업로드한 후 파일을 테이블에 적재한다.

자바 프로그램에서 ]DBC 드라이버 를 사용해 LOAD DATA LOCAL INFILE ... 명령을 사용할 때는 파일 경로에 URL을 사용할 수도 있다. 파일 경로에 URL을사용할 수 있다는 것은 데이터 파일이 MySQL 서버와클라이언트 컴퓨터 이외
의 호스트에 위치할 수도 있다는 의미다. (단, allowUrlInLocalInfile 옵션이 true로 설정돼 있어야 한다는 점이다)

Load data infile의 성능향상 

- InnoDB: auto-commit모드, foreign key관련내용

- 모든스토리지(InnoDB, MyIsam): 유니크 인덱스 

auto-commit

Innodb스토리지엔진은 트랜잭션을 사용할 수 있다. auto-commit이 활성화(true)된 상태는 레코드단위로 삽입될때마다 commit을 실행한다. (레코드 단위로 디스크 동기화 = flush)작업을 발생 디스크 i/o에 많은 부하를 일으킨다

auto-commit모드를 비활성화(false)하려면 

레코드가 INSERT될 때마다 디스크에 플러시하는 ξ법을 피할 수
있다 그리고 COMMIT을 실행하는 시점에서야 비로소 버떠링됐던 내용을 디스크에 플러시하게 된다 이는 InnoDB
의 로그(Redo log)파일뿐 아니라 바이너리 로그에도 똑같이 영흔떨 미치거| 된다

많은 사용자들이 auto-commit모드가 가장 빠를것이라 생각하지만, 쿼리실행마다 로그 플러시를 실행한다는것은 결국 디스크 부하를 가중시키는 것이므로 더욱 느리게 작동한다는 사실이다.

unique index(중복체크를 건너뛰도록 설정하는 방법이며, 디스크 i/o를 줄일 수 있다)

set unique_checks = 0;

load data ...

set unique_checks  = 1;

foreign key(무결성을 해치는 데이터가 없는것을 먼저확인해야하며 설정 변경방법은 다음과 같다)

set foreign_key_checks = 0;

load data ...

set foreign_key_checks = 1;

 

중요) 아시아권 데이터의 캐릭터셋 확인방법 

방법1)윈도우 메모장>다른이름으로저장> 하단의 인코딩(E)의 캐릭터셋 값을 확인한다. 

방법2)

mysql> show variables like 'character_set_%';

mysql> set names 'euckr';

mysql> show variables like 'character_set_%';

방법3) load data infile '파일경로명/파일명.csv'

ignore

into table 테이블명 charater set 'utf8'

fileds

terminated by ','

optionally enclosed by '"' escaped by '"' .....