MySQL data import and export methods and tools _ MySQL

1). mysqlimport syntax introduction :? | T ^ 5slgmysqlimport is located in the mysqlbin directory and is a very effective tool for mysql to load (or import) data. This is a command line tool. There are two parameters and a large number of options to choose from. This tool imports a text file to your specified database and table. For example, mysql management tools

1). mysqlimport syntax introduction :? = | T ^ 5slg
Mysqlimport is located in the mysql/bin directory and is a very effective tool for mysql to load (or import) data. This is a command line tool. There are two parameters and a large number of options to choose from. This tool imports a text file into your specified database and table. In other words, we want to import data from the customers.txt file to the database Meet_A_Geek table Custermers: C \ t "I + 3
Mysqlimport Meet_A_Geek Customers.txt/"M1WP6l
Note: customers.txt is the text file for data import, while Meet_A_Geek is the database to be operated, and the table name in the database is Customers, the data format of the text file must be the same as that of the record in the MERS table. Otherwise, the mysqlimport command will fail. By 5o # s7q
The table name is the character string of the file before the first period (.) of the imported file. for another example: 8Z [iHx @
Mysqlimport Meet_A_Geek Cus.to.mers.txt gcb % "Z'
Then we will import the content in the file to the Cus table in the database Meet_A_Geek. In the above example, only two parameters are used and no more options are used. The following describes the options of mysqlimport.

2) Introduction to common mysqlimport options: Kso7:
Option function iaT # JP (C
-D or -- delete all information in the data table before the new data is imported into the data table. c (a + 'p
-F or -- force: no matter whether an error occurs or not, mysqlimport will force the data to continue to be inserted. f + 9p9; MKP"
-I or -- ignore mysqlimport skips or ignores rows with the same unique keyword. data in the import file will be ignored. X: {2.e)
-L or-lock-tables: the table is locked before data is inserted. This prevents you from affecting your queries and updates when updating the database. FhT '_~
-R or-replace is opposite to-I. This option replaces records with the same unique keywords in the table. S? G} q
-- Fields-enclosed-by = char Ra8; UYA
Specifies the title of a data record in a text file. in many cases, the data is enclosed by double quotation marks. By default, data is not enclosed by characters. C' C (, = \
-- Fields-terminated-by = char HRME2Wc
Specifies the delimiter between values of each data. in a file separated by periods, the delimiter is a period. You can use this option to specify the delimiter between data. The default delimiter is the Tab character "R6 (1y/X
-- Lines-terminated-by = str jJb [peoa
This option specifies the delimiter string or character between the row and row in a text file. By default, mysqlimport uses newline as the line separator. KbAX5w0t.
You can choose to use a string to replace a single character: a new line or a carriage return. ? GVI (8IF
Common options of the mysqlimport command include-v display version and-p prompt for password.

3). example: import a file with a comma as the delimiter. the row record format is as follows: e! Ia? U
"1", "ORD89876", "1 Dozen Roses", "19991226" UO (jW)> N
Our task is to import the data in this file to the table Orders in the database Meet_A_Geek. We use this command: t cy & J
Bin/mysqlimport-prolactin-fields-enclosed-by = "-fields-terminated-by =, Meet_A_Geek Orders.txt R! UaQ> QU
This command may seem uncomfortable, but it is very simple when you are familiar with it. YteX9ej7
The first part is bin/mysqlimport, which tells the operating system that the command you want to run is mysqlimport in the mysql/bin directory. option p requires a password, this requires you to enter the password before changing the database, which makes the operation safer. We use the r option because we want to replace the record with a unique keyword that already exists in the file record with the data in the file. The data in our form is not up-to-date and needs to be updated using the data in the file. Therefore, we use the r option to replace the existing records in the database. L option is used to lock the table when we insert data, which prevents users from querying or changing the table when we update the table. # JT !, I ^
N: x9 _ @ gO4
-------------------------------------------------------------------------------- G46P7l?
How to import and export large amounts of data from MySQL d06 JTN
Http://www.cx66.com/cxgzs/tips/00773.htm S \ M @ 0Mo
You must have used the database import and export function in phpmyadmin, which is very convenient. But in practice, I found the following problems: ^ jXgg> + V,
1. the size of the database exceeds a certain size. for example, if the size of 6 MB is used for export, the database can be correctly saved to the local hard disk, but the import will not work! The reason is: the size of temporary files/uploaded files is limited to 2 MB in PHP. INI, while phpmyadmin uses the upload method, resulting in a failure. DiB! 2% GB
2. when importing the. SQL file exported to the hard disk back, the import may fail due to some single quotation marks, which can only be imported using applications such as mysql. @ EYvpfz
My database has exceeded 10 MB, so this problem must be solved. My idea: Zv | Q = t
Export: use phpmyadmin to save the database/table structure, use scripts to read the database content, and save it to a file! B ^ Nm: T
Import: use phpmyadmin to restore the database/table structure, use scripts to read files, and save them to the database! } LIA =
Ts} s &~ 4z
The export program is as follows: the call method is ***. php? Table = tablename tOO _ @ =
This simple program currently saves a table at a time !! Data of one field for each behavior !! YuOR ")
LI \~ 7BJ
G =) Lvj
If ($ table = "") exit (); v_U, K; P
Mysql_connect ("localhost", "name", "password"); NTZ2.H284
Mysql_select_db ("database"); T2M @ V
$ Result = mysql_query ("select * from $ table"); eU $ hQ/r
If (mysql_num_rows ($ result) <= 0) exit (); d (G Echo "start to convert data to text... [.. (b4g7R * %
";]-(]/B'
$ Handle = fopen ("export table.txt", "w"); % S _] m9hN
$ Numfields = mysql_num_fields ($ result); GJ + r @ RQp
Fputs ($ handle, $ numfields. ""); DC1 $ 'mdd
For ($ k = 0; $ k 7EZg9x? /.
{8 k * \ z1 ^ = M;
$ Msg = mysql_fetch_row ($ result); G | 7 z )}-
For ($ I = 0; $ I <$ numfields; $ I ++) ip1k ~} 1 <
{AqK $ Msg [$ I] = str_replace ("", "& php2000mysqlreturn &", $ msg [$ I]);! X81omV?
$ Msg [$ I] = str_replace ("", "& php2000mysqlreturn &", $ msg [$ I]); LoY) [3u (X
Fputs ($ handle, $ msg [$ I]. ""); A 4? %) Bv
} N % (O! 6
Fputs ($ handle, "------- php2000 dump data program V1.0 for MySQL --------"); >^| xa> *
} 0m1R # ^ Fclose ($ handle); 78d} XJQV
Echo "OK"; g [DVi
?> Lzfwy (
KOCsy6j8
The imported program is as follows: the usage is the same as above! NF {a> 2 + B
If ($ table = "") exit (); G? . Uy"
Mysql_connect ("localhost", "name", "password"); 8'a5ivd
Mysql_select_db ("database"); <> Z + w [;
$ Message = file ("audio table.txt"); iSo 'f \ uQ
Echo $ numfields = chop ($ message [0]); rdG [-z: mC
For ($ k = 1; $ k) EaR # m 1
{B 1 rYrgo I
$ Value = ""; EjT bi6'
For ($ I = $ k; $ I <($ k + $ numfields-1); $ I ++) ah (& <\ p,
{? M9k = cy
$ Tmp = str_replace ("& php2000mysqlreturn &", "", chop ($ message [$ I]); + Y) "VFYB
$ Value. = "'". addslashes ($ tmp). "',"; 5 p:} @ Zhw #
} RV3vY} Qsz
$ Tmp = str_replace ("& php2000mysqlreturn &", "", chop ($ message [$ k + $ numfields-1]); x H3 (Y o %
$ Value. = "'". $ tmp. "'"; l5V7 (V ?. X
$ Query = "insert into $ table values (". $ value. ")";-Dr zN]
Echo mysql_error (); IEGZ '5> 8
Mysql_query ($ query); 8} ec "0 ^ g
Echo $ k. ""; y (~ Xf <0 "} B
}? FP [> 3 <
Echo "OK"; N ^ rN_A @)
?>

Usage and possible problems! XYBn; % Hd}
1. there may be problems with the file () function during import (my 10 M data is normal). you can change it to fopen () and then read a row again !! D JmJ0W.
2. ftp is required for import and export. after export, ftp is used to transfer the data to the local machine. ftp is used to transfer the data to the server before import! % Duj & rhBo
Use cases for mysql database export and import commands CFFZ $ M @
================================ 'Vijqoj}
Several common use cases: & GrKJ + N: u
1. export the entire database 0Q_ B <'a
Mysqldump-u username-p database name> exported file name % 5'ei6q
Mysqldump-u WCNC-p smgp_rj_wcnc> WCNC. SQL

2. export a table I nkdq !? '
Mysqldump-u user name-p database name table name> exported file name 9X-'M
Mysqldump-u WCNC-p smgp_rj_wcnc users> WCNC_users. SQL 5 Ot-| $ S ^
3. export a database structure Ur! Ab5'
Mysqldump-u WCNC-p-d -- add-drop-table smgp_apps_WCNC> d: WCNC_db. SQL I; h {L (/2
ETXG9B (
-D no data -- add-drop-table add a drop table B before each create statement] QUhDx, N
Pl9HL 4. import database V 0B | 3
Common source commands: dh2vI/r4.7>
Go to the mysql database console, B; | Sm
For example, mysql-u root-p I '? 4 _ 'oa
[QXnk "Eh
Mysql> use database % 6 'O ". l:
Hg) B; 8UV3
Then run the source Command. the following parameter is the script File (for example,. SQL used here) wr =} 6ei @ M.
Mysql> source d: WCNC_db. SQL

See MySQL data export and import tool: mysqldump 20w' q17> aY
Tu ^ 4cg50 '{
========================================================== YlbA <26hc"
Ey % PK
MySQL data import and export method and tool Introduction 2 Z9 \ mB) f
4hmhGnY.
Batch processing is a non-interactive way to run mysql programs. like the commands you use in mysql, you will still use these commands. To implement batch processing, you need to redirect a file to the mysql program. First, we need a text file containing the same text as the command we entered in mysql. The suffix and any other valid names do not have to end with SQL.): 4 #4 fpDQ
SF @ pVFp
Kd. ZwL +
USE Meet_A_Geek;. ^ g3? O
Insert into mers (Customer_ID, Last_Name) VALUES (NULL, "Block"); Gx = sj ')
Insert into mers (Customer_ID, Last_Name) VALUES (NULL, "Newton");. = ~ Tcg
Insert into mers (Customer_ID, Last_Name) VALUES (NULL, "Simmons"); + 3k9jO1sU
) U, Q $ z * 6
Note that the syntax of these sentences must be correct, and each sentence ends with a semicolon. Select the database for the above USE command, and INSERT the command to INSERT data. 'Q ~ AZ +
3 VGsZN $ Z
Next, we need to import the above files to the database. before importing the files, we need to confirm that the database is running, that is, the mysqld process (or service, which is called "service" under Windows NT). unix is running. Run the following command: ^ 'jhlq3
Bin/mysql-p Enter the password as Prompted. if the statements in the above file are correct, the data will be imported to the database. XabVf3
JB ~ R
Use load data infile in the command line to import DATA from the file to the database: Q> Yo ^ U, X
Now you may ask yourself, "Why do I need to input all these SQL statements into the file and then run them through the program ?" This seems to require a lot of work. That's good. you may be right. But what if you have a log record generated from all these commands? This is great now. well, most databases will automatically generate the log of event records in the database. Most logs contain original SQL commands that have been used. Therefore, if you cannot export data from your current database to a new mysql database, you can use the batch processing features of log and mysql, to quickly and conveniently import your data. Of course, this saves the trouble of typing. F3S ~ IGf2
Load data infile EYlT/V {
This is the last method to import data to the MySQL database. This command is very similar to mysqlimport, but this method can be used in the mysql command line. That is to say, you can use this command in all the programs that use the API. With this method, you can import the data you want to import in the application. \ O} fG! 1a
3FM B * P
Before using this command, the mysqld process (service) must be running. Start mysql command line:} q = | AcXD
Bin/mysql-p-* 5 tvZk
Enter the password as Prompted. after entering the mysql command line, enter the following command: R2, 'kwnr
USE Meet_A_Geek; Pz %-OOKg
Load data infile "/home/mark/data. SQL" INTO TABLE Orders; aUE e)
To put it simply, the content in the file data. SQL will be imported into the table Orders. like the mysqlimport tool, this command also has some optional parameters. For example, if you want to import data from your computer to a remote database server, you can run the following command: 57n, n \ B @
Load data local infile "C: \ MyDocs \ SQL .txt" INTO TABLE Orders; R37 0
The LOCAL parameter above indicates that the file is a LOCAL file, and the server is the server you log on. In this way, you can use ftp to upload files to the server. MySQL has completed. uoA $ "La" O for you.
RWfe-; j
You can also set the priority of the insert statement. if you want to mark it as LOW_PRIORITY, MySQL will wait until no one else reads the table, to insert data. You can run the following commands:>,/S; 8 <
Load data LOW_PRIORITY INFILE "/home/mark/data. SQL" INTO TABLE Orders; T ^; 9_MF
You can also specify whether to replace or ignore duplicate key values in the file and data table when inserting data. Syntax for replacing duplicate key values: T $ Ya. Tb {
Load data LOW_PRIORITY INFILE "/home/mark/data. SQL" REPLACE INTO TABLE Orders; ^ BFDg5}
The above sentence looks a little clumsy, but it puts the keywords in a place that your parser can understand. K 7W-AV
C ({2Q $ H}
The following options describe the file record format. these options are also available in mysqlimport. They look a little different here. First, you need to use the FIELDS keyword. if you use this keyword, the MySQL parser wants to see at least one of the following options: w] [} p
Terminated by character [Q ra [-I
Enclosed by character u3 iAb $ ns <
Escaped by character # ucS4c
These keywords have The same usage as their parameters in mysqlimport. The 9SkY, N ~ 4 (
The terminated by delimiter used to describe the field. the default delimiter is the tab character (\ t) G29AmQMeJ.
Enclosed by describes the starting characters of a field. For example, enclose each field in quotation marks. WPr x
Escape characters described by escaped. The default value is backslash: \. 0'5 +} sU S.
The following example uses the mysqlimport command to import the same file to the database using the load data infile statement: = ze "; Nmk K
Load data infile "/home/mark/Orders.txt" replace into table Orders fields terminated by ', 'un <% iy/. c
Enclosed by '"'; q3F &! 2
A mysqlimport tool in the load data infile statement does not have the following features: load data infile can import files to the database based on specified columns. L | cLr
This feature is important when we want to import part of the data. For example, when upgrading from an Access database to a MySQL database, you need to add some columns (columns/fields) to the MySQL database to meet some additional requirements. At this time, the data in our Access database is still available, but because the columns (fields) of the data are no longer matched with those in MySQL, the mysqlimport tool cannot be used. However, we can still use load data infile. the following example shows how to import DATA to a specified field: Ww "Vfb] q
Load data infile "/home/Order.txt" into table Orders (Order_Number, Order_Date, Customer_ID); cma [= xTfHG
As you can see, we can specify the required topic (fields ). These specified fields are still enclosed in parentheses and separated by commas. if you omit any of them, MySQL will remind you