Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to import data from mysql". In daily operation, I believe many people have doubts about how to import data from mysql. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubts about "how to import data from mysql". Next, please follow the editor to study!

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 a database. The above statement database is test.

Www.2cto.com

Import csv

Mysqlimport-uroot-p-- local-- lines-terminated-by= "\ r\ n"-- 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 may not specify a database

In the above statement, mysql must have read permission of / tmp/

Import csv

Mysql > load data infile'/ tmp/mytbl.csv' into table mytbl fields terminated by', 'enclosed by' "'lines terminated by'\ r\ n'

Www.2cto.com

Deal with duplicate primary key

Replace existing values

Mysql > load data infile'/ tmp/mytbl.txt' replace into table mytbl fields terminated by'\ t 'lines terminated by'\ n'

Do not import if you already have it in the table.

Mysql > load data infile'/ tmp/mytbl.csv' ignore into table mytbl fields terminated by', 'enclosed by' "'lines terminated by'\ r\ n'

Skip file lines

The following example skips the first line

Mysql > load data infile'/ tmp/mytbl.txt' into table mytbl ignore 1 lines

Pretreatment

The contents of the file data.txt:

Date Time Name Weight State

2006-09-01 12:00:00 Bill Wills 200 Nevada

2006-09-02 09:00:00 Jeff Deft 150 Oklahoma

2006-09-04 03:00:00 Bob Hobbs 225 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)

) www.2cto.com

The contents of the states 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; www.2cto.com +-+

| | 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 local file into mysql under linux and add local

Load data local infile 'Cburex stop mytbl.txt' into table mytbl fields terminated by'\ t 'lines terminated by'\ r\ n'

At this point, the study on "how to import data from mysql" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.