MySQL Cookbook 10th Chapter Reading notes

1, using load data and Mysqlimport to import

1 x ok,2 warning!!!

Why are there warning, and the data is not load in?

2, specify the data file location

Learn about MySQL's rules for locating file locations.

If the load data statement does not have a local option, the MySQL read data file will locate the file on the machine where the server is located according to the following rules:

• The absolute full path name of the file (starting at the root of the filesystem), MySQL reads the file directly.
• Given the relative pathname of the file, it is interpreted and processed in two different forms of the path name.

3, specifying the structure of the data file

There is a data file but not the load data default format, then we must use the fields and the lines clause to tell load data how to interpret the file. In addition the local specified client location rule is the current directory. A starting by clause is also included under the lines clause to specify the sequence in which portions of the content are stripped in the middle of each line. If starting by ' X ' is specified and the input line begins with ABCX, all four characters are stripped.

4, handling quotes and special characters

Load data beware of quotes and escape characters. Three clauses of the fields clause (enclosed by, escaped by and terminated by)

Where you want to completely prohibit escape character processing, you can specify an empty escape sequence: Fields escaped by ', for example, the reference and escape characters are "and \, the input value is" a "" b\ "C", is interpreted as a "B" c.

For Mysqlimport, the command options for specifying references and transitions are-fields-enclose-by–fields-escaped-by.

5. Import the CSV file

The CSV file is a comma-rather than a tab-delimited value.

For example: A CSV file that contains lines that end with a carriage return newline mytbl.txt can be loaded into MYTBL using the following statement:

The corresponding use of the Mysqlimport statement:

6. read files from different operating systems

Use different end-of-line sequences in different operating systems

Unix usually ends the line with a newline character, and is used in the load data statement: lines terminated by ' \ n '

MAC Os x uses the carriage return character, which is used in the load data statement: lines terminated by ' \ R '

Windows uses the carriage return newline pair, which is used in the load data statement: lines terminated by ' \ r \ n '

7, Handling duplicate key values

When your input contains records whose key values match the existing table row key values, you can choose to have load data ignore new records or replace old records

For example, when you make sure that the data in your database only has the most up-to-date data, such as periodically receiving meteorological data about the current weather conditions from different monitoring stations, and storing the data in a database, you can choose replace when the file is load data or ignore to tell MySQL to replace old rows with new values or ignore duplicate rows.

Load data local infile ' data.txt ' replace into table Weatherdata

The corresponding mysqlimport has the ignore and replace keyword options with load data.

8, get diagnostic information about the wrong input data

After operating load data, you want to display some diagnostic information for the input data, and when a file is loaded into a table, the information that you see:

You can use Show messages to view related diagnostic information.

9, skipping data file lines

Adding a clause to load data ignore n lines means ignoring the start n rows of the data file, Mysqlimport supports-ignore-lines=n options with the same effect

10, specifying the input column order

The columns and tables in the data file do not have the same column order, and need to be specified manually:

Assuming that the table has column a,b,c and the data in the data file corresponds to the column b,c,a, the file is loaded at this point:

Load data local infile ' mytbl.txt ' into table Mytbl (b,c,a)

The corresponding Mysqlimport statement uses the-COLUMNS option to specify the column list:

Mysqlimport--Local--columns=b,c,a cookbook Mytbl.txt

11, preprocessing the data file before inserting the input value

If the data file contents and corresponding tables are:

Use the following command to Preprocess:

The contents of the data table after load:

12, ignoring data file columns

Assume that the information is loaded from the UNIX password file/etc/passwd, which contains lines in the following format:

Account:password:UID:GID:GECOS:directory:shell

CREATE table structure for additional column information:

Dummy is a user-defined variable, which ignores any column, which is called a dummy variable.

or use Mysqlimport in--columns:

13. Export query results from MySQL

Want to export MySQL query structure to a file or redirect the output of another MySQL program

1,select. The into OUTFILE statement exports the query structure to a file on the server.

2, if you want to get results on the client host, the result of the export query is to redirect the output of the MySQL program

Using the MySQL client to export data, select ... into outfile write the data file to the host file, and your MySQL account needs file permissions.

--skip-column-names tells you not to write the column name rows that are typically located in the front of the statement output.

14. Export the table as a text file

Use the Mysqldump program with the--tab option. The mysqldump program is used to copy or back up tables and databases. You must specify a--tab to specify the directory to write to the file

Because Mysqldump is a lot of time equal to select ... into outfile must have file permission

Change to root account ok

15. Export table contents and definitions in SQL format

SQL format:

To export multiple tables of a database:

All databases:

Some options are used to control the output format:

You can also use these exported files to generate the corresponding databases on other servers:

16, using SQL mode to control incorrect input data processing

When inserting data that is not suitable for tabular format, a warning appears, but the data is still partially inserted into the table.

To eliminate this problem, we usually set the SQL mode

This mode requires more stringent, there are other settings mode does not explain, it should be pointed out that the Sql_mode is only valid in the current SQL dialog, to be effective in SQL client, you need to use the keyword global: