Shulou(Shulou.com)06/01 Report--

Import ACCESS data into Mysql [@ more@] in the process of building a website, we often have to deal with the import and export of some data. in Mysql database, there are two ways to deal with data export (general).

1. Use select * from table_name into outfile "file_name"

two。 Use the mysqldump utility

Let's give an example:

Suppose we have a library of samp _ db and a table of samp _ table in our database. Now we want to export the data of samp_table. We can use the following methods:

Type select * from samp_table into outfile "file_name" at the Mysql prompt

Type mysqldump-u root samp_db samp_table > samp.sql at the system command prompt

(of course, mysqldump has many options. For example,-d means only export table structure;-t means only import table data)

How to handle data import: generally, we type mysqlimport-u root samp_db samp_table.txt at the system command prompt (note: this TXT file name must be named after the table). For data exported by mysqldump, we can also use mysql-u root samp_db < file_name to import. At the Mysql prompt we use Load data infile "file_name" into table samp_table.

In addition, in addition to the above methods, on the premise that the moved database system is consistent with the original system, we can import and export the data through the copy of the file. first, we use mysqladmin-u root variables (at the system command prompt) or show variables; (at the Mysql prompt) to find datadir. If in my environment, this directory is in c:mysqldata. Then copy some of the files. All ok!

With the above knowledge, we get to the point: how to import ACCESS data into Mysql.

First of all, we export the data from ACCESS as a text file. In the export process, pay attention to select the field delimiter and text identifier, and look at the text file to determine whether a record is on the same line. If not, manually remove the enter key. Suppose the text file we export is c:samp_table.txt. Its contents are as follows:

1, Zhang Xinhua, male

2. Jiang Zunjin, female

Next we use mysqlimport-u root-fields-terminated-by= "," samp_db samp_table.txt "

Or use load data infile "c:samp_table.txt" into table samp_table fields terminated by ","

Give it a try (pay attention to the escape characters), isn't everything ok! If not, please take a closer look at the help of specific commands. Here are some options:

-- fields-enclosed-by=char indicates that column values should be included in specified characters. Quotation marks are usually used. By default, it is assumed that column values are not included in any characters.

-- fields-escaped-by=char represents the escape character used to escape special characters. Default means no escape character

-- fields-terminated-by=char specifies the characters that separate columns. By default, column values are assumed to be separated by tabs.

-- lines-terminated-by=str specifies to end the output.

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.