author ： Yang Taotao
Senior database expert , Specialized research MySQL More than ten years . Good at MySQL、PostgreSQL、MongoDB And other open source database related backup recovery 、SQL tuning 、 Monitoring operation and maintenance 、 High availability architecture design, etc . At present, I work in akerson , For major operators and bank financial enterprises MySQL Related technical support 、MySQL Related courses, training, etc .
In this paper, the source ： Original contribution
* Produced by aikesheng open source community , Original content is not allowed to be used without authorization , For reprint, please contact the editor and indicate the source .
MySQL 8.0.22 In this year's 10 month 19 Promulgated by the , It brings us a lot of very useful features . Especially for MySQL Shell 8.0.22 Come on ,UTIL Components have more functions , More abundant , It's easier to use .
For example, today we will introduce import_table(importTable) The extension function of ： Customizable line input .
import_table(importTable) We've introduced , Is a parallel import of various formats of text tools , Encapsulates the MySQL sentence load data local infile.
For example, to import one to TAB Text data file for separator ：/tmp/sample_ytt.txt To table ：ytt_new.t1, You can execute the following statement ：
It turns out that load data infile The import result of the statement . If you switch to import_table Methods to do the same thing , be based on Python grammar , How to use it is as follows ：
So let's look at another requirement ： Preprocess each line when importing a text file （ For example, changing columns before importing data r2 The value of is mod(r1,10), Column r5 The value of is abs(r4-46) ）, This can reduce the time cost of processing again after import .
This demand uses load data infile Statements are very easy to implement ：（ Change columns on import r2 and r5 The data of , similar UPDATE grammar ）
If you want to use util.import_table(importTable) To achieve the above requirements , stay MySQL 8.0.22 There was no way before .
With MySQL 8.0.22 Release ,MySQL Yes import_table Methods do some extension functions , An option has been added “decodeColumns” It can realize the customized function of field input in advance , And it can be richer .
Next use import_table To achieve the above requirements , Custom fields r2 and r5：
above Options Options , See the picture below ：
Let me explain in detail the meaning of the figure above ： In blue columns The corresponding array specifies the fields of each line in the data file , Which is the default TAB The value of each column that the separator separates ,1 and 2 Represents a placeholder ,1 Represents the first column of each row in the data file ,2 Represents the fourth column of each row in the data file ,decodeColumns The dictionary processes the fields that need to be entered in advance . such as r1 Fields remain as variables @1,r2 The corresponding field mod(r1,10) etc. .
If you still don't understand the transformation rules , It can be opened temporarily general log, above import_table（importTable） Corresponding MySQL Log for ：
The above log is very clear , Internal conversion to the most basic load data infile grammar .
Here I have a brief interpretation of MySQL 8.0.22 Yes MySQL Shell A new feature of customized input text files , More new features can continue to be focused on .