mysql 随笔

 表联合update

UPDATE jieguo1 a
LEFT JOIN jieguo1_ck1 b ON 1 = 1
SET a.`买家实际支付金额` = b.`买家实际支付金额`
WHERE a.`订单编号` = b.`订单编号`;

修改表字段类型

ALTER TABLE `rock_test`.`xg_child`
MODIFY COLUMN `总金额` float(2, 0) NULL DEFAULT NULL AFTER `订单修改时间`,
MODIFY COLUMN `应付金额` float(2, 0) NULL DEFAULT NULL AFTER `总金额`；

修改表字段信息

https://www.cnblogs.com/zsg88/p/7818684.html

增加字段设置默认值

alter table xg_master_04 add realPointFee int(10) default 0;

 表的联合查询

create table xg_master_03 as  select d.* ,case c.statu when  0 then "交易自动关闭" else "交易成功" end statu from ( select b.`订单编号` ,sum(b.statu) statu from (select a.`订单编号`, case  a.`订单状态` when "交易自动关闭" then 0 else 1  end statu  from xg_trade a ) b group by b.`订单编号`) c left join xg_master d on c.`订单编号` = d.`订单编号`;

Mysql查询报错

Error Code: 1267. Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' 0.001 sec

修改表编码格式

参考：http://www.cppblog.com/zhangyq/archive/2018/04/21/215575.html

ALTER TABLE xg_phone CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;