mysql工作台，替换文件中的列 [英] mysql workbench, replace column from file

问题描述

我有一个1,000,000行.csv文件，该文件是使用mySQL Workbench上传到表中的，但是我忘记在开始之前将日期设置为YYYY-MM-DD，所以它们都以0000-00-00的形式上传.

I have a 1,000,000 row .csv file that I uploaded into a table using mySQL Workbench, but I forgot to makes the dates YYYY-MM-DD before I started, so they all uploaded as 0000-00-00.

上百万条记录花了将近8个小时，所以我真的不想不必一遍又一遍，但是我不知道是否有办法让我替换一下现在，我已经将日期更改为正确的格式，因此数据来自我最初上传的同一文件.

It took almost 8 hours to upload the million records, so I'd REALLY like to not have to do it all over again, but I can't figure out if there's a way for me to replace JUST that one column of data from the same file I originally uploaded, now that I've changed the dates to be in the correct format.

有人知道这是否可能吗?

Does anyone know if this is possible?

修改

发布所有内容的时间太长了，但是:这是show create table，其中有一些肉被取出:

It's WAY too long to post everything, but: here's the show create table with some of the meat taken out:

CREATE TABLE myTable
(   lineID int(11) NOT NULL AUTO_INCREMENT,
    1 varchar(50) DEFAULT NULL,
    2 varchar(1) DEFAULT NULL,
    3 int(4) DEFAULT NULL,
    4 varchar(20) DEFAULT NULL,
    DATE date DEFAULT NULL,
    PRIMARY KEY (lineID)
) ENGINE=InnoDB AUTO_INCREMENT=634205 DEFAULT CHARSET=utf8

版本为 5.6.20

截屏:

推荐答案

好.我建议明确使用 LOAD DATA INFILE .对于尚未使用过的用户，请将其视为一条选择语句，直到您看到为止.

Ok. I would recommend using LOAD DATA INFILE explicitly. For those that have not used it, consider it just as a select statement for now til you see it.

这是一篇关于性能和策略的好文章，标题为

Here is a nice article on performance and strategies titled Testing the Fastest Way to Import a Table into MySQL. Don't let the mysql version of the title or inside the article scare you away. Jumping to the bottom and picking up some conclusions:

无需使用原始文件即可将表导入MySQL的最快方法 files是LOAD DATA语法.对InnoDB使用并行化 取得更好的结果，并记住调整基本参数，例如您的 事务日志大小和缓冲池.仔细的编程和 导入可能会使2小时以上的问题变成2分钟的过程.你 可以暂时禁用某些安全功能以提高性能

The fastest way you can import a table into MySQL without using raw files is the LOAD DATA syntax. Use parallelization for InnoDB for better results, and remember to tune basic parameters like your transaction log size and buffer pool. Careful programming and importing can make a >2-hour problem became a 2-minute process. You can disable temporarily some security features for extra performance

这里也有一些要点，主要是在关于次级索引(您没有)的同行评论中来回回荡.对于其他人来说，重要的一点是在事实之后添加它们.

There are also fine points in there, mainly in peer comments back and forth about secondary indexes (which you do not have). The important point for others is to add them after the fact.

我希望这些链接有用.您的数据将在10分钟内到达(在另一个测试表中，并带有LOAD DATA INFILE).

I hope these links are useful. And your data comes in ... in 10 minutes (in another test table with LOAD DATA INFILE).

最慢的方式是通过编程语言通过while循环逐行进行.一定要快得多，这是批处理，一次插入一条语句一次传递200到1k行. LOAD DATA INFILE的性能显着提高.最快的是原始文件(我该怎么做，但超出了此处的讨论范围).

About the slowest way to do it is in a programming language via a while loop, row by row. Getting faster is certainly batch, where one insert statement passes along, say, 200 to 1k rows at a time. Up substantially in performance is LOAD DATA INFILE. Fastest is raw files (what I do, but beyond the scope of talking here).

这篇关于mysql工作台，替换文件中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！