DEV Community

pcreem
pcreem

Posted on

how to load data from txt file into mysql

create database, table

mysql>CREATE DATABASE pet;
mysql>USE pet;
mysql>CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
Enter fullscreen mode Exit fullscreen mode

create data in pet.txt (use \N to represent null data)

Whistler        Gwen    bird    \N      1997-12-09      \N
Bowser  Diane   dog     m       1979-08-31      1995-07-29

Enter fullscreen mode Exit fullscreen mode

logout mysql and login with --local-infile=1 parameter

mysql --local-infile=1 -u user -p
Enter fullscreen mode Exit fullscreen mode

load data and check result

mysql>USE pet;
mysql>LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
mysql>select * from pet;

+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
| Bowser   | Diane | dog     | m    | 1979-08-31 | 1995-07-29 |
+----------+-------+---------+------+------------+------------+
2 rows in set (0.04 sec)

Enter fullscreen mode Exit fullscreen mode

reference:
https://dev.mysql.com/doc/refman/8.0/en/loading-tables.html

Top comments (0)