[MySQL] 4. 데이터의 추가, 삭제, 갱신

16. 행 추가하기 - INSERT

1) INSERT로 행 추가하기

INSERT INTO 테이블명 VALUES(값1, 값2, ...)

INSERT INTO 테이블명(열1, 열2, ...)
VALUES(값1, 값2, ...)
, (값1, 값2, ...)
, (값1, 값2, ...)
, ...

테이블의 열에 NOT NULL제약이 있을 경우, 해당 열에 NULL 삽입 시 에러가 발생한다.

CREATE TABLE IF NOT EXISTS sample
(
    id INT,
    num INT NOT NULL
);

INSERT INTO sample
VALUES(1);            //error...
INSERT INTO sample
VALUES(1, NULL);    //error...


CREATE TABLE IF NOT EXISTS sample1
(
    id INT,
    num    INT
);

INSERT INTO sample1(id)    //num에는 NULL이 들어간다.
VALUES(1);
+------+------+
| id   | num  |
+------+------+
|    1 | NULL |
+------+------+

디폴트 값이 지정된 열에 삽입할 값을 지정하지 않으면 설정된 디폴트값이 들어간다.

CREATE TABLE IF NOT EXISTS sample2
(
    id INT,
    num INT DEFAULT 101
);
INSERT INTO sample2(id)
VALUES(13);                    //(13, 101) 데이터 삽입
+------+------+
| id   | num  |
+------+------+
|   13 |  101 |
+------+------+

INSERT ... SELECT 문을 통해 특정 테이블의 데이터를 삽입할 수 있다.

SELECT * FROM sample2;    //source table
+------+------+
| id   | num  |
+------+------+
|   13 |  101 |
|   14 |  102 |
+------+------+


CREATE TABLE sample7(
    id INT,
    num INT
);

INSERT INTO sample7
    SELECT id
        , num
    FROM sample2;

+------+------+
| id   | num  |
+------+------+
|   13 |  101 |
|   14 |  102 |
+------+------+

AUTO_INCREMENT로 지정된 열의 값을 다음과 같이 LAST_INSERT_ID()로 확인할 수 있다.

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                9 |
+------------------+

17. 삭제하기 - DELETE

데이터베이스는 하드디스크와 같은 저장장치에 데이터를 저장해 관맇나다. 하짐나 저장장치에는 저장 용량이 정해져 있는 만큼 데이터를 무제한으로 저장할수는 없다. 따라서 필요한 경우 데이터를 삭제해야한다.

1) DELETE로 행 삭제하기

DELETE FROM 테이블명
WHERE 조건식

WHERE 구를 생략하면 모든 행을 대상으로 적용된다.
WHERE 구를 사용해 삭제할 행을 지정할 수 있다.
MySQL에서는 ORDER BY와 LIMIT을 사용할 수 있다.

CREATE TABLE IF NOT EXISTS sample3
(
    id INT,
    num INT
);

INSERT INTO sample3(id, num)
VALUES(1, 101),
(2, 102),
(3, 103),
(4, 104);
+------+------+
| id   | num  |
+------+------+
|    1 |  101 |
|    2 |  102 |
|    3 |  103 |
|    4 |  104 |
+------+------+

DELETE FROM sample3
ORDER BY id DESC
LIMIT 2;        //내림차순 정렬 후 상위 2개 행 삭제
+------+------+
| id   | num  |
+------+------+
|    1 |  101 |
|    2 |  102 |
+------+------+

테이블 전체를 삭제할 때

DELETE FROM tb1; : 데이터를 순차적으로 삭제한다. 순회하기 때문에 느림
DROP TABLE tb1; : 테이블 자체를 삭제, 빠름
TRUNCATE TABLE tb1;
- 테이블 자체를 삭제하고 같은 이름의 테이블을 새로 생성 후 테이블 DROP 후 CREATE하는 명령어 (속도가 빠르다)
- AUTO_INCREMENT가 초기화 : DROP 후 CREATE하기 때문에?
- 모든 데이터를 삭제할 때 복구가 가능한 DELETE 명령과 달리 TRUNCATE 명령은 데이터 복구가 불가능

18. 데이터 갱신하기 - UPDATE

UPDATE 테이블명
SET c1 = v1,
    c2 = v2,
    ...
WHERE 조건식;

SET 구를 사용하여 갱신할 열과 값을 지정한다. 이 때 = 은 비교연산자가 아닌 대입 연산자이다.


SELECT * FROM sample3;
+------+------+
| id   | num  |
+------+------+
|    1 |  101 |
|    2 |  102 |
|    3 |  103 |
|    4 |  104 |
+------+------+

UPDATE sample3
SET num = 10001
WHERE id = 1;

SELECT * FROM sample3;
+------+-------+
| id   | num   |
+------+-------+
|    1 | 10001 |
|    2 |   102 |
|    3 |   103 |
|    4 |   104 |
+------+-------+

UPDATE sample3
SET num = 10001

SELECT * FROM sample3;
+------+-------+
| id   | num   |
+------+-------+
|    1 | 10001 |
|    2 | 10001 |
|    3 | 10001 |
|    4 | 10001 |
+------+-------+

여러개의 열을 한 번에 갱신할 수 있지만 SET구가 어떤 순서로 갱신 처리를 하는지 알아둘 필요가 있다.

CREATE TABLE sample5 (
    id INT,
    num INT
);

INSERT INTO sample5(id, num)
VALUES(1, 101),
(2, 102);

SELECT * FROM sample5;
+------+------+
| id   | num  |
+------+------+
|    1 |  101 |
|    2 |  102 |
+------+------+

+------+------+
| id   | num  |
+------+------+
|    1 |  101 |
|    2 |  102 |
+------+------+

UPDATE sample5
SET id = id + 10, num = id;
+------+------+
| id   | num  |
+------+------+
|   11 |   11 |
|   12 |   12 |
+------+------+

UPDATE sample5
SET num = id, id = id + 10;
+------+------+
| id   | num  |
+------+------+
|   11 |    1 |
|   12 |    2 |
+------+------+

MySQL에서는 UPDATE 명령을 실행하면 SET 구에 기술된 순서대로 갱신 처리가 일어난다.

- INSERT ON DUPLICATE KEY UPDATE statement

INSERT INTO table (column_list)
VALUES (value_list)
ON DUPLICATE KEY UPDATE
   c1 = v1,
   c2 = v2,
   ...;

MySQL에서는 UNIQUE 인덱스나 PRIMARY KEY가 중복된 데이터를 삽입을 할 경우 INSERT ON DUPLICATE KEY UPDATE를 통해 데이터를 업데이트 할 수 있다.
UNIQUE 인덱스 행에 중복된 데이터를 삽입하면 오류가 발생한다.

CREATE TABLE sample4 (
    id INT UNIQUE,
    name VARCHAR(32)
);

INSERT INTO sample4
VALUES(1, 'test');

INSERT INTO sample4
VALUES(1, 'test2');
ERROR 1062 (23000): Duplicate entry '1' for key 'id'

명령문에 ON DUPLICATE KEY UPDATE옵션을 지정하면 기존 행을 새로운 값으로 업데이트한다.

SELECT * FROM sample4;
+------+------+
| id   | name |
+------+------+
|    1 | test |
+------+------+

INSERT INTO sample4
VALUES(1, 'test2')
ON DUPLICATE KEY UPDATE
name = 'test2';

SELECT * FROM sample4;
+------+-------+
| id   | name  |
+------+-------+
|    1 | test2 |    //값 변경
+------+-------+

MySQL은 수행하는 동작에 따라 영향을받는 행 수를 반환하는데 ON DUPLICATE KEY UPDATE를 사용했을 때 반환하는 행의 수의 의미는 다음과 같다.

새 행이 삽입되면 영향을 받은 행 수는 1이다.
기존 행이 갱신될 때, 영향을 받은 행 수는 2이다.
기존 행이 현재 값을 사용하여 갱신될 때, 영향을 받은 행의 수는 0이다.

테스트

CREATE TABLE inv(
    id INT NOT NULL AUTO_INCREMENT UNIQUE,
    user_id INT,
    item_id INT,
    amount INT DEFAULT 0,
    UNIQUE(user_id, item_id)
);

INSERT INTO inv(user_id, item_id, amount) VALUES
(1, 1, 10),
(1, 2, 0),
(2, 1, 0),
(3, 1, 0);

mysql> SELECT * FROM inv;
+----+---------+---------+--------+
| id | user_id | item_id | amount |
+----+---------+---------+--------+
|  1 |       1 |       1 |     10 |
|  2 |       1 |       2 |      0 |
|  3 |       2 |       1 |      0 |
|  4 |       3 |       1 |      0 |
+----+---------+---------+--------+
4 rows in set (0.00 sec)

4개의 item을 넣었고, 다음 INCREMENT 값은 5일 것이다.

INSERT ON DUPLICATE KEY UPDATE를 하고 INSERT실행

INSERT INTO inv(user_id, item_id, amount)
VALUES(1, 5, 0)
ON DUPLICATE KEY UPDATE
amount = amount+1;
# 1row affected

mysql> SELECT * FROM inv;
+----+---------+---------+--------+
| id | user_id | item_id | amount |
+----+---------+---------+--------+
|  1 |       1 |       1 |     10 |
|  2 |       1 |       2 |      0 |
|  3 |       2 |       1 |      0 |
|  4 |       3 |       1 |      0 |
|  5 |       1 |       5 |      0 |
+----+---------+---------+--------+
5 rows in set (0.00 sec)

새로운 값을 삽입하면 1개 행이 영향받고, 다음 inc값으로 삽입된다.

기존 값을 업데이트

INSERT INTO inv(user_id, item_id, amount)
VALUES(1, 5, 0)
ON DUPLICATE KEY UPDATE
amount = amount+1;
# 2 rows affected

mysql> SELECT * FROM inv;
+----+---------+---------+--------+
| id | user_id | item_id | amount |
+----+---------+---------+--------+
|  1 |       1 |       1 |     10 |
|  2 |       1 |       2 |      0 |
|  3 |       2 |       1 |      0 |
|  4 |       3 |       1 |      0 |
|  5 |       1 |       5 |      1 |
+----+---------+---------+--------+
5 rows in set (0.00 sec)
# 5번째 데이터의 amount가 0에서 1로 업데이트되었다!

여기서 새로운 데이터를 INSERT해서 auto_increment 값이 어떻게 되는지 확인해봤다.


mysql> SELECT * FROM inv;
+----+---------+---------+--------+
| id | user_id | item_id | amount |
+----+---------+---------+--------+
|  1 |       1 |       1 |     10 |
|  2 |       1 |       2 |      0 |
|  3 |       2 |       1 |      0 |
|  4 |       3 |       1 |      0 |
|  5 |       1 |       5 |      1 |
|  7 |       2 |       3 |      0 |
+----+---------+---------+--------+
6 rows in set (0.00 sec)

새로 들어온 데이터의 id가 7이 되었다.
INSERT ON DUPLICATE KEY UPDATE로 업데이트를 하더라도 AUTO_INCREMENT의 값이 증가하게된다.

이미 존재하는 값을 수정하는 방법들

INSERT IGNORE
- 삽입하려는 데이터가 이미 존재하는 거면 무시
- 삽입이 안되도 AUTO_INCREMENT 증가
REPLACE INTO
- DELETE 한후에 INSERT되기 때문에 주의 : val=val+1이면 의도와 다른 값이 나온다
INSERT ... ON DUPLICATE KEY UPDATE
- UNIQUE로 지정된 것이 중복되는지 확인한다.
- 값이 수정되면 2 rows affected... 라고 나오는데 삭제하고 처리되는것은 아니다.
- AUTO_INCREMENT 증가

19. 물리삭제와 논리삭제

물리삭제는 SQL의 DELETE 명령을 통해 직접 삭제할 수 있다. 이렇게 진행하는 것이 물리삭제이다. 논리삭제는 테이블에 삭제 플래그를 위한 열을 정의하여 삭제 여부를 갱신하는 방법이다.

- 논리삭제 장점

데이터를 삭제하지 않기 때문에 삭제되기 전의 상태로 간단히 되돌릴 수 있음

- 논리삭제 단점

삭제해도 DB 저장공간이 늘어나지 않음 (DB 크기가 증가하면 검색속도가 떨어짐)
애플리케이션 측 프로그램에서는 DELETE 명령임에도 불구하고 UPDATE 명령을 실행하므로 혼란을 야기할 수 있음

- 삭제방법 선택

SNS 서비스처럼 사용자의 개인정보를 다루는 시스템에서는 사용자가 탈퇴한 경우 데이터를 삭제한다. 이때 개인정보를 취급하는 마스터 테이블에서 삭제할 경우에는 물리삭제를 하는 편이 안전할 것이다. 개인정보 유출을 미연에 방지하는 측면에서도 좋은 선택이라고 할 수 있다.

반면, 쇼핑 사이트의 경우 사용자가 주문을 취소할 경우에도 데이터를 삭제한다. 이러한 경우에는 논리삭제 방법을 많이 사용한다. 주문이 취소되었다고해도 발주는 된 것으로, 해당 정보가 완전히 불필요한 것이라고는 말할 수 없다. 이러한 데이터는 특히 주문 관련 통계를 낼 때 유용하게 사용할 수 있기 때문이다.

따라서 물리삭제와 논리삭제는 어느 쪽이 좋은지 따지기보다는 상황에 따라 용도에 맞게 데이터 삭제 방법을 선택하는 것이 중요하다.

실험

IDEX 유무에 따른 UPDATE, INSERT, DELETE 수행 시간

index 유무가 INSERT, UPDATE, DELETE 문 실행 시간에 걸리는 영향을 측정했다.

테스트를 위한 테이블을 생성하고, 100만개의 데이터를 넣었다.

CREATE TABLE IF NOT EXISTS health_data
(
    id INT,
    gender INT,
    age_code TINYINT,
    area_code TINYINT,
    height SMALLINT,
    weight SMALLINT,
    waist SMALLINT,
    eye_left FLOAT,
    eye_right FLOAT,
    ear_left TINYINT,
    ear_right TINYINT
);

PRIMARY KEY를 지정할 경우, 기본 인덱스가 생성되기 때문에 생략했다.

id를 기준으로 EXPAIN을 실행해봤다.

EXPLAIN
SELECT *
FROM health_data
WHERE id = 10004;

index_x_explain

다음과 같은 INSERT, UPDATE, DELETE문을 100번씩 실행하고, 걸리는 시간을 측정했다.

DELETE FROM health_data WHERE id = {0};

UPDATE health_data SET height={rand} WHERE id = {0};

INSERT INTO health_data(id, gender, age_code, area_code, height, weight, waist, eye_left, eye_right, ear_left, ear_right) VALUES(..., ..., ...);

실행결과

index_x_result

이번엔 인덱스를 생성하여 실험해봤다.

CREATE INDEX hd_index ON health_data(id);

index_o_explain

동일하게 SQL을 실행한 결과는 다음과 같다.

index_o_result

UPDATE와 DELETE는 큰 시간차이를 보였지만, INSERT문은 인덱스 유무에 영향을 받지 않았다.

프라이머리키와 유니크키가 모두 존재할 때 인덱스가 어디에 생기는가

PK와 UNIQUE KEY가 다른 열에 존재할 때 인덱스가 PK에만 걸리는지 UK에도 걸리는지 확인

CREATE TABLE sample01(
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    item_id INT,
    amount INT NOT NULL DEFAULT 0,
    CONSTRAINT UNIQUE(user_id, item_id)
);

INSERT INTO sample01(user_id, item_id)
VALUES (1, 1),
    (1, 2),
    (1, 3);

USE information_schema;

SHOW TABLES;

SELECT *
FROM statistics;    //생성된 인덱스 정보를 확인할 수 있다.

SELECT *
FROM statistics
WHERE table_name='sample01'    # sample01의 인덱스 정보 확인

show_schema

sample01 테이블에 PK와 UNIQUE KEY로 인덱스가 생성된 것을 확인할 수 있다.

저작자표시 비영리 변경금지

'DB > MySQL' 카테고리의 다른 글

[MySQL] 6. 데이터베이스 객체 작성과 객체 (0)	2019.05.12
[MySQL] 5. 집계와 서브쿼리 (0)	2019.05.04
[MySQL]3. 정렬과 연산 (0)	2019.04.19
[MySQL] 2. 테이블에서 데이터 검색 (0)	2019.04.16
[MySQL] 1. 데이터베이스와 SQL (0)	2019.04.09

이것저것끄적

[MySQL] 4. 데이터의 추가, 삭제, 갱신

16. 행 추가하기 - INSERT

1) INSERT로 행 추가하기

17. 삭제하기 - DELETE

1) DELETE로 행 삭제하기

테이블 전체를 삭제할 때

18. 데이터 갱신하기 - UPDATE

- INSERT ON DUPLICATE KEY UPDATE statement

테스트

이미 존재하는 값을 수정하는 방법들

19. 물리삭제와 논리삭제

- 논리삭제 장점

- 논리삭제 단점

- 삭제방법 선택

실험

IDEX 유무에 따른 UPDATE, INSERT, DELETE 수행 시간

프라이머리키와 유니크키가 모두 존재할 때 인덱스가 어디에 생기는가

'DB > MySQL' 카테고리의 다른 글

티스토리툴바

[MySQL] 4. 데이터의 추가, 삭제, 갱신

16. 행 추가하기 - INSERT

1) INSERT로 행 추가하기

17. 삭제하기 - DELETE

1) DELETE로 행 삭제하기

테이블 전체를 삭제할 때

18. 데이터 갱신하기 - UPDATE

- INSERT ON DUPLICATE KEY UPDATE statement

테스트

이미 존재하는 값을 수정하는 방법들

19. 물리삭제와 논리삭제

- 논리삭제 장점

- 논리삭제 단점

- 삭제방법 선택

실험

IDEX 유무에 따른 UPDATE, INSERT, DELETE 수행 시간

프라이머리키와 유니크키가 모두 존재할 때 인덱스가 어디에 생기는가

'DB > MySQL' 카테고리의 다른 글

'DB/MySQL' Related Articles

티스토리툴바