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

文章目录 mysql多表之间的常用 *** 作1. 根据主键，把一个表的字段赋值到另一张表2. replace into：把一张表的数据新增或更新到另一张表

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 tablereplace 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目录下，执行mysqlbinlog.exe --no-defaults --base64-output=decode-rows -v ../Data/binlog.000018 > test.txt 把binlog打印到 test.txt文件中

在test.txt文件中查看即可

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 tidList.size > 0">
            and tid in
            <foreach collection="tidList" index="index" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>

	</insert>