How to import access data to MySQL)

Http://www.cnblogs.com/ninahan0419/archive/2009/11/18/1605090.html

Copyright Disclaimer: This document is original to me and can be reproduced freely, but cannot be used for commercial purposes. You must retain the original author's name and address on this site.

References:
1) MySQL entry: http://space.lzu.edu.cn/homeof/usmot/bbs/show.asp? Id = 85
2) Mysql Data Type: http://blog.xinweb.org /? Action = show & id = 20
3) MySQL Chinese manual: http://www.linuxforum.net/books/mysqlmanual/manual_toc.html
4) MySQL Import and Export: http://it.yn.cninfo.net/pubnews/doc/read/8742421943220786691/103.oldsystem14.2635/index.asp

Background description:

To implement the new FTP search engine version, convert the current VB + ACCESS + ASP version to C + MySQL + PHP version.

Plan to conduct both web development and underlying development

Therefore, you must first try to transfer the data in access to MySQL.

So how to implement it?

Transfer subject
Next, start converting access data to MySQL one by one.

Step 1:

According to the logical structure of the database in access [or the database design document written previously]
Design and create the corresponding database in MySQL

Let's take a look at the structure of several tables in access and perform a preliminary data type conversion.
Site_tab // site information table

Field name data type (ACCESS) data type (MySQL)
ID: int
Site text char (15)
Port Number int
User text char (15)
PW text char (15)
ACC yes/no Enum ('n', 'y ')
Indb yes/no Enum ('n', 'y ')
Info remarks text

Key_tab // keyword statistics table

Key text char (100)
Acctime numeric int

File_tab // file table

File text (100) Char (100)
Postfix text (4) Char (4)
PID integer int
Ipid integer int
Acctime long int

Cat_tab // directory table

Id integer int
Cat text (2, 100) Char (100)
Postfix text (4) Char (1) // This can be deleted because it is redundant.
PID integer int
Ipid integer int
Acctime long int

The following table describes how to create a MySQL database and corresponding table items based on the preceding conversions.
[Note: The Mysql Data Types involved above may not be suitable enough and need to be adjusted in the future]

First, create a database named falcon_search.
Create Database falcon_search;

Create Table site_tab
Create Table site_tab
(
Id int not null primary key,
Site char (15) not null,
Port int default 21,
User char (15) not null default 'anonymous ',
PW char (15) not null default 'falcon ',
ACC Enum ('n', 'y') default 'n ',
Indb Enum ('n', 'y') default 'n ',
Info text
);

Create Table key_tab
Create Table key_tab
(
Skey char (100) binary not null unique,
Acctime int default 0
);

Create a table cat_tab
Create Table cat_tab
(
Id int,
Cat char (100) binary not null,
Postfix char (1) binary,
PID int references cat_tab (ID ),
Ipid int references site_tab (ID ),
Acctime int,
Primary Key (ID, ipid)
);

Create a file table file_tab
Create Table file_tab
(
File char (100) binary not null,
Postfix char (4) binary not null,
PID int references cat_tab (ID ),
Ipid int references site_tab (ID ),
Acctime int
);
// Because there are many file names, you do not need to set the primary keyword.
Step 2:

Both databases and tables are created and pulled. Now we have to find a way to get data from access to MySQL.
How can this problem be solved?

1) first export the data in access as a TXT file [text file]
Specific Method: Open the Access database to be operated, select "file"> export> text file
After selecting export, a window is displayed. Click "advanced" to set the field delimiter to {tab}, and change the text recognition symbol to {none }, you can also set other settings.
Note: Name the corresponding table name for each file name (Unified to facilitate subsequent operations)

2) then, copy to the directory where the bin under MySQL is located.

Step 3:

Try to import the data in the text file to the table just created.

This statement is mainly used: load data local in file contains the data text file name into Table table name;

Next, we can import data from a text file to the corresponding table one by one.

1)site_tab.txt ==> site_tab
Load data local infile "site_tab.txt" into Table site_tab;

OK, pull successfully, but there seems to be a warning.

Let's first use select * site_tab;
Originally, ACC and indb were all empty.

Now, we only need to update the ACC and indb values to 'n', so we can pull them:
Update site_tab set indb = "N ";
Update site_tab set ACC = "N ";

2)key_tab.txt ==> key_tab

Load data local infile "key_tab.txt" into Table key_tab;

3366cat_tab.txt ==> cat_tab

Load data local infile "cat_tab.txt" into Table cat_tab;

4)file_tab.txt ==> file_tab

Load data local infile "file_tab.txt" into Table file_tab;