mysql - 根据特定条件将数据从一张表复制到另一张表

Question

我想将数据从一个表复制到另一个表，但仅限于在特定日期之后列值中已processed='1'的数据。

我有可以做到这一点的代码，但执行起来需要很长时间。

"INSERT INTO eamglo5_billingsystem.`consignment` ( 
`consignment_status`,
  `account`,
  `awb`,
  `hawb`,
  `service`,
  `handling`,
  `reference`,
  `date_submitted`,
  `date_imported`,
  `date_printed`,
  `printed_file_id`,
  `date_received`,
  `date_booked`,
  `booked_file_id`,
  `date_exported`,
  `export_file_id`,
  `company`,
  `contact`,
  `address_line_1`,
  `address_line_2`,
  `address_line_3`,
  `id`
)
     SELECT
        'Y',
       `account`,
      `awb`,
      `hawb`,
      `service`,
      `handling`,
      `reference`,
      `date_submitted`,
      `date_imported`,
      `date_printed`,
      `printed_file_id`,
      `date_received`,
      `date_booked`,
      `booked_file_id`,
      `date_exported`,
      `export_file_id`,
      `company`,
      `contact`,
      `address_line_1`,
      `address_line_2`,
      `address_line_3`,
      `id` 
      FROM  `eamglo5_singaporelive`.`consignment` 
      left join  (
        SELECT eamglo5_billingsystem.`consignment`.`id` as id1 
         FROM eamglo5_billingsystem.`consignment` 
      ) t  ON  `eamglo5_singaporelive`.`consignment`.id >id1
      WHERE `eamglo5_singaporelive`.`consignment`.`processed`=1 
       and `eamglo5_singaporelive`.`consignment`.date_booked>'2018-07-17'

预期:应将数据从 eamglo5_singaporelive.consignment 表复制到 eamglo5_billingsystem.consignment 表中，仅包含processed=1 值。

实际:花费无限的时间来执行和获取行。

Answer 1

您的 LEFT JOIN 与条件 consignment.id >id1 几乎创建了一个 catesian 产品。您可能想要的是仅插入源表中 id 比目标表中最高 id1 更高的行。您应该使用 SELECT MAX(id) 子查询来代替:

SELECT [..]
FROM  `eamglo5_singaporelive`.`consignment` 
WHERE `eamglo5_singaporelive`.`consignment`.`processed`=1 
  and `eamglo5_singaporelive`.`consignment`.date_booked>'2018-07-17'
  and `eamglo5_singaporelive`.`consignment`.id > (
     SELECT MAX(id1) FROM eamglo5_billingsystem.`consignment`
  )