Pseudo table:
| primary_key | first_name | last_name | date_of_birth |
| 1 | John Smith | | 07/04/1982 |
At the moment first_name contains a users full name for many rows. The desired outcome is to split the data, so first_name contains "John" and last_name contains "Smith".
目前,first_name包含许多行的用户全名。期望的结果是拆分数据,因此first_name包含“John”,last_name包含“Smith”。
I have a CSV file which contains the desired format of data:
我有一个CSV文件,其中包含所需的数据格式:
| primary_key | first_name | last_name |
| 1 | John | Smith |
Is there a way of using the LOAD DATA INFILE command to process the CSV file to UPDATE all rows in this table using the primary_key - and not replace any other data in the row during the process (i.e. date_of_birth)?
有没有办法使用LOAD DATA INFILE命令处理CSV文件以使用primary_key更新此表中的所有行 - 而不是在过程中替换行中的任何其他数据(即date_of_birth)?
2 个解决方案
#1
5
No. While LOAD DATA INFILE has a REPLACE option, it will actually replace the row in question - that is, delete the existing one and insert a new one.
不会。虽然LOAD DATA INFILE有一个REPLACE选项,它实际上会替换有问题的行 - 也就是说,删除现有行并插入一个新行。
If you configure your LOAD DATA INFILE to only insert certain columns all others will be set to their default values, not to values they currently contain.
如果将LOAD DATA INFILE配置为仅插入某些列,则所有其他列将设置为其默认值,而不是其当前包含的值。
Can you modify your CSV file to contain a bunch of UPDATE statements instead? Should be reasonably straightforward via some regex replaces.
您是否可以修改CSV文件以包含一堆UPDATE语句?通过一些正则表达式取代应该相当简单。
#2
7
In this situation I usually LOAD DATA INFILE to a temp table with identical structure. Then I do INSERT with ON DUPLICATE KEY UPDATE from the temp table to the real table. This allows for data type checking without wrecking your real table; it's relatively quick and it doesn't require fiddling with your .csv file.
在这种情况下,我通常将DATA INFILE加载到具有相同结构的临时表。然后我用ON DUPLICATE KEY UPDATE从临时表INSERT到真实表。这允许数据类型检查而不会破坏您的真实表格;它相对较快,不需要摆弄你的.csv文件。
#1
5
No. While LOAD DATA INFILE has a REPLACE option, it will actually replace the row in question - that is, delete the existing one and insert a new one.
不会。虽然LOAD DATA INFILE有一个REPLACE选项,它实际上会替换有问题的行 - 也就是说,删除现有行并插入一个新行。
If you configure your LOAD DATA INFILE to only insert certain columns all others will be set to their default values, not to values they currently contain.
如果将LOAD DATA INFILE配置为仅插入某些列,则所有其他列将设置为其默认值,而不是其当前包含的值。
Can you modify your CSV file to contain a bunch of UPDATE statements instead? Should be reasonably straightforward via some regex replaces.
您是否可以修改CSV文件以包含一堆UPDATE语句?通过一些正则表达式取代应该相当简单。
#2
7
In this situation I usually LOAD DATA INFILE to a temp table with identical structure. Then I do INSERT with ON DUPLICATE KEY UPDATE from the temp table to the real table. This allows for data type checking without wrecking your real table; it's relatively quick and it doesn't require fiddling with your .csv file.
在这种情况下,我通常将DATA INFILE加载到具有相同结构的临时表。然后我用ON DUPLICATE KEY UPDATE从临时表INSERT到真实表。这允许数据类型检查而不会破坏您的真实表格;它相对较快,不需要摆弄你的.csv文件。