导入期间的数据翻译

发布于 2024-11-09 03:34:04 字数 777 浏览 0 评论 0原文

我使用 PHP（Symfony 框架，如果重要的话）将大约 350,000 行从 CSV 文件导入 MySQL，我正在读取每一行并插入到 SQL 表中。这将是一个日常过程 - 并且行数将会增加。

我必须将其中 2 列转换为数字（这是外键）。我想知道最有效的方法。我正在尝试在 MySQL 中使用查找表以及在 PHP 中使用数组 - 但我想知道是否有更有效的方法。

两列可能有大约 60 种不同的组合，这是我拥有的翻译表的示例

Feature         Type          CODE
ANZIRL          Voice Call    8
BT2DT2          Data Call     6
BT2IL2          Voice Call    1
BT2UK2          Voice Call    2
DG2DG2          Voice Call    4
DG2DT2          Data Call     3
DG2EC           Voice Call    1
DG2EDQ          Voice Call    5
DG2EZY          Voice Call    7

，CSV 将如下所示，

DG2DG2,x,x,x,Voice Call,x,x
DG2DT2,x,x,x,Data Call,x,x

等等...

我需要将其插入到我的表中，

x,4,x,x
x,3,x,x

等等...

原文

I am importing approx 350,000 lines from a CSV file into MySQL using PHP (Symfony Framework if that matters) I am reading in each line and inserting into the SQL table as I go. This will be a daily process - and the number of lines will increase.

I have to translate 2 of the columns to a number (which is a foreign key). I want to know the most efficient way of doing it. I am experimenting with using a lookup table in MySQL and also an array in PHP - but I am wondering if there is a more efficient way.

There are approx 60 different combinations the 2 columns could be, here is an example of the translation table I have

Feature         Type          CODE
ANZIRL          Voice Call    8
BT2DT2          Data Call     6
BT2IL2          Voice Call    1
BT2UK2          Voice Call    2
DG2DG2          Voice Call    4
DG2DT2          Data Call     3
DG2EC           Voice Call    1
DG2EDQ          Voice Call    5
DG2EZY          Voice Call    7

And the CSV will be as follows

DG2DG2,x,x,x,Voice Call,x,x
DG2DT2,x,x,x,Data Call,x,x

etc...

I need to insert that into my table as

x,4,x,x
x,3,x,x

etc ....

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

ゝ偶尔ゞ 2024-11-16 03:34:04

我将按原样将 CSV 文件批量导入 MySQL（即创建一个镜像 CSV 字段布局的表），然后针对 CSV 导入表运行查询以获取外键代码。

类似这样的：

INSERT into sometable (code,other,fields)
SELECT code, other, fields
FROM codes c
JOIN csvtable ct
WHERE ct.feature = c.feature AND ct.type = c.type

基本思想是不在 PHP 或 MySQL 中逐行执行处理，而是将所有数据放入 MySQL，然后运行上述查询的某个版本，以高效地一步完成外键转换。

如果你逐行处理 350,000 多条记录，那将是一个相当慢的过程，php 效率不是很高......

I would batch import the CSV file into MySQL as is (i.e. create a table that mirrors CSV field layout), and then run a query against the CSV import table to get the foreign key code.

Something like:

INSERT into sometable (code,other,fields)
SELECT code, other, fields
FROM codes c
JOIN csvtable ct
WHERE ct.feature = c.feature AND ct.type = c.type

Basic idea is to not perform processing on row-by-row basis in PHP or MySQL, but to get all the data into MySQL and then run some version of above query to efficiently do the foreign key translation in 1-step.

If you go row-by-row with 350,000+ records, that will be a fairly slow process, php is not terribly efficient...

回复收藏 0 原文

~没有更多了~