Import Access data to mysql_MySQL

Import Access data to mysql bitsCN.com

Extract some information from Access and import it to mysql.

Well, there are many simple things... It's just SQL statement import and export... That's what I thought at the beginning...

We found that Access does not have the SQL export function and does not want to use third-party products...

Here is a brief description of how I did it!

1. create a view in access to obtain the desired data;

2. export the data queried in this view to the text format. I use CVS;

3. use a text editor to process exported data into SQL statements (thanks to vim, other editors do not know what to do, opening is a problem, let alone editing quickly... When UE is turned on, the scroll wheel cannot roll ...);

4. use the mysql command to import mysql.

The idea is clear, so I did it. The problem was not found until the last step: the data volume was too large (0.68 million records), which was slow and slow.

It's just a slow job. it's okay to only do it once (wait for me for nearly 2 hours... However, we immediately discovered that the time format in the data was incorrect. For example, Access is 2011-1-1 1, while mysql expects 100,000. at this time, it would be better to re-import a new one if we need to change several hundred million records...

Therefore, Repeat step 2 to obtain the data.

Because the import process is too slow, I found a file backed up by mysqldump to see how the mysql official thought it would be faster, so I found the following content:

SQL code
Lock tables 'tablename' WRITE;
Alter table 'tablename' disable keys;
Insert into 'tablename' (id, name, password) VALUES
-- Here is the data
;
Alter table 'tablename' enable keys;
Unlock tables;

Lock tables 'tablename' WRITE;
Alter table 'tablename' disable keys;
Insert into 'tablename' (id, name, password) VALUES
-- Here is the data
;
Alter table 'tablename' enable keys;
Unlock tables;

Obviously, the SQL here has been optimized from three aspects:

1. added a write-only lock (no query is allowed before unlocking, and the index can be done at the same time to insert data quickly );

2. Disable all the keys (as a result, no foreign key check is required and data is fully inserted );

3. insert into is completed with a statement (the performance of a statement and a golden statement is not very different. think about it as well ...).

In this case, it will take several minutes.

In addition, let's talk about text processing...

At the beginning, it was customary to write an SQL statement, so I thought of using macros. Macros are simple, and small data volumes are very convenient, so you don't need to think about them. However, after a while, we found that it was too slow to process large data volumes.

I usually seldom write vim scripts. now I want to use them very slowly. after all, I need to execute a line of lines...

Therefore, write the regular expression and replace the search. Soon, the replacement will be completed in several seconds. The power of regular expressions is not very powerful... The time format conversion mentioned above is also done here.

Note: Even if your text looks good, you can try column editing. It is applicable to the beginning of a general line, and almost all the later sections are unavailable...

It is unnecessary not to convert multiple rows into one row. After a line is changed, the readability will be poor, and the text processing will be very slow. If a row matches too many times, using the g parameter will often be slow...

Let's just say that...

I reviewed my ideas...

It is estimated that this will be done in the next 10 minutes...

BitsCN.com