mysql把一个表的字段赋值到另一张表，多表之间常用的操作

文章目录

mysql多表之间的常用操作
- 1. 根据主键，把一个表的字段赋值到另一张表
- 2. replace into：删除或插入
- 3. on duplicate key update：更新或插入

mysql多表之间的常用操作

1. 根据主键，把一个表的字段赋值到另一张表

user表

userId	username	password	sex	addr	phone
1	张珊	123	男	北京市	1562356586
2	李思	456	女	北京市	1562354256

score表

scoreId	userId	username	score	course	phone
1	1		80	语文
2	1		85	数学

需求：根据userId 把user表的 username 和phone字段填充到score表中

update score,user
set score.username = user.username , score.phone = user.phone
where score.userId = user.userId

或者

update score join user on score.userId=user.userId
set score.username = user.username , score.phone = user.phone

（生产案例）

update shopee_finances_escrow,ods_api_trade 
set shopee_finances_escrow.trade_status = ods_api_trade.trade_status
where shopee_finances_escrow.id = ods_api_trade.rec_id
and shopee_finances_escrow.shop_id = 28

2. replace into：删除或插入

把一张表的数据新增或更新到另一张表

replace是insert的增强版

replace into 首先尝试插入数据到表中，

如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。
否则，直接插入新数据

replace into的三种使用：点击查看详情！

replace into … values …
replace into … select (valueA, valueB, …) from table
replace into tbl_name set colA=valueA, …

replace into 在binlog的表现形式：注意如果有重复数据时，binlog会记录成update的形式

1、replace into tbl_name(col_name, …) values(…)
无重复数据时插入replace into t1 values(1,“liu”,28);

有重复数据时插入replace into t1 values(1,“yun”,29);

2.replace into tbl_name(col_name, …) select …
无重复数据时插入replace into t1 select * from t2

有重复数据时插入replace into t1 select * from t2

windows查看binlog步骤

查看binlog是否开启： show variables like 'log_bin';
列出binlog：show binary logs; show binlog events in ‘binlog.000018’（展示细节）;
binlog文件位于mysql的data目录下
binlog文件执行器位于mysql的bin目录下
在 bin目录下，执行 --no-defaults --base64-output=decode-rows -v ../Data/binlog.000018 > 把binlog打印到文件中
在文件中查看即可

replace into的应用注意事项：

插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。
replace操作在自增主键的情况下，遇到唯一键冲突时执行的是delete+insert,但是在记录binlog时,却记录成了update操作,update操作不会涉及到auto_increment的修改。备库应用了binlog之后，备库的表的auto_increment属性不变。如果主备库发生主从切换，备库变为原来的主库，写新的主库则有风险发生主键冲突点击查看id自增时，使用replace into备机可能存在的问题!
点击查看id自增时，使用replace into备机可能存在的问题!

示例：


    <insert id="replaceIntoOrderItemData">
        REPLACE INTO shopee_aggregation_order_items (
            id,
            platform_id,
            shop_id,
            tid,
            item_id,
            item_name,
            item_sku
        ) 
        SELECT
	        id,
	        platform_id,
	        shop_id,
	        tid,
	        item_id,
	        item_name,
	        item_sku
        FROM
            shopee_finances_escrow_items
        WHERE 1=1
        <if test="shopId != null and shopId != '' ">
            and shop_id = #{shopId}
        </if>
        <if test="tidList != null and  > 0">
            and tid in
            <foreach collection="tidList" index="index" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>

	</insert>

3. on duplicate key update：更新或插入

这条语句的规则如下：

如果你插入的记录导致一个UNIQUE索引或者primary key(主键)出现重复，那么就会认为该条记录存在，则仅执行update语句，反之，执行insert语句。 on duplicate key update与replace into的不同点在于：

当传入字段包含了唯一索引或者主键id，replace into 是先删除在插入，create_time与update_time会更新成当前时间
当传入字段包含了唯一索引或者主键id ，on duplicate key update 是直接更新，create_time不变，update_time会更新成当前时间
当 传入字段VALUES(value1, value2, value3, ...) 中对应字段的值和数据库一摸一样，就不会触发更新和插入，影响行数为 0 ， create_time 和 update_time 都不会发生变更！

语法如下：

INSERT INTO 
	tablename(field1,field2, field3, ...) 
	VALUES(value1, value2, value3, ...) 
	ON DUPLICATE KEY UPDATE 
	field1=values(field1),field2=values(field2), field3=values(field3), ...;

真实使用案例


    <insert id="replaceActualIncome" parameterType="">
        insert into platform_actual_income (
                platform_id,
                shop_id,
                tid,
                consign_time,
                escrow_release_time,
                kj_type,
                shop_name,
                country_code,
                trade_status,
                actual_amount,
                need_check,
                currency_code)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.platformId},
            #{item.shopId},
            #{item.tid},
            #{item.consignTime},
            #{item.escrowReleaseTime},
            #{item.kjType},
            #{item.shopName},
            #{item.countryCode},
            #{item.tradeStatus},
            #{item.actualAmount},
            10,
            #{item.currencyCode})
        </foreach>
        ON DUPLICATE key update
        platform_id = values(platform_id),
        shop_id = values(shop_id),
        tid = values(tid),
        consign_time = values(consign_time),
        escrow_release_time = values(escrow_release_time),
        kj_type = values(kj_type),
        shop_name = values(shop_name),
        country_code = values(country_code),
        trade_status = values(trade_status),
        actual_amount = values(actual_amount),
        need_check = values(need_check),
        currency_code = values(currency_code)
    </insert>

秒客网