LOAD DATA INFILE父子表与外键的关系 [英] LOAD DATA INFILE parent child table and foreign keys relationship
问题描述
所以我有2张桌子.路径表具有与媒体表"id"连接的外键"media_id".每个媒体可以有多个路径.这一切都很好.
当我尝试使用csv导出和导入这些表时出现问题.我可以很好地导出它们，但是当我去导入它们(第一个媒体表)时，媒体表中的主键'id'设置为自动递增，并且当使用LOAD DATA INFILE导入媒体表时，它将生成新的'id'.单独运行，因此我将失去与路径表的任何连接.
$sql = "CREATE TABLE $media_table (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) DEFAULT NULL,
`description` varchar(2000) DEFAULT NULL,
PRIMARY KEY (`id`),
) $charset_collate;";
$sql = "CREATE TABLE $path_table (
`id` int(11) NOT NULL AUTO_INCREMENT,
`path` varchar(500) DEFAULT NULL,
`def` varchar(50) DEFAULT NULL,
`media_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
INDEX `media_id` (`media_id`),
CONSTRAINT `mvp_path_ibfk_1` FOREIGN KEY (`media_id`) REFERENCES {$media_table} (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) $charset_collate;";
这是查询:
$query = "LOAD DATA INFILE '$csv' INTO TABLE {$table}
FIELDS OPTIONALLY ENCLOSED BY '^'
TERMINATED BY '|'
ESCAPED BY ''
LINES TERMINATED BY '\n'
IGNORE 1 LINES";
我的数据库设计有问题吗?我该如何改善它，或者解决这个问题?
我通常不运行这种类型的操作，因此这可能是伪代码，需要进行调整，但是我认为您可以这样做:
https://stackoverflow.com/a/2710714
CREATE TEMPORARY TABLE
IF NOT EXISTS temp_{$table_name_with_datetime}
AS (SELECT * FROM {$table_name});
# Not sure if this will work, it would need some way
# for the CREATE to be gotten.
SELECT (SHOW CREATE TABLE temp_{$table_name_with_datetime})
INTO OUTFILE '{$table_name_with_datetime}_create.sql'
FIELDS TERMINATED BY ''
OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY '\n'
FROM temp_{$table_name_with_datetime};
SELECT * INTO OUTFILE '{$table_name_with_datetime}.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM temp_{$table_name_with_datetime};
然后使用PHP，例如，拉出这些文件并加载它们:
// This creates the table.
$dbo->query(file_get_contents("{$table_from_filename}_create.sql"));
$dbo->query("
LOAD DATA INFILE '{$table_from_filename}.csv'
INTO TABLE temp_{$table_from_filename}
FIELDS OPTIONALLY ENCLOSED BY '"'
TERMINATED BY '\n'
ESCAPED BY ''
LINES TERMINATED BY '\n'
");
如果$table_from_filename是media_201809041045，那么现在您已将其导入为temp_media_201809041045，所以
$tablename = "temp_$table_from_filename";
现在，有了五个表，事情可能会变得复杂，例如，您必须保持优先级顺序(父级先于子级)，除非有一种方法可以禁用它，直到您导入每个表为止.
如果您可以将它们作为(临时)表进入数据库，那么现在您可以通过查询每个父行，在原始表上进行插入，然后使用last_insert_id来创建它们.以后的子行的交换索引.如果外键始终遵循相同的模式，例如media和media_id，那么您可以轻松地创建一个函数来处理该问题，只需使用要复制的五个表名即可.
另一件事，在CREATE调用中，您可能需要从呼叫中删除TEMPORARY并保留表，如果您需要的不仅仅是调用并获取一个或一系列UNION'd DROP TABLE，只需确保它是实际的临时表即可.
So I have 2 tables. Path table has foreign key 'media_id' connected with media table 'id'. Each media can have several paths. This all works well.
The problem comes when I try to export and import these tables with csv. I can export them ok, but when I go to import them (first media table) primary key 'id' in media table is set to auto increment, and when media table is being imported with LOAD DATA INFILE it will generate new 'id' on its own, so I will loose any connection to path table.
$sql = "CREATE TABLE $media_table (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) DEFAULT NULL,
`description` varchar(2000) DEFAULT NULL,
PRIMARY KEY (`id`),
) $charset_collate;";
$sql = "CREATE TABLE $path_table (
`id` int(11) NOT NULL AUTO_INCREMENT,
`path` varchar(500) DEFAULT NULL,
`def` varchar(50) DEFAULT NULL,
`media_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
INDEX `media_id` (`media_id`),
CONSTRAINT `mvp_path_ibfk_1` FOREIGN KEY (`media_id`) REFERENCES {$media_table} (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) $charset_collate;";
Here is the query:
$query = "LOAD DATA INFILE '$csv' INTO TABLE {$table}
FIELDS OPTIONALLY ENCLOSED BY '^'
TERMINATED BY '|'
ESCAPED BY ''
LINES TERMINATED BY '\n'
IGNORE 1 LINES";
Is there something wrong with my database design and how could I improve this, or get around this problem?
I don't normally run this type of operation, so this may be pseudo-code and need tweaking, but I would think you could do this:
https://stackoverflow.com/a/2710714
CREATE TEMPORARY TABLE
IF NOT EXISTS temp_{$table_name_with_datetime}
AS (SELECT * FROM {$table_name});
# Not sure if this will work, it would need some way
# for the CREATE to be gotten.
SELECT (SHOW CREATE TABLE temp_{$table_name_with_datetime})
INTO OUTFILE '{$table_name_with_datetime}_create.sql'
FIELDS TERMINATED BY ''
OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY '\n'
FROM temp_{$table_name_with_datetime};
SELECT * INTO OUTFILE '{$table_name_with_datetime}.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM temp_{$table_name_with_datetime};
Then with PHP, say, you pull those files and load them:
// This creates the table.
$dbo->query(file_get_contents("{$table_from_filename}_create.sql"));
$dbo->query("
LOAD DATA INFILE '{$table_from_filename}.csv'
INTO TABLE temp_{$table_from_filename}
FIELDS OPTIONALLY ENCLOSED BY '"'
TERMINATED BY '\n'
ESCAPED BY ''
LINES TERMINATED BY '\n'
");
If $table_from_filename were media_201809041045, now you've imported it as temp_media_201809041045, so
$tablename = "temp_$table_from_filename";
Now, with five tables things can get complex, for instance you have to maintain the order of precedence (parent before children), unless there's a way to disable it until you're through importing each table.
If you can get them into the database as a (temp) table, now you can work on them for instance by querying for each parent row, doing the insert on the origin table, then with last_insert_id, you can create a swap index for later child rows. If foreign keys always follow the same pattern, e.g. media and media_id, then you could fairly easily make a function to handle that just with the table name's of the five you're copying.
Another thing, on the CREATE call, you may want to remove TEMPORARY from the call and keep the table around if you need more than to call and get one or a series of UNION'd SELECTs. You can end in a query, but with a temp table, it's gone by the next query. Then at the end, run a DROP TABLE for the now "temporary" regular table, just make sure it's the actual temporary one.
这篇关于LOAD DATA INFILE父子表与外键的关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
