The import _ table (Import_table(importTable) that we introduced before is a tool for importing texts in various formats in parallel, encapsulating MySQL statements? Load data local file.

For example, import a text data file with TAB as the separator: /tmp/sample_ytt.txt? For the table: ytt_new.t 1, you can execute the following statement:

What is the result above? Load data in a file? The import result of the statement. If you use the import_table method to do the same thing, based on Python syntax, you can use it as follows:

Then let's look at another requirement: preprocess each line when importing text files (for example, change the value of r2 column to mod(r 1, 10) and the value of r5 column to abs(r4-46) before importing data, which can reduce the time cost of reprocessing after importing.

This demand has been used? Load data in a file? Statement is very easy to implement: (Change the data of r2 and r5 columns when importing, similar to UPDATE syntax)

So if you want to use util.import_table(importTable) to achieve the above requirements, there is no way before MySQL 8.0.22.

With the release of MySQL 8.0.22, MySQL has expanded the import_table method and added an option "deColumns", which realizes the pre-input customization function of fields and can be more abundant.

Next, use import_table to realize the above requirements, and customize the fields r2 and r5:

See the figure below for the above options:

Let me explain the meaning of the above figure in detail: the array corresponding to the blue font column specifies each row of fields in the data file, that is, the value of each column is separated by the default tab separator, 1 and 2 represent placeholders, 1 represents the first column of each row in the data file, and 2 represents the fourth column of each row in the data file. decodeColumns dictionary handles the fields that need to be input in advance respectively. For example, the r 1 field is kept as the variable @ 1, and the r2 field corresponds to mod(r 1, 10).

If you still don't understand the conversion rules, you can temporarily open the general log. The MySQL log corresponding to the above import_table(importTable) is:

The above log is clearly written and internally converted into the most basic load data infile syntax.

Here, I have simply interpreted a new feature of MySQL 8.0.22 in customizing the input text file of MySQL Shell, and more new features can continue to be concerned.