[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;