MySQL 8.0 Reference Manual - Tutorial - Creating and Using a Database3

3.3.3 Loading Data into a Table

 

데이터 로딩을 위해 LOAD DATA와 INSERT 명령어를 사용할 수 있다.

먼저 LOAD DATA 명령어를 사용해 보도록 하자

 

아래 데이터를 로딩하는 작업을 수행해 보자

 

name	owner	species	sex	birth	death
Fluffy	Harold	cat	f	1993-02-04
Claws	Gwen	cat	m	1994-03-17
Buffy	Harold	dog	f	1989-05-13
Fang	Benny	dog	m	1990-08-27
Bowser	Diane	dog	m	1979-08-31	1995-07-29
Chirpy	Gwen	bird	f	1998-09-11
Whistler	Gwen	bird		1997-12-09
Slim	Benny	snake	m	1996-04-29

위 테이터를 아래의 텍스트 내용으로 pet.txt 파일로 준비한다.

 

Fluffy Harold cat f 1993-02-04 \N Claws Gwen cat m 1994-03-17 \N Buffy Harold dog f 1989-05-13 \N Fang Benny dog m 1990-08-27 \N Bowser Diane dog m 1979-08-31 1995-07-29 Chirpy Gwen bird f 1998-09-11 \N Whistler Gwen bird \N 1997-12-09 \N Slim Benny snake m 1996-04-29 \N

 

위의 텍스트를 보면 컬럼사이에 Tab으로 구분되어 있으며 null 값의 경우 \N 으로 처리되어 있는 것을 확인할 수 있다.

위와 같이 null에 대한 부분을 처리하지 않을 경우 에러가 발생한다.

 

이제 LOAD DATA 명령어를 사용해 데이터를 테이블에 넣어 보자

 

PS C:\Users\User> mysql -u root -p
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.17 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| dbperson           |
| information_schema |
| menagerie          |
| mpdbdiag           |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
9 rows in set (0.00 sec)

mysql> use menagerie
Database changed
mysql> load data infile 'pet.txt' into table pet
    -> lines terminated by '\r\n';
Query OK, 8 rows affected (0.01 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
+----------+--------+---------+------+------------+------------+
8 rows in set (0.00 sec)

mysql>

 

위 테스트의 경우 에러 없이 진행된 듯하지만 만약 default로 MySQL을 설치했다면 여러 에러 상황을 겪게 된다.

명령어 중 lines terminated by '\r\n' 부분은 테스트 환경이 윈도우 이기 때문에 추가한 부분이다.

아래는 그 에러 상황에 대한 내용이며 이 부분을 더 상세히 기술해 보도록 하겠다.

 

---

처음 pet.txt 파일을 생성하고 명령어를 통해 아래와 같이 로딩을 하려하면 에러가 발생한다.

 

mysql> load data local infile 'C:\Users\User\pet.txt' into table pet;
ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

 

기본적으로 보안과 관련한 내용으로 데이터 로딩이 disable 되어 있다. 또 load data에 local 옵션을 사용하는 것과 그렇지 않은 것은 차이가 있다.

서버단의 파일을 읽을지 client 단의 파일을 읽을지의 문제와 또 client 파일을 읽게 해줄지 여부에 대한 사항이다.

이 부분은 추후 Security Issues with LOAD DATA LOCAL 섹션에서 설명하도록 하겠다.

 

일단 서버단에서 파일을 읽도록 할 것이기 때문에 위의 disable과는 연관성이 없다.

대신 secure_file_priv 설정이 해당 파일을 읽어 오는데 문제를 일으킨다.

 

local 옵션을 없애고 파일을 읽으려 하게 되면 아래와 같은 현상이 발생한다.

 

mysql> load data infile 'C:\Users\User\pet.txt' into table pet;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

 

secure_file_priv 옵션 값을 확인해 보도록 하자

 

mysql> select @@global.secure_file_priv;
+------------------------------------------------+
| @@global.secure_file_priv                      |
+------------------------------------------------+
| C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |
+------------------------------------------------+
1 row in set (0.00 sec)

 

그럼 옵션에 지정된 곳에 파일을 두고 load data 명령어를 수행하면 어떨까?

 

mysql> select @@global.secure_file_priv;
+------------------------------------------------+
| @@global.secure_file_priv                      |
+------------------------------------------------+
| C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql> load data infile 'pet.txt' into table pet
    -> lines terminated by '\r\n';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
mysql>

 

마찬가지 에러가 발생한다.

 

그래서 my.ini 파일에서 secure-file-priv 설정 값을 변경해 서버를 다시 시작해 보자(my.ini 파일에 대한 내용을 "Windows 버전 MySQL의 my.ini 파일 찾기" 게시물 참조)

my.ini 파일의 설정 내용 중 secure-file-priv="" 와 같이 설정 변경 후 서버 다시 시작.

 

PS C:\Users\User> mysql -u root -p
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.17 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| menagerie          |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
7 rows in set (0.00 sec)

mysql> use menagerie
Database changed
mysql> select @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
|                           |
+---------------------------+
1 row in set (0.00 sec)

mysql> load data infile 'C:\Users\User\pet.txt' into table pet;
ERROR 29 (HY000): File 'C:\ProgramData\MySQL\MySQL Server 8.0\Data\UsersUserpet.txt' not found (OS errno 2 - No such file or directory)
mysql> load data infile 'pet.txt' into table pet;
ERROR 29 (HY000): File 'C:\ProgramData\MySQL\MySQL Server 8.0\Data\menagerie\pet.txt' not found (OS errno 2 - No such file or directory)
mysql> load data infile 'pet.txt' into table pet;
ERROR 29 (HY000): File 'C:\ProgramData\MySQL\MySQL Server 8.0\Data\menagerie\pet.txt' not found (OS errno 2 - No such file or directory)
mysql> load data infile 'pet.txt' into table pet
    -> lines terminated by '\r\n';
Query OK, 8 rows affected (0.01 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
+----------+--------+---------+------+------------+------------+
8 rows in set (0.00 sec)

mysql>

 

설정 후에도 load data 명령어 사용시 에러를 확인할 수 있다.

기본적으로 secure-file-priv 값을 정하지 않을 경우 "C:\ProgramData\MySQL\MySQL Server 8.0\Data\데이터베이스명" 위치에서 파일을 찾고 있다.

---

 

load data 명령어를 통해 데이터 로딩하는 부분을 확인해 봤다.

이제 기본적인 Insert 문장을 통해 데이터를 넣은 것을 확인해 보자

 

mysql> insert into pet
    -> values ('Puffball','Diane','hamster','f','1999-03-30',NULL);
Query OK, 1 row affected (0.01 sec)

mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)