Mysql 查询B表的数据新增到A表（已测试过）

亦凉 2022-11-21 01:08 158阅读 0赞

*  ### **把B表的数据查询出新增到A表去** ###

INSERT INTO 
        a(id,c1)
    SELECT 
        b.id AS id,
        b.colum1 AS c1 
    FROM 
        table b;
    --以上SQL需要一次选中，全部执行

*  **测试如下：**
 *  **表b  reply\_card**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTcwNjU4_size_16_color_FFFFFF_t_70][]

*  **表a  ref**

![20201029110703453.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTcwNjU4_size_16_color_FFFFFF_t_70 1][]

*  **执行成功后**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTcwNjU4_size_16_color_FFFFFF_t_70 2][]

*  ### **B表的字段满足不了A的情况下** ###

> **now()是对应的`create_time`**

INSERT INTO a
    (
        id,
        c1,
        creat_time
    )
    SELECT 
        b.id AS id,
        b.colum1 AS c1,
        now() 
    FROM 
        table b;
    
    --以上SQL需要一次选中，全部执行

*  **执行成功后**

![20201029111420698.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTcwNjU4_size_16_color_FFFFFF_t_70 3][]

*  ** 清空表数据**

TRUNCATE TABLE a;
    --以上SQL需要一次选中，全部执行

DELETE FROM a 
    WHERE 1=1;
    --以上SQL需要一次选中，全部执行

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTcwNjU4_size_16_color_FFFFFF_t_70]: /images/20221120/92be4316b57f4d4caca56e4b74751762.png
[20201029110703453.png]: https://img-blog.csdnimg.cn/20201029110703453.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTcwNjU4_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20201029110632645.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTcwNjU4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTcwNjU4_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20201029110729388.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTcwNjU4,size_16,color_FFFFFF,t_70
[20201029111420698.png]: https://img-blog.csdnimg.cn/20201029111420698.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTcwNjU4_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20201029111441996.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTcwNjU4,size_16,color_FFFFFF,t_70