使用选择查询加载数据 infile 以进行数据查找 [英] load data infile with select query for data lookup

问题描述

我能够使用以下创建和加载数据 infile 命令成功加载文件:

I was able to load in a file successfully using the following create and load data infile commands:

--Create and load addresses
CREATE TABLE `addresses` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `address1` varchar(100) NOT NULL,
  `address2` varchar(100) DEFAULT NULL,
  `city` varchar(100) NOT NULL,
  `stateCode` varchar(2) NOT NULL,
  `zipCode` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=64 DEFAULT CHARSET=utf8;

LOAD DATA INFILE 'C:/temp/address.dat'    
    INTO TABLE mbc.addresses
    FIELDS
        TERMINATED BY X'1F'
    LINES
        TERMINATED BY X'1E'
    (name,address1,address2,city,stateCode,zipCode);

现在，我想使用加载到地址中的地址 ID 将数据从另一个文件 (receipt.dat) 加载到收据表中.目前，地址名称存储在receipt.dat 的addressId"字段中.我想做这样的事情，但不确定正确的语法是什么:

Now, I would like to load data from another file (receipt.dat) into a receipts table using the address id that was loaded into addresses. Currently, the address name is stored in the "addressId" field in receipt.dat. I'm looking to do something like this but not sure what the right syntax is:

--Create and load receipts
CREATE TABLE `receipts` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `receiptDate` date NOT NULL,
  `addressId` bigint(20) NOT NULL,
  `amount` decimal(10,0) NOT NULL,
  `notes` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `addressFK_idx` (`addressId`),
  CONSTRAINT `addressFK` FOREIGN KEY (`addressId`) REFERENCES `addresses` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOAD DATA INFILE 'C:/temp/receipt.dat'    
    INTO TABLE mbc.receipts
    FIELDS
        TERMINATED BY X'1F'
    LINES
        TERMINATED BY X'1E'
    (receiptDate, 
    select max(addressId) from mbc.addresses where name = @addressId,  
    amount, 
    notes);

格式化上面的 select max(addressId... 行(如果有办法)的正确方法是什么?

What is the proper way to format the select max(addressId... line above (if there is a way)?

推荐答案

这就是最终对我有用的东西...希望这对其他人有帮助

This is what ended up working for me...hope this helps someone else

--Create and load receipts (uses data from the table create/load scripts above)
CREATE TABLE `receipts` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `receiptDate` date NOT NULL,
  `addressId` bigint(20) NOT NULL,
  `designationId` bigint(20) NOT NULL,
  `amount` decimal(10,0) NOT NULL,
  `notes` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOAD DATA INFILE 'C:/temp/receipt.dat'    
    INTO TABLE mbc.receipts
    FIELDS
        TERMINATED BY X'1F'
    LINES
        TERMINATED BY X'1E'
    (@var1,@var2,@var3,amount,notes)
    SET receiptDate = STR_TO_DATE(@var1,'%m/%d/%Y'),
    addressId = (select max(id) from addresses where name = @var2),
    designationId = (select max(id) from designations where name = @var3);

这篇关于使用选择查询加载数据 infile 以进行数据查找的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！