MySQL Import data method (Txt,csv)

Mysqlimport
Example
mysqlimport-uroot-p123456 Test/tmp/mytbl.txt;
Convention: The last part of the file name is the table name, and the above statement is imported into the table Mytbl
Mysqlimport must specify the database, the above statement database is test

Import CSV

Mysqlimport-uroot-p--local--lines-terminated-by= "rn"--fields-terminated-by= ","--fields-enclosed-by= "" "Test/tmp /mytbl.csv

Load data
Example
mysql> load Data infile '/tmp/mytbl.txt ' into table mytbl
Load data can not specify a database
In the above statement, MySQL must have/tmp/Read permission
Import CSV
mysql> load Data infile '/tmp/mytbl.csv ' into the table mytbl fields terminated by ', ' enclosed by ' ' lines terminated by ' RN '

Handling Duplicate primary keys
Replace an existing value
mysql> load Data infile '/tmp/mytbl.txt ' replace into table mytbl fields terminated by ' t ' lines terminated by ' n '
The table has not been imported
mysql> load Data infile '/tmp/mytbl.csv ' ignore into table mytbl fields terminated by ', ' enclosed by ' ' lines Termina Ted by ' RN '
Skip File rows
The following example skips the first row
mysql> load Data infile '/tmp/mytbl.txt ' into table mytbl ignore 1 lines;
Pretreatment
File Data.txt content:
Date Time Name Weight State
2006-09-01 12:00:00 Bill Wills Nevada
2006-09-02 09:00:00 Jeff Deft Oklahoma
2006-09-04 03:00:00 Bob Hobbs Utah
2006-09-07 08:00:00 Hank Banks 175 Texas
The file must be loaded into the following table
CREATE TABLE TBL
(
DT datetime,
Last_Name Char (10),
First_Name Char (10),
weight_kg float,
St_abbrev Char (2)
)
CREATE TABLE states
(
Name varchar (20),
ShortName Char (2)
)
States the contents of the table:
Name ShortName
Nevada NV
Oklahoma OK
Utah UT
Texas TX
INSERT INTO states
VALUES (' Nevada ', ' NV ')
, (' Oklahoma ', ' OK ')
, (' Utah ', ' UT ')
, (' Texas ', ' TX ')
Import:
Load data infile '/tmp/data.txt ' into table tbl
Ignore 1 lines
(@date, @time, @name, @weight_lb, @state)
Set Dt=concat (@date, ', @time),
First_name=substring_index (@name, ', 1),
Last_name=substring_index (@name, ',-1),
Weight_kg= @weight_lb *. 454,
St_abbrev = (select ShortName from states where Name= @state);

Results:
Mysql> select * from TBL; +---------------------+-----------+------------+-----------+-----------+
| DT | last_name | first_name | weight_kg | St_abbrev |
+---------------------+-----------+------------+-----------+-----------+
| 2006-09-01 12:00:00 | Wills |      Bill | 90.8 | NV |
| 2006-09-02 09:00:00 | Deft |      Jeff | 68.1 | OK |
| 2006-09-04 03:00:00 | Hobbs |    Bob | 102.15 | UT |
| 2006-09-07 08:00:00 | Banks |     Hank | 79.45 | TX |
+---------------------+-----------+------------+-----------+-----------+
Import the Windows local files to the MySQL database under Linux, plus the
Load data local infile ' c:/mytbl.txt ' to table mytbl fields terminated by ' t ' lines terminated by ' RN '