MySQL如何使用LOAD DATA INFILE指定字符串位置 [英] MySQL how to specify string position with LOAD DATA INFILE

问题描述

我有ASCII文件，每行有固定数量的字符，没有定界符.我想使用LOAD DATA INFILE导入到我的表中.

I have ASCII files with a static number of characters for each line with no delimiters. I'd like to use LOAD DATA INFILE to import into my table.

文件示例:

USALALABAMA                                                               
USARARKANSAS                                                              
USFLFLORIDA                                                               

此表的结构:

country Char(2)
state Char(2)
name Varchar(70)

CREATE TABLE `states` (
  `country` char(2) COLLATE latin1_general_ci NOT NULL,
  `state` char(2) COLLATE latin1_general_ci NOT NULL,
  `name` varchar(70) COLLATE latin1_general_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1_general_ci COLLATE=latin1_general_ci;

是否可以为每列指定开始和结束位置?

Is it possible to specify a start and end position for each column?

推荐答案

根据文档，您可以加载固定格式的文件，而无需使用临时表.

According to the documentation, you can load a fixed format file without using a temporary table.

如果FIELDS TERMINATED BY和FIELDS ENCLOSED BY值均为空('')，则使用固定行(无分隔符)格式.使用固定行格式时，字段之间不使用定界符(但是您仍然可以使用行终止符).而是使用足够宽的字段宽度来读取和写入列值，以容纳该字段中的所有值.对于TINYINT，SMALLINT，MEDIUMINT，INT和BIGINT，无论声明的显示宽度是多少，字段宽度分别为4、6、8、11和20.

If the FIELDS TERMINATED BY and FIELDS ENCLOSED BY values are both empty (''), a fixed-row (nondelimited) format is used. With fixed-row format, no delimiters are used between fields (but you can still have a line terminator). Instead, column values are read and written using a field width wide enough to hold all values in the field. For TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT, the field widths are 4, 6, 8, 11, and 20, respectively, no matter what the declared display width is.

位置是从列定义派生的，在您的情况下，列定义与文件的结构匹配.因此，您只需要执行以下操作即可:

The positions are derived from the columns definitions, which in your case match the structure of the file. So you just need to do:

LOAD DATA INFILE 'your_file' INTO TABLE your_table
  FIELDS TERMINATED BY ''
  LINES TERMINATED BY '\r\n'
  SET name = trim(name);

这篇关于MySQL如何使用LOAD DATA INFILE指定字符串位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！