Question

I have several groups of CSV files that I need to take into a mysql database. The CSVs are as follows:

Products (it has a product_id) ShippingDetails (related to products csv file through product_id) Categories (related to products csv file through product_id)

我有3组这些档案。

Group1: Products1.csv, ShippingDetails1.csv, Categories1.csv Products2.csv, ShippingDetail2.csv, ... ...

每个组别的档案都以产品为回报。

问题是,我想将所有一组档案装入一个数据库,但产品补贴则从每组零开始。因此,我需要改变补贴,以便它成为所有产品的独特产品,不管它来自什么卷宗。此外,我还需要改变每个集团的关系(外国钥匙),以便在把所有数据单上表时,使之与新的独特优势相匹配。在实际情景中,我有一组与产品——每组产品——相关的表格。因此,我很想知道,我的SQL是否能够在路上确定关系,或者是怎样才能做到这一点。

Answer 1

我不敢肯定我的SQL能为你们做,但可以用简单的文字做。例如,在PHP中,假设你在贵国数据库中拥有表格产品,而“货物”则空:

$group_count = 3;

mysql_connect(DB_HOST, DB_USER, DB_PASS);
mysql_select_db(DB_NAME);

for ($max_product_id = 0, $group = 1; $group <= $group_count; $group++)
{
    // create temporary tables
    mysql_query("CREATE TABLE products_tmp LIKE products");
    mysql_query("CREATE TABLE shipping_details_tmp LIKE shipping_details");

    // load data from csv files
    mysql_query("LOAD DATA INFILE  Products$group.csv  INTO TABLE products_tmp");
    mysql_query("LOAD DATA INFILE  ShippingDetails$group.csv  INTO TABLE shipping_details_tmp");

    // update unique ids
    mysql_query("UPDATE products_tmp SET product_id = product_id + $max_product_id");
    mysql_query("UPDATE shipping_details_tmp SET product_id = product_id + $max_product_id");

    // insert into final tables
    mysql_query("INSERT INTO products SELECT * FROM products_tmp");
    mysql_query("INSERT INTO shipping_details SELECT * FROM shipping_details_tmp");

    // drop temporary tables
    mysql_query("DROP TABLE products_tmp");
    mysql_query("DROP TABLE shipping_details_tmp");

    // get max product_id to update next group of data
    list($max_product_id) = mysql_fetch_row(mysql_query("SELECT MAX(product_id)+1 FROM products"));
}

The logic of this code is to insert the first group of data into the tables, get the max product_id + 1, and before inserting the next group of data you add that number to every product_id. That way product_id will always be unique and relationships will be kept. In the example code I just used 2 tables but you can extend this to how many tables you need.

<>说明:

When loading data from csv files (with LOAD DATA INFILE...) you should give an absolute path to the file. I didn t do that to keep the code cleaner.
I used old mysql functions because it s simple to write, but for production code it s better to use something like PDO.

友情链接