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

This article introduces how to operate text data in mysql, the content is very detailed, interested friends can refer to it, I hope it can help you.

1. Establish test tables and prepare data

First, create a table that represents student information for testing, with fields id, name, age, city, and salary. Id and Name No

can be empty.

The copy code is as follows:

create table person(

id int not null auto_increment,

name varchar(40) not null,

city varchar(20),

salary int,

primary key(id)

)engine=innodb charset=gb2312;

Then write a text file for import: c: /data.txt.

Zhang San 31 Beijing 3000

Li Si 25 Hangzhou 4000

King V 45 /N 4500

Xiaoming 29 Tianjin/N

Each item is separated by the Tab key, and if the field is NULL, it is represented by/N.

II. Import data

Enter the command to import.

load data local infile "c:/data.txt"

into table person(name,age,city,salary);

Local means local. After execution, you can see that NULL data is also imported correctly.

III. Export data

Now export this table as a text file: c: /data_out.txt.

The copy code is as follows:

select name,age,city,salary

into outfile "c:/data_out.txt"

lines terminated by "/r/n"

from person;

where lines terminated by "/r/n" means that each line (i.e., each record) is separated by/r/n, and/r/n is the window system

The traditional line break. The exported data_out.txt is exactly the same as the data.txt.

IV. Operating environment

Windows vista home basic

MySQL 5.1.34-community

V. Attention

The separation between fields and records (rows) defaults to/t(Tab) and/n. It can be changed, for example:

FIELDS TERMINATED BY ',' --Fields are separated by,

LINES TERMINATED BY ';' --records are separated by;

Also note that newlines for other operating systems may not be the same as windows.

VI. Implementation in Java Programs

LOAD DATA LOCAL INFILE 'C://data.txt' INTO TABLE person;

Because this is an SQL statement, you can also use:

st.execute("LOAD DATA LOCAL INFILE 'C://data.txt' INTO TABLE person");

(st An instance variable representing Statement, assuming database connection, initialization of st has been completed)

About how to operate text data in mysql to share here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

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.