Data output
The data export has several methods: using the SELECT INTO OUTFILE "filename" statement Using the MySQLDUMP Utility Use the SELECT INTO OUTFILE "filename" statement to execute it in the mysql command line or in the PHP program. I will take the following as an example of the mysql command. When used in PHP, it is changed to the corresponding query. However, when using this command, it is required that the user has a file permission. As we have a library for PHPTEST, there is a table for Driver. Now remove the driver into a file. Execute command: mysql> use phptest; Database Changed MySQL> Select * from driver inTo Outfile "a.txt"; Query OK, 22 ROWS AFFECTED ANTD DRIVER CONTER in. Note that the file name is to add single quotes. So where is this file? There is a Data directory in the mysql directory, which is where the database file is placed. Each library accounted for a subdirectory separately, so phptest's directory is C: / mysql / data / phptest (Note: My MySQL is installed under C: / MySQL). Ok, now we go in, A.txt is it. Open this file, probably: 1 Mika Hakinnen 1 2 David Coulthard 1 3 Michael Schumacher 2 4 Rubens Barrichello 2 ... There may be a lot of records. Between each field is separated by a tablet (/ t). Then we can modify the directory of the output file name to place it in the specified location. If "a.txt" can be changed to "./a.txt" or "/a.txt". Among them, "./a.txt" is placed in the C: / MySQL / Data directory, and the "/a.txt" file is placed in the C: / directory. So the current directory that the select command is the current directory of the database, here is C: / MySQL / DATA. Use the select command to specify the separation character, escape character, including characters, and record line dividing characters when the field is unloaded. Column in the following: Fields Terminated by "/ t" [optionally] enclosed by "" escaped by "//" terminated by "/ n" terminated indicates that field separation [optionally] encluded indicates what characters used by fields, if used Optionally, only CHAR and VERCHAR are included in Escaped indicating what is used as an escape character line Terminated when the escape is needed, indicating that the separation of each line is default, and these items are optional, not Elect the default value. You can make modifications as needed.
Examples are as follows: mysql> Select * from driver inTo outfile "A.TXT" Fields Terminated By "," Enclosed by ""; Query OK, 22 ROWS Affected (0.06 sec) results may be as follows: "1", " Mika, "Hakinnen", "1" "2", "David", "Coulthard", "1" "3", "Michael", "Schumacher", "2" "4", "Rubens", "Barrichello "," 2 "... You can see that each field is separated by", ", and each field is" "" includes it. Note that the record separator can be a string, please test it yourself. However, if the output file is reported in the specified directory, it will report an error, first delete the retest. It can be seen from the SELECT method using the mysqldump utility that the output file is only data without a table structure. Moreover, only one table can be handled at a time, it is not easy to handle multiple tables. However, you can write the select command to a SQL file (copy text should be easy), then execute it under the command line: mysql Database name first: mysqldump phptest> a.sql possible results as follows : # Mysql dump 7.1 ## Host: localhost database: phptest # ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------- # Server Version 3.22.32-Shareware-Debug ## Table Structure for Table "Driver" #create Table Driver (DRV_ID INT (11) Default "0" Not Null Auto_Increment, DRV_FORENAME VARCHAR (15) Default "" NOT NULL, DRV_SURNAME VARCHAR (25) Default "" NOT NULL, DRV_TEAM INT (11) Default "0" Not Null, Primary Key (DRV_ID)); ## DUMPING DATA for TABLE "Driver" #insert Into Driver Values (1, "Mika", "Hakinnen", 1); Insert Into Driver Values (2, "David", "Coulthard", 1); Insert Into Driver Values (3 , "Michael", "Schumacher", 2); Insert Into Driver Values (4, "Rubens", "Barrichello", 2); ... If there is a plurality of tables, it is listed below. It can be seen that this file is a complete SQL file. It is very convenient if you want to import it into other databases. It is convenient to pass the command line.
If you transfer data from locally to the server, you can upload this file and then load data in the server through the command line. If you just want to remove the table instruction, the command is as follows: mysqldump -d phptest> a.sql If you only want to remove the SQL command of the inserted data, the command is required as follows: mysqldump -t phptest> a. SQL, if I only want data, don't want the SQL command, how should I operate? MySQLDUMP -T./ PHPTest Driver Among them, only the -t parameter can remove the plain text file, indicating the directory of the data,. / Represents the current directory, which is the same directory with MySQLDUMP. If you do not specify a DRIVER table, the data of the entire database will be removed. Each table generates two files, one for a .sql file, contains the implementation of the build. The other is the .txt file, only the data is included, and there is no SQL instruction. For the detailed data file, you can specify a field separator, including characters, escape fields, and row log separators. The parameter is listed below: - Fields-Terminated-by = field separator - Fields-enclosed-by = field includes characters - Fields-optionally-enclosed-by = fields include characters, only on the char and verchar fields - -fields-escaped-by = escape character --Lines-Terminated-by = line record separator I think everyone should understand these parameters. One example is as follows: mysqldump -t./ --fields-tERMINATED-by =, --fields-enclosed-by = / "phptest driver output is:" 1 "," mika "," hakinnen "," 1 "" 2 "," David "," Coulthard "," 1 "" 3 "," Michael "," Schumacher "," 2 "," Rubens "," Barrichello "," 2 "... please pay attention to characters Use. Summary or more to unload the text using the SELECT and MySQLDUMP utilities .Select is suitable for processing, and mysqldump is manual operation while providing powerful export functions, and can handle the entire library, or the library Multi-table. You can decide yourself according to your needs. There are also some ways, such as direct database file copies, but the mobile database system and the original system should be consistent. This is no longer mentioned.
