mysql +导入一个文件在列标题+如何处理的空格 [英] mysql + importing a file with spaces in the column headers + how to handle

问题描述

我有一个csv文件，我想导入到mysql，我慢慢地到那里。

I have a csv file that I would like to import into mysql, and I am slowly getting there.

我试图使用LOAD DATA INFILE 但是首先我必须创建表，这是我的问题所在。

I am trying to use the LOAD DATA INFILE but first I have to create the table and this is where my issues is.

我试图导入的file.csv看起来像这样（但是这个例子中只有1列Rec Open Date）：

The file.csv I am trying to import looks like this(but it only has 1 column for this example "Rec Open Date"):

"Rec Open Date", <other columns e.g. "Data Volume (Bytes)">
"2015-10-06", <other values>

现在我想在表中的列标题与csv文件相同，不能得到这个工作使用'或，如下所示在EG1和EG2
我可以得到这个工作的方式是更改替换空格用下划线表头，即Rec开放日期去到Rec_Open_Date，但这将涉及更改csv文件中的列标题名称，即用表头中的下划线替换空格这似乎是我最好的选择，但任何人都可以告诉其他明智的理想情况下，我想

Now I would like the column header in the table to be the same as the csv file, but I can't get this to work using ' or ", shown below in EG1 and EG2. The way I can get this to work is to change replace the spaces with underscores in the table header i.e. "Rec Open Date" goes to "Rec_Open_Date". But this would involve changing the column header names in the csv file i.e. replace the spaces with underscores in the table header. This seems to be the best option for me, but can anyone advise other wise? Ideally I would like the csv file column headers to be the same as the datatable headers and EG3 seems to be the way to achieve this.

EG1：

mysql> CREATE TABLE IF NOT EXISTS test1234 (
    ->   id int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
    ->   'Rec Open Date' Date NOT NULL COMMENT 'Rec Open Date',
    ->   PRIMARY KEY (id)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='datatable demo table' AUTO_INCREMENT=64 ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Rec Open Date' Date NOT NULL COMMENT 'Rec Open Date',
  PRIMARY KEY (id)
) ENGI' at line 3
mysql>

EG2

mysql> ^MCREATE TABLE IF NOT EXISTS test1234 (
    ->   id int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
    ->   "Rec Open Date" Date NOT NULL COMMENT 'Rec Open Date',
    ->   PRIMARY KEY (id)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='datatable demo table' AUTO_INCREMENT=64 ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"Rec Open Date" Date NOT NULL COMMENT 'Rec Open Date',
  PRIMARY KEY (id)
) ENGI' at line 3
mysql>

EG3：

mysql> CREATE TABLE IF NOT EXISTS test1234 (
    ->   id int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
    ->   Rec_Open_Date Date NOT NULL COMMENT 'Rec Open Date',
    ->   PRIMARY KEY (id)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='datatable demo table' AUTO_INCREMENT=64 ;
Query OK, 0 rows affected (0.00 sec)

mysql>

注意
在EG3之后，请执行以下操作，但文件中的列标题必须与数据表上的列标题相同，如我所知。

NOTE following EG3, creating the table I would then do the following, but the column headers in the file would have to be the same as the ones on the datatable, as I understand it.

LOAD DATA LOCAL INFILE'file.csv'INTO TABLE test1234 COLUMNS TERMINATED BY'，';

推荐答案

MySQL LOAD DATA语句从文件按位置加载值，而不是按名称加载。

The MySQL LOAD DATA statement loads values from the file by position, not by name.

MySQL没有任何关于匹配任何列名称的字段（来自.csv文件的标题行）的名称的要求。 （你的系统的某些其他组件可能有这种类型的需求，但MySQL LOAD DATA没有。）

MySQL doesn't have any requirement about the "names" of the fields (from the header line in the .csv file) "matching" any column names. (It's possible that some other component of your system has that type of requirement, but MySQL LOAD DATA doesn't.)

它对于.csv文件有效标题行。当有标题行时，我们通常使用 IGNORE 1 LINES 跳过它。

It's valid for a .csv file to not have a header line. When there is a header line, we typically "skip over" it using IGNORE 1 LINES.

，我更喜欢在LOAD DATA语句中使用关键字 FIELDS 代替 COLUMNS 。

As an aside, I prefer to use the keyword FIELDS in the LOAD DATA statement in place of COLUMNS.

在MySQL中，标识符（列名，表名）可以使用反引号字符进行转义。要使用包含无效字符的标识符（如空格），必须对标识符进行转义。

In MySQL, identifiers (column names, table names) can be escaped using backtick characters. To use an identifier that includes invalid characters, like a space, the identifier must be escaped.

CREATE TABLE ... 
... 
`Rec Open Date` DATE NOT NULL COMMENT 'Rec Open Date',
^             ^

在我的键盘上，反引号是位于 1左侧`〜左上角的键。

On my keyboard, the backtick is the key at the upper left ` ~ just to the left of the 1 ! key.

ANSI_QUOTES

如果 sql_mode 变量包括 ANSI_QUOTES ，那么您也可以使用双引号来转义标识符。例如

If sql_mode variable includes ANSI_QUOTES, then you can also use double quotes to escape identifiers. e.g.

SHOW VARIABLES LIKE 'sql_mode' ;

SET sql_mode = 'ANSI_QUOTES,...' ;

SHOW VARIABLES LIKE 'sql_mode'

Variable_name  Value        
-------------  -----------
sql_mode       ANSI_QUOTES  

然后：

CREATE TABLE ... 
... 
"Rec Open Date" DATE NOT NULL COMMENT 'Rec Open Date',
^             ^

如果 sql_mode / em> include ANSI_QUOTES （显式或包含在某些其他设置中），则identifers的双引号不起作用。

If sql_mode does not include ANSI_QUOTES (either explicitly or included in some other setting), then the double quotes around identifers doesn't work. And using single quotes around identifiers shouldn't ever work... single quotes enclose string literals.

外卖：use反引号字符转义标识符（列名，表名等），并在字符串文字周围使用单引号。避免在字符串文字周围使用双引号，以使您的SQL代码即使在设置ANSI_QUOTES时也可以工作。

The takeaway: use backtick characters to escape identifiers (column names, table names, etc.) and use single quotes around string literals. Avoid using double quotes around string literals, to make your SQL code work even when ANSI_QUOTES is set.

这篇关于mysql +导入一个文件在列标题+如何处理的空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！