[Mysql舉例]-- mysql向A表插入B表新增資料(即找出2表不同的資料)

一、建立測試表結構

t_basicinfo表結構一:

CREATE TABLE `t_basicinfo` (
  `id` varchar(36) NOT NULL COMMENT '唯一ID',
  `name` varchar(45) DEFAULT NULL COMMENT '名稱',
  `type` varchar(30) DEFAULT NULL COMMENT '類型',
  `code` varchar(30) DEFAULT NULL COMMENT '代碼',
  `company_id` varchar(36) DEFAULT NULL COMMENT '公司ID',
  `timestamp` varchar(20) DEFAULT NULL COMMENT '時間戳',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='基礎表'      

station表結構二：

CREATE TABLE `station` (
  `id` varchar(36) COLLATE utf8_bin NOT NULL,
  `name` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `code` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `company_id` varchar(36) COLLATE utf8_bin DEFAULT NULL,
  `factoryCode` varchar(8) COLLATE utf8_bin DEFAULT NULL,
  `bigDataCode` varchar(12) COLLATE utf8_bin DEFAULT NULL COMMENT '大資料要的code',
  PRIMARY KEY (`id`)
) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='基礎表2';      

二、編寫實作的sql語句

SELECT
  tf.*
FROM
  station tf
WHERE
  NOT EXISTS (
    SELECT
      *
    FROM
      station st
    INNER JOIN t_basicinfo tb ON st.id = tb.id
    WHERE
      tf.id = tb.id
  );
  
  
-- 如果需要找出相同部分，隻需更改多行比對關鍵字：NOT EXISTS------》EXISTS      

三、應用：主要是用于向A表插入B表新增的資料

-- 向t_basicinfo表中插入station表的新增資料
INSERT INTO t_basicinfo SELECT
  sa.id,
  sa. NAME,
  "JQZ",
  sa.bigDataCode,
  sa.company_id,
  DATE_FORMAT(now(), '%Y%m%d%H%i%s') AS TIMESTAMP
FROM
  (
    SELECT
      tf.*
    FROM
      station tf
    WHERE
      NOT EXISTS (
        SELECT
          *
        FROM
          station st
        INNER JOIN t_basicinfo tb ON st.id = tb.id
        WHERE
          tf.id = tb.id
      )
  ) sa;