时间戳

首页 > 解决方案 > MYSQL 错误 1366, 1265 导入 200 万+ 行大 csv 文件时

mysql - MYSQL 错误 1366, 1265 导入 200 万+ 行大 csv 文件时

问题描述

问题：我需要导入一个包含超过 200 万行、12 列的 csv 文件。为此，我将其命名为 stack.csv

列： Search_term、货币、成本、Avg_CPC、展示次数、点击次数、Impr_Top_Percent、Impr_Abs_Top_Percent、转化次数、Conv_value、Conv_rate、Conv_value_cost

行示例：

表格样本

到目前为止，我遇到了两个主要问题。我会提出问题是什么以及我的解决方案。

1）问题：

csv 上有一个标题（行开始的上方）导致匹配错误，并且实际的列标题使用了特殊字符。我无法使用 excel 打开它，因为它被限制为 1,000,000 行。单元格中还内置了百分比符号。

解决方案：经过许多不同的解决方案后，我在一个简单的文本文件中打开它并删除了主标题并重命名了列标题。我还使用查找/替换删除了所有百分号。

2）我创建了一个表，使用导入向导将 1:1 导入 MYSQL。这是代码：

CREATE TABLE stack (
  id INT(7) NOT NULL AUTO_INCREMENT,
  Search_Term VARCHAR(255) NOT NULL,
  Currency VARCHAR(10) NOT NULL,
  Cost DECIMAL(19,2) NOT NULL,
  Avg_CPC DECIMAL(19,2) NOT NULL,
  Impressions INTEGER(10) NOT NULL,
  Clicks INTEGER(10) NOT NULL,
  Impr_Top_Percent DECIMAL(10,2) NOT NULL,
  Impr_Abs_Top_Percent DECIMAL(5,2) NOT NULL,
  Conversions DECIMAL(5,2) NOT NULL,
  Conv_value DECIMAL(10,2) NOT NULL,
  Conv_rate DECIMAL(5,2) NOT NULL,
  Conv_value_cost DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (id)
);

问题：当我开始导入时，遇到以下错误：

行导入失败并出现错误：（“不正确的十进制值：第 1 行的列 'Conv_rate' 的'0.00%'”，1366）

行导入失败并出现错误：（“不正确的十进制值：第 1 行的列 'Conv_value_cost' 的'0.00%'”，1366）

行导入失败并出现错误：（“第 1 行的“展示次数”列的数据被截断”，1265

解决方案：我给了 DECIMAL 值足够的空间，所以我真的卡住了。在尝试导入 (SET SESSION sql_mode = '';) 之前，我运行了一些代码以使其脱离严格模式。它什么也没做。

所以我的问题是

我该怎么做才能消除错误？

这是我第一次进口这么大的东西——通常需要几个小时吗？我让它运行了 50 分钟，它大约是 20%

当我停止导入以对某些行（大约 10,000 行）进行超级快速抽查时，数据似乎很好？错误更多的是形式吗？它会自动更正以适合我的参数吗？

我是否应该尝试为我遇到错误的列做 VARCHAR 作为最后的手段？甚至做VARCHAR然后抽查？

仅供参考，我正在运行 MYSQL 8.0。非常感谢大家。

标签： mysqlsqlcsv

解决方案

请注意我在 Mac 上。我将简洁地发布答案，然后进行一些解释。

  1. 将所有错误列切换为 VARCHAR

  2. 不要使用 MYSQL 数据导入向导。*1

  3. 在 MYSQL 中使用以下代码导入。

    加载数据文件'[文件路径]/stack.csv'

    INTO TABLE [您在 SQL SCHEMA 中创建的表的名称]

    以“，”结尾的字段

    由'"'包围

    以“\n”结尾的行

    忽略 1 行；[这忽略了第一行/标题，仅供参考]

  4. 我收到了一个安全文件私有问题 ERROR 1290

  5. 在 MYSQL>administration>Instance [click wrench]>system profile[确保 在此处输入图像描述

确保配置文件指向您的 my.cnf

my.cnf 文件应位于这些文件夹位置之一（请注意，usr 文件可能被隐藏/变灰）

/etc/my.cnf 
/etc/mysql/my.cnf 
/usr/local/etc/my.cnf [most likely]
~/.my.cnf

使用简单的文字编辑器打开 cnf 文件

将以下内容添加到底部并保存：secure-file-priv = ""

在终端输入 mysql.server 重启

现在你应该很好！

*1 它花费的时间太长并且没有优化。我说的是几个小时。

推荐阅读